VBA. Лабораторна робота 5.
Excel. Об’єктна модель Excel.
Об'єкти, властивості й методи
Кожний об'єкт Excel має у своєму розпорядженні набір властивостей (properties) і методів (methods). Прикладами об'єктів в Excel є книги, аркуші, діаграми, шрифти й т.п. Сюди включаються 192 об'єкта, у тому числі приховані й застарілі.
Властивості
Термін властивість у цьому випадку використовується у тому ж контексті, що й у звичайній українській мові. Це особливість, атрибут або характеристика об'єкта. Наприклад, об'єкт Worksheet має 55 властивостей, у число яких входять Cells, Mate, ProtectionMode і UserRange. Значення властивості може належати до будь-якого дійсного типу даних, скажемо, Integer, Single, String або навіть до одного з об'єктних типів даних.
Якщо значення властивості належить до типу Integer, воно називається цілочисельним. Цілочисельні властивості настільки поширені, що компанія Microsoft створила велику кількість вбудованих перечислень (їхнє число досягає 152, з 1266 індивідуальними константами), що дозволяють привласнювати цим значенням символьні імена. Наприклад, властивість Calculation об'єкта Application може приймати будь-яке значення з наступного перерахування:
Enum XlCalculation
xlCalculationManual = 4135
xlCalculationAutomatic = -4105
xlCalculationSemiatomatic = 2
End Enum
Значення властивості може бути об'єктом. Наприклад, властивість ActiveChart об'єкта Workbook повертає об'єкт Chart, що має свій власний набір властивостей і методів.
Тому що об'єкт Chart можна отримати з об'єкта Workbook, його називають дочірнім (child object). Відповідно, об'єкт Workbook буде батьківським (parent) стосовно об'єкта Chart.
Методи
Методом (method) називається дія, що може бути виконана над об'єктом. Наприклад, з об'єктом Worksheet зв'язаний метод Protect, що захищає аркуш від редагування.
У термінах програмування властивості й методи об'єкта є всього лише вбудованими функціями й підпрограмами. Важливо підкреслити, що в деяких випадках можна зіштовхнутися з досить довільним трактуванням даних термінів. Наприклад, елемент Item іноді називається властивістю, а іноді методом, залежно від розглянутого об'єкта. Іноді навіть співробітники компанії Microsoft не можуть точно визначитися.
Властивості й методи мають загальну назву: члени об'єкта (members). Їх не варто плутати з дочірніми об'єктами.
Сімейства об'єктів
При роботі з об'єктною моделлю Excel (втім, як і з будь-якою іншою об'єктною моделлю) часто потрібно обробити кілька об'єктів відразу. Наприклад, кожна комірка у виділеному наборі є окремим об'єктом (об'єкт Range), як і кожен стовпець і рядок у відкритому аркуші. Для таких випадків був розроблений спеціальний об'єкт, названий сімейством (collection object).
Звичайно сімейства складаються з об'єктів одного типу. Наприклад, в об'єктній моделі Excel існує сімейство Rows, у яке входять всі рядки розглянутого аркуша. Для кожного відкритого аркуша існує своє сімейство Rows. Властивості й методи об'єкта Collection сконструйовані з урахуванням необхідності керування сімейством.
У загальному випадку пізнати сімейство об'єктів можна по його назві, що являє собою множину від назви вхідних у сімейство об'єктів. Наприклад, набір Worksheets містить об'єкти Worksheet. Однак із цього правила існує ряд виключень. Наприклад, набір Rows містить об’єкти Range. В об'єктній моделі Excel не існує таких об'єктів, як Cell, Row або Column. Замість них використовується об'єкт Range.
Сімейства повсюдно зустрічаються в об'єктній моделі Office. Більше того, до них належить майже половина всіх об'єктів об'єктної моделі Excel. У табл. 9.1 перераховані найбільше часто використовувані в Excel сімейства.
Таблиця 9.1. Деякі сімейства об'єктної моделі Excel
Areas FormatConditions Series Collection
Axes LegendEntries Sheets
Borders Names Windows
ChartObjects PivotFields Workbooks
Charts PivotTables Worksheets
DataLabels Points
Filters Range
Ще раз підкреслимо, що сімейство являє собою всього лише спеціальний тип об'єктів, що задовільняє наступним вимогам:
Наявність властивості Count, що повертає число об'єктів набору. Цю властивість неможливо відредагувати вручну, вона автоматично оновлюється редактором VBA.
Наявність методу Add, що дозволяє додавати в сімейство нові об'єкти.
Наявність методів Remove, Close і Delete або їм подібних, що дозволяють видаляти об'єкти із сімейства.
Наявність методу Item, що дає доступ до будь-якого об'єкта сімейства. Об'єкт при цьому звичайно визначається по імені або по індексу.
Перераховані вище вимоги не є суворими. Існують як сімейства, що не володіють будь-якою із зазначених властивостей, так і сімейства з безліччю додаткових властивостей і методів. Наприклад, сімейства Areas і Borders не мають метод Add, тобто користувач не має змоги додавати туди нові об'єкти.
В Excel існують і дуже складні сімейства, що володіють рядом властивостей і методів, властивих винятково типу об'єктів, що у них утримуються. Наприклад, із сімейством Sheet пов'язані 10 властивостей і 8 методів. Деякі з них, наприклад метод PrintOut, включені спеціально, щоб дати можливість одночасно обробляти всі вхідні в сімейство аркуші. У цьому випадку під терміном sheet мається на увазі як аркуш, що містить звичайні дані, так і аркуш із діаграмою.
Нумерація членів сімейства
Нумерація членів сімейства може починатися як з нуля, так і з одиниці. Більшість сімейств в Excel і в VBA відноситься до другого виду. Але деякі більш старі сімейства мають нумерацію, що починається з нуля. Я думаю, що фірма Microsoft одержала безліч скарг із приводу наявності таких сімейств, і тому було вирішено поміняти їхню нумерацію.
Перед звертанням до членів сімейства по номеру важливо визначити, до якого виду це сімейство відноситься. У деяких випадках необхідну інформацію можна знайти в довідковій системі VBA, але простіше скористатися зразком коду. Наприклад, код:
For i = 1 To Selection.Cells.Count
Debug.Print Selection.Cells(i).Value
Next i
повністю коректний, тому що нумерація сімейства Cells починається з одиниці. Однак нумерація сімейства UserForm, в яку входять всі нестандартні діалогові вікна, завантажені в цей момент, починається з нуля, тому код
For i = 1 To UserForms.Count
Debug.Print UserForms(1).Value
Next i
приведе до появи повідомлення про помилку. Правильний код виглядає так:
For i = 0 To UserForms.Count -l
Debug.Print UserForms(i).Value
Next i
Він повідомляє про кількість завантажених в активний проект нестандартних діалогових вікон.
Ієрархія об'єктної моделі
Той факт, що деякі об'єкти Excel включені в інші об'єкти, має першорядну важливість і додає в об'єктну модель дуже корисну структуру.
У процесі читання різних джерел створюється враження, що не існує однозначної відповідності між батьківськими й дочірніми об'єктами. У нашому випадку, якщо об'єкт А має властивість або метод, що повертає об'єкт В, то об'єкт А буде батьківським відносно об'єкта В. Наприклад, об'єкт Range має властивість Font, що повертає об'єкт Font. Відповідно, об'єкт Range є батьківським стосовно об'єкта Font. При цьому останній є дочірнім також відносно об'єкта ChartArea. Тобто число предків і нащадків об'єкта необмежено.
Не варто розуміти аналогію предок-нащадок дослівно. Пам’ятайте, що ієрархія об'єктів наповнена замкнутими співвідношеннями предок-нащадок. Наприклад, об'єкт Range є дочірнім відносно об'єкта Worksheet, а об'єкт Worksheet - відносно об'єкта Range. У більшості моделей багато об'єктів мають властивості, які повертають самий верхній об'єкт в ієрархії моделі. В об'єктній моделі Excel практично кожен об'єкт має властивість Application, що повертає об'єкт Application, розташований на верхньому рівні ієрархії. Відповідно, практично кожен об'єкт у цьому випадку можна вважати батьківським відносно об'єкта Application.
Ієрархія об'єктів в об'єктній моделі часто зображується у вигляді деревоподібної структури, невелика частина якої показана на рис.9.1

Рис. 9.1. Частина об'єктної моделі Excel. Тег <v> означає, що об'єкт вперше з'явився у версії X додатка Excel
Синтаксис об'єктної моделі
Прийшов час обговорити базовий синтаксис, що використовуєтся при програмуванні об'єктної моделі.
Посилання на властивості й методи об'єкта здійснюються досить просто. Якщо objVar - змінна об'єкта, а AProperty - властивість цього об'єкта, то доступ до читання й редагування даної властивості здійснюється в такий спосіб: objVar.AProperty(необхідні параметри)
Наприклад, наступний код задає шрифт у першому ряді активного аркуша:
' Опис змінної об'єкта
Dim rng As Range
' Зіставлення змінної rnd першого ряду
Set rng = ActiveSheet.Rows(1)
' Вказання імені шрифту
Rng.Font.Name = “Arial”
Останній рядок коду активує дві властивості; властивість Font змінної rng повертає об'єкт Font, властивості Name якій привласнене значення Arial.
Метод AMethod для об'єкта objVar можна викликати за допомогою інструкції:
objVar.AMethod (необхідні параметри)
Зверніть увагу, що в цьому випадку використовується практично той самий синтаксис, що й при виклику звичайних підпрограм і функцій. Просто тепер разом з ім'ям об'єкта потрібно вказати властивість або метод, які викликаєм. Наприклад, можна застосувати метод CheckSpelling до ряду комірок, позначеному змінною rng:
rng.CheckSpelling
Як параметр даного методу використовується, наприклад, ім'я словника користувача.
Об'єктні змінні
Як для доступу до властивості об'єкта, так і для виклику методу можна застосовувати прямий і непрямий підхід. В останньому випадку використовується об'єктна змінна.
Наприклад, призначення властивості Bold об'єкту Font у першому рядку активного аркуша прямим методом виглядає таким чином:
ActiveSheet.Rows(1).Font.Bold = True
Як альтернативу можна використовувати об'єктну змінну. Ось два варіанти її застосування:
Dim rng As Range
Set rng = ActiveSheet.Rows(l)
Rng.Font.Bold = True
Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
Fnt.Bold = True
Об'єктні змінні грають більш важливу роль, чим це може здатися на перший погляд. Найбільш значимою причиною їхнього використання є можливість збільшити читаність коду при наявності декількох посилань на той самий об'єкт. Наприклад, замість того, щоб писати:
ActiveSheet.Rows(1).Font.Bold = True
ActiveSheet.Rows(l).Font.Italic = True
ActiveSheet.Rows(1).Font.Underline = False
ActiveSheet.Rows(1).Font.Size = 12
ActiveSheet.Rows(1).Font.Name = "Arial".
можна використати об'єктну змінну Font. У результаті код буде виглядати таким чином:
Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
fnt.Bold = True
fnt.Italic = True
fnt.Underline = False
fnt.Size = 12
fnt.Name = "Arial"
Оператор With
Оператор With у мові VBA дозволяє істотно скоротити звертання до об'єкта, що значно зменшує обсяг коду. Ось як буде виглядати після застосування цього оператора код з попереднього розділу:
Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
With fnt
.Bold = True
.Italic = True
.Underline = False
.Size = 12
.Name = "Arial"
End With
Загальний синтаксис розглянутого оператора виглядає таким чином:
With об'єкт
‘Тут йдуть оператори
End With
Оператори в цьому випадку відносяться до об'єкта, але при цьому вам не потрібно знову й знову використовувати ім'я цього об'єкта.
Скорочення часу виконання програми
Основним достоїнством об'єктних змінних є не стільки поліпшення читаності коду, скільки збільшення продуктивності. Наприклад, для виконання кожного з п'яти рядків у першій версії попереднього коду потрібно розпізнати посилання на різні об'єкти Excel, а саме ActiveSheet, Rows(1) і Font. Тобто доводиться спускатися вниз по об'єктній моделі, що забирає час.
Якщо ж код використовує об'єктну змінну типу Font, розпізнавання посилання відбувається тільки один раз і, відповідно, швидкість виконання зростає. При наявності сотень або навіть тисяч посилань, які потрібно розпізнати, економія часу може виявитися досить значною.
Посилання на об'єкт
Між об'єктними і необ'єктними змінними існує велика різниця. Як уже згадувалося, необ'єктну змінну можна розглядати як місце в пам'яті комп'ютера, у якому зберігаються якісь дані. Розглянемо, наприклад, код
Dim iVar As Integer
iVar = 123
Тут змінна iVar являє собою 4-байтну комірку в пам'яті, де зберігається значення 123. У графічному вигляді це показано на рис. 9.2. У дійсності значення 123 зберігається в бінарному форматі, але для даного обговорення це несуттєво.

Рис. 9.2. Змінні типу Integer у пам'яті
Якщо далі написати:
Dim iVar2 As Integer
iVar2 = iVar
iVar2 = 567.
не варто очікувати, що останній рядок коду вплине на значення змінної iVar. Воно як і раніше залишиться рівним 123. Це пов'язане з тим, що змінні iVar і iVar2 представляють різні області пам'яті.
У свою чергу, об'єктна змінна є місцем у пам'яті, де зберігається адреса посилання на об'єкт, як показано на рис. 9.3. Саме тому об'єктні змінні іноді називають змінними-вказівниками. На малюнку об'єктна змінна rng вказує на об'єкт типу Range, а саме на перший стовпець активного аркуша.

Рис. 9.3. Об'єктна змінна в пам'яті
Ось як виглядає код, що відповідає рис. 9.3:
Dim rng as Range
Set rng = ActiveSheetColumns(1)
Посилання на той самий об'єкт може здійснюватися з різних змінних. Наприклад:
Dim rng as Range
Dim rng2 as Range
Set rng = ActiveSheet.Columns(1).
Set rng2 = rng
Ситуація, описана цим кодом, показана на мал. 9.4.

Рис. 9.4. Дві об'єктні змінні вказують на той самий об'єкт
Ще раз підкреслимо, що різні об'єктні змінні rng і rng2 мають те саме значення й, отже, вказують на той самий об'єкт. Тобто для редагування першого стовпця активного аркуша можна використати кожну із цих змінних.
У процесі програмування важливо відслідковувати всі об'єктні змінні й об'єкти, на які вони вказують. Саме тому описана вище ситуація, коли на один об'єкт вказують дві змінні, повинна виникати як можна рідше й тільки при наявності вагомих причин. Дуже легко забути, що вигляд об'єкта був змінений за допомогою першої змінної (наприклад, rng), і, використовуючи другу змінну (rng2), думати, що ви працюєте з вихідним об'єктом.
Ключове слово Nothing
Для очищення значення об'єктної змінної використовується ключове слово Nothing.
Наприклад:
Set rng2 = Nothing
Вважається гарним стилем програмування звільняти об'єктну змінну, коли в ній відпадає потреба, тому що це заощаджує ресурси системи. Втім, об'єктній зміннй це значення привласнюється автоматично після завершення її часу життя.
Майте на увазі, що після звільнення змінна автоматично знищується, звільняючи ресурси. Однак перед цим повинні бути знищені всі посилання на об'єкт. Це ще одна причина не ставити у відповідність тому самому об'єкту більше однієї об'єктної змінної.
Оператор Is
Для порівняння значень двох звичайних змінних досить написати:
If Varl = Var2 Then...
Однак щоб порівняти дві об'єктні змінні й визначити, чи не вказують вони на той самий об'єкт, використовується інший синтаксис. Ця операція виконується за допомогою оператора Is:
If rng Is rng2 then...
Так само перевіряється, чи вільна потрібна вам змінна:
If rng Is Nothing Then...
Однак даний оператор у версіях VBA для Office 97 і Office 2000 не завжди працює коректно. Компанія Microsoft інформована про цю проблему. Наприклад, код:
Dim Wks As Worksheet
Dim Wks2 As Worksheet
Set Wks = ActiveSheet
Set Wks2 = ActiveSheet
MsgBox Wks Is Wks2
покаже як результат коректне значення True. У той час як аналогічний код:
Dim rng As Range
Dim rng2 As Range
Set rng = ActiveSheet.Rows(l)
Set rng2 = ActiveSheet.Rows(l)
MsgBox rng Is rng2
приведе до появи невірного значення False. Щоб виправити ситуацію, потрібно записати передостанній рядок у такий спосіб:
Set rng2 = rng
У цьому випадку інформаційне вікно покаже результат True.
Параметри об'єктної змінної за замовчуванням
У більшості об'єктних моделей багато змінних мають заданий за замовчуванням набір властивостей і методів, що використовується при відсутності явної вказівки їх на властивості й методи. Наприклад, в об'єктній моделі Microsoft Word змінна Range за замовчуванням має властивість Text. Саме тому код:
Dim rng As Range
Set rng = ActiveDocument.Words(1)
rng = "Маша"
задає як перше слово активного документа слово "Маша". Адже прикладна програма Word в останньому рядку коду зіставляє зі змінною задану за замовчуванням властивість, звільняючи користувача від необхідності писати:
rng.Text = "Маша"
На жаль, ні в документації по Excel VBA, ні в об'єктній моделі Excel не можна знайти згадування про те, які параметри привласнюються змінним за замовчуванням. Тому я пропоную не звертати пильну увагу на цьому питанні в процесі програмування. Тим більше що використання цих параметрів зменшує читаність коду.
Виключенням є сімейства об'єктів. Звичайно їм за замовчуванням зіставляється метод Item. Саме тому можна писати
Selection.Cells(4)
замість більш громіздкого
Selection.Cells.Item(4)
У цьому випадку використання заданого за замовчуванням методу не створить ніякої плутанини, так що цим цілком можна користуватися.
Глобальні параметри
Багато властивостей і методів об'єкта Application можна використовувати без вказування імені цього об'єкта. Це так звані глобальні параметри. Наприклад, до них відноситься властивість Selection, що дозволяє писати:
Selection.Cells.Count
замість
Application.Selection.Cells.Count
Для перерахування глобальних параметрів існує спеціальний об'єкт Global, параметри якого є суворою підмножиною параметрів об'єкта Application. Інакше кажучи, не всі властивості й методи об'єкта Application відносяться до глобальних.
У табл. 9.2 перераховані глобальні параметри об'єктної моделі Excel.
_Evaluate CommandBars Parent
_Run2 Creator Range
ActiveCell DDEAppReturnCode Rows
ActiveChart DDEExecute Run
ActiveDialog DDEInitiate Selection
ActiveMenuBar DDEPoke SendKeys
ActivePrinter DDERequest Sheets
ActiveSheet DDETerminate ShortcutMenus
ActiveWindow DialogSheets ThisWorkbook
ActiveWorkbook Evaluate Toolbars
Addlns Excel4IntMacroSheets Union
Application Excel4MacroSheets Windows
Assistant ExecuteExcel4Macro Workbooks
Calculate Intersect WorksheetFunction
Cells MenuBars Worksheets
Charts Modules
Columns Names
1О Прикладні програми Excel
Прикладні програми Office можна визначити як документ Office (наприклад, база даних Access, книга Excel, документ або шаблон Word і презентація PowerPoint), що містить спеціальні налаштування. Ці налаштування звичайно являють собою комбінацію процедур VBA і налаштувань меню і/або панелей інструментів для спрощення або автоматизації певних завдань. Сюди можуть входити службові програми (utilities), що виконують такі завдання, як, наприклад, роздруківка тексту або сортування.
Подібне визначення дає достатню свободу дій. Наприклад, додавши до шаблона Word ще один пункт меню, що просто вставляє підпис («Щиро ваш» і т.п.) у кінець документа, ми можемо розглядати цей видозмінений шаблон як прикладну програму Word. Однак сумнівно, щоб на подібну прикладну програму знайшовся покупець.
Тут ми хочемо підкреслити, що прикладна програма Office відрізняється від традиційних прикладних програм Windows, таких як, наприклад, Excel. Традиційні прикладні програми Windows вбудовані в основний виконавчий файл. У випадку з Excel це файл excel.exe. Зрозуміло, така складна прикладна програма, як Excel, включає безліч допоміжних файлів. Сюди входять виконавчі файли, довідкові файли, файли бібліотек об'єктних модулів, файли ресурсів, інформаційні файли, керуючі файли Active і всюдисущі файли DLL.
Прикладні програми Office не мають відношення до автономних виконавчих файлів. Вони створюються усередині документів Office. Зокрема, додатки Access створюються усередині баз даних Access, прикладної програми Excel - усередині книг Excel, прикладної програми Word - усередині документів Word, а прикладної програми PowerPoint - усередині презентацій PowerPoint. Прикладні програми Office також можуть бути створені всередині шаблонів Office або вбудованих програм.
У зв'язку із цим виникає цілий ряд питань із приводу поширення прикладних програм Office. При їхній розробці потрібно вирішити, куди помістити код прикладної програми і які засоби надати користувачу для доступу до функцій цього додатка. Перше питання ускладнюється тим, що необхідно вирішити, чи варто надавати користувачу доступ до коду прикладної програми чи ні.
Відповіді на ці питання, як нескладно догадатися, залежать від природи прикладної програми.
Доступ до функцій прикладних програм
Нещодавно я створив прикладну програму Excel для відомої мережі ресторанів швидкого харчування. Цій фірмі було потрібно оперативно відсилати дані про продажі в периферійні офіси у формі досить складних зведених таблиць Excel. Фахівці з експлуатації повинні були фільтрувати ці таблиці за різними значеннями (формуючи тим самим більш дрібні зведені таблиці) і створювати різні діаграми. Прикладна програма мала і інші функції, але перерахованого цілком достатньо для ілюстрації суті того що відбувається.
Зокрема, основна зведена таблиця містила кілька типів даних (продажу, підрахунок перерахувань і т.п.) для ряду певних областей продажу і типів магазинів. Однією з функцій цієї прикладної програми була службова програма для створення діаграм. Але куди помістити цей код і як надати фахівцям з експлуатації доступ до формуючої діаграми службової програми?
Тому що ця службова програма напряму містить у собі зведену таблицю, здається розумним помістити кнопку з написом Make Chart(s) (Створити діаграму) безпосередньо на аркуш із цією таблицею. Натискання цієї кнопки призводить до появи діалогового вікна, вказаного на рис. 10.1. Це вікно дозволяє користувачу вибирати різні типи даних і створювати на їхній основі одну або кілька діаграм.

Рис. 10.1. Діалогове вікно службової програми, призначеної для створення діаграм
Забезпечити доступ до формуючої діаграми службової програми, тобто до показаного на рис. 10.1 діалогового вікна, можна декількома способами. Втім, ці способи підходять для доступу до будь-якого макросу.
Вибір потрібного макросу в діалоговому вікні Macro (Макрос), що викликається командою Tools > Macro > Macros (Сервіс > Макрос > Макроси). Це найбільш ефективний спосіб для користувачів, які пишуть макроси й хочуть мати можливість їхнього швидкого запуску. Але тому що в цьому вікні показані тільки назви макросів, навряд чи такий спосіб підходить для користувачів, які не мають поняття про їхні функції. Цей спосіб також навряд чи варто рекомендувати для запуску макросів, що часто використовуються.
Якщо зв'язати код службової програми з подією Open, діалогове вікно буде з'являтися автоматично при відкритті книги Excel. Докладна розмова про події піде в наступній главі.
Помістити кнопку виклику діалогового вікна безпосередньо на аркуш.
Помістити кнопку виклику діалогового вікна на одну з існуючих панелей інструментів Excel. Це можна зробити як програмними методами, так і за допомогою інтерфейсу користувача.
Створити нову панель інструментів, на якій буде розміщена кнопка виклику діалогового вікна. Це також можна зробити як програмно, так і за допомогою інтерфейсу користувача.
Додати новий пункт в одне з існуючих меню Excel.
Створити нове меню й додати туди команду, що викликає потрібне діалогове вікно.
У моєму випадку було потрібно, щоб користувач не міг викликати службову програму, що формує діаграми, якщо активний аркуш не містить зведеної таблиці. Тому був обраний варіант із розміщенням кнопки безпосередньо на аркуші. Однак не можна сказати, що інші варіанти в цьому випадку не будуть працювати.
З іншого боку, якщо розглянута прикладна програма має більш широку область застосування, імовірно, має сенс створити кнопку на панелі інструментів або додати відповідний пункт у меню. Я віддаю перевагу другому варіанту, тому що в цьому випадку службову програму можна легко викликати за допомогою клавіатурних комбінацій.
Інакше кажучи, рішення про спосіб доступу до функцій додатка Office залежить від ряду речей, включаючи складність прикладної програми, набору його функцій і ваших власних побажань.
Інтерактивна робота з панелями інструментів і меню
Поза залежністю від того, де розміщається командна кнопка для виклику макросу - на існуючій раніше або спеціально створеній панелі інструментів, за допомогою інтерфейсу Excel потрібно вказати, коли буде з’являтися ця панель інструментів. Для створення нових і для показу прихованих панелей інструментів використовується вкладка Toolbars (Панелі інструментів) діалогового вікна Customize (Налаштування), що показано на рис. 10.2. Його виклик здійснюється за допомогою команди Customize (Налаштування) меню Tools (Сервіс).

Рис. 10.2. Діалогове вікно Customize, відкрите на вкладці Toolbars

Рис. 10.3. Діалогове вікно Customize, відкрите на вкладці Commands
Для створення нової панелі інструментів клацніть на кнопці New (Створити). З'явиться діалогове вікно New Toolbar (Створення панелі інструментів), в якому потрібно ввести ім'я нової панелі інструментів. Після призначення унікального імені прикладної програми Excel створить нову панель інструментів і помістить її ім'я в список Toolbars (Панелі інструментів). Тепер можна заповнити цю панель кнопками.
Щоб сховати або зробити видимою будь-яку панель інструментів, досить зняти або встановити прапорець напроти назви потрібної панелі в списку Toolbars (Панелі інструментів).
Також можна створити нове підменю, що додається до існуючого меню або панелі інструментів. Ця операція виконується на вкладці Commands (Команди) діалогового вікна Customize (Налаштування), як показано на рис. 10.3.
У списку Categories (Категорії) потрібно вибрати варіант New Menu (Нове меню) і перетягнути пункт New Menu (Нове меню) на потрібне меню або панель інструментів. Залишиться тільки клацнути правою кнопкою миші й ввести ім'я нового меню в поле Name (Ім'я).
Призначення макросів меню і панелям інструментів
На вкладці Commands (Команди) діалогового вікна Customize (Налаштування) також можна призначати макроси існуючої меню і панелям інструментів.
Хоча багато користувачів вважають процес налаштування меню і панелей інструментів в Office надто складним й заплутаним, це помилкова думка. Досить пам'ятати, що початково меню й панелі інструментів перебувають у режимі виконання (run mode). Тобто вибір пункту меню або натискання кнопки на панелі інструментів призводить до виконання відповідних дій. Але коли ви викликаєте діалогове вікно Customize (Налаштування), меню і панелі інструментів переходять у режим редагування (edit mode). У цьому режимі вибір пункту меню або натискання кнопки панелі інструментів призводять до зовсім іншого ефекту. Зокрема, клацання правою кнопкою миші на пункті меню призводить до появи переліку його властивостей. Крім того, можна переміщати, видаляти або додавати пункти меню, просто рухаючи їх мишею.
УВАГА
________________________________________________________________________
У режимі редагування перенос пункту меню (або кнопки) з меню (або з панелі інструментів) призводить до видалення цього елемента. Тому потрібно бути дуже уважним, щоб не зробити це ненавмисно.
Для створення кнопки на панелі інструментів або пункту меню, які призводять до виконання певного макросу, потрібно вибрати варіант Macro (Макрос) у списку Categories (Категорії) діалогового вікна Customize (Налаштування) і перетягнути макрос зі списку Commands (Команди) на потрібне місце в меню або на панелі інструментів. Завдання виконане!
Необхідно згадати, що хоча описаний вище процес підходить для розроблювачів, навряд чи варто змушувати клієнта виконувати подібні дії. Альтернативою є програмне створення нового елемента інтерфейсу.
Місце зберігання прикладних програм
У випадку з прикладною програмою Excel для мережі ресторанів швидкого харчування всі дані зберігалися в одній книзі. Це було цілком припустимо, тому що там не було даних, які треба було би приховати від користувача. У результаті книга стала зовсім самостійним об'єктом, для якого не була потрібна процедура встановлення. Досить переслати потрібну книгу з головного офісу у відділення по електронній пошті. Однак навіть у цьому випадку є кілька варіантів дій.
Зберігати прикладну програму і її дані безпосередньо в документі, у якому вони будуть використовуватися. Це підходить для самостійних прикладних програм, подібних показаним на рис. 10.1, і для невеликих макросів, які повсюдно зустрічаються в даній книзі як приклади.
Зберігати прикладну програму і її дані в шаблоні Excel. Це підходить, якщо шаблон буде основою для набору таблиць.
Зберігати прикладну програму і її дані в прихованій книзі Excel у завантажувальній папці цієї програми.
Зберігати прикладну програму і її дані в надбудовах Excel.
Кожний із цих варіантів має свої недоліки й переваги, різні для різних прикладних програм Office. Наприклад, шаблони набагато частіше використовуються в Word, чим в Excel, а надбудови найкраще застосовувати в Excel, а не в Access.
Завантажувальна папка Excel
При завантаженні Excel автоматично завантажуються всі великоформатні таблиці, що зберігаються в завантажувальній або в дублюючій її папці. За замовчуванням це підпапка основної папки Excel, що називається XlStart. Початково дублююча папка відсутня, але її можна задати на вкладці General (Загальні) діалогового вікна Options (Параметри).
Тому що вміст цих папок відкривається при запуску Excel у вигляді звичайної книги, їхні макроси можна легко використати в інших книгах. Саме тому дані папки є ідеальним місцем для збереження макросів. Єдиний недолік полягає в тому, що Excel у дійсності відкриває таблиці, що зберігаються в цих папках. Щоб це запобігти, потрібно вибрати команду Hide (Сховати) у меню Window (Вікно) для активного аркуша.
Для доступу до макросів, що зберігаються в даних папках, використовується діалогове вікно Macro (Макрос). Потім за допомогою інтерфейсу користувача Excel ці макроси можна призначити панелі інструментів або меню. Втім, надбудови, про які піде мова трохи пізніше, призначаються панелям інструментів або меню винятково програмним чином.
Книги, що зберігаються в кожній із цих папок, можна використати як бібліотеку макросів, що доступна для кожного з інших аркушів. У них можна зберігати також створені вами макроси, хоча надбудови, з якими ви докладно познайомитеся трохи пізніше, мають більшу гнучкість і можливість налаштування.
Шаблони Excel
Шаблони Excel є відправною крапкою для нових книг, аркушів, діаграм і модулів коду. Створити шаблон дуже просто. Досить відкрити нову книгу й зберегти її у форматі xlt за допомогою команди Save As (Зберегти як).
Припустимо, що була відкрита нова книга й у першу комірку першого аркуша було введене число 123. Потім ця книга була збережена у вигляді шаблона test.xlt. При наступному виборі команди New (Створити) у меню File (Файл) з'явиться діалогове вікно, показане на рис. 10.4. Залишиться виділити значок test.xlt і клацнути на кнопці OK, і прикладна програма Excel відкриє нову книгу й скопіює в неї дані з шаблону.

