VB_VBA

210	Програмування мовою Visual Basic/VBA

-ActiveSheet – повертає активну сторінку робочої книги;

-ActiveChart – повертає об’єкт класу Chart; це може бути діаграма на сторінці діаграм або активна діаграма на активній робочій сторінці;

-Names – колекція назвробочої книги;

-Container – об’єкт, який містить робочу книгу (робоча книга може бути вбудована в інший об’єктчи бутийого частиною).

Атомарних властивостей об’єкта Workbook є значно більше. Серед них чимало булевих властивостей, які дають змогу вмикати/вимикатитучиіншувластивістьробочоїкниги.

Приклад10.1. Виведеннянаекранназвробочоїкнигиішляхудонеї:

Public Sub AllNames() ' Друк назв документа

With Activeworkbook

Debug.Print " Властивість Name - ", Name Debug.Print " Властивість CodeName - ", CodeName Debug.Print " Властивість FullName - ", FullName Debug.Print " Властивість Path - ", Path

End With

End Sub

Осьяквиглядаютьрезультатиувікнінегайноговиконання:

Властивість Name - BookTwo.xls Властивість CodeName - ThisWorkbook

Властивість FullName - D:\Книга VisBasic\BookTwo.xls Властивість Path - D:\Книга VisBasic\

Короткоохарактеризуємодеякіметодиоб’єктаWorkbook. Створюються і відкриваються робочі книги методами Add і Open колекції Workbooks. А ось закриваються і зберігаються, використовуючи власніметоди:

•Save, SaveAs, SaveCopyAs – дають змогу зберегти робочу книгу без видалення її звідповідної колекції.

10. Програмування на VBA	211

•Close – виконує ті ж функції, що і Save, проте водночас закриває книгу і вилучаєїїзколекції.

•Activate – активізує робочу книгу.

•Protect, ProtectSharing, Unprotect, UnproteсtSharing – дають змогу вмикати/вимикати паролі.

•RunAutoMacros – запускає на виконання автомакроси книги.

10.2.4. Властивості та методи колекції Worksheet. Колекція Worksheets містить об’єкти класу Worksheet – робочі сторінки електронних таблиць. За домовленістю при створенні кожної нової робочої книги до її складу зачислюють три такі сторінки. Об’єктно це означає, що унаслідок створення нової книги автоматично створюється колекція Worksheets, яка містить три елементи. Колекція Worksheets має типовий набір властивостей: Application, Count, Parent, Item.

Крім цих властивостей є менш типова властивість для колекцій – властивість Visible, яка дає змогу зробити видимими або невидимими робочісторінкикниги. НайважливішіметодиколекціїWorksheets:

•Function Add([Before], [After], [Count]) As Object

– даєзмогудодатиновуробочусторінкудокниги, повертаючивідповідний об’єкт як результат. Додана сторінка стає активною. Параметри Before і After зазначають, куди помістити сторінку, – перед чи після сторінки, яка до виконання методу була активною. Параметр Count дає змогу одночасно додавати декілька сторінок.

•Sub Copy([Before], [After]) – копіюванняробочоїсторінки.

•Sub Delete() – видаленняколекціїробочихсторінок.

•Sub FillAcrossSheets(Range As Range, [Type As _

xlFillWith = xlFillWithAll])

– область, задана параметром Range, копіюється у відповідне місце всіх робочих листів. Тип копіювання задається другим параметром (можна, наприклад, копіювати тільки формули чи форматування). За домовленістю копіюється весь вміст області, заданої параметром Range. Об’єкт, що копіюється, повинен бути частиноюодногозробочихлистівколекції.

212	Програмування мовою Visual Basic/VBA

Приклад 10.2. Копіювання діапазону комірок:

Public Sub CopyRange()

'Копіювання об'єкта Range першої сторінки

'на всі сторінки робочої книги

With ThisWorkbook

.Worksheets.FillAcrossSheets(.Worksheets(1) _

.Range("B9:E23"))

End With End Sub

•Sub Move([Before], [After]) – використовуються з метою переміщення листів. До колекції його краще не застосовувати.

•Sub PrintPreview, Sub Printout – використовують з ме-

тою попереднього перегляду колекції робочих листів перед друком і длядруку колекції.

•Sub Select [Replace] – використовують з метою виділення сторінокколекції.

10.2.5. Властивості та методи об’єкта Worksheet. Об’єкт Worksheet (робоча сторінка) – головний тип сторінок робочої книги. Саме на цих сторінках здійснюються основні дії в комірках електронної таблиці. Головна особливість електронної таблиці полягає в тому, що в її комірки можна вводити не тільки дані, але й формули. При зміні даних електронної таблиці, ініційованих користувачем, зовнішнім посиланням або макросом програмного проекту, перераховуються усі формули.

