Управление приложением Excel с помощью VBA

 

Цель работы: изучение основных объектов VBA, их свойств и методов; приобретение навыков управления поведением и внешним видом объектов.

 

1 Теоретические сведения

 

Язык VBA разработан преимущественно для работы с приложениями и для расширения их возможностей. VBA доступно множество объектов Excel. Свойства и методы этих объектов позволяют управлять из программ VBA почти всеми объектами функционирования Excel.

 

1.1 Объект Application

Объект Application (Приложение) занимает самый верхний уровень иерархии объектов Excel. Роль объекта Application не ограничивается заданием параметров и опций Excel. Объект содержит встроенные функции Excel.

Основные свойства объекта Application:

- ActiveWorkbook возвращает активную (текущую) книгу;

- ActiveSheet возвращает активный лист в активной рабочей книге. Возвращаемый лист может быть любого поддерживаемого типа, включая рабочий лист и лист диаграмм;

- ActiveCell возвращает активную ячейку на активном листе активной рабочей книги;

- ThisWorkbook возвращает рабочую книгу, где находится выполняемая процедура;

- MailSystem возвращает почтовую систему, установленную на компьютере. Полезно, когда электронная почта работает в автоматическом режиме;

- Selection определяет текущее выделение. Выделением может быть диапазон ячеек, элементы диаграммы и т.д.

Основные методы объекта Application:

- InputBox отображает окно ввода и позволяет указать тип возвращаемого значения;

- Quit применяется для выхода из Excel.

1.2 Объект Workbook

В Excel каждая рабочая книга является объектом Workbook, а Workbooks является коллекцией всех открытых рабочих книг в текущем списке Excel. Чтобы обратиться к конкретной рабочей книге, следует воспользоваться методом Workbooks объекта Application.

Синтаксис: Workbooks(Index).

Основные методы объекта Workbook:

- Activate делает активной указанную рабочую книгу;

- Close закрывает рабочую книгу;

- Save сохраняет рабочую книгу;

- Save as сохраняет рабочую книгу. Имеет ряд необязательных аргументов: Filename (Имя файла), FileFormat (Формат файла), Password (Пароль), WriteResPassword (Пароль на запись), ReadOnlyRecommended (Рекомендовать режим «только для чтения»);

- PrintOut печать всего содержимого рабочей книги;

- PrintPreview отображает рабочую книгу в режиме предварительного просмотра;

- Add создает рабочую книгу;

- Open открывает рабочую книгу;

Основные свойства объекта Workbook:

- ActiveSheet возвращает активный лист;

- Path (Путь) используется, если необходимо найти каталог, в котором сохранена рабочая книга;

- Saved (Сохраненная) возвращает значение Истина, если книга была сохранена после внесения в нее последних изменений. В противном случае, возвращает значение Ложь.

Пример:

Sub ПримерРабКниги()
Dim wbNewWorkbook As Workbook	Объявление переменной- объекта
Set wbNewWorkbook = Workbooks.Add	Этой переменной-объекту назначается новая создаваемая книга. Оператор создает рабочую книгу, добавляя новый элемент в коллекцию Workbooks
wbNewWorkbook.Worksheets(“Лист1”)._ Range(“A1”).Value = 100	Присваивается значение ячейке А1
wbNewWorkbook.SaveAs “Лаб5”
wbNewWorkbook.Close
MsgBox “Рабочая книга закрыта”
End Sub

 

1.3 Объект Worksheet

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

Каждый рабочий лист является объектом Worksheet, а Worksheets представляет собой коллекцию всех листов в данной рабочей книге.

Object. Worksheets(Index)

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

Основные методы объекта Worksheet:

- Activate активизирует рабочий лист;

- Add добавление (вставка) новых листов.

Синтаксис:

Object.Worksheets.Add([Before] [, After] [, Count] [, Type])

Before указывает на лист, перед которым вставляется новый лист, After указывает рабочий лист, после которого добавляется новый лист. По умолчанию VBA добавит новый рабочий лист перед активным листом. Count представляет собой число добавляемых новых рабочих листов. Type представляет тип листа, который вы хотите вставить;

- CheckSpeling используется для проверки орфографии содержимого рабочего листа;

- Copy – копирование рабочего листа;

- Move – перемещение рабочего листа;

- Delete – удаление рабочего листа.

Основные свойства объекта Worksheet:

- Name – переименование рабочего листа.

Пример задания активному листу нового имени:

ActivSheer.Name = “Бюджет”

- Cells (Ячейки) – выбор ячеек листа.

Пример задания шрифта для всех ячеек листа:

Worksheets(“Лист1”).Cells.Font.Name = “Arial”

Пример 1

Sub
Dim wsNewWorkSheet As Worksheet	Объявляется переменная-объект
Set wsNewWorkSheet = Worksheets.Add	Переменной-объекту назначается создаваемый рабочий лист
wsNewWorkSheet.Name = Format (Date, _ “d mmmm yyyy”)	Свойство Name используется для создания имени в виде текущей даты. Функция Format применяется для управления внешним видом даты
End Sub

 

Пример 2

