Основы программирования на языке Visual Basic for Applications (VBA). Модель объектов MS Excel.

В модели объектов Excel имеется более 100 объектов. Наиболее часто используемыми объектами Excel являются объекты Application, Workbooks и Workbook, Worksheets и Worksheet, Range, Selection.

Коллекция представляет собой объект, содержащий несколько других объектов, как правило, одного и того же типа.

Объект Workbooks содержит все открытые объекты Workbook (рабочая книга).

Доступ к заданному элементу коллекции осуществляется либо по номеру, либо по имени.

Например, Worksheets(1) обозначает первый рабочий лист активной книги, а Worksheets(“Лист1”) - рабочий лист с именем Лист1.

Методы и Свойства

Объект.Метод

Пример: Application.Quit

Объект.Свойство = ЗначениеСвойства

Примеры: Application.Caption = “Пример”

MsgBox Worksheets.Count

Полная ссылка на объект состоит из ряда имен вложенных последовательно друг в друга объектов. Их имена в этом ряду разделяются точками. Ряд начинается с объекта Application и заканчивается именем самого объекта.

Пример: Application.Workbooks(“Архив”).Worksheets(“Продажа”).Range(“A1”)

В неявной ссылке, в отличие от полной, активный в данный момент объект, как правило, можно опускать.

• Если программа выполняется в Excel, то Workbooks(“Архив”).Worksheets(“Продажа”).Range(“A1”)

• Если рабочая книга Архив является активной, то ссылку можно сократить:
Worksheets(“Продажа”).Range(“A1”)

• Если рабочий лист Продажа активен, то Range(“A1”)

Объект Application – это главный (корневой) объект в иерархии объектов Excel, представляет само приложение Excel. Он имеет огромное число свойств и методов, позволяющих установить общие параметры приложения Excel.

Свойства объекта Application

• ActiveWorkbook (активная рабочая книга)

• ActiveSheet (активный рабочий лист)

• ActiveCell (активная ячейка)

• ActiveChart (активная диаграмма)

Пример:

With ActiveCell

.Font.Bold = True

.Value = “Отчет за Май”

End With

Caption – Возвращает или устанавливает текст из заголовка главного окна Excel. Установка значения свойства равным Empty возвращает заголовок, используемый по умолчанию.

Application.Caption = “Отчет за 2000 год”
Application.Caption = Empty

Метод Quit – Закрывает приложение.

Пример: Application.Quit

После объекта Application в иерархии объектов Excel следует коллекция Workbooks, которая содержит объекты Workbook. Объект Workbook представляет собой рабочую книгу Excel. Все открытые в Excel рабочие книги включены в коллекцию Workbooks.

Свойства коллекции Workbooks и объекта Workbook

• Count - возвращает число объектов коллекции Workbooks.

• Worksheets – возвращает коллекцию всех рабочих листов книги.

• Charts – возвращает коллекцию всех диаграмм книги (которые не внедрены в рабочие листы)

Методы коллекции Workbooks и объекта Workbook

• Activate - активизирует рабочую книгу.

• Add – создает новый объект в коллекции Workbooks.

• Close –закрывает книги.

• MsgBox Workbooks.Count

• Workbooks(“Отдел кадров”).Activate

• Workbooks(1).Close

• Workbooks.Close

Обработка событий объекта Workbook

Перейти в редактор VBA;
В окошке Project Explorer выбрать объект ЭтаКнига и перейти в окошко кода для этого объекта;
В верхнем левом списке выбрать объект Workbook;
В верхнем правом списке выбрать необходимое событие.

Коллекция Worksheets включает в себя множество всех объектов Worksheet (рабочий лист) в рабочей книге. В иерархии Excel объект Worksheets идет сразу после объекта Workbook.

Свойства коллекции Worksheets и объекта Worksheet

• Name – возвращает или устанавливает имя рабочего листа.

• Visible – возвращает True, если объект – видимый.

• Cells – возвращает коллекцию всех ячеек рабочего листа.

• Columns, Rows – возвращает коллекцию всех столбцов и строк рабочего листа.

Методы коллекции Worksheets и объекта Worksheet

• Activate – активизирует рабочий лист.