З об’єктної точки зору окремі комірки електронної таблиці та області, що містять сукупності цих комірок, є об’єктами типу Range. Ці об’єкти є головними об’єктами робочої сторінки. Серед властивостей Worksheet передусім розглянемо властивості, які повертають деякий окремий об’єкт або колекцію об’єктів (власти- вості-учасники):

•Range(Cell1, [Се112]) As Range – повертає об’єктRange,

що визначається кутовими комірками Cell1 і Се112.

10. Програмування на VBA	213

•Cells As Range – повертає колекцію комірок електронної таблиці. Оскільки Cells одночасно є об’єктом Range і колекцією комірок, то можна використати індекси, щоб добратися до окремого елемента колекції (комірки електронної таблиці).

•Rows As Range і Columns As Range – повертають, відповід-

но, колекції рядків і стовпців таблиці. За індексом можна добратися до окремого рядка або стовпця таблиці. Водночас ці колекції є об’єктами Range, оскільки задають деяку область робочої сторінки.

•UsedRange As Range – повертає область робочої сторінки, що використовується. Зазвичай, лише незначна частина робочої сторінки зайнята даними, формулами, малюнками чи діаграмами. Властивість UsedRange дає змогу отримати мінімальну прямокутну область, що використовується.

•Circular Reference As Range – повертає об’єкт Range, що містить перше циклічне посилання на робочій сторінці. При відсутності такого посилання повертається значення Nothing.

Отже, одну і ту ж область таблиці (об’єкт Range) можна отримати різними способами. Наведемо приклад, що демонструє два способи отримання комірки "С5":

Debug.Print ActiveSheet.Range("C5")

Debug.Print ActiveSheet.Cells(5, 3)

У наступному прикладі робота йде над окремим стовпцем і рядком, але, фактично, й тут діє той самий об’єкт Range:

ActiveSheet.Columns(2).Value = "Так"

ActiveSheet.Rows( 1 ) .Value = "Hi"

ActiveSheet.Rows(1).Font.Bold = True

•Shapes – повертає колекцію, елементами якої є об’єкти класу Shape. Цю колекцію сформовано з об’єктів різних типів (малюнків, діаграм, графіків, вбудованих і пов’язаних об’єктів OLE, елементівкерування тощо).

•Names – повертає колекцію назвробочої сторінки.

•Comments – повертає колекцію коментарів робочої сторінки.

•QueryTables – повертає колекцію, елементами якої є об’єкти

214	Програмування мовою Visual Basic/VBA

класу QueryTable (таблиці, отримані на основі запиту до зовнішнього джерела даних– бази даних, Web-сторінки тощо).

•AutoFilter – повертає об’єкт, який здійснює фільтрацію даних.

•Next і Previous – повертають, відповідно наступну і попередню сторінкиробочої книги.

•PageSetup – задає параметри сторінки.

Розглянемо деякі атомарні властивості об’єкта Worksheet:

•CodeName – містить кодову назву робочої сторінки. Властивість маєстатустількидлячитання;

•Name – міститьназвуробочоїсторінки.

•EnableAutoFilter – вмикає/вимикає стрілки автофільтрації назахищенійсторінці.

•EnableCalculation – вмикає/вимикає автоматичне переобчисленняформулпризмініданих.

•EnablePivotTable – вмикає/вимикає елементи керування зведеноютаблицеюназахищенійсторінці.

•EnableSelection – вмикає/вимикає доступ комірок захищеної сторінки. Має три можливі значення: xlNoRestrictions (для вибору доступні всі комірки), xlNoSelection (всі комірки є недоступними для вибору) і xlUnlockedCells (відкритими є комірки, в яких властивість Locked має значення False).

•StandardHeight – повертає стандартну (за домовленістю) висоту всіх рядків. Властивість має статус тільки для читання (для всіх рядків одночасно змінити висоту не можна). Для кожного окремо взятого рядка можна змінити висоту, використовуючи властивість RowHeight.

•StandardWidth – повертає стандартну (за домовленістю) ширинувсіхрядків. Користувачможезмінюватицювластивість.

•Tуре – повертає тип робочої сторінки (xlWorksheet, xlExcel4MacroSheet, xlExcel4IntlMacroSheet). Власти-

вістьмаєстатустількидлячитання.

•Visible – вмикає/вимикає видимість робочої сторінки. За-

10. Програмування на VBA	215