Рис. 10.4. Діалогове вікно New зі значками шаблонів
Важливо розуміти, що як дані (у тому числі і їхнє форматування), так і макроси копіюються в нову книгу після того, як будуть розірвані всі зв'язки між нею й шаблоном. Це докорінно відрізняється від роботи у прикладній програмі Word, у якій шаблони пов'язані з документом. Зміни певного характеру, наприклад додавання стилів або макросів, можна зберегти як у шаблоні, так і в самому документі, але Word ніколи не копіює макроси із шаблона в документ. Крім того, існує можливість відкрити одночасно кілька шаблонів, кожний з яких буде якимось чином впливати на вид документа. Тобто шаблони Word є динамічними, у той час як шаблони Excel - статичними.
Це значно зменшує корисність шаблонів Excel. Наприклад, кожна книга, заснована на шаблоні, що містить велику кількість коду, буде постачена копією даного коду. У деяких випадках це є даремною витратою місця й сильно ускладнює керування кодом і його відновлення. Із цієї причини я намагаюся, по можливості, не користуватися шаблонами Ехсеl.
Отже, перелічимо елементи, які передаються в нову книгу, відкриту на основі шаблона:
число й тип аркушів;
форматування комірок і аркуша, створене за допомогою команд меню Format (Формат);
стилі комірок;
формати сторінок і налаштування області друку для кожного аркуша;
вміст комірок;
графіка, що використовується на кожному з аркушів;
панелі інструментів користувача, макроси, гіперпосилання, елементи керування Active (панелі інструментів користувача мають бути приєднані до шаблона);
захищені й приховані області книги;
параметри обчислень книги.
Прикладна програма Excel також підтримує кілька типів спеціальних шаблонів, називаємих автошаблонами (autotemplate). Ось їхні імена:
Book.xlt
Sheet.xlt
Chartxlt
Dialog.xlt
Module.xlt
Macro.xlt (для макросів четвертої версії Excel)
Якщо в папці XlStart перебуває шаблон Book.xlt, всі книги, створені при виборі значка Workbook у діалоговому вікні, показаному на мал. 10.4, створюються на основі цього шаблона.
Якщо потрібно, щоб нова книга була відформатована особливим чином, досить створити шаблон Sheet.xlt і помістити його в папку XlStart. У результаті при кожному виклику команди Insert > Worksheet (Вставка > Аркуш) Excel буде створювати копії всіх аркушів шаблона Sheet.xlt і поміщати їх у поточну книгу. Майте на увазі, що буде створена саме та кількість аркушів, що містяться в шаблоні.
Інші автошаблони працюють подібним чином.
Важливо пам'ятати, що всі прикладні програми Office використовують для шаблонів ту саму задану за замовчуванням папку. Тобто в цій папці можуть одночасно зберігатися шаблони для прикладних програм Word, Excel, PowerPoint і Access. При цьому єдиною прикладною програмою, що дозволяє користувачеві міняти папку зберігання шаблонів, є Word. Це можна зробити на вкладці File Locations (Розташування) діалогового вікна, викликаємого командою Options (Параметри) з меню Tools (Сервіс). Але майте на увазі, що вказівка нової адреси папки із шаблонами в Word приведе до зміни адреси цієї папки для інших прикладних програм Office!
Надбудови Excel
Надбудови Excel являють собою спеціальний тип книги, що зберігається з розширенням х1а. Для зв'язку надбудов з Excel потрібно встановити прапорець напроти імені надбудови в діалоговому вікні Add-Ins (Надбудови), показаному на рис. 10.5. Для його виклику використовується команда Add-Ins (Надбудови) з меню Tools (Сервіс).
Надбудову достатньо приєднати один раз. Навіть перезапуск прикладної програми Excel не зробить впливу на її зв'язок з документом. Для розриву цього зв'язку потрібно зняти відповідний прапорець у діалоговому вікні Add-Ins (Надбудови). Функції надбудов доступні у всіх книгах Excel. Тобто про них можна говорити як про розширення Excel.
Код надбудов призводить до появи нових пунктів у меню або кнопок на панелях інструментів. Таким чином, користувачеві надається доступ до процедур, що виконуються надбудовою. Цей код пов'язаний з подією Workbook_Open, завдяки чому меню (або панелі інструментів) створюються/редагуються в момент приєднання надбудови.