• Add – создает новый рабочий лист.

• Delete – удаляет рабочий лист.

• Worksheets(1).Name = “Декабрь”

• Workbooks(1).Worksheets(3).Cells(1,3).Font.Size = 20

• Worksheets(“Итоги”).Activate

• ActiveSheet.Delete

• Worksheets(3).Add

• Activate – активизирует рабочий лист.

• Add – создает новый рабочий лист.

• Delete – удаляет рабочий лист.

• Worksheets(1).Name = “Декабрь”

• Workbooks(1).Worksheets(3).Cells(1,3).Font.Size = 20

• Worksheets(“Итоги”).Activate

• ActiveSheet.Delete

• Worksheets(3).Add

Обработка событий объекта Worksheet

Перейти в редактор VBA;
В окошке Project Explorer выбрать объект Лист1 (или другой лист) и перейти в окошко кода для этого объекта;
В верхнем левом списке выбрать объект Worksheet;
В верхнем правом списке выбрать необходимое событие.

События объекта Worksheet

• Activate – при активизации рабочего листа.

• BeforeDoubleClick – при двойном щелчке по рабочему листу.

• Calculate – при пересчете рабочего листа.

• Change – при изменении содержимого ячейки пользователем.

• Deactivate – когда рабочий лист теряет фокус.

• SelectionChange – при изменении выделенного диапазона ячеек.

Объект Range

В иерархии Excel объект Range (диапазон), один из ключевых объектов VBA,следует сразу после объекта Worksheet. Объект Range может представлять собой ячейку, строку, столбец или диапазон ячеек.

Объект Selection

Объект Selection – это любые выделенные ячейки на рабочем листе. При работе с объектом Selection можно использовать свойства и методы объекта Range. Для определения объекта Selection можно использовать метод Select.

Задание группы строк и столбцов

Range(“A:C”) – задает диапазон, состоящий из столбцов A, B и C.
Range(“2:2”) – задает диапазон состоящий из второй строки.
Range(“2:5”) – задает диапазон состоящий из 2, 3, 4, 5 строки.
Rows(2) – задает вторую строку.
Columns (1) – задает столбец А.

Связь объекта Range и свойства Cells объекта Worksheet

Range(“A2”) – задает ячейку A2.
Cells(1,2) – задает ячейку B1.
Range(“A2:C3”) – задает диапазон ячеек A2:C3.
Range(“A2:C3, A5:C6”) – задает диапазон ячеек A2:C3 и A5:C6.
Range(Cells(1,2), Cells(3,3)) – задает диапазон ячеек B1:C3.
Range(“B2:D4”).Select
Selection.Cells(2,2).Value = 2 – значение 2 вводится в ячейку C3.

Свойства объекта Range

• Value – возвращает или устанавливает значение в ячейках диапазона.

• Interior – возвращает объект, представляющий собой фон ячейки. Свойство ColorIndex этого объекта задает цвет фона (от 1 до 56: 1-черный, 2-белый, 3-красный, 4-зеленый, 5-синий, 6-желтый, 7-фиолетовый ).

• Font – возвращает объект, представляющий собой шрифт. Свойства: Name, FontStyle (Regular (обычный), Bold (жирный), Italic (курсив)), Size, ColorIndex.

• Formula – возвращает или устанавливает формулу в формате A1.

• Address – возвращает адрес ячейки.

Примеры:

X = Range(“C1”).Value ‘X - переменная

Range(“A1:B3”).Value = 1

With Range (“A1:B2”).Font
.Size = 14 : .FontStyle = “Bold” : .ColorIndex = 3
End With

• Range(“A1”).Formula = “=$A$4 + $A$10”

Методы объекта Range

• Activate – активизирует ячейку.

• Clear – очищает диапазон.

• Copy – копирует диапазон в другой диапазон или в буфер обмена.

• Delete – удаляет диапазон.

• Select – выделяет диапазон.

Примеры:

Range(“A1:B3”).Clear

Range (“A1:D4”).Copy Worksheets(“Лист2”).Range(“E5”)

Rows(3).Delete

Range(“A1:C3”).Select : Range(“B1”).Activate