галом – це булева властивість, що має значення True, якщо об’єкт видимий. Але програмно можна задати третє значення xlSheetVeryHidden – користувач не зуміє вручну добратися до прихованої робочої сторінки. Це здійснюють тільки програмно, змінивши значення цієї властивості на True або False.

Об’єкт Worksheet має чимало методів. Розглянемо спочатку методи, які є спільними для багатьох об’єктів і вже траплялися або ще будуть траплятися при описі інших об’єктів (це даватиме змогу нам уникнути зайвих подробиць):

•Activate – активізує робочу сторінку.

•Delete – видаляє робочу сторінку. Цей метод, як і нижченаведені методи Copy, Move, Select та інші, розглянуто при описі колекції робочих сторінок. Зазвичай саме при роботі з окремою сторінкою ці методи найчастіше застосовують.

•Сору – має два варіанти. У першому варіанті використовується без параметрів, копіюючи вміст робочої сторінки в буфер. У другому варіанті Copy(Before, After) створює копію сторінки, розміщуючи її перед або після сторінки, що викликала метод. Зрозуміло, що тільки один з двох параметрів – Before або After можна задати.

•Move(Before, After) – переміщує робочу сторінку, змінюючи порядок проходження сторінок у робочій книзі.

•Paste([Destination] [, Link]) – розташовує вміст бу-

фера на робочу сторінку. Можливий параметр Destination типу Range – діапазон, в який буде розташовано вміст буфера. Другий можливий параметр Link набуває значення True у випадку, коли встановлюється зв’язок з джерелом даних (за домовленістю має значення False). Одночасно можна задати тільки одинз цих параметрів.

•PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, Iconlndex, IconLabel) – також розташовує вміст буфера в область виділення робочої сторінки. Різниця полягає у тому, що метод застосовується тоді, коли вміст буфера зберігається у спеціальному форматі, відмінному від форма-

216	Програмування мовою Visual Basic/VBA

ту Excel. Найчастіше метод застосовують для розміщення об’єктів інших додатків. Параметр Format типу рядка задає формат об’єкта, що зберігається у буфері. Параметр Link має те ж значення, що і в попередньому випадку. Іншу групу параметрів використовують тоді, коли об’єкт “приклеюється” у вигляді позначки: DisplayAsIcon має значення True, IconFileName задає назву файлу, що містить значки, Iconlndex – індекс позначки у файлі, IconLabel – текст, пов’я- заний з позначкою. Оскільки об’єкт або значок належить до області виокремлення, то така виокремлена область повинна бути встановлена заздалегідь.

•Select([Replace]) – створює об’єкт Selection. Можли-

вий параметр Replace має значення True, якщо новий об’єкт замінює виокремлення, що раніше існувало, і False, коли відбувається розширення області виокремлення так, щоб вона охоплювала і новий об’єкт.

•CheckSpelling – перевіряє правопис робочої сторінки.

•Protect – захищає робочу сторінку від змін.

•Unprotect – відміняє захист робочої сторінки.

Розглянемо тепер методи, які раніше нетраплялися. Здебільшого ці методивідображають специфіку MS Excel:

•Calculate – здійснює обчислення формул робочої сторінки. Зазвичай властивість EnableCalculation увімкнена і обчислення відбуваються автоматично. Проте при вимкненій властивості EnableCalculation необхідно застосовувати цей метод для ініціювання обчислень.

•СlearArrows – видаляє стрілки трасування, встановлені для перегляду залежності при обчисленнях. Для програмного задання трасування використовуються методи ShowDependents і ShowPrecedents, які є методами об’єкта Range.

•Evaluate(Name) – перетворює назву в об’єкт або значення. Цей метод зручно застосовувати, коли користувач вводить назву під час діалогу. Наприклад, користувач вводить назву комірки, а йому повертається її значення, або користувач задає деякий

10. Програмування на VBA	217

вираз, що містить звернення до стандартних функцій, і отримує значення цього виразу.

•PivotTableWizard – створює зведену таблицю.

•ResetAllPageBreaks – відновлює початкове розбиття робочої сторінкина сторінки виведення.

•SetBackgroundPicture(Filename) – встановлює графіч-

ний фон для робочої сторінки або сторінки діаграм. Картинку для фону обирають з файла, назву якого задає FileName.

•ShowDataForm – вказує на форму даних, пов’язану з даною робочоюсторінкою.

10.2.6. Об’єкти Range і Selection. Об’єкти Range і Selection