Рис. 10.5. Діалогове вікно Add-Ins
Створення надбудов
Створення надбудови являє собою досить простий процес. Почнемо його із книги, що називається SRXUtils.xls. Зрозуміло, дана книга містить ряд макросів. Щоб створити для неї надбудову, користуйтесь наступною інструкцією.
Компіляція проекту за допомогою редактора VBA. При редагуванні й виконанні коду будь-якої процедури VBA спочатку потрібно зробити компіляцію цього коду, тобто його переклад на мову, що зрозуміла комп'ютеру. Ця операція автоматично виконується прикладною програмою Excel при першому запуску коду. Згодом при виконанні коду компіляція не потрібна, якщо, звичайно, у код не було внесено ніяких змін. Для компіляції коду в книзі SRXUtils.xls виберіть команду Compile (Компілювати) у меню Debug (Налагодження).
Налаштування властивостей аркуша й проекту. Виберіть команду Properties (Властивості) у меню File (Файл) і відкрийте діалогове вікно, що з'явилося, на вкладці Summary (Документ), як показано на рис. 10.6. Інформація з текстового поля Titlе (Назва) буде фігурувати в діалоговому вікні Add-Ins (Надбудови), показаному на рис. 10.7. Інформація, введена в поле Comments (Примітки), буде показана в нижній частині діалогового вікна Add-Ins (Надбудови). Отже, потрібно заповнити оба поля. Потім властивості проекту задаються в редакторі VBA. У вікні Project (Проект) виділіть проект із ім'ям SRXUtils.xls, а потім виберіть команду VBAProjectProperties у меню Tools для виклику діалогового вікна, показаного на рис. 10.8. Введіть у відповідні поля ім'я й опис проекту.
Захист коду від перегляду. Щоб приховати код від перегляду, використайте діалогове вікно Project Properties (Властивості проекту). Перейдіть на вкладку Protection (Захист), як показано на рис. 10.9. Встановлення прапорця Lock Project for Viewing (Блокувати перегляд проекту) і введення пароля захищає проект від перегляду й, відповідно, від запису. Такий проект відображається у вікні Project (Проект) редактора VBA, але щоб розкрити дерево ієрархії, для цього проекту буде потрібно введення пароля.

