Програмування VBA в Excel

Лекція № 14 - Програмування VBA в Excel
План лекції
Об'єктна структура Excel.
Об'єкт Workbook.
Об'єкт Worksheet.
Об'єкт Range.
Висновки.

Об'єктна структура Excel
У VBA для кожного додатку Office 2007 визначена безліч об'єктів, організованих в ієрархію, звану об'єктною моделлю додатку. Тому, працюючи з мовою VBA в середовищі Excel, для досягнення успіху необхідно добре розуміти, що таке об'єкт, і чітко уявляти собі об'єктну модель цього застосування.

Властивості об'єкту Application
ActiveCell (Активний Осередок) — ця властивість містить посилання на осередок активного листа, в якому розташований курсор;
ActiveChart (Активна Діаграма) — ця властивість містить посилання на виділену діаграму з активної книги;
ActivePrinter (Активний Принтер) — ця властивість містить посилання на активний принтер;
ActiveSheet (Активний Робочий лист) — ця властивість містить посилання на активний лист активної книги;
ActiveWindow (Активне Вікно) — ця властивість містить посилання на активне вікно Excel;
ActiveWorkbook (Активна Робоча книга) — ця властивість містить посилання на активну робочу книгу.

Оновлення екрану
Application.ScreenUpdating = False

Властивості
Application
Worksheet
Range
Chart
Window

Об'єкт Workbook
У ієрархії Ехсеl відразу після об'єкту Application слідує об'єкт Workbook — робоча книга. Кожен об'єкт Workbook в додатку представляє один файл з розширенням .XLSX (стандартна робоча книга) або .XLSM (стандартна робоча книга з макросами).

Відкриття і створення робочих книг
Оскільки об'єкти робочих книг Workbook входять в колекцію Workbooks, для створення нової робочої книги слід використовувати метод Add колекції Workbooks:
Workbooks.Add (шаблон)
xlWBATemplate, SheetsInNewWorkbook

Метод Add
Dim Новая_кніга As Excel.Workbook
Set Новая_кніга = Workbooks.Add (шаблон)
Workbooks.Open (Імя_файла)
Set Workbook1 = Workbooks.Open (FileName := "C:\Data\SalesData1.xls")

Збереження і закриття робочих книг
Для збереження робочої книги, якій вже було привласнено ім'я, використовується метод Save об'єкту Workbook. Цей метод не має аргументів.
Новая_кніга.SaveAs (Імя_файла)
Hoвaя_книга.SaveCopyAs (Імя_файла)