належать до групи схожих об’єктів, що трапляються у різних додатках MS Office. Це головні об’єкти, з якими доводиться працювати програмісту. У додатку MS Word є чітка логіка в тому, як створюються об’єкти Range. Об’єкти верхнього рівня, наприклад, Document, мають метод Range, що дає змогу створити новий діапазон. Об’єкти дещо нижчого рівня, наприклад Paragraph, мають властивість Range, що повертає діапазон, пов’язаний з об’єктом. У MS Excel ситуація інша. Об’єкти Application і Worksheet мають тільки властивість Range. Цю ж властивість має і сам об’єкт Range, який представляє об’єкти нижнього рівня аж до комірки. Синтаксис цієї властивості такий:

Property Range(Cell1 [, Се112]) As Range

Об’єкт Selection у MS Excel виникає двояко: внаслідок роботи методу Select, або внаслідок виклику властивості Selection. Тип отриманого об’єкта може бути різним і визначається типом виокремленого об’єкта. Найчастіше об’єкт Selection належить класу Range і тоді при роботі з ним можна використати всі властивості та методи об’єктів класу Range.

Зазначимо, що Range є унікальним об’єктом – він може представляти як єдиний елемент таблиці, так і стовпець або рядок, деяку зв’язну і незв’язну прямокутну область, а також об’єднання і перетин усіх подібних елементів. Це ж саме стосується і об’єкта Selection. Параметри Cell1 і Се112 мають складний синтаксис,

218	Програмування мовою Visual Basic/VBA

який дає змогу, відповідно, повернути об’єкт Range складної конфігурації. Якщо при виклику властивості Range використовується тільки один параметр, то Cell1 може бути:

-назвою комірки, наприклад, – "А1";

-діапазоном комірок, наприклад, – "А1:В5";

-виразом над діапазонами, що містить операції об’єднання (кома) або перетину (пропуск), наприклад, – "А1:В5, Fl:G8"

або "А1:В5 A3:G8".

Якщо задаються обидва параметри Cell1 і Се112, то вони визначають прямокутну область, задану найменшим лівим верхнім кутом і максимальним правим кутом діапазонів, що визначаються параметрами. У цьому випадку параметри можуть бути і змінними класу Range. Розглянемо декілька простих прикладів.

Приклад 10.3. Реалізація простих обчислень у комірках:

Public Sub Work3()

ThisWorkbook.Worksheets("Sheetl").Activate

Range("C4").Value = 5

Range("C5").Formula = "=C4+2"

Range("C6:C8").Formula = "=C4+C5-1"

End Sub

Властивості Value і Formula об’єкта Range у цьому контексті є властивостями за домовленістю, отож оператори присвоєння можна ще записати так:

Range("C4") = 5 Range("C5") = "=С4+2" Range("С6:С8") = "=С4+С5-1"

Коли формула присвоюється діапазону комірок, то адреси комірок у формулі є відносними і змінюються при переході до наступної комірки діапазону. Отож формула, приписана комірці С7, матиме вигляд: "=С5+С6-1".

Приклад10.4. Відносністьадрескомірокщододіапазону:

10. Програмування на VBA	219
Public Sub Work4()
Dim myRange As Range
Set myRange = Range("Cl:C4")
myRange.Range("Al") = 7	' C1=7
myRange.Range("Bl") = 8	' Dl=8
myRange.Range("A2") = "=Al+2"	' C2=2

myRange.Range("A3:A5") = "=A1+A2" ' C3:C5=0

End Sub

Спочатку створюється об’єкт myRange (діапазон "С1:С4"). Виклик myRange.Range("Al") визначає комірку з адресою, яка обчислюється відносно об’єкта myRange (комірка С1). У формулах правої частини А1 і А2 прив’язані до адрес робочої сторінки.

Приклад10.5. Відносністьадрескомірокщодо виокремлення:

Public Sub Work5()
Range("Dl").Select
Selection.Range("Al") = 7	' Dl=7
Selection.Range("A2") = "=Cl+2"	' D2=2
Selection.Range("A3:A4") = "=C1+C2"	' D3:D4=0
End Sub
Приклад10.6. ВикликRange здвомапараметрами:
Public Sub Work6()
Dim myRange1 As Range
Set myRange1 = Range("El", "E6")	' 6
Debug.Print myRange1.Count
myRange1.Range("Al") = 27	' El=27
myRange1.Range("A2") = "=Dl+2"	' E2=2
myRange1.Range("A3:A6") = "=D1+D2"	' E3:E6=0
End Sub

10.2.7. Способи адресування комірок. У попередніх прикладах використано відносне адресування комірок у форматі А1. Адреса комірки у цьому форматі формується з назви стовпця (А, B, ..., Z,