Методы и свойства некоторых объектов VBA
Sheets(“Исследования”).Select – выбрать лист Исследования
Sheets(“Исследования”).Range(“B4:B10”).Select – выделить диапазон B4:B10 на листе Исследования
События
Событие представляет собой действие, распознаваемое объектом (например, щелчок мышью или нажатие клавиши), для которого можно запрограммировать отклик, т.е. реакцию объекта на произошедшее событие – выполнение программы. Такая программа называется процедурой обработки событий и имеет стандартное имя.
Элементы языка VBA
Объекты
Константы– числовые (целое, вещественное число) и символьные (заключаются в “”)
Переменные – данные, которые меняются в ходе выполнения программы, задается имя и тип. В имени нельзя использовать “.”, “ “, “!”, “%”, “&”, “$”, “#”, “@”. Длина имени не должна превышать 255 символов.
Основные типы переменных, их размеры и диапазоны принимаемых значений приведены в таблице 1.
Таблица 1. Типы переменных
| Тип данных | Размер (байт ) | Диапазон значений |
| Byte (байт) | от 0 до 255 | |
| Boolean (логический) | True или False | |
| Integer (целое число) | от –32768 до 32767 | |
| Long (длинное целое) | от –2147483648 до 2147483647 | |
| Single (число с плавающей запятой обычной точности) | для отрицательных значений от -3,4E38 до -1,4Е-45 для положительных значений от 1,4-45 до 3,4E38 | |
| Double (число с плавающей запятой двойной точности) | для отрицательных значений от -1,79E308 до -4,94Е-342 для положительных значений от 4,94-324 до 1,79E308 | |
| Currency (денежный) | числа с четырьмя десятичными знаками от –922 337 203 685 477.5808 до 922 337 203 685 477,5807 | |
| Date (дата и время) | от 1 января 100 до 31 декабря 9999 г. | |
| String(строковый переменной длины) | 10+длина строки | от 0 до 231 |
| String (строковый постоянной длины) | задается при выполнении оператора Dim | от 1 до 216 |
| Variant | для чисел – 16 | значения соответствуют типу данных Boolean, Byte, Integer, Long, Currency, Single, Double или Date, определяемому автоматически |
| для строк – 22+длина строки | от 0 до 231 | |
| Object | используется при объявлении объектов, аналогиченVariant |
В таблице 2 приведен пример некоторых функций.
Объект Application позволяет вызвать функции при помощи конструкций вида:
Application.Функция(действия над объектами)
Примеры:
Application.Sum(Sheets(“Исследования”).Range(“B2:B15”)) – суммируются значения из ячеек диапазона B2:B15, расположенного на листе «Исследования».
Range("F2:F5") = Rnd(5) – в диапазон от F2 до F5 записывается случайное число от 0 до 5.
Sheets(“Числа”).Cells(5,1) = Int (Rnd() * 100) – 50.
Таблица 2. Функции
| Функция | Выражение |
| Математические функции | |
| Abc(x) | Модуль (абсолютная величина числа) |
| Cos(x) | Косинус |
| Exp(x) | Экспонента, т.е. результата возведения основания натурального логарифма в указанную степень |
| Log(x) | Натуральный логарифм |
| Sin(x) | Синус |
| Sqr(x) | Квадратный корень |
| Rnd() Rnd(x) | генерирует случайное число от 0 до 1 генерирует случайное число от 0 до х |
| Int() | округляет до целого числа |
| Функции времени и даты | |
| Date | Текущая дата |
| Now | Текущая дата и время |
| Day | День месяца |
| DateDiff | Разность двух дат |
| WeekDayName | Название дня недели |
Структура редактора VBA
Редактор активизируется вкладкой «Разработчик»/»Код»/»VisualBasic» (рисунок 8):
| Меню |
| Панель инструментов |
| Окно редактирования кода |
| Окно свойств |
| Окно проекта |
Рисунок 8.Структура редактора VisualBasic
Процедуры и модули для кода VBA
Программа– последовательность операторов, включая оператор комментариев (для этого вводится (‘)или слово Rem вместо апострофа, комментируется текст до конца строки).
Модуль –совокупность объявлений (описательная часть) и процедур, хранящихся как единое целое
Процедура – совокупность кода VBA, рассматриваемая как единое целое, имеет свое уникальное имя. Выполнение процедуры является реакцией на какое-либо событие, т.е. процедура обрабатывает событие, ограничивается ключевыми словами Sub ….. EndSub.
Описание данных начинается с оператора Dim. Тип переменных лучше указывать, так как от типа данных зависит время выполнения процедур и ресурсы памяти.
Пример:
Dim A As Integer, B As Byte, C As String
Для объявления констант и их значений используется оператор Const.
Пример:
ConstРост_девочкиAs Byte = 11
Управляющие структуры VBA
Управляющие структуры определяют последовательность выполнения программы.
UУсловные операторы
1)IfусловиеThenвыражение – для одной строки
IfусловиеThenвыражение1, выражение2 EndIf
– в случае истинности условия выполняется последовательность операторов
IfусловиеThenвыражение1, …Else выражение2, … EndIf
– в зависимости от условия выполняются разные блоки операторов
IfусловиеThenвыражение1, …ElseIfусловие Thenвыражение2, … Elseвыражение3, … EndIf–определяет вложенность конструкций
2) SelectCaseпроверяемое выражение
Caseсписок выражений1
блок операторов1
Caseсписок выражений2
блок операторов2
Caseсписок выражений3
блок операторов3
….
CaseElse{не обязательный блок}
EndSelect
Каждый список выражений может содержать одно или более значений, которые отделяются запятыми.
UОператоры цикла
}фиксированное число повторений
1) Forпеременная = M1 ToM2 [StepM3]
операторы
Next
Если шаг не указан, то он полагается равным 1.
} переменное число повторений
2) DoWhileусловие
операторы
Loop
Цикл с предусловием. Все операторы выполняются до тех пор, пока условие будет истинным.
3) Do
…
LoopUntil<условие>
Цикл с постусловием. Проверяется условие перед началом цикла и цикл выполняется до тех пор, пока оно False. Как только условие цикла станет равно True, выполнение цикла прекратится.
| Макрос3() Range("D2").Select ActiveCell.FormulaR1C1 = "=(RC[-1]*10000)/RC[-2]^2" Selection.AutoFill Destination:=Range("D2:D5"), Type:=xlFillDefault Range("D2:D5").Select Range("D6").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)" Range("E2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-1]<18,""low"",IF(AND(RC[-1]>18,RC[-1]<24),""middle"",""heigh""))" Selection.AutoFill Destination:=Range("E2:E5"), Type:=xlFillDefault Range("E2:E5").Select End Sub |
Рисунок 9. Таблица с расчетом ИМТ
Рассмотрим листинг макроса.
Первый исполняемый оператор программы Range(“D2”).Selectсоздан системой в виде выражения, которое содержит в терминологии VBA свойство Range в сочетании с методом Select. Обратите внимание на то, что свойство имеет записанный в круглых скобках аргумент в виде строки символов и отделяется от метода точкой. В нашем примере аргумент свойства представляет собой ссылку на ячейку в стиле A1, с которой началось программирование макроса.
Фактически анализируемая строка программы представляет собой набор действий по активизации ячейки C3 рабочего листа Excel. Система всегда одинаково интерпретирует действия пользователя Excel, поэтому в случае затруднений с анализом результатов ее работы удобно создать новый дополнительный макрос как результат конкретного короткого действия и изучить его содержимое. Наконец, в особо сложных случаях можно скопировать текст созданного макроса, изменить его имя и запустить его из Excel для того, чтобы увидеть результат действий интересующего вас оператора.
Отдельно остановимся на заливке ячеек, диапазона ячеек.
Цвет фона ячейки в MicrosoftOfficeExcel определяется свойством ColorIndex объекта Interior этой ячейки.
Так, заливка ячейки осуществляется командой:
Cells(4,5).interior.colorindex = 5, где 5 – синийцветили
Заливка некоторого диапазона –
Range(“A1:D1”).interior.colorindex = 3, где 3 – красныйцвет
Установка цвета текста:
Cells(4,5).Font.Color = vbBlue
Range(“A1:D1”).Font.Color =vbRed
Числовой индексцветов и константы RGBпредставлены в таблице 3.
Таблица 3. Числовой индекс цвета и константы RGB
| код | цвет | константы RGB | цвет | |
| черный | vbRed | красный | ||
| белый | vbBlack | черный | ||
| красный | vbGreen | зеленый | ||
| зеленый | vbYellow | желтый | ||
| желтый | vbBlue | синий | ||
| малиновый | vbMagenta | фиолетовый | ||
| 33–42 | пастельные тона | vbCyan | бирюзовый | |
| 9–14, 18, 21, 25, 49, 51-56 | темные тона | |||
Если мало стандартных цветов (56 цветов) то используют RGB палитру, например
Cells(3,4).Interior.Color = RGB(160,255,30)