Методы и свойства некоторых объектов 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):

Меню

Панель инструментов

Возврат в рабочую книгу нажатием кнопки MSExcel

 

Окно редактирования кода

Окно свойств

Окно проекта

Окно редактора состоит из следующих компонентов:

 

 

Рисунок 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

Пример 1. Рассмотрим таблицу, показанную на рисунке 9. В ней необходи­мо рассчитать индекс массы тела (ИМТ) каждого пациента, рассчитать среднее значения индекса по всем пациентам и выставить оценку индекса. Записывался макрос с именем Макрос3.

 

Рисунок 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)