Sub УдалениеРабЛиста ()
‘Удаляет все временные листы
Dim Sheet As Worksheet
Application.DisplayAlerts = False	Подавляет обычное диалоговое окно сообщения Excel при удалении рабочего листа
For Each Sheet InWorkbook(“Лаб5.xls”).Worksheets	Начинается цикл для просмотра всех листов рабочей книги Лаб5.xls
If InStr(1, Sheet.Name, “Temporary”) Then	Лист тестируется функцией InStr. Цель – не входит ли подстрока Temporary в имя данного рабочего листа
Sheet.Delete	Если да, то лист удаляется
End If
Next Sheet
Application.DisplayAlerts = True	Свойству DisplayAlerts присваивается значение True

1.4 Объект Range

В VBA ячейки трактуются как объект Range. В качестве объекта могут выступать:

– отдельная ячейка;

– выделенный диапазон ячеек;

– несколько выделенных диапазонов ячеек;

- строка и столбец;

- трехмерный диапазон (т.е. состоящий из диапазонов, расположенных на разных рабочих листах).

Основные свойства объекта Range:

- Address (Адрес) возвращает текущее положение диапазона;

- Count (Счет) возвращает количество ячеек в диапазоне;

- Formula (Формула) возвращает формулу, по которой вычисляется значение, отображаемое в ячейке;

- Resize (Изменение размеров) позволяет изменять текущее выделение диапазона;

- Value (Значение) возвращает значения ячеек, составляющих диапазон.

Основные методы объекта Range:

- Offset возвращает объект Range, смещенный относительно конкретной ячейки на заданное количество строк и столбцов.

Object.Offset ([RowOffset] [, ColumnOffset])

где RowOffset – количество строк смещения относительно Object (по умолчанию 0);

ColumnOffset – количество столбцов смещения относительно объекта.

Пример использования метода Offset для указания диапазона.

Sub Выделение ()
'Выделяет данные в диапазоне
Dim DBRows As Integer
WorkSheets(“Sheet1”).Select	Программа выбирает лист Sheet1
With Range(“Database”)	Выбирает объект Range с именем Database
DBRows =.Rows.Count	Вычисляется количество строк в диапазоне и сохраняется
.Offset(1, 0).Resize(DBRows-1, _ .Colums. Count).Select	Возвращает диапазон, который смещен относительно диапазона Database на одну строку вниз. Используется свойство Resize для изменения размеров диапазона
End With
End Sub

- Select (Выделить) выделяет диапазон;

- Activate (Активизировать) активизирует диапазон;

- Clear (Очистить) очищает содержимое диапазона;

- Copy (Копировать) копирует содержимое диапазона;

- Cut (Вырезать) перемещает содержимое диапазона;

- Past Special (Специальная вставка) вставляет содержимое буфера в диапазон, используя при этом различные аргументы.

Если вам необходимо получить содержимое ячейки или требуется ввести данные в диапазон, используйте два предоставляемых VBA свойства объекта Range: Value и Formula.

Для получения содержимого ячейки:

- если требуется значение ячейки, используйте свойство Value. Например, если в ячейке А1 содержится формула =2*2, то выражение Range(“A1”).Value вернет значение 4;

- если вас интересует содержащаяся в ячейке формула, используйте свойство Formula. Например, если ячейка А1 содержит формулу =2*2, то выражение Range(A1).Format вернет текстовую строку “=2*2”.

Пример процедуры, выполняющей простой подсчет выплат по ссуде на листе Sheet3 активной рабочей книги.

Sub Арендная плата()

WorkSheets(“Sheet3”).Select

With Range(“A1”) 'Надписываем ячейки

.Value = “Расчет выплат по ссуде”

.Font.Bold = True

.Font.Italic = True

.Font.Size = 18

.Offset(1).Value = “Rata”

.Offset(2).Value = “Period”

.Offset(3).Value = “Amount”

.Offset(5).Value = “Payment”

End With

'Вводим формат ячеек и формулу

With Range(“A1”)

.Offset(1, 1).NumberFormat = “0.00%”

.Offset(3, 1).NumberFormat = “”$#, ##0_); [Red]($#, ##0)”

.Offset(5, 1).NumberFormat = “”$#, ##0.00_); [Red]($#, ##0.00)”

.Offset(5, 1).Formula = “=PMT($B$2)12, $B$3*12, $B$4)”

End With

End Sub

 

2 Практическая часть

 

З а д а н и е 1

Создайте процедуру, которая должна выполнять следующее:

- создать новую рабочую книгу;

- вставить в эту книгу новый рабочий лист;

- дать новому рабочему листу ваше имя;

- сохранить рабочую книгу под именем Лаб5.

Выполните процедуру. Откройте рабочую книгу Лаб5 и введите насколько значений в рабочий лист с вашим именем. Создайте новую процедуру с именем СохрЛаб5. Эта процедура должна определить, сохранена ли рабочая книга после внесения в нее изменений. Если книга была сохранена, то должно выводиться окно с соответствующим сообщением. Выполните эту процедуру.

 

3 Контрольные вопросы

 

3.1 Какой объект находится на высшем уровне иерархии объектов?

3.2 Какой метод применяется для создания новых рабочих книг и рабочих листов?

3.3 Как в VBA удалить рабочий лист из рабочей книги?

3.4 Какое свойство объекта Range позволяет определить адрес одного диапазона на основе адреса другого?

3.5 Как определить, какое количество ячеек составляет диапазон?

3.6 Какой метод применяется для удаления содержимого диапазона?

Лабораторная работа 6