VBA

VBA Основы VBA Комментарии и отст упы коде Комментарии не участвуют в процессе выполнения программы и не влияют на результат работы макроса. Каждая строка, начинающаяся апострофом (‘), будет считаться в VBA комментарием. Редактор VBA в Excel выделит такую строку зелёным цветом шрифта, чтобы с первого взгляда было понятно, что это комментарий, который не будет выполняться. 2 Переносы ст рок в VBA Ещё один способ сделать код более читаемым и облегчить работу с ним – делать переносы и разбивать одну длинную строку кода на несколько коротких. В VBA, чтобы разбить строку, нужно вставить символы « _» (пробел+подчёркивание) непосредственно перед переносом строки. Это сообщает компилятору VBA, что текущая строка кода продолжается на следующей строке. НЕПРАВИЛЬНЫЙ вариант Правильный вариант 3 Типы данны х 4 Тип данны х Разме р Описание Диапазон значений Byte 1 байт Положительные целые числа; часто используется для двоичных данных Boolean 2 байта Может принимать значения либо True, либо False True или False Integer 2 байта Целые числа (нет дробной части) от -32 768 до +32 767 Long 4 байта Большие целые числа (нет дробной части) от -2 147 483 648 до +2 147 483 647 Single 4 байта Число с плавающей точкой одинарной точности от -3.4e38 до +3.4e38 Double 8 байт Число с плавающей точкой двойной точности от -1.8e308 до +1.8e308 Currenc 8 байт Число с плавающей точкой, с от -922 337 203 685 477.5808 от 0 до 255 Типы данны х 5 Тип данны х Разме р Описание Диапазон значений Date 8 байт Дата и время – данные типа Date представлены числом с плавающей точкой. Целая часть этого числа выражает дату, а дробная часть – время от 1 Января 100 до 31 Декабря 9999 Object 4 байта Ссылка на объект Любая ссылка на объект String изменя ется Набор символов. Тип String может иметь фиксированную или изменяющуюся длину. Чаще используется с изменяющейся длиной Фиксированной длины – около 65 500 символов. Переменной длины – приблизительно до 2 млрд символов Variant изменя ется Произвольные данные. Этот тип используют в тех случаях, когда Число – Double, строка – String Объявление переменны х и конст ант Если объявляется переменная, без присваения ей значения, то она инициализируется значением по умолчанию: • текстовые строки – инициализируются пустыми строками; • числа – значением 0; • переменные типа Boolean – False; • даты – 30 декабря 1899. Прежде чем использовать переменную или константу, её нужно объявить. Для этого в макрос добавляют вот такую простую строку кода: Объявление констант: 6 Объявление переменны х и конст ант Объявлять переменные в Excel не обязательно. По умолчанию все введённые, но не объявленные переменные в Excel будут иметь тип Variant и смогут принять как числовое, так и текстовое значение. При этом рекомендовано всегда объявлять переменные т.к.: 1. Использование памяти и скорость вычислений. Variant - Этот тип данных использует больше памяти и обрабатывается гораздо медленнее, чем другие типы данных. 2. Профилактика опечаток в именах переменных. Если все переменные объявляются, то можно использовать оператор VBA – Option Explicit, чтобы выявить все не объявленные переменные 3. Выделение значений, не соответствующих объявленному типу переменной. Option Explicit Оператор Option Explicit заставляет объявлять все переменные, которые будут использованы в коде VBA, и при компиляции выделяет все не объявленные переменные как ошибки 7 Область действия переменны х и констант Объявлять переменные в Excel не обязательно. По умолчанию все введённые, но не объявленные переменные в Excel будут иметь тип Variant и смогут принять как числовое, так и текстовое значение. Если переменная sVAT_Rate объявлена в самом начале модуля, то областью действия этой переменной будет весь модуль Если переменная sVAT_Rate объявлена в начале функции Total_Cost, то её область действия будет ограничена только этой функцией 8 Область действия переменны х и констант При необходимости, чтобы объявленными переменными можно было пользоваться в других модулях. В таких случаях для объявления переменной вместо ключевого слова Dim нужно использовать ключевое слово Public. Для того, чтобы объявить переменную на уровне модуля, вместо ключевого слова Dim можно использовать ключевое слово Private, которое укажет на то, что данная переменная предназначена для использования только в текущем модуле. Для объявления констант также можно использовать ключевые слова Public и Private, но не вместо ключевого слова Const, а вместе с ним. 9 Массивы в VBA Массивы в Visual Basic for Application – это структуры, которые обычно хранят наборы взаимосвязанных переменных одного типа. Доступ к записям массива осуществляется по их числовому индексу. Преимущество хранения данных в массиве становится очевидным, когда возникает необходимость выполнить одно и то же действие над каждым элементом массива По умолчанию нумерация элементов массива начинается с 0, и в объявлении массива начальный индекс может быть не указан: 10 Многомерны е массивы в Excel VBA Предположим, что нужно сохранить ежедневные показатели продаж за январь для 5 разных команд. Для этого потребуется двумерный массив, состоящий из 5 наборов показателей для 31 дня. Объявим массив таким образом: Для того, чтобы получить доступ к элементам массива Jan_Sales_Figures, нужно использовать два индекса, указывающих день месяца и номер команды. Например, адрес элемента, содержащего показатели продаж для 2-ой команды за 15-ое января будет записан так: Таким же образом можно объявить массив с 3-мя и более измерениями – достаточно добавить дополнительные измерения в объявление массива и использовать дополнительные индексы для ссылки на элементы этого массива. 11 Динамические массивы Если размер массива заранее не известен, то можно использовать динамический массив – это такой массив, размер которого может быть установлен и изменён сколько угодно раз в процессе выполнения макроса. Далее нужно будет объявить измерение массива в процессе выполнения кода при помощи выражения ReDim: И если в процессе выполнения кода потребуется снова изменить размер массива, то можно использовать выражение ReDim снова: Ключевое слово Preserve сохраняет данные изменяемого массива. В противном случае данные будут потеряны 12 Процедуры « Function» и « Sub» в VBA В Excel Visual Basic набор команд, выполняющий определённую задачу, помещается в процедуру Function (Функция) или Sub (Подпрограмма). Главное отличие между процедурами Function и Sub состоит в том, что процедура Function возвращает результат, процедура Sub – нет. При помощи аргументов процедурам VBA могут быть переданы различные данные. Список аргументов указывается при объявлении процедуры. Процедуры VBA могут иметь необязательные аргументы. Это такие аргументы, которые пользователь может указать, если захочет, а если они пропущены, то процедура использует для них заданные по умолчанию значения. 13 Передача аргументов по значению и по ссы лке Аргументы в VBA могут быть переданы процедуре двумя способами: ByVal – передача аргумента по значению. Это значит, что процедуре передаётся только значение (то есть, копия аргумента), и, следовательно, любые изменения, сделанные с аргументом внутри процедуры, будут потеряны при выходе из неё. ByRef – передача аргумента по ссылке. То есть процедуре передаётся фактический адрес размещения аргумента в памяти. Любые изменения, сделанные с аргументом внутри процедуры, будут сохранены при выходе из процедуры. 14 По умолчаниюаргументы в VBA передаются по ссылке VBA процедура « Function» Пример кода процедуры Function, которая получает три аргумента типа Double (числа с плавающей точкой двойной точности). В результате процедура возвращает число типа Double, равное сумме первых двух аргументов минус третий аргумент: Вызов VBA процедуры «Function» из другой процедуры Вызов VBA процедуры «Function» из рабочего листа 15 =SumMinus(10, 5, 2) VBA процедура « Sub» Рассмотрим пример простой VBA процедуры Sub, задача которой – изменить форматирование выделенного диапазона ячеек. В ячейках устанавливается выравнивание по центру (и по вертикали, и по горизонтали) и размер шрифта изменяется на заданный пользователем: Вызов VBA процедуры «Sub» из другой процедуры Вызов VBA процедуры «Sub» из рабочего листа • Нажмите Alt+F8. • В появившемся списке макросов выберите тот, который хотите запустить. • Нажмите Выполнить (Run) 16