Додавання книги і збереження активної книги в робочому файлі
Application.Workbooks.Add
Call Application.ActiveWorkbook.SaveAs ("temp.xls")
'Збереження копії, отриманої по номеру з колекції Workbooks
Application.Workbooks.Add
Call Application.ActiveWorkbook.SaveAs ("temp1.xls")
Application.Workbooks ("temp1.xls)".SaveAs ("Copy of temp1.xls")
'Використання збереженого посилання на доданий об'єкт
Dim As Workbook
Set W = Workbooks.Add
Call W.SaveAs ("temp1.xls)"

Для закриття робочої книги використовується метод Close
Новая_кніга.Close (SaveChanges, FileName, RouteWorkbook)

Аргументи
Аргумент SaveChanges задає варіант дій для випадку, коли робоча книга містить незбережені зміни. Можливі значення: True (для збереження змін) і False (для ігнорування змін). Якщо цей аргумент опущений, користувачеві буде запропоновано зберегти зміни.
Аргумент FileName - це ім'я файлу, в якому зберігається робоча книга. Якщо цей аргумент опущений, використовується поточне ім'я. Якщо такого не існує, користувачеві буде запропоновано його ввести.
Аргумент RouteWorkbook відноситься тільки до тих робочих книг, які мають список розповсюдження (routing slip), і ще не були поширені. Встановлюється значення True, якщо потрібно відправити робочу книгу наступному одержувачеві, інакше встановлюється False. Якщо цей аргумент опущений, користувачеві виводиться відповідний запит.

Метод Close об'єкту Workbook
Sub CloseWorkbook()
Dim Workbook1 As Workbook
Set Workbook1 = Workbooks.Open (FileName: ThisWorkbook Path &"\Temp.xls")
Range (A1).Value = Format (Date, "ddd mmm dd, yyyy")
Range(A1).EntireColumn.AutoFit
Workbook1.Close SaveChanges := True
End Sub

PrintOut та доступні об'єкти і колекції
Chart (діаграма);
Charts (колекція діаграм);
Range (діапазон);
Sheets (колекція таблиць);
Window (вікно);
Workbook (робоча книга);
Worksheet (робочий лист);
Worksheets (колекція робочих листів).

Синтаксис методу Printout
Object.PrintOut (From, To, Copies, Preview _
ActivePrinter, PrintToFile, Collate, PrToFileName)

Аргументи
Аргументи From і То задають першу і останню сторінки для друку. Значенням за умовчанням є перша і остання сторінки об'єкту.
Аргумент Copies задає кількість друкованих копій. За умовчанням друкується одна копія.
Аргумент Preview може приймати значення True або False залежно від того, чи потрібно при друці відкривати вікно попереднього перегляду. Значення за умовчанням — False.
Аргумент ActivePrinter задає ім'я використовуваного принтера. Якщо цей аргумент опущений, вибирається той принтер, який заданий в Windows для використання за умовчанням.
Аргумент PrintToFile приймає значення True або False залежно від того, чи слід замість пристрою друку перенаправити вивід у файл. Значення прийняте за умовчанням — False.
Аргумент Collate приймає значення True або False залежно від того чи слід розкласти сторінки по копіях при друці. Значення, прийняте за умовчанням, True.
Аргумент PrToFileName задає ім'я файлу, в який буде направлений вивід, якщо аргумент PrintToFile має значення True. Якщо цей параметр опущений, у користувача запрошується ім'я файлу.

Об'єкт Worksheet
Велика частина роботи, яка виконується програмами в середовищі Excel, зазвичай пов'язана безпосередньо з робочими листами, представленими об'єктами Worksheet в колекції Worksheets.

Додавання нового робочого листа
Dim Новий_лист As Excel.Worksheet
Set Новий_лист = Worksheets.Add (Before, After, Count)
Новий_лист.Name = "Об'єми_продажу"

Конструкція
ActiveWorkbook.Worksheets ("Sheet1")
ActiveWorkbook.Worksheets (1)
Для видалення робочого листа використовується метод Delete об'єкту Worksheet:
ActiveWorkbook.Worksheets (Імя_листа).Delete

Копіювання і переміщення робочих листів
Методи Сору і Move об'єкту Worksheet дозволяють копіювати і переміщати чи один декілька листів в результаті виконання однієї операції. Копіювати і переміщати робочі листи можна як в межах однієї робочої книги, так і між ними.

Синтаксис
Лист.Copy (Before, After)
Worksheet ("Об'єми_продажу ") .Сору
After := Worksheets (Worksheets.Count)
Worksheet ("Об'єми_продажу ").Move Before := "Проекти"

Об'єкт Range
Об'єкт Range є одним з ключових об'єктів VBA і в ієрархії Excel слідує відразу після об'єкту Worksheet. У Excel об'єкт Range може представляти один осередок, цілу рядок або колонку робочого листа і навіть довільний двух- або тривимірний блок осередків робочої книги.

Визначення об'єкту Range
Стандартне посилання на осередок. Так званий А1-стіль посилання на осередок є, мабуть, найпростішим способом роботи з об'єктами Range. Для визначення діапазону необхідно укласти посилання в лапки і вказати його в дужках безпосередньо після ключового слова Range, як показано нижче.
ActiveSheet.Range ("В3")
Worksheets ("Ліст2").Range ("M5:S20")
Іменовані діапазони. Якщо робочий лист містить іменовані діапазони, для звернення до подібних об'єктів Range їх імена можуть використовуватися замість вказівки конкретних посилань. Для привласнення діапазону імені відповідне значення повинне бути поміщене у властивість Name об'єкту Range.
Worksheets ("Фінансовий звіт").Range ("A3:В4").Name = "Виплата_відсотків"
Range ("Виплата_процентов").Cells.Interior.ColorIndex = 8
Скорочений запис. Оскільки об'єкти Range використовуються в програмному коді дуже часто, Excel дозволяє опускати ключове слово Range при вказівці діапазону в А1-стіле або при зверненні до нього по імені. В цьому випадку посилання на осередки або ім'я діапазону полягає в квадратні дужки, як показано в приведеному нижче прикладі:
ActiveSheet [A1:Z26] 'Звернення до явно заданого діапазону осередків’
[Квартальний_отчет] 'Звернення до пойменованого об'єкту Range’

Визначення об'єкту Range
Властивість Cells об'єкту Worksheets. Цей спосіб особливо зручний при написанні складних VBA-программ, оскільки дозволяє визначати діапазон не шляхом явної вказівки фіксованих адрес осередків, а за допомогою використання для його завдання вмісту змінних.
Властивість Selection. Коли програма повинна працювати з діапазоном, який в даний момент виділений користувачем у вікні додатку, використовується властивість Selection об'єктів Application або Window. Докладніше про це мова піде нижчим, в розділі "Робота з виділенням" даного розділу.
Властивість ActiveCell. Властивість ActiveCell (Активний осередок) використовується для доступу до діапазону, що представляє активний осередок вказаного вікна. При опусканні специфікатора об'єкту вікна (що означає звернення до об'єкту Application) властивість ActiveCell посилається на поточне активне вікно.
Значення осередку = ActiveCell.Value 'Читання значення осередку
Властивості Rows або Columns об'єкту Worksheet. Доступ до діапазону, що включає весь рядок або весь стовпець, здійснюється за допомогою властивостей Rows і Columns об'єкту робочого листа відповідно.
Workbooks ("Звіт.xls").Worksheets ("Звідна відомість").Columns (7)

Визначення об'єкту Range
Іменовані посилання на об'єкти. Оскільки діапазон є об'єктом, в програмі можна створити іменоване об'єктне посилання на нього, після чого доступ до даного діапазону виконуватиметься за допомогою вказівки імені посилання. Такий підхід простіший і надійніший, ніж багатократна вказівка необхідного діапазону. Нижче, після приміщення посилання на оброблюваний діапазон в об'єктну змінну Діапазон1, її значення використовується для доступу до властивостей цього діапазону.
Dim Діапазон1 As Range
Set Діапазон1 = Worksheets ("Ліст1").Range ("B12:В12")
К1 = Діапазон1.Value 'Вибірка значення коефіцієнта’

Приклад використання різних способів завдання діапазону оброблюваних осередків
Public Sub Proba() Зміна кольору фону вказаного осередку
Dim n As Integer Ціле число
Dim а As Range Об'єктна змінна класу Range
'Стандартне посилання на осередок - колір жовтий
Worksheets ("Ліст1").Range ("А2:в5").Cells.Interior.ColorIndex = 6
'Іменовані діапазони - колір блакитний
Worksheets ("Ліст1").Range ("B4:D9").Name = "Виплата_процентов"
Range ("Виплата_процентов").Cells.Interior.ColorIndex = 8
'Скорочений запис - колір бежевий і синій
ActiveSheet.[C6:E8].Cells.Interior.ColorIndex = 12
Worksheets ("Ліст1").Range ("C9:E12").Name = "Відсотки"
[Відсотки].Cells.Interior.ColorIndex = 25
' Властивості Rows або Columns об'єкту Worksheet - колір бірюзовий
ActiveSheet.Columns (7).Cells.Interior.ColorIndex = 14
'Іменовані посилання на об'єкти - колір фіолетовий
Set а = Worksheets ("Ліст1").Range ("D10:H16")
а.Cells.Interior.ColorIndex = 21
'Властивість ActiveCell - відображення значення в активному осередку Е2
n = ActiveCell.Value 'Читання значення активного осередку
MsgBox (n)
End Sub

Результат роботи прикладу

Використання властивості Cells для визначення діапазону
При використанні у виразі без вказівки координат властивість Cells об'єкту Worksheets визначає діапазон, що включає всі осередки даного робочого листа.
Worksheets ("Попередній звіт").Cells(3,5)
Місяць = Month (Now ())
Показник = Worksheets ("Річний звіт").Cells (Місяць, 8)
Worksheets ("Ліст2").Range (Worksheets ("Ліст2").Cells (3, 5), _
Worksheets ("Ліст2").Cells (4, 6))
With Worksheets ("Ліст2")
.Range (.Cells (3, 5), .Cells (4, 6)).Font.Bold = True
End With

Параметр стовпця
Cells.Item (2, 2)
Cells.Item (2, “B”)
Cells (2, 2)
Cells (2, "В")

Виконання групових операцій над осередками
За допомогою властивостей діапазону можна однією дією змінювати характеристики всіх осередків, що входять в даний діапазон. Наприклад, наступного оператора змінює розмір шрифту для всіх осередків вказаного діапазону:
Worksheets ("Ліст1").Range ("B12:H13").Font.Size = 14

Циклічний перебір рядків і стовпців в межах діапазону з використанням об'єкту Range
Public Sub FormatCells()
Dim Діапазон1 As Range
Dim nmДиапазон1 As String
Set Діапазон1 = Worksheets("Ліст1").Range("B3:C11")
With Діапазон1 'визначена раніше об'єктне посилання
.Value = 20 'значення всіх осередків встановлюється рівним 20
.Font.Name = "Arial Cyr" 'Використовуваний шрифт
.Font.Italic = True 'Зображення курсивне
.Name = "Базовая_табліца 'Привласнення імені діапазону
nmДиапазон1 = .Name 'Збереження імені в змінній
End With
MsgBox (nmДиапазон1) 'Відображення значення змінної
End Sub

Форматування осередків за допомогою об'єкту Range

Робота з окремими осередками діапазону
Діапазон.Value = Діапазон.Value + 10
For Each aCell In Діапазон
aCell.Value = aCell.Value + 10
Next

Робота з виділенням
Application.Selection.Value = 20
Selection.Value = 20
Dim Діапазон1 As Range
Set Діапазон1 = Windows ("Список товарів.xlsx").Selection
With Діапазон1 'Обробка осередків виділеного діапазону’
.CheckSpelling 'Перевірка правопису’
.Сору 'Копіювання в буфер’
End With

Виділення діапазону
With Worksheets ("Статотчет")
.Activate 'Активізація робочого листа
.Range ("Робочий годинник"). Select 'Виділення іменованого діапазону
End With
Range ("C4").Activate