Основы офисного программирования в MS EXCEL VBA

Лекция №2. ОСНОВЫ ОФИСНОГО ПРОГРАММИРОВАНИЯ В MS EXCEL VBA Visual Basic for Application (VBA) - это один из самых доступных языков программирования, который представляет собой упрощенную реализацию языка программирования Visual Basic. Он интегрирован в линейку продуктов Microsoft Office, а также во многие другие программные пакеты, такие как AutoCAD, CorelDRAW и т.д. VBA позволяет автоматизировать процессы выполнения различных операций и пользовательских действий, конструировать профессиональные приложения, решающие сложные практические задачи. Использование VBA совместно с возможностями электронных таблиц MS Excel особенно эффективно, так как позволяет совмещать возможности электронных таблиц с помощью языка программирования VBA. Таким образом, изучение основ использования VBA рекомендуется всем студентам и специалистам инженерных профилей. 2.1 Правило записи в редакторе Visual Basic Запуск редактора Visual Basic в MS Excel осуществляется или с помощью комбинации клавиш Alt+F11, или путем нажатия на кнопку Visual Basic на панели Разработчик (рис.1). Рис.1 – Кнопка запуска редактора Visual Basic в MS Excel Типы данных VBA Данные в Visual Basic могут иметь различные типы. Каждый тип данных характеризуется диапазоном допустимых значений и количеством байтов, требуемых для хранения значений. В следующей таблице перечислены поддерживаемые Visual Basic типы данных, размеры и диапазоны. Таблица – Типы данных Visual Basic Тип данных Размер Диапазон значений Byte (байт) 1 байт От 0 до 255 Boolean (логический) 2 байта True или False Integer (целое) 2 байта От -32768 до 32767 Long (длинное целое) 4 байта От -2147483648 до 2147483647 Single (с плавающей точкой обычной точности) 4 байта От -3,4x1038 до -1,4x10-45 и от 1,4x10-45 до 3,4x1038 Double (с плавающей точкой двойной точности) 8 байт От -1,79x10308 до -4,94x10-324 и от 4,94x10-324 до 1,79x10308 Currency (денежный) 8 байт От -922337203685477,5808 до 922337203685477,5807 Date (даты и время) 8 байт Дата – от 1 января 100 г. до 31 декабря 9999 г., время – от 0:00:00 до 23:59:59 String (строка переменной длины) 10 байт + длина строки От 0 до приблизительно 2109 Переменные Переменная – это именованная область памяти, в которой могут храниться различные данные, которые можно изменять во время выполнения программы. Для работы с данными в Visual Basic используются переменные. Каждая переменная должна иметь имя, которое начинающееся с буквы и содержащее не более 255 символов, и объявленный тип данных: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, Object, Variant. Точки или символы описания типа в имени не допустимы. Указание типа данных для переменной необязательно. Инструкции объявления переменных Для объявления переменных применяются следующие инструкции: Dim, Private, Public, Static, Option Explicit. Рассмотрим пример объявления переменных: Пример. Dim x1 As Byte Dim x2 As Integer Dim x3 As Long , x4 As Single , x5 As Double , x6 As Date Вместо одного оператора Dim можно использовать два и более. Допускается описание нескольких переменных в одной строке. Для задания типа данных надо указать определенный тип для каждой переменной. Если в описании не указан тип переменной, для нее устанавливается тип Variant. Арифметические выражения Арифметические выражения образуются из констант, переменных, функций и выражений, заключенных в круглые скобки, которые соединены знаками арифметических операций. Стандартные математические функции Visual Basic приведены в таблице 1. Таблица 1.Стандартные математические функции Visual Basic Математическая запись Запись на Visual Basic SQR(X) ABS(X) LOG(X) EXP(X) COS(X) SIN(X) TAN(X) ATN(X) Примеры арифметических выражений X^Y X^(Y/Z) COS(X)^2 В Visual Basic существуют следующие виды арифметических операций: ^ – возведение в степень; - и + – присвоение знака числу (унарные операции); * – умножение; / – деление; \ – целочисленное деление; mod – взятие остатка от деления; + – сложение, - – вычитание. При составлении арифметических выражений необходимо учитывать приоритет выполнения арифметических операций. Наивысший приоритет имеют стандартные математические функции, затем – возведение в степень. После возведения в степень идет присвоение знака числу. Умножение и деление осуществляется после присвоения знака числу. Более низкий приоритет, за умножением и делением, имеют целочисленное деление и взятие остатка от числа. Самый низкий приоритет у вычитания и сложения. Операции в арифметических выражениях выполняются последовательно: слева на право в порядке их приоритета. В ряде случаев, приоритет может быть изменен при помощи скобок. При написании арифметических выражений, как правило, числитель и знаменатель берутся в круглые скобки. Если знаменатель или числитель составляет только одна функция, то скобки можно не ставить. Также скобки не ставятся, когда в числителе присутствуют только операции умножения или деления, что укладывается в естественный ход вычисления выражения: последовательно слева на право. Например, при вычислении выражения , скобки в числителе не ставятся, но обязательны в знаменателе: F=7*x^3*a^2/(5*x^5). Если скобки в знаменателе не поставить, то компилятор Visual Basic разделит числитель на 5, а затем, результат умножит на x^5. В случае, когда арифметическое выражение не помещается в одной строке, то при переходе на следующую строчку следует поставить знак _ и продолжить запись выражения на следующей строке. Пример. Пусть требуется написать арифметическое выражение на языке программирования VBA: Арифметическое выражение будет иметь следующий вид: F=(X^(3/5)+COS(X)^2-EXP(-7*X+4))/(LOG(ABS(X-7))*X^2-4*SQR(ABS(X))) Более оптимальной для вычислений будет следующая запись: F=(X^(3/5)+COS(X)^2-EXP(-7*X+4))/(LOG(ABS(X-7))*X*X-4*SQR(ABS(X))) Дело в том, что при возведении X в квадрат, X*X будет работать быстрее, чем X^2. При однократном вычислении эта тонкость останется незаметной для пользователя, но может привести к некоторой задержке выполнения программного кода при многократном повторении в теле цикла. 2.2 Последовательность операторов (ЛИНЕЙНОСТЬ). Последовательность Последовательность операторов означает последовательное их исполнение друг за другом. В языке программирования последовательно выполняемые операторы отделяются друг от друга символом конца строки (каждый оператор начинается с новой строки) или двоеточием. Присваивание Обычный синтаксис оператора присваивания: <переменная> <знак присваивания> <выражение>, где <знак присваивания> имеет вид «=» в VBA). Выполняется присваивание так: вычисляется выражение в правой части этого оператора и полученное значение присваивается переменной левой части (переменная получает это значение, «стирая» предыдущее). Пример. Например, последовательность операторов присваивания (в языке VBA): a = 4 + 7 a = a + 2 b = 2 a = b*3 +a исполняется так: вычисляется 4+7, результат 11 присваивается переменной а. Прежнее значение а стирается, новое значение а есть 11. Во второй строке к этому значению прибавляется 2, результат выражения есть 13 (11+2=13). Это значение присваивается переменной а, теперь уже значение а есть 13. В третьей строке b получает значение 2. Наконец, в четвертой строке снова пересчитывается значение переменной а: вычисляется выражение b*3+а (вместо b и а подставляются их значения 13 и 2), результат 2*3 + 13 = 19 присваивается переменной а (говорят, 19 «заносится» в переменную а или «запоминается» в а). Результат выполнения всей последовательности: а=19, b=2. Присваивание можно трактовать как запоминание (сохранение) вычисляемых в ходе исполнения алгоритма значений для последующего их использования. Сохраняясь в переменной, значение приобретает имя, например, в предыдущем примере далее в алгоритме вместо числа 19 можно использовать его (временное) имя a, вместо числа 2 – имя b. Обратную операцию – извлечение значения из переменной называют разыменованием. На блок-схеме присваивание представляется прямоугольником со входом и выходом. Внутри прямоугольника записывается сам оператор. Например, а = b*3+а выглядит так: Вся последовательность предыдущего примера изображается так: 2.3. Условный оператор (ветвление) Эта алгоритмическая структура представляет разветвление алгоритма в зависимости от значения (истинности или ложности) некоторого условия. В общем виде конструкция выглядит так: <если> <условие> <то> <действия1> <иначе> <действия2>, и читается как «если условие истинно, то выполнить действия1, иначе (если условие ложно), выполнить действия2». Слова «если», «то», «иначе» в разных языках могут иметь разный синтаксис, но в большинстве языков это «if», «then», «else». В VBA синтаксис условного оператора: If <условие> Then <действия1> Else <действия2> End If Условный оператор может быть неполным, без ветки <иначе> <действия2>. Тогда, если условие ложно, управление передается следующему в общей последовательности оператору. На блок-схемах эти два случая изображаются так: Здесь знаки «+» и «-» обозначают «да» (условие выполняется) и «нет» (условие не выполняется). Например, вычисление y, заданного формулой: можно представить следующей блок-схемой: На языке программирования VBA эта конструкция выглядит так: If x<0 Then y=x*x Else y=x+1 End If Обратите внимание, что операторы после Then (ветка «+») и Else (ветка «-») начинаются с новой строки, а сам условный оператор заканчивается фразой End If – признаком конца конструкции ветвления. Пример использования неполного условного оператора: суммируются числа, вводимые с клавиатуры; если число отрицательное, оно прежде заменяется единицей. Пусть переменная а хранит значение введенного числа, а переменная S хранит сумму введенных чисел. Фрагмент блок-схемы решения такой задачи: На языке программирования VBA эта конструкция выглядит так: If a<0 Then a=1 End If S=S+a Каждая из ветвей условного оператора может содержать произвольное количество операторов, среди которых могут быть снова ветвления (вложенные «если»). Например, следующая программа: x=10 If x>5 Then x=x-5 If x>7 Then x=x+4 y=x Else x=x*x-1 y=x+8 End If Else x=x-4 y=1 End If выполняется так: переменная x получает в результате присваивания значение 10. Затем проверяется условие x>5? В данном случае оно выполняется, поэтому исполняются операторы после Then: вначале x получает новое значение 5, затем проверяется (вложенное) условие x>7? Оно ложно, тогда последовательно исполняются операторы, идущие после Else: x=x*x-1 и y=x+8, результат x=24, y=32. На этом внутренний «If» заканчивается, ветка «Else» внешнего «If» не исполняется, общий результат остается тем же: x=24, y=32. Если же изменить начальное присваивание, например, вместо x=10 записать x=2, то исполнение программы будет идти по другой ветке, соответствующей внешнему «Else»: x=x-4, y=1, и результат будет другой: x = -2, y = 1. Блок-схема для этой программы имеет вид: Какие операции можно включать в условие? Условие – это логическая формула, значение которой может быть вычислено. Простейшее условие – это отношение: больше (>), меньше (<), больше или равно (>=), меньше или равно (<=), не равно (<>). Более сложные условия составляются из простых с помощью операций конъюнкции (в VBA это And), дизъюнкции (Or), отрицания (Not), импликации (Imp). Например, условие х меньше пяти, но больше или равно двум, записывается как (x<5) And (x>=2).