Рис. 10.6. Введення властивостей надбудови в діалоговому вікні Properties

Рис. 10.7. Діалогове вікно Add-Ins

Рис. 10.8. Властивості проекту VBA

Рис. 10.9. Вкладка Protection діалогового вікна Project Properties
Збереження книги як надбудови в обраній вами папці. Виберіть команду Save As (Зберегти як) у меню File (Файл), а потім у списку, що розкривається, Save as Type (Тип файлу) - варіант Microsoft Excel Add-In (*.xla) (Надбудова Microsoft Excel (*.xla)). Введіть ім'я файлу в поле File Name (Ім'я файлу) (у нашому прикладі це ім'я SRXUtils.xla) і клацніть на кнопці Save (Зберегти).
У кожній книзі Excel є властивість IsAddIn. Якщо ця властивість має значення True, прикладна програма вважає, що книга постачена надбудовою. Одним з наслідків є невидимість книги. Тобто неможливо просто задати властивість IsAddIn і зберегти проект у вигляді файлу з розширенням XLA, тому що книга буде недоступна з інтерфейсу користувача Excel. На щастя, фірма Microsoft організувала процес збереження книги у вигляді надбудови таким чином, що властивість IsAddIn автоматично прийме значення True. Про те, як вручну змінити це значення на False, ми поговоримо трохи пізніше.
Характеристики надбудов
Перелічимо характеристики надбудов, завдяки яким вони відрізняються від звичайних книг.
Книга й всі аркуші надбудови є прихованими. Це необхідно, щоб зробити безпечною надбудову до непередбачених змін з боку користувачів. Якщо потрібно включити в надбудову аркуші, їх потрібно зберегти в окремій книзі, у той час як код VBA зберігається у файлі надбудови. У цьому випадку при запуску Excel відкриє обидва файли.
Як ви, імовірно, вже знаєте, при спробі закрити відредаговану книгу Excel з'являється вікно з питанням, чи хочете ви зберегти зроблені зміни. Для надбудов не передбачено нічого подібного. Тобто створювач надбудови може вносити до неї будь-які зміни, не турбуючись про те, що користувачу буде показане вікно з попередженням, на яке той навряд чи зможе адекватно відреагувати. Зрозуміло, створювач може в будь-який час зберегти результат редагування надбудови, за допомогою команди Save As (Зберегти як).
При відкритті книги Excel запускається подія Workbook_Open. Якщо відкривається звичайна книга, користувач може зупинити дану подію, натиснувши й утримуючи клавішу Shift. Подію Open для надбудов не можна зупинити.
Назви макросів, які використовуються в надбудовах, неможливо побачити в діалоговому вікні Macro (Макрос).
Налагодження надбудов
Налагодження надбудов здійснюється таким же способом, як і налагодження звичайних книг. Вам не потрібно знову посилатися на вихідний файл XLS. Зокрема, надбудови відкриваються, як звичайні книги. Однак без знання пароля (передбачається, що надбудова захищена від доступу) неможливо побачити ні коду надбудови, ні її книги. Введення пароля дозволяє розкрити дерево ієрархії надбудови й отримати доступ до розташованих нижче вузлів. Щоб побачити код, досить вибрати вузол проекту у вікні Project (Проект). Якщо вибрати вузол ThisWorkbook і відкрити вікно Proprerties (Властивості), з'явиться можливість привласнити властивості IsAddIn значення False. Це зробить вікно книги видимим і дасть можливість його редагувати. Після внесення необхідних змін досить повторно відкомпілювати код і повернути властивості IsAddIn значення True.
ПРИМІТКА
Програма Excel 2000 підтримує не тільки стару модель надбудов з Excel 97, але й більш нову модель СОМ. Надбудови СОМ являють собою Active DLL або ж виконавчий файл, який може бути пов'язаний з набором прикладних програм Office. Ці надбудови створюються в редакторі Visual Basic або Visual C++, тому що редактор VBA не дозволяє створювати виконавчі і файли Active DLL.
Видалення надбудов
Можливо, ви звернули увагу на те, що діалогове вікно Add-Ins (Надбудови), показане на рис. 10.5, не постачено кнопкою Delete (Видалити). Для видалення надбудови зі списку потрібно зняти прапорець, розташований поруч із її ім'ям, перейменувати файл XLA і знову встановити прапорець. З'явиться повідомлення з питанням, чи повинен Excel видалити надбудову зі списку.
Якщо вже даний розділ присвячений властивостям надбудов, згадаємо, що ввведення нового імені в поле True (Назва) ніяк не відіб'ється в діалоговому вікні Add-Ins (Надбудови) до перезавантаження Excel.
Приклади надбудов
Почнемо створення надбудови з оболонки. Це надасть вам можливість для розробки закінчених прикладних програм Excel на основі надбудов шляхом додавання нових функцій. Я строго рекомендую вам дотримуватися описаного процесу. У даній главі буде створена оболонка надбудови, яка всього лише приводить до появи інформаційного вікна. На даний момент не варто ускладнювати питання створення надбудови додаванням безлічі функцій. З розробкою багатофункціональних надбудов, а також зі створенням для них користувацьких меню ви познайомитеся в главі 12.
Створення вихідної книги
Для початку потрібно створити нову книгу, що послужить основою для надбудови. Я буду називати її SRXUtils.xls, але ви можете використати будь-яке інше ім'я.
При редагуванні надбудови зміни вносяться в аркуш SRXUtils.xls, що потім буде збережений поверх поточної надбудови. Перед цим, зрозуміло, варто відключити поточну версію надбудови.
Налаштування користувацьких меню
Для активації пробних функцій оболонки потрібно створити користувацьке меню. Докладно процес створення меню й панелей інструментів буде пояснений у главі 12, а поки ми зведемо його до мінімуму, щоб ви одержали загальне поняття про те, як це відбувається.
У цьому випадку користувацьке меню повинне автоматично створюватися при завантаженні надбудови й зникати при її відключенні. Відповідно, код потрібно зв'язати з подіями Open і BeforeClose об'єкта ThisWorkbook, як показано в лістінгу 10.1.
Лістінг 10.1. Програма обробки подій Open і BeforeClose
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCustomMenuItem
End Sub
Private Sub Workbook_0pen
CreateCustomMenuItem
End Sub
Даний код просто викликає процедури, що створюють і видаляють користувацьке меню. Ці процедури потрібно помістити в новий модуль коду, тому додайте модуль до проекту SRXUtils і привласніть йому ім'я basMenus. Потім помістіть в цей модуль процедуру CreateCustomMenuItem, описану в лістінгу 10.2. На даний момент від вас не потрібно повного розуміння коду, тому що він буде обговорюватися в главі 12. Пам’ятайте, що даний код призводить до появи пункту ActiveteSheet у меню Custom, а вибір цього пункту меню запускає процедуру, описану властивістю OnAction. У цьому випадку це процедура ActivateSheet.
Лістінг 10.2. Процедура CreateCustomMenuItem
Sub CreateCustomMenuItem()
Dim cbcpop As CommandBarControl
' Перевірка наявності користувацького меню
Set cbcpop = Application.CommandBars( _
"Worksheet menu bar").
FindControl(Type:=msoControlPopup. _
Tag:="SRXUti1sCustomMenu")
If Not cbcpop Is Nothing Then Exit Sub
' Елемент керування не існує, створимо його
Set cbcpop = Application.CommandBars( _
"Worksheet menu bar"). _
Controls.Add(Type:=msoControlPopup. _
Temporary:=True)
Cbcpop.Caption = "Cu&stom"
' Задання властивості тега, щоб потім знайти його для видалення
With cbcpop.Controls.Add(Type:=msoControlButton. _
Temporary:=True)
.Caption = "&ActiveSheet"
.OnAction = "ActiveSheet"
End With
End Sub
У модуль basMenus також варто помістити процедуру DeleteCustomMenuItem, описану в лістінгу 10.3.
Лістінг 10.3. Процедура DeleteCustomMenuItem
Sub DeleteCustomMenuItem()
Dim cbc As CommandBarControl
Set cbc = Application.CommandBars( _
"Worksheet menu bar").
FindControl(Type:=msoControlPopup. _
Tag:="SRXUtilsCustomMenu")
If Not cbc Is Nothing Then cbc.Delete
End Sub
Виконання функцій надбудови
Тому що службова програма ActivateSheet (вона активується, коли користувач вибирає в меню пункт ActivateSheet, створений у лістінгу 10.2) дуже проста, для неї не потрібно власного модуля коду. Досить додати наступну процедуру в модуль basMain, що також варто створити:
Public Sub ActivateSheet()
MsgBox "Це службова програма ActiveSheet"
End Sub
Заключні кроки
Нарешті потрібно виконати наступні кроки:
Скомпілюйте проект. Для цього використовується меню Debug (Налагодження).
Налаштуйте властивості книги й проекту, як показано на рис. 10.6 і 10.8.
Встановіть захист надбудови. На вкладці Portection (Захист) діалогового вікна Properties (Властивості) Встановіть прапорець Lock Project for Viewing (Блокувати перегляд проекту) і введіть пароль.
Збережіть надбудову під ім'ям SRXUtils.xla у папці по вашому виборі.
Тепер усе готово для випробування роботи надбудови. Закрийте книгу SRXUtils.xls і відкрийте нову книгу. Виберіть у меню Tools (Сервіс) команду Add-Ins (Надбудови) і клацніть на кнопці Browse (Огляд) у діалоговому вікні, що з'явилося. Знайдіть файл SRXUtils.xla, а потім встановіть прапорець напроти його імені в списку Add-Ins Available (Доступні надбудови). У головному меню повинен з'явитися пункт Custom з командою ActiveSheet. Вибір цієї команди призводить до появи інформаційного вікна.
Як згадувалося раніше, по мірі виконання вправ даної книги призначення цієї надбудови стане зрозуміліше.

11 Події Excel
У процесі роботи з Excel відбуваються різні події. Прикладом події може служити створення нового аркуша або зміна розміру діаграми. В Microsoft Excel визначені 63 події.
При виникненні події запускається код для її обробки. Як ви незабаром переконаєтеся, цей код розміщується у спеціальну процедуру. Він називається кодом події (event code). Нескладний код був написаний у минулій главі для подій Open і BeforeClose при створенні надбудови SRXUtils.
Події Excel природно діляться на п'ять груп, перерахованих у табл. 11.1-11.5. Ці групи частково відображують рівень, на якому відбувається подія, - рівень додатка (вищий), рівень книги (середній) або рівень аркуша/діаграми (низький).
Для ілюстрації перелічимо події, які наступають при активації аркуша користувачем або при виклику методу Activate.
Подія Activate. Вона настає при кожній активізації аркуша.
Подія SheetActivate на рівні книги, що настає при кожній активізації аркуша в книзі.
Подія SheetActivate на рівні прикладної програми. Вона настає при активізації будь-якого аркуша будь-якої активної на даний момент книги. Однак незабаром ви переконаєтеся в тому, що для запуску цієї події потрібно написати спеціальний код.
Властивість EnableEvents
Важливо розуміти, що жодна подія Excel не настане, якщо властивості EnableEvents не привласнене значення True. Це дає можливість програмісту контролювати настання подій. Властивість EnableEvent властива об'єкту Application, тобто, наприклад, для запобігання події Save при збереженні активної книги варто скористатися наступним кодом:
Application.EnableEvent = False
ActiveWorkbook.Save
Application.EnableEvent = True
Події і об'єктна модель Excel
Об'єктна модель Excel містить у собі кілька об'єктів, які існують просто для зручності, дозволяючи включити події Excel в об'єктну модель. У код ці об'єкти не включаються. Ось їх назви: AppEvents, DocEvents, ChartEvents, WorkbookEvents, OLEObjectEvents і RefreshEvents. Наприклад, події, що пов'язані з аркушем Excel, є методами об'єкта DocEvents, що, у свою чергу, є дочірнім стосовно об'єктів Worksheet і Chart.
Доступ до процедури обробки події
Ви, імовірно, хочете довідатися, як написати процедуру обробки події. Коротко кажучи, в Excel для кожної події існує спеціальна оболонка, в яку й розміщується код процедури.
Для ілюстрації розглянемо подію SelectionChange для об'єкта Worksheet. На рис. 11.1 показане вікно коду для об'єкта Sheet1 (Аркуш 1). Зверніть увагу на те, що в розкритому списку Object (Об'єкт) обраний об'єкт Worksheet. Це приводить до появи в списку Procedure (Процедури) переліку подій для даного об'єкта. Тепер залишається тільки вибрати потрібну подію.

Рис. 11.1. Події для об'єкта Worksheet
Вибір події SelectionChange спричиняє автоматичну появу наступної оболонки коду:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
End Sub
При цьому курсор з’явиться між цими двома рядками, запрошуючи вас почати введення коду даної події.
Ця подія запускається при зміні виділеної на аркуші області. Майте на увазі, що як параметр Target буде фігурувати об'єкт типу Range, що представляє нову виділену область. Відповідно, код буде мати доступ винятково до нового набору виділених об'єктів, що дає прикладній програмі Excel можливість передати нам інформацію, що відноситься до даної події.
Описаний підхід буде працювати й для подій, зв'язаних із книгою або діаграмою, у той час як події рівня прикладної програми вимагають трохи іншої обробки. Але про це ми поговоримо трохи пізніше.
Події об'єкта Worksheet
Процедури для обробки подій об'єкта Worksheet, утримуються в програмному модулі, зв'язаному з робочою книгою. Події, підтримувані цим об'єктом, перераховані в табл. 11.1.
Таблиця 11.1. Події об'єкта Worksheet:
___________________________________________________________________________
Подія Опис ______ _
Activate Запускається при активізації аркуша
BeforeDoubleClick Запускається після подвійного клацання користувачем, але до стандартної реакції Excel
BeforeRightClick Запускається після того, як користувач клацне правою кнопкою
миші, але до настання стандартної реакції Excel
Calculate Запускається після перерахування аркуша
Change Запускається при зміні даних аркуша
Deactivate Запускається при переході з поточного аркуша на інший
PivotTableUpdate Запускається після оновлення розташованої на аркуші
(Тільки для Excel 10) зведеної таблиці
SelectionChange Запускається при зміні виділеної області аркуша
Події об'єкта Workbook
Процедури для обробки подій об'єкта Workbook знаходяться у програмному модулі об'єкта ThisWorkbook, доступному у вікні Project (Проект). Події, що підтримуються даним об'єктом, перераховані в табл. 11.2.
Таблиця 11.2. Події об'єкта Workbook
___________________________________________________________________________
Події Опис _
Activate Запускається при активізації книги
Addinlnstall Запускається при установці робочої книги як надбудова
AddinUninstall Запускається при видаленні робочої книги, що служила
надбудовою
BeforeClose Запускається перед закриттям робочої книги
BeforePrint Запускається перед роздруком робочої книги
BeforeSave Запускається перед збереженням робочої книги
Deactivate Запускається при переході від поточної робочої книги до іншої
NewSheet Запускається при вставці в книгу нового аркуша
Open Запускається при відкритті робочої книги
PivotTableCloseConnection Запускається при розриві зв'язку зведеної таблиці з
(Тільки для Excel 10) джерелом даних
PivotTableOpenConnection Запускається при встановленні зв'язку зведеної таблиці з
(Тільки для Excel 10) джерелом даних
SheetActivate Запускається при активізації будь-якого аркуша, що входить у книгу
SheetBeforeDoubleClick Запускається після подвійного клацання користувачем на будь-якому з аркушів, що входять у книгу, але до стандартної реакції Excel
SheetBeforeRightClick Запускається після того, як користувач клацне правою кнопкою миші на будь-якому з аркушів, що входять у книгу, але до стандартної реакції Excel
SheetCalculate Запускається після перерахування будь-якого аркуша, що входить у книгу, або після відображення змінених даних на діаграмі
SheetChange Запускається при зміненні вмісту будь-яких комірок
SheetDeactivate Запускається при переході з одного аркуша на інший
SheetSelectionChange Запускається при зміні виділеної області на будь-якому
аркуші. Це не відноситься до аркушів з діаграмами
WindowActivate Запускається при активізації вікна, якщо в одній книзі
відкрито декілька вікон
WindowDeactivate Запускається при переході від поточного вікна до іншого, якщо в одній книзі відкрито декілька вікон
WindowResize Запускається при зміні розмірів вікна
Майте на увазі, що, утримуючи натиснутою клавішу Shift при відкритті книги, можна відмінити подію Open для об'єкта Workbook.
Події об'єкта Chart
У цьому випадку події діляться на пов'язані з діаграмою в цілому й пов'язані з її окремими елементами. Вони перераховані в табл. 11.3.
Таблиця 11.3. Події об'єкта Chart
___________________________________________________________________________
Події Опис _
Activate Запускається при активізації діаграми, наприклад, щигликом кнопки миші
BeforeDoubleClick Запускається безпосередньо перед дією за замовчуванням при подвійному клацанні на елементі діаграми
BeforeRightClick Запускається перед появою контекстного меню, активізованого клацанням на елементі діаграми правою кнопкою миші
Calculate Запускається при перерахуванні діаграми із-за зміни вихідних і додавання нових даних
Deactivate Запускається при блокуванні діаграми при клацанні поза діаграмою кнопкою миші
DragOver Запускається при перетаскуванні по діаграмі діапазону комірок
DragPlot Запускається при завершенні перетаскування по діаграмі діапазону комірок
MouseDown Запускається при натисканні лівої або правої кнопки миші, коли вказівник розташовується на діаграмі
MouseMove Запускається при переміщенні вказівника миші в межах діаграми
MouseUp Запускається при відпусканні лівої або правої кнопки миші, коли вказівник розташовується на діаграмі
Resize Запускається при зміні розмірів впровадженої діаграми або вікна з діаграмою на окремому аркуші
Select Запускається, коли користувач клацанням вибирає конкретний елемент діаграми
SeriesChange Запускається при перетаскуванні крапки даних по діаграмі
Події об'єкта Application
У табл. 11.4 перераховані події об'єкта Application. Вони зв'язані з усіма об'єктами в Excel.
Таблиця 11.4. Події об'єкта Application
___________________________________________________________________________
Події Опис _
NewWorkbook Запускається при створенні нової книги
SheetActivate Запускається при активізації будь-якого аркуша
SheetBeforeDoubleClick Запускається при подвійному клацанні на будь-якому аркуші, але до стандартної реакції Excel
SheetBeforeRightClick Запускається після клацанні на будь-якому аркуші правою кнопкою миші, але до стандартної реакції Excel
SheetCalculate Запускається при перерахуванні будь-якого аркуша або відображенні змінених даних на діаграмі
SheetChange Запускається при зміні комірок будь-якого аркуша
SheetPivotTableUpdate Запускається після оновлення аркуша, що містить звіт
(Тільки в Excel 10) зведеної таблиці
SheetDeactivate Запускається при деактивізації аркуша
SheetSelectionChange Запускається при зміні виділеного набору на будь-якому аркуші. Не виникає, якщо виділений елемент знаходиться на діаграмі
WindowActivate Запускається при активізації вікна будь-якої книги
WindowDeactivate Запускається при переході до іншого вікна книги
WindowResize Запускається при зміні розміру вікна книги
WorkbookActivate Запускається при активізації книги
WorkbookAddinlnstall Запускається при встановленні книги в якості надбудови
WorkbookAddinUninstall Запускається при видаленні робочої книги, що служила надбудовою
WorkbookBeforeClose Запускається безпосередньо перед закриттям будь-якої відкритої книги
WorkbookBeforePrint Запускається перед роздруком будь-якої відкритої книги
WorkbookBeforeSave Запускається перед зберіганням будь-якої відкритої книги
WorkbookDeactivate Запускається при переході до будь-якої іншої книги
WorkbookNewSheet Запускається при створенні у відкритій книзі нового аркуша
WorkbookOpen Запускається при відкритті книги
WorkbookPivotTableCloseConnection Запускається після розриву зв'язку зведеної таблиці
(Тільки в Excel 10) із джерелом даних
WorkbookPivotTableOpenConnection Запускається після встановлення зв'язку зведеної
(Тільки в Excel 10) таблиці із джерелом даних
На жаль, процес написання коду для подій об'єкта Application трохи ускладнений. Ось покрокова процедура, що дозволяє досягти оболонки коду:
За допомогою меню Insert (Вставка) редактора VBA ввімкніть в проект модуль класів. Назвемо цей модуль CApp (скорочене від Class Application). В область опису модуля класів введіть рядок
Public WithEvent App As Application
Тепер вибір об'єкта App у списку, що розкривається, Object (Об'єкт) дасть доступ до оболонки коду для події об'єкта Application, як показано на рис. 11.2.

Рис. 11.2. Події об'єкта Application
У модулі коду, що буде викорисовуватися для активізації події рівня прикладної програми, помістіть наступний опис:
Dim AppObj As New CApp
І нарешті залишається призначити властивість App об'єкта AppObj об'єкту Application. Це робиться за допомогою коду:
Set AppObj.App = Excel.Application
Неважливо, у якому місці коду ви розмістите даний рядок, але він повинен бути виконаний, щоб активізувати події рівня прикладної програми. У цьому випадку має місце певне зациклення, тому що цей код природно зв'язувати з подією WorkbookOpen. Однак ця подія буде виконана тільки після виконання даного коду.
Відмінити використання подій рівня прикладної програми можна як привласнивши властивості EnableEvent значення False, так і вказавши наступний рядок коду:
Set AppObj.App = Nothing
12 Користувацькі меню й панелі інструментів
У цій главі будуть обговорюватися методи програмного керування меню й панелями інструментів. Хоча дана тема досить проста, у користувачів, проте, періодично виникають проблеми, тому що документація не настільки вичерпна, як хотілося би.
Меню й панелі інструментів: загальний огляд
Меню й панелі інструментів не відносяться до об'єктної моделі Excel. Вони фігурують у всьому наборі прикладних програм Office і відповідно належать до об'єктної моделі Office. Частина цієї об'єктної моделі, пов'язана з меню й панелями інструментів, показана на рис. 12.1.
Напевно, ви вже помітили, що ця модель вкрай невелика й містить тільки два об'єкти й пов'язані з ними сімейства:
об'єкти CommandBar і сімейство CommandBars;
об'єкти CommandBarControl і сімейство CommandBarControls.
Вид меню
Щоб допомогти вам освоїтися з умовними позначеннями, на рис. 12.2 показаний компонент структури меню Office. У цьому випадку це меню прикладної програми Word, що, однак, не має значення.
Об'єкт CommandBar
Панелі інструментів, рядка меню, меню, підменю й контекстні меню відносяться до об'єктів CommandBar. Контекстними називаються меню, що з'являються при клацанні на об'єкті правою кнопкою миші. Зрозуміло, панелі інструментів, рядка меню й контекстні меню є об'єктами верхнього рівня, а дочірніми для них є меню й підміню.

Рис. 12.1. Частина об'єктної моделі Office, яка відноситься до меню й панелів інструментів

Рис. 12.2. Меню Office
Важливо розуміти, що редактор VBA оброблює об'єкти CommandBar по-різному. Наприклад, властивість Command сімейства CommandBars враховує тільки елементи верхнього рівня, тобто панелі інструментів, рядка меню й контекстні меню. Підрахунок меню й підменю не проводиться. За допомогою методу Add цього ж сімейства можуть бути створені панелі інструментів і рядка меню, але не підлеглі по відношенню до них об'єкти.
Об'єкт CommandBar має властивість Type, що може приймати значення констант із наступного переліку:
Enum MsoBarType
msoBarTypeNormal = 0 ' Панель інструментів
msoBarTypeMenuBar = 1 ' Рядок меню
msoBarTypePopup = 2 ' Меню, підменю або контекстне меню
End Enum
Елементи управління об'єкта CommandBar
Елементи керування панелями інструментів, рядками меню, пунктами меню й підміню є об'єктами CommandBarControl. Як ви незабаром переконаєтеся, існують різні типи елементів керування, які можна розділити на дві категорії: користувацькі (включаючи створені користувачем текстові поля, списки що розкриваються й т.п.) і вбудовані. Майте на увазі, що ці елементи керування відрізняються від використовуваних в об'єктах UserForm. Вони були сконструйовані спеціально для панелей інструментів і меню.
Існують два спеціальних типи користувацьких елементів керування - це меню, що розкриваються (popup controls) і кнопки (buttoncontrols).
Меню, що розкриваються
Елемент управління типу msoControlPopup призначений для створення меню, що розкриваються, і підменю, приклад яких показаний на рис. 12.2. При наведенні на такі елементи управління вказівника миші вони приймають вигляд вдавленої кнопки. Їх також можна ідентифікувати по розташованій праворуч невеликій стрілці.
Отже, елемент керування що розкривається відноситься до типу msoControlPopup і використовується для виклику меню або підменю. Командний рядок, що розкривається, відноситься до типу msoBarTypePopup і являє собою меню, підменю або контекстне меню. Для показу командного рядка, що розкривається, користувачу потрібно активізувати елемент керування, що розкривається.
Кнопки
Елементи управління типу msoControlButton називаються кнопками. При їхній активізації шляхом клацання лівою кнопкою миші або натискання гарячої клавіші виконується відповідний макрос. Кнопки мають строкову властивість OnAction, якому можна привласнити ім'я виконуваного при активізації кнопки макросу.
Створення пункту меню
Процес створення меню супроводжується рядом хитрощів. Зокрема, користувачі спочатку припускають, що додати нове меню можна за допомогою методу Add сімейства CommandBars, вказавши ім'я цього меню і його розташування. Зрештою, меню є об'єктом CommandBar, а описана вище процедура застосовується для додавання в сімейство нового об'єкта.
Процес створення нового меню являє собою додавання елемента керування msoControlPopup у сімейство CommamndBarControl батьківського меню й вказання положення нового елемента керування стосовно батьківського елемента. Такий підхід дозволяє користувачу заощадити час. Достатньо вказати, що меню або підменю активізується за допомогою елемента керування, що розкривається, і створити цей елемент. Відповідні порожні меню створюються автоматично. Цей процес буде трохи пізніше пояснений на прикладі, тому не турбуйтеся, якщо вам щось незрозуміло на даний момент.
При генеруванні нової панелі інструментів або меню можна створити даний об'єкт тимчасово. Це означає, що він буде знищений, як тільки вікно Excel закриється. У випадку непередбачених результатів відновити вихідну ситуацію легко. Досить просто закрити й знову відкрити прикладну програму Excel. Альтернативним методом видалення пункту меню є його перетаскування за границю меню при відкритому діалоговому вікні Customize (Налаштування). Для видалення панелі інструментів можна використати клавішу Delete.
Сімейство CommandBars
Верхнім об'єктом ієрархії для панелей інструментів і меню є сімейство CommandBars, що містить всі об'єкти CommandBar для даної доданої прикладної програми. Доступ до цього сімейства можна отримати за допомогою властивості CommandBars об'єкта Application, тобто написавши:
Application.CommandBars
У лістінгу 12.1 демонструється код, що виводить список об'єктів CommandBars у вікні. Ви здивуєтесь величезній кількості цих об'єктів, більшість із яких у цей момент є невидимими.
Лістінг 12.1. Список об'єктів CommandBar для прикладної програми Excel
Public Sub ShowCmdBars()
Dim sType as string, cbar as CommandBar
For Each cbar In Application.CommandBars
Select Case cbar.Type
Case msoBarTypeNormal ' Панель інструментів
sType = "Normal"
Case msoBarTypeMenuBar ' Рядок меню
sType = "Menu bar"
Case msoBarTypePopup ' Меню, підменю
sType = "Popup"
End Select
Debug.Print cbar.Name & "." & sType & "." &cbar.Visible
Next
End Sub
Після виконання даного коду серед безлічі інших об'єктів повинні виявитися:
Worksheet Menu Bar.Menu bar.True
Chart Menu Bar.Menu bar.False
Це показує, що головне меню виглядає по-різному для аркушів з даними й з діаграмами, що, втім, мабуть, очевидно якщо подивитися на самі меню. Відповідно, якщо потрібно додати користувацьке меню в головний рядок поза залежністю від вигляду активного в цей момент аркуша, це буде потрібно зробити й для рядка меню аркуша з даними, і для рядка меню аркуша з діаграмою.
Властивість CommandBars має особливість, про яку не можна не згадати. Якщо остання пов'язана з об'єктом Application, тобто зазначено Aplication.CommandBars, вона повертає список всіх вбудованих і користувацьких командних панелей для розглянутої прикладної програми, в цьому випадку для Excel. Саме тому в лістінгу 12.1 було використано повний вираз ApplicationCommandBars. Пам’ятайте, що для стандартного модуля коду цей опис можна пропустити, вказавши просто CommandBars.
Однак для об'єкта Workbook ця властивість повертає зовсім інший список. У цьому випадку існують дві можливості. Якщо книга є частиною іншої прикладної програми, а Excel був активізований подвійним клацанням на ній, властивість CommandBars поверне набір командних панелей іншої прикладної програми. Елементи даного набору можуть докорінно відрізнятися від командних панелей Excel. Якщо ж книга не є частиною іншої прикладної програми, ця властивість поверне значення Nothing. Крім того, програмного способу одержати набір командних панелей, пов'язаних із книгою, просто не існує.
Тобто властивість CommandBars об'єкта Workbook має сенс тільки для книг, вбудованих в іншу прикладну програму.
Створення нових меню й панелей інструментів
Як уже згадувалося, рядки меню й панелі інструментів відрізняються від меню й підменю способом свого створення. Для формування нового рядка меню або нового контекстного меню достатньо використати метод Add із сімейства CommandBars. Ось синтаксис цього методу:
CommandBarsObject.Add(Name.Position.MenuBar.Temporary)
Необов'язковий параметр Name задає ім'я нової командної панелі. При пропуску даного аргументу Excel привласнює панелі системне ім'я, наприклад Custom 1. Параметр Position також є необов'язковим і вказує положення нової командної панелі. Він може приймати значення msoBarLeft, msoBarTop, msoBarRight, msoBarBottom, msoBarFloating (для плаваючої командної панелі) і msoBarPopup (для контекстного меню).
Необов'язковий логічний параметр MenuBar має значення True для рядка меню й False для панелі інструментів. За замовчуванням він має значення False, тобто пропуск цього параметра приводить до появи панелі інструментів. Майте на увазі, що, створивши новий рядок меню й зробивши його видимим, ви заміните ним існуючий рядок меню. Повернути все до вихідного стану можна, натиснувши комбінацію клавіш Alt +F4 і знову запустивши прикладну програму Excel.
Присвоєння необов'язковому параметру Temporary значення True робить нову командну панель тимчасовою, тобто вона буде видалена після закриття вікна програми. За замовчуванням даний параметр має значення False.
Для ілюстрації розглянемо код, що приводить до появи нової плаваючої панелі інструментів з ім'ям Користувацька панель:
Dim cbar As Office.CommandBar
Set cbar = Application.CommandBars.Add ("Користувацька панель". _
msoBarFloating. False. True)
cbar.Visible = true
Якщо об'єкт CommandBar з ім'ям "Користувацька панель" уже існує, цей код приведе до появи повідомлення про помилку "Invalid procedure call" (Невірний виклик процедури). Тобто перед використанням методу Add необхідно перевірити існування об'єкта CommandBar, як показано в лістінгу 12.2.
Лістінг 12.2. Створення нової панелі інструментів
Public Sub CreateToolbar()
Dim char As Office.CommandBar
Dim bExists As Boolean
bExists = False
for Each cbar In Application.CommandBars
If cbar.Name = "Custom Toolbar" Then bExists = True
Next
If Not bExists Then
Set cbar = Application.CommandBars.Add("користувацька панель"._
msoBarFloating. False. True)
cbar.Visible = True
End If
End Sub
Елементи керування командної панелі
Початково одним із самих незрозумілих моментів системи меню Office є той факт, що елементи рядка меню являють собою зовсім не меню і навіть не їх назви. Насправді це елементи управління типу CommandBarControl, які можна додати і на інші командні панелі. Вони належать до сімейства CommandBarControls. Крім того, кожний елемент керування є об'єктом одного із трьох типів:
CommandBarButton;
CommandBarComboBox;
CommandBarPopup.
Подвійна природа об'єктів CommandBarControl дає різним елементам керування як загальний, так і властивий тільки їм набір властивостей і методів. Це має сенс, тому що, наприклад, текстові поля істотно відрізняються від списків, що розкриваються. Крім цього, трохи пізніше буде показано, що об'єкти CommandBarPopup повинні мати особливу властивість (вона називається Controls), що надає доступ до елементів керування зв'язаного меню. Іншим типам об'єктів CommandBarControl ця властивість не потрібна.
Властивість Type об'єкта CommandBarControl допомагає визначити, до якого типу даних відноситься елемент керування. Вона може приймати значення з наступного перерахування:
Enum MsoControlType
msoControlCustom = 0
msoControlButton = 1 'CommandBarButton
msoControlEdit = 2 'CommandBarComboBox
msoControlDropdown = 3 'CommandBarComboBox
msoControlComboBox = 4 'CommandBarComboBox
msoControlButtonDropdown = 5 'CommandBarComboBox
msoControlSplitDropdown = 6 'CommandBarComboBox
msoControlOCXDropdown = 7 'CommandBarComboBox
msoControlGenericDropdown = 8
msoControlGraphicDropdown = 9 'CommandBarComboBox
msoControlPopup = 10 'CommandBarPopup
msoControlGraphicPopup = 11 'CommandBarPopup
msoControlButtonPopup = 12 'CommandBarPopup
msoControlSplitButtonPopup = 13 'CommandBarPopup
msoControlSplitButtonMRUPopup = 14 'CommandBarPopup
msoControlLabel = 15
msoControlExpandlngGrid = 16
msoControlSplitExpandlngGrid = 17
msoControlGrid = 18
msoControlGauge = 19
msoControlGraphicCombo = 20 'CommandBarComboBox
End Enum
Коментарі, що написані після деяких констант, вказують тип даних відповідного елемента керування. Ця інформація перебуває в довідковій системі Microsoft. Відсутність коментарів означає, що цей елемент керування не належить до жодного із трьох розглянутих типів даних.
Створення нового елемента керування командної панелі
Для створення нового елемента керування командної панелі використовується метод Add сімейства CommandBarControls. Цей метод повертає об'єкт CommandBarButton, CommandBarComboBox або CommandBarPopup, залежно від значення параметра Type. Ось його синтаксис:
CommandBarControlsObject.Add(Type. Id. Parameter. Before. Temporary)
Параметр Type задає тип елемента керування, що буде доданий на вказану командну панель. У табл. 12.1 перераховані можливі значення цього параметра разом з відповідними елементами керування й типом значення, що повертає метод Add.
Таблиця 12.1. Значення параметра Type
Значення параметра Елемент керування Повертає объект
MsoControlButton (1) Кнопка CommandBarButton
msoControlEdit (2) Текстове поле CommandBarComboBox
msoControlDropdown (3) Список, що розкривається CommandBarComboBox
msoControlComboBox (4) Комбінований CommandBarComboBox
керуючий елемент
msoControlPopup (5) Елемент керування, CommandBarPopup
що розкривається
Необов'язковий параметр Before є числом, яке вказує положення нового елемента керування на командній панелі. При цьому якщо в даному положенні вже перебуває інший елемент керування, новий елемент буде вставлений перед ним. Пропуск даного параметра призводить до появи нового елемента наприкінці командної панелі.
Щоб додати користувацький елемент керування одного з типів, перерахованих у табл. 12.1, привласніть параметру Id значення 1 або просто пропустіть його. Якщо потрібно додати один з вбудованих елементів керування, привласніть даному параметру номер цього елемента (пропустивши при цьому аргумент Type). На цю тему ми поговоримо в наступному розділі.
Як і у випадку з командними панелями, присвоєння параметру Temporary значення True призводить до появи тимчасового елемента керування, що буде вилучений після закриття вікна програми.
Важливо пам'ятати, що об'єкт CommandBar не має властивість CommandBarControls. Тобто для повернення об'єкта CommandBarControls потрібно використати властивість Controls, як показано нижче:
CommandBars("Worksheet Menu bar").Controls
Не менш важливо усвідомлювати, що серед всіх типів об'єкта CommandBarControls властивістю Controls володіє тільки один об'єкт. Це об'єкт CommandBarControl типу CommandBarPorup. Вищезгадана властивість дає доступ до сімейства CommandBarControls, що зв'язано відповідним меню для елементів керування, що розкриваються.
Вбудовані елементи керування командної панелі
Як буде показано в лістінгу 12.3, на панелі інструментів і в меню можна помістити й вбудовані елементи керування. Для цього достатньо присвоїти параметру Id методу Add сімейства CommandBarControls номер вбудованого елемента керування.
Залишається тільки одне питання. Як визначити номер убудованого елемента керування? По-перше, можна скористатися методом FindControl. Його синтаксис виглядає в такий спосіб:
expression.FindControl(Type. Id. Tag. Visible. Recursive)
де expression являє собою об'єкт CommandBar або CommandBars. Всі інші параметри є необов'язковими. Цей метод повертає перший об'єкт CommandBarControl, що задовольняє критерію, заданому параметрами. Якщо пошук виявився безуспішним, повертається значення Nothing. Ось короткий опис параметрів:
Type — одна з констант msoControlType перерахування, наведеного раніше в цій главі;
Id — номер елемента керування;
Tag — підпис елемента керування;
Visible — якщо даний параметр має значення True, пошук буде здійснюватися тільки серед видимих елементів керування;
Recursive — присвоєння даному параметру значення True включить у пошук командні панелі й всі підлеглі панелі, що розкриваються.
На жаль, метод FindControl вимагає ще одного способу визначення елемента керування, а саме вказання параметра Tag. Тобто даний метод найбільш корисний при пошуку створених вами елементів керування, яким ви особисто привласнили цей параметр.
Альтернативним способом визначення номера вбудованого елемента керування є створення одноразового списку для подальших звернень. Код з лістінга 12.3 створює текстовий файл і заповнює його списком назв всіх вбудованих елементів керування і їхніх номерів. Майте на увазі, що для успішного виконання коду на диску D: повинна існувати папка temp. Втім, припустимо вказати папку й маршрут доступу до неї, що існують у вашій системі. Код створює тимчасову панель інструментів, на яку додає всі вбудовані елементи керування за допомогою циклу For і потім досліджує кожний із цих елементів. Подібний підхід є єдиним можливим у цьому випадку.
Лістінг 12.3. Складання списку номерів убудованих елементів керування
Public Sub ListControlIDs()
Dim fr As Integer
Dim cbar As Office.CommandBar
Dim ctl As CommandBarControl
Dim i As Integer
Const maxid = 4000
fr = FreeFile
Open "d:\temp\ids.txt" For Output As #fr
' Створення тимчасової панелі інструментів
Set cbar = Application.CommandBars.Add("temporary". msoBarTop._
False. True)
For i = 1 To maxid
On Error Resume Next ' Якщо неможливо пропустити, додати
елемент
cbar.Controls.Add Id:=I
Next I
On Error GoTo 0
For Each ctl In cbar.Controls
Print #fr. ctl.Caption & " " & ctl.Id
Next
cbar. Delete
Close #fr
Лістінг 12.4 демонструє невелику частину файлу, що отриманий у результаті запуску вищенаведеного коду в моїй системі. Повний список перебуває в додатку В.
Лістінг 12.4. Результат виконання коду з лістінга 12.3
<Custom> 1
&Spelling... 2
&Save 3
&Print... 4
&New... 18
&Copy 19
Cu&t 21
&Paste 22
Open 23
Can't Repeat 37
&Microsoft Word 42
Clear Contents 47
Custom 51
&Piggy Bank 52
Custom 59
&Double Underline 60
Custom 67
Custom 68
&Close 106
AutoFormat 107
&Format Painter 108
Print Pre&view 109
Custom 112
&Bold 113
&Italic 114
&Underline 115
Приклад використання вбудованих елементів керування буде розглянутий наприкінці даної глави, коли стане остаточно ясно, що вони собою представляють.

Завдання
Створити макрос який проводить пошук по комірках відповідний тип (текст; грошовий) і задає для усіх знайдених комірок відповідного типу колір, курсив, розмір шрифта ...
Створити макрос який рахує кількіть листів в робочій книзі. Виводить їх на форму з якої можна перейти до заданого листа
Створити макрос який дозволяє імпортувати таблицю з Word в Excel
Створити макрос який дозволяє швидко побудувати діаграму
Створити макрос який демострує роботу з колонтитулами
Створити макрос який дозволяє задавати та розбивати на відповідні формати сторінок
Створити макрос який демонструє роботу з друком
Створити макрос який рахує кількість комірок відповідного типу
Створити макрос який дозволяє швидко виконувати арифметичні дії над комірками
Створити макрос який проставляє доду, назву файлу, шлях при друці в нижньому колонтитулі
Створити макрос який демонструє роботу з фільтром
Створити макрос який центрує вміст комірок та змінює розмір та шрифт символів у комірці.
Створити макрос який змінює колір та розмір символів у комірках
Створити макрос який міняє місцями вмістиме двох комірок
Створити макрос який встановлює назву робочого листка
Створити макрос який додає примітку до комірки
Створити макрос який здійснює автозаповнення рядка комірок місяцями року
Створити макрос який копіює вмістиме однієї комірки в іншу
Створити макрос який міняє місцями два задані листа робочої книги
Створити макрос який добавляє новий рядок над заданим рядком
Створити макрос який вставляє формулу у комірку
Створити макрос який додає новий стовпчик справа віз заданого
Створити макрос який міняє місцями два задані рядки
Створити макрос який міняє місцями два задані стовпчики
Створити макрос який об’єднує дві комірки
Створити макрос який об’єднує два стовпчики
Створити макрос який присвоює комірці ім’я та центрує її вміст
Створити макрос який присвоює ім’я діапазону комірок
Створити макрос який видаляє вміст даного рядку
Створити макрос який центрує стовпчик
Створити макрос який додає в комірку текст заданого кольору
Створити макрос який змінює параметри комірки
Створити макрос який змінює колір фону комірки та колір символів
Створити макрос який змінює формат числа в комірці
Створити макрос який замінює формулу числовим значенням при копіюванні вмісту комірки
Створити макрос який видаляє весь уміст з робочої книги