-
Объекты, методы, свойства, события
Объектам VBA присуща функциональность – они действуют определенным образом и могут откликаться на определенные ситуации. При этом, если свойства объекта определяют его внешний вид и состояние, методы объекта определяют те задачи, которые может выполнить данный объект. Методы, по сути дела, представляют собой сегмент программного кода, внедренного в объект.
Существует определенный формат программного кода, задающего установку свойства, получения значения свойства и использование метода:
Объект.Свойство = Значение
Переменная=Объект.Свойство
Объект.Метод [Параметр1 [… …]
Здесь Объект – имя настраиваемого объекта; Свойство – это характеристика, которую нужно изменить; Метод – это команда, которая используется для изменения объекта; Значение – это новая установка свойства; Параметр – это аргумент, используемый методом.
Пример 1. Следующий пример показывает, как можно изменить текст в строке заголовка окна приложения посредством установки нового значения свойству Caption объекта Application:
Application.Caption = “Пример установки свойства”
Различные типы объектов могут иметь различные методы.
Пример 2. Пример применения метода Show (показать) к объекту UserForm (Форма Пользователя) :
UserForm 1. Show
В результате применения этого метода Форма Пользователя будет выведена на активный рабочий лист текущей книги Excel.
Объекты могут реагировать на события – действия пользователя или другие внешние действия, например, щелчок по кнопке, изменение текста, нажатие клавиши и др. Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик.
Иногда свойства и методы объекта оказываются связанными в том смысле, что выполнение некоторого метода приводит к изменению свойств объекта. В свою очередь, изменение некоторых свойств может вызвать наступление событий. Очевидно, что применение метода Move (переместить), допустим, к тому же объекту UserForm изменит значения его свойств (Left и Top), определяющих его местоположение. С другой стороны, результатом принудительного изменения размеров объекта , очевидно, будет событие Resize.
Программа может обрабатывать два основных типа событий: инициируемые пользователем и генерируемые системой. События, инициируемые пользователем, возникают в результате его действий: нажатие клавиши, щелчки кнопками мыши. Но есть события, являющиеся следствием действий пользователя. Например, снятие или установка Флажка пользователем изменяет значение его свойства Value, а это означает наступление события Change.
Таким образом, любое из действий пользователя может вызвать целый набор событий, и порядок их вызова может быть важным. Основными действиями пользователя, генерирующими вызов событий в программе, являются следующие:
-
Запуск программы;
-
Нажатие клавиши;
-
Щелчок кнопкой мыши;
-
Перемещение мыши;
-
Выход из программы.
-
Вввод-вывод данных в vba
1. Функция InputBox
Функция InputBox имеет следующий синтаксис:
Переменная = InputBox(Приглашение[, Заголовок] [, по_умолчанию] [, Xпоз] [, Yпоз] [, файл_справки, содержание])
Эта функция требует обязательного задания только аргумента Приглашение Значение аргумента Приглашение – единственный обязательный аргумент этой функции, служит текстовая строка, которая отображается в диалогом окне ввода в качестве сообщения. Этот текст должен быть заключен в двойных кавычках.
Аргумент Заголовок используется для задания текста, который помещается в строке заголовка окна ввода. Если этот аргумент не задан, то в строке заголовка отображается Microsoft Excel.
Аргумент по_умолчанию задает значение, которое отображается по умолчанию в поле ввода, пока пользователь не введет свое значение. Если этот аргумент опустить, то поле ввода отображается пустым.
Необязательные аргументы Xпоз и Yпоз задают положение окна ввода на экране. Аргументы файл_справки и содержание используются в том случае, если вы создаете для своего приложения собственную систему справок.
Возвращаемым значением функции InputBox является значение, введенное пользователем в поле ввода.
2. Функция MsgBox
Окно сообщения создаётся функцией MsgBox, которая имеет следующий синтаксис:
Переменная = MsgBox(Приглашение[, Кнопки] [, Заголовок [, файл_справки, содержание])
Значение аргумента Приглашение – единственный обязательный аргумент этой функции, служит текстовая строка, которая отображается как сообщение в диалогом окне. Этот текст должен быть заключен в двойных кавычках. Отметим использование круглых скобок в синтаксисе MsgBox – они указывают на то, что в данном случае MsgBox является функцией, возвращающей какое-либо значение. Если скобки опущены, то для VBA это признак того, что данное выражение значение не возвращает. Если Вы хотите возвращать значение, надо использовать код, подобный следующему:
Dim i As Integer
i = MsgBox(“Сохранить изменения в ‘ “ & ThisWorkbook.Name & “ ‘& “, vbYesNoCancel + vbExclamation)
Результатом работы такого кода в VBA будет диалоговое окно с тремя кнопками «Да», «Нет», «Отмена» и с пиктограммой восклицательный знак в желтом треугольнике.
Если не указан аргумент Кнопки, то VBA предлагает только одну кнопку «OK». Аргумент Кнопки позволяет управлять следующими параметрами окна сообщения:
-
Количеством кнопок в окне.
-
Типы кнопок и и х размещение в окне.
-
Пиктограммы, отображаемые в окне.
-
Какая кнопка назначается по умолчания.
-
Режим (модальность) окна сообщения.
В табл.1 показаны возможные установки для этого аргумента. Первая группа значений устанавливает число и тип кнопок. Вторая позволяет выбрать пиктограмму, отображаемую в окне. Третья назначает кнопку по умолчанию. Четвертая группа устанавливает режим окна сообщения. Для создания конечного значении аргумента Кнопки можно использовать только одно значение из каждой группы, объединив их значком «плюс».
Группа |
Константа |
Значение |
Описание |
Группа 1 |
vbOKOnly |
0 |
Отображает только кнопку OK (установка по умолчанию) |
VbOKCancel |
1 |
Отображает кнопки OK и Отмена |
|
VbAbortRetryIgnore |
2 |
Отображает кнопки Стоп, Повтор и Пропустить. |
|
VbYesNoCancel |
3 |
Отображает кнопки Да, Нет и Отмена |
|
vbYesNo |
4 |
Отображает кнопки Да и Нет |
|
VbRetryCancel |
5 |
Отображает кнопки Повтор и Отмена |
|
Группа 2 |
VbCritical |
16 |
Отображает запрещающую пиктограмму |
VbQuestion |
32 |
Отображает предупреждающую пиктограмму |
|
VbExclamation |
48 |
Отображает предупреждающую пиктограмму |
|
VbInformation |
64 |
Отображает информационную пиктограмму |
|
Группа 3 |
VbDefaultButton1 |
0 |
Первая кнопка – кнопка по умолчанию |
VbDefaultButton2 |
256 |
Вторая кнопка – кнопка по умолчанию |
|
VbDefaultButton3 |
512 |
Третья кнопка – кнопка по умолчанию |
|
VbDefaultButton4 |
768 |
Четвертая кнопка – кнопка по умолчанию |
|
Группа 4 |
VbApplicationModal |
0 |
Режим приложения: пользователь должен закрыть окно сообщения перед продолжением работы в текущем приложении |
VbSystemModal |
4096 |
Системный режим: все приложения недоступны, пока пользователь не закроет окно сообщения |
|
Дополнительная группа |
vbMsgBoxHelpButton |
16384 |
Отображает кнопку Справка |
|
vbMsgBoxSetForeground |
65538 |
Делает окно сообщения окном переднего плана |
|
vbMsgBoxRight |
524288 |
Отображает окно сообщения, выровненным по правому краю окна приложения |
|
vbMsgBoxRtlReading |
1048576 |
Для иврита и арабского языка указывает, что текст должен выводиться справа налево. |
Табл. 1. Установки для аргумента Кнопки функции MsgBox
Чтобы не ошибаться при вводе значений аргумента Кнопки, используйте список констант, который появляется после ввода знака «+». Знак «+» используется для объединения нескольких констант при задании сложного аргумента Кнопки.
Аргумент Заголовок используется для задания текста, который помещается в строке заголовка окна ввода. Если этот аргумент не задан, то в строке заголовка отображается Microsoft Excel.
Аргументы файл_справки и содержание используются в том случае, если вы создаете для своего приложения собственную систему справок.
В табл.2 представлен список значений, возвращаемых функцией MsgBox. Возвращаемое значение зависит от нажатой пользователем кнопки.
-
Возвращаемое значение
Кнопка
1
OK
2
Отмена
3
Стоп
4
Повтор
5
Пропустить
6
Да
7
Нет
Табл. 2. Значения, возвращаемые функцией MsgBox
Наилучший тип Переменной возвращаемой функцией MsgBox является Integer.
Объекты Range и Cells
В VBA ячейки рабочего листа трактуются как объект Range. Это наиболее часто используемый объект.
Объект Range при работе с ячейками использует формат А1.
Формат А1. Ссылка состоит из имени столбца (обозначаются буквами от А до IV, 256 столбцов максимально) и номера строки (от 1 до 65536). Например, А77. Для ссылки на диапазон ячей указываются адреса левой верхней и правой нижней ячейки диапазона, разделенных двоеточием. Например, В10:В20, 7:7 (все ячейки в 7-й строке), 5:10 (все ячейки между 5-й и 10-й строками включительно), D:D (все ячейки в столбце D), H:J (все ячейки между столбцами H и J включительно). Признаком абсолютной ссылки является знак доллара перед именем строки или столбца
Объект Cells при работе с ячейками использует формат R1C1.
Формат R1C1. В формате R1C1, после буквы «R» указывается номер строки ячейки, после буквы «С» -- номер столбца. Например, абсолютная ссылка R1C1 эквивалентна абсолютной ссылке $A$1 для формата А1. Для задания относительной ссылки указывается смешение по отношению к активной ячейке. Смешение указывается в квадратных скобках. Знак указывает направление смещения. Например, R[-3]С (относительная ссылка на ячейку, расположенную на три строки выше в том же столбце). R[2]С[2] (относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее). R2С2 (абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце). R[-1] (относительная ссылка на строку, расположенную выше текущей ячейки), R (абсолютная ссылка на текущую строку).
Полный адрес ячейки может содержать также имя рабочего и адрес книги. После имени листа ставится знак «!», а адрес книги заключается в квадратные скобки. Например: [Книга1.xls]Лист5!D$2.
В качестве объекта Range и могут выступать:
-
отдельная ячейка;
-
выделенный диапазон ячеек;
-
несколько выделенных диапазонов ячеек (т.е. совокупность несмежных диапазонов);
-
строка и столбец;
-
трехмерный диапазон (т.е. состоящий из диапазонов, расположенных на разных рабочих листах).
Свойства объекта Range и Cells
Свойства |
Описание и допустимые значения |
Value |
Возвращает значение из ячейки или диапазона (в ячейку или диапазон): X=Range(“A1”).Value Range(“A1”).Value=10 |
Name |
Возвращает имя диапазона: Range(“B1:B4”).Name=”Приложение” |
Address |
Возвращает текущее положение диапазона |
Count |
Возвращает количество ячеек в диапазоне |
Offset |
Возвращает величину смещения одного диапазона относительно другого |
Resize |
Позволяет изменять текущее выделение диапазона |
CurrentRegion |
Возвращает текущий диапазон, содержащий указанную ячейку и ограниченный пустыми строкой и столбцом. |
WrapText |
True (False) – разрешает (не разрешает) перенос текста при вводе в диапазон. |
EntireColumn, EntireRow |
Возвращает строку и столбец. |
ColumnWidth, RowHeight |
Возвращает ширину столбцов и высоту строк диапазона. |
Font |
Возвращает объект Font (шрифт). Например: With Worksheets(“Z3”).Range(“F10”).Font .Size=22 .Bold=True .Italic=True End With |
Formula |
Формула в формате А1. Например, так можно ввести формулу в ячейку C2: Range(“C2”).Formula=”=$B$2+$A$2” |
FormulaLocal |
Формула в формате А1 с учетом языка пользователя (для неанглоязычных версий Excel). Например: Range(“C1”).FormulaR1C1= “=ПИ ( )” |
FormulaR1C1 |
Формула в формате R1C1. Например, Range(“C1”).FormulaR1C1= “=R1C1+2” |
FormulaR1C1Local |
Формула в формате R1C1 с учетом языка пользователя (для неанглоязычных версий Excel). |
HorizontalAlignment |
Горизонтальное выравнивание. Возможные значения: xlHAlignGeneral (обычное), xlHAlignCenter (по центру), xlHAlignCenterAcrossSelection (по центру выделения), xlHAlignJustify (по ширине), xlHAlignRight (по правому краю), xlHAlignLeft (по левому краю) и другие. |
VerticalAlignment |
Вертикальное выравнивание. Возможные значения: xlVAlignBottom (по нижнему краю), xlVAlignCenter (по центру), xlVAlignTop (по верхнему краю) и другие. |
Методы объекта Range и Cells
Методы |
Действия |
Address |
Возвращает адрес ячейки. |
AutoFit |
Автоматически настраивает ширину столбца и высоту строки. Например: Range(“B1:B3”).Columns.AutoFit Использование свойства Columns или Rows в данном случае необходимо, так как значением диапазона должны быть строки или столбцы, иначе будет выдаваться ошибка. |
Clear |
Очищает диапазон. Например: Range(“В1:В20”).Clear |
Copy |
Копирует диапазон в другой диапазон или буфер обмена (если параметр Destination не задан). Например, так можно скопировать значения диапазона с одного листа (Л1) на другой (Л2): Worksheets(“З1”).Range(“D1:D5”).Copy Destination:=Worksheets(“P2”).Range(“D5”) |
Cut |
Копирует диапазон с удалением (вырезает) в другой диапазон или буфер обмена (если параметр Destination не задан). Например, скопируем диапазон ячеек с удалением в буфер обмена: Worksheets(“Лист1”).Range(“D1:E5”).Cut |
Delete |
Удаляет диапазон. Параметр Shift определяет направление сдвига ячеек при удалении. Например: Range(“B6:D6”).Delete Shift:=xlShiftToLeft |
Insert |
Вставляет ячейку или диапазон ячеек. Например, так можно вставить строку перед шестой строкой на листе «Лист2»: Worksheets(“Лист2”).Rows(6).Insert |
Select |
Выделяет диапазон: Range(“A1:C7”).Select |
Методы объекта Range и Cells, реализующие команды Excel
Методы |
Действия |
DataSeries |
Создает прогрессию. DataSeries(rowcol,date,step,stop,trend) Вручную метод выполняется с помощью команды Правка\Заполнить\Прогрессия |
AutoFill |
Автозаполнение. Автоматически заполняет ячейки диапазона элементами последовательности: Объект(Диапазон, Тип). |
AutoFilter |
Автофильтр. Реализует запрос на фильтрацию данных на рабочем листе: Объект.AutoFilter(Поле, Условие1, Оператор, Условие2) Соответствует команде Данные\Фильтр\Автофильтр. |
AdvancedFilter |
Расширенный фильтр. Соответствует команде Данные\Фильтр\Расширенный фильтр. |
Consolidate |
Объединение данных из нескольких диапазонов в одну итоговую таблицу. Соответствует команде Данные\Консолидация. |
Find |
Поиск данных. Вручную вызывается командой Правка\Найти. |
TblGoalSeek |
Подбор параметра. Вручную выполняется с помощью команды Сервис\Подбор параметра. |
Sort |
Сортировка данных. Вручную выполняется с помощью команды Данные\Сортировка |
Subtotal |
Добавляет промежуточные итоги. Вручную вызывается командой Данные\Промежуточные итоги. |
Примечание. Следует особо отметить, что в VBA (в отличие от Excel) операция присваивания выполняется независимо от статуса ячейки и не меняет его. Т.е. для присвоение значения ячейке (или для получения её значения) совсем не обязательно, чтобы эта ячейка была активной (вспомните, что в Excel перед вводом в ячейку её необходимо активизировать), не станет она активной (если до этого не была таковой) и после присвоения ей какого-либо значения.