Налагодження макросів і функцій користувача

• Налагодження програм при їх розробці

• Обробка помилок на етапі виконання

Якщо ви успішно засвоїли матеріал трьох попередніх глав, ви вже маєте деяке уявлення про роботу з мовою Visual Basic for Applications (VBA) і, можливо, хотіли б поглибити свої знання Хорошим способом удосконалюватися в будь-якій мові програмування є вивчення текстів програм і експериментування з ними Виконуючи щоденну роботу в Excel, проаналізуйте ваші дії і подумайте, чи не можна їх автоматизувати Коли ви знайдете такі дії, включите запис макросу, а потім уважно вивчіть код, що вийшов Якщо в тексті макросу вам зустрінуться незнайомі ключові слова, скористайтеся довідкою VBA Коли текст макросу стане для вас зрозумілим, спробуйте зробити його більш ефективним Спочатку видаліть ті інструкції, які здаються вам зайвими, і переконайтеся, що їх видалення дійсно не впливає на роботу макросу Потім відшукайте інструкції, що виділяють діапазони або інші обєкти, і спробуйте, де це можливо, обійтися без виділення, замінивши ці інструкції іншими

При збільшенні розмірів і складності створюваних макросів і функцій іноді вони просто не будуть працювати або будуть давати зовсім не той результат, якого ви очікували З подібними проблемами стикаються не тільки початківці програмісти, а й досвідчені «гуру» На щастя, при створенні мови і редактора VBA розробники вбудували в них набір засобів, що полегшують пошук і усунення помилок Ці кошти і будуть розглянуті в даній главі

Засоби роботи з помилками діляться на два класи Перший клас використовується на стадії розробки, тобто під час створення і редагування програмного коду, а другий клас – на стадії виконання програми

Налагодження програм при їх розробці

Засоби часу розробки редактора VBA дають можливість виправляти синтаксичні помилки в інструкціях мови, а також виявляти неправильно записані імена змінних Крім того, редактор дозволяє виконувати макрос або функцію в покроковому режимі, демонструючи зміна значень змінних

Виявлення синтаксичних помилок

При введенні некоректною формули Excel відмовляється прийняти її, видаючи попередження Компілятор VBA (системний компонент, що перетворює інструкції VBA в машинний код) веде себе аналогічно по відношенню до інструкцій Наприклад, якщо ви забули поставити круглі дужки там, де вони потрібні, компілятор зреагує на помилку відразу, як тільки ви натиснете клавішу Enter: буде видано пояснювальний повідомлення, а рядок, що містить помилку, – виділена кольором (але замовчуванням червоним)

Але деякі помилки компілятор не в змозі перехопити до моменту виконання макросу або функції Так, якщо ви введете код <

With SelectionBorder

.Weight = xlThin

.LineStyle = xlAutomatic

і спробуєте виконати його в такому вигляді (без завершальній інструкції End With),-то отримаєте наступне повідомлення:

Виконання процедури буде перервано, і ви опинитеся в режимі зупину Візуально це знайде відображення у вигляді слова break, укладеного в дужки, в заголовку вікна редактора VBA Рядок коду, яка привела до помилки, буде виділена кольором (за замовчуванням жовтим) Режим зупину дозволяє вам відредагувати код, а потім продовжити його виконання з того місця, де була виявлена ​​помилка Таким чином, якщо ви забули включити в процедуру інструкцію End With, ви можете додати її в режимі зупинки і, натиснувши клавішу F5, продовжити роботу макросу Для того щоб вийти з режиму зупину, в меню редактора виберіть команду Run ► Reset (Виконати ► Скидання)

Якщо ви не хочете, щоб компілятор повідомляв вам про синтаксичні помилки відразу після того, як ви їх допустили, виберіть у меню редактора команду Tools ► Options (Сервіс ► Установки), потім на вкладці Editor (Редактор) діалогового вікна Options (Параметри) зніміть прапорець Auto Syntax Check (Автоматична перевірка синтаксису), як показано на рис 331 Тепер всі синтаксичні помилки можуть бути виявлені лише при виконанні коду

Рис 331 Якщо ви не хочете виявляти синтаксичні помилки до виконання коду, зніміть прапорець Auto Syntax Check

РАДА

У діалоговому вікні Options (Параметри) ви також вправі змінити колір, яким редактор VBA позначає місце помилки Клацніть на вкладці Editor Format (Формат редактора), у списку Code Colors (Забарвлення синтаксису) виберіть спочатку елемент Syntax Error Text (Синтаксична помилка), а потім потрібний колір

Крім автоматичної перевірки синтаксису редактор VBA має ще три «автоматичні» функції, прапорці яких знаходяться у вкладці Editor (Редактор) діалогового вікна Options (Параметри): Auto List Members (Список членів), Auto Quick Info (Швидка довідка) і Auto Data Tips (Підказки для даних) Ці прапорці не рекомендується знімати, поки у вас немає достатніх навичок роботи з VBА Функції швидкої довідки та список членів допомагають вам у складанні інструкцій, показуючи можливі варіанти, доступні в поточній позиції точки вставки (курсора), або імена аргументів функції, яку ви в даний момент вводите Підказки для даних діють тільки в режимі зупинки Якщо ви помістіть курсор на імя змінної в режимі зупинки, зявиться виноска з поточним значенням цієї змінної

Помилки в іменах змінних

Компілятор VBA не розрізняє букви верхнього і нижнього регістрів в іменах змінних Наприклад, імена MyVar, myVar і myvar компілятор сприйме як ідентичні Якщо ви використовуєте різні написання одного й того ж імені змінної, редактор VBA автоматично приведе їх до одного виду Однак при роботі з іменами змінних в VBA є одна дуже неприємна особливість: в випадку, якщо ви редагуєте імя в середині програми, компілятор створює нову змінну, що не кращим чином позначається на працездатності вашої програми Подібні помилки іноді дуже важко виявити, оскільки робота програми до їх прояви не викликає нарікань

Справитися з цими помилками можна програмним шляхом, вставивши в самому початку модуля (до інструкцій Sub або Function) інструкцію

Option  Explicit

Наявність інструкції Option Explicit зобовязує васоголошувативсі змінні, використовувані в модулі, за допомогою інструкції Dim (для отримання довідки про інструкцію Dim наберіть слово Dim в будь-якому місці коду та натисніть клавішу F1) Якщо ви тепер спробуєте звернутися до змінної без її явного оголошення, при виконанні програми отримаєте повідомлення про помилку

Краще взагалі задати режим автоматичної вставки інструкції Option Explicit при створенні кожного нового модуля Для цього виберіть у меню редактора команду Tools ► Options (Сервіс ► Установки) і потім на вкладці Editor (Редактор) діалогового вікна Options (Параметри) встановіть прапорець Require Variable Declaration (Вимагати оголошення змінних) За замовчуванням цей прапорець знятий, але явне оголошення змінних є хорошим тоном в програмуванні Це не тільки позбавить вас від помилок, що виникають через неправильне написання імен змінних, але і змусить обдумувати свої дії заздалегідь

Покрокове виконання коду

За допомогою команд меню редактора VBA можна ініціювати обробку однієї інструкції або блоку інструкцій, а потім в режимі зупинки визначати свої подальші дії: дати команду на виконання наступного кроку, перейти в нормальний режим виконання або взагалі завершити роботу макросу або користувальницької процедури Режим покрокового виконання надає вам корисні відомості про стан програми Наприклад, дає побачити, які гілки програми фактично працюють в блоках If або Select Case (Інструкція Select Case дозволяє перейти до виконання однієї з декількох гілок програми в залежності від поточного значення деякої змінної Більш детальну інформацію ви отримаєте, набравши в коді ключове слово Select Case і натиснувши клавішу F1) На кожному кроці ви можете також переглядати поточні значення змінних

РАДА-

Щоб під час покрокового виконання інструкцій бачити значення змінних, відкрийте вікно Watch (Спостереження) або діалогове вікно Quick Watch (Швидке спостереження) або підведіть покажчик миші до імені потрібної змінної, перебуваючи в режимі зупинки Про використання вікна спостереження піде мова в розділі «Спостереження за значеннями змінних і властивостей»

Для налагодження програми в покроковому режимі призначені чотири команди меню

Debug (Налагодження):

► Step Into (Крок із заходом) обробляє наступну по порядку інструкцію

► Step Over (Крок без заходу) аналогічна команді Step Into (Крок із заходом), за винятком випадку, коли наступною інструкцією є виклик процедури При кроці із заходом ви перейдете в тіло спричиненої процедури, а у варіанті кроку без заходу спочатку виконається викликається процедура, а останов буде виконаний на інструкції, розташованої за місцем виклику процедури

► Step Out (Останов наприкінці процедури) виконує всі залишилися інструкції те-

Кучок процедури

► Run To Cursor (Останов в позиції курсора) виконує всі інструкції до поточної позиції курсора

Кожній з команд меню Debug (Налагодження) присвоєна клавіатурна комбінація швидкого доступу Так, наприклад, замість вибору Debug ► Run To Cursor (Виконати

► Останов в позиції курсора) можна натиснути клавіші Ctrl + F8, а замість команди Step Into (Крок із заходом) – клавішу F8

Установка контрольних точок

Контрольна точка (Або, що те ж, точка зупинки) – це інструкція в процедурі, перед виконанням якої компілятор переходить в режим зупину Найпростіший спосіб встановити контрольну точку – вибрати в меню редактора VBA

команду Debug ► Toggle Breakpoint (Налагодження ► Контрольна точка) або натиснути клавішу F9 Щоб видалити точку зупину, виберіть цю ж команду ще раз Подібним способом ви можете задати в процедурі стільки контрольних точок, скільки забажаєте Команда Toggle Breakpoint (Контрольна точка) задає так звану безумовну контрольну точку,в якій компілятор завжди переходить в режим зупину, як тільки лічильник інструкцій доходить до цього місця Існують також іумовні контрольні точки,в яких виконання переривається лише при дотриманні деякої умови Про те, як визначити умовні контрольні точки, ми розповімо в наступному розділі

Як показано на рис 332, в редакторі VBA точки зупину виділені контрастним кольором, а на кордоні вікна коду відображається маркер «•» Ви вправі змінити колір, яким виділяються контрольні точки Для цього виберіть команду Tools ► Options (Сервіс ► Установки), клацніть на вкладці Editor Format (Формат редактора) вікна параметрів і виберіть у списку елемент Breakpoint Text (Текст контрольної точки)

Рис 332 Редактор VBA виділяє кольором контрольні точки

Програмне завдання умовних контрольних точок

За допомогою методу Assert обєкта Debug можна дати вказівку компілятору VBA перейти в режим зупину у разі, якщо заданий вираз має значення БРЕХНЯ На рис 333 представлений приклад, який ілюструє сказане

Рис 333 Тут інструкція DebugAssert переводить компілятор в режим зупину, коли х стає рівним 9

У цьому фрагменті коду, по суті яке не несе ніякої корисної навантаження, критерієм продовження є х < 9. Коли це умова перестає виконуватися, компілятор перериває роботу процедури. Це відбувається, коли змінна х стає рівною 9

ПРИМІТКА

Насправді умовні контрольні точки можна визначати і з інтерфейсу користувача Про те, як це зробити, піде мова в розділі «Установка умовних контрольних точок засобами інтерфейсу»

Спостереження за значеннями змінних і властивостей

Під час покрокового виконання процедури часто буває важливо відстежувати зміни, які з перемінними і обєктами в результаті виконання тих чи інших інструкцій У VBA є відповідне засіб – вікно Watch (Спостереження), в якому відображаються поточні значення змінних або виразів і властивості виділених обєктів

Для того щоб відобразити вікно спостереження, виберіть у редакторі VBА команду View

► Watch Window (Вид ► Вікно спостереження) (Щоб приховати вікно, клацніть на його кнопці закриття) Для додавання змінної, вирази або обєкта у вікно спостереження

виділіть цей елемент в коді, а потім перетягніть його у вікно Watch (Спостереження)

Існує ще один спосіб включення елементів у список що відслідковуються У редакторі VBA виберіть команду Debug ► Add Watch (Налагодження ► Встановити спостереження) У полі Expression (Вираз) діалогового вікна Add Watch (Додати спостереження), представленого на рис 334, введіть імя змінної або правильне вираз VBA

Установка умовних контрольних точок засобами інтерфейсу

Для того щоб визначити умовну контрольну точку з діалогового вікна Add Watch (Додати спостереження) (рис 334), в поле Expression цього вікна введіть імя змінної або вираз, а потім встановіть перемикач Watch Туре (Тип контролю) у положення Break When Value Is True (Останов з виконання) або Break When Value Changes (Останов по зміні) У першому випадку контрольна точка буде встановлена ​​за аналогією з методом DebugAssert Різниця полягає лише в тому, що програмний останов провадиться, якщо результатом вираження є БРЕХНЯ, а певний в інтерфейсі – коли вираз зводиться до ІСТИНИ

Рис 334 Діалогове вікно Add Watch дозволяє додати спостережувану змінну і задати умовну точку зупину

Діалогове вікно швидкого контролю

У режимі зупинки можна виділити будь-яку змінну або вираз в тексті програми, вибрати команду Debug ► Quick Watch (Налагодження ► Швидкий контроль) (або натиснути клавіші Shift + F9), щоб побачити поточне значення певного елемента Якщо ви хочете спостерігати за цим елементом безперервно весь час налагодження програми, клацніть у діалоговому вікні Quick Watch (Швидкий контроль) на кнопці Add (Додати)

Тепер про стан виділеної змінної або виразу вас інформуватиме вікно Watch (Спостереження)

Робота з вікном налагодження

Коли ви перебуваєте в режимі зупинки або тільки збираєтеся запустити процедуру, ви можете вирахувати значення будь-якого вираження у вікні Immediate (Вікно налагодження) (якщо воно не присутня на екрані, виберіть команду View ► Immediate Window (Вид ► Вікно) або натисніть клавіші Ctrl + G) Наприклад, для того щоб дізнатися поточне значення змінної х, в вікні налагодження потрібно ввести Print х або х (В редакторі VBA знак питання є скороченим еквівалентом ключового слова Print)

Вікно налагодження (інакше безпосереднього контролю – immediate) також дозволяє стежити за зміною елементів під час виконання процедури Це робиться спільно з методом DebugPrint Так, якщо ви дозволите в процедуру інструкцію DebugPrint x, при досягненні місця розташування даної отладочной інструкції поточне значення змінної х буде показано в цьому вікні

Вікно налагодження відноситься до засобів часу розробки, хоча ви як автор можете перевіряти інструкції VBA не тільки під час створення процедури, а й при її виконанні Якщо ви точно не знаєте, як працюватиме конкретна інструкція, розставте налагоджувальні інструкції обєкта Debug і випробуйте програму, спостерігаючи за проміжними результатами у вікні налагодженняОбробка помилок на етапі виконання

Як правило, помилки, які трапляються під час виконання програми, знаходяться поза контролем користувача Припустимо, що ви створили макрос, форматує числа у виділеному діапазоні в одиницях індійської монетарної системи: 10 000 000 рупій крор (сгоге), 100 000 рупій лак (lakh)

Sub LakhsCrores() Dim cell as Object

For Each cell In Selection

If Abs(cellValue) &gt 10000000 Then

cellNumberFormat = &quot’#&quot&quot ,&quot&quot##&quot&quot, &quot&quot##,,,,

&quot&quot###&quot

Elself Abs(cellValue) &gt 100000 Then cellNumberFormat = &quot#&quot&quot,&quot&quot##&quot&quot,&quot&quot###&quot

.End If Next cell

End Sub

Цей макрос працює безпомилково, якщо вихідний діапазон містить тільки числа Але якщо користувач перед запуском макросу виділить, наприклад, впроваджену в робочий лист діаграму, він отримає наступне повідомлення про помилку:

Макрос згенерував якусь помилку виконання 438 і перейшов у режим зупину тому, що інструкція For Each передбачає роботу з масивом або сімейством обєктів, в той час як діаграма не є ні тим ні іншим (діапазон в термінах обєктної моделі – сімейство осередків) Автор процедури LakhsCrores миттєво встановить причину цієї помилки і виправить її, однак подив інших користувачів тут цілком обгрунтовано

Для того щоб позбавити себе та інших від отримання подібних малоінформативних повідомлень про помилки, включіть в макрос інструкцію On Error GoTo Ця інструкція повинна передувати фрагменту коду, потенційно який представляє собою джерело помилки під час виконання, і записується у вигляді On Error GoTo label, де label – Імя мітки, що вказує на блок обробки помилок Якщо користувач зіткнеться з некоректною роботою макросу, інструкція On Error GoTo передасть управління оброблювачу помилок Застосуємо її до макросу LakhsCrores:

Sub  LakhsCroresQ Dim cell   as  Object

‘Обробка помилки, викликаної некоректним виділенням

On Error Goto Errorhandler

For  Each cell   In Selection

If Abs(cellValue)   &gt  10000000 Then cellNumberFormat =  &quot#&quot&quot,&quot&quot##&quot&quot,&quot&quot##&quot&quot,&quot&quot###&quot Elself

Abs(cellValue)  &gt  100000 Then

cellNumberFormat =  &quot#&quot&quot,&quot&quot##&quot&quot,&quot&quot###&quot End  If Next cell

‘Інструкція Exit Sub забезпечує нормальне завершення

‘Макросу, якщо помилки не відбулося

Exit  Sub

‘Обробник помилок

Errorhandler:

MsgBox Необхідно виділити діапазон робочого аркуша End Sub

Тут при виникненні помилки управління передається тій інструкції, на яку вказує мітка Errorhandler Після імені мітки завжди слід двокрапка, а після нього на цій же рядку не повинні бути присутніми інші символи

Перед обробником помилок розташовується інструкція переходу, в даному випадку Exit Sub, яка забезпечує повернення з макросу при нормальному завершенні Якщо ж відбудеться збій, користувач отримає інформативне повідомлення, а не безлику помилку 438

Хоча ми впоралися з некоректним виділенням, це далеко не всі прийняті до уваги проблеми Наприклад, макрос безпомилково виконається лише тоді, коли всередині виділеного діапазону містяться виключно числа Якщо ж виявиться, що діапазон крім чисел зберігає інші дані, наприклад помилкове значення # Н / Д (# NA), то при виконанні макросу станеться інша помилка з номером 13, повязана з невідповідністю типів Це означає, що такий обробник помилок не зуміє виручити нас в даній ситуації

Потрібно дещо ускладнити алгоритм обробки помилки і залучити до розгляду її ідентифікаційний код Значенням властивості Number обєкта Err завжди є номер останньої помилки часу виконання (0 при нормальному завершенні) Знаючи це, ми можемо змінити обробник так, щоб відстежувалися обидві помилки:

ErrorHandler:

If  ErrNumber = 438 Then

MsgBox Необхідно виділити діапазон робочого аркуша

Else

MsgBox Виділений діапазон не повинен містити значний помилок

End  If

Якщо ж уявити, що в даній ситуації можуть статися і інші невідомі нам помилки, то ми можемо переписати обробник таким чином:

ErrorHandler:

If ErrNumber = 438 Then

MsgBox Необхідно виділити діапазон робочого аркуша

Else Else

MsgBox Виділений діапазон не повинен містити значний Н / Д” MsgBox Невідома помилка”

End If

Звичайно, і такий обробник не є досконалим, але все-таки він бере в розрахунок основні потенційні неприємності

Всі розглянуті вище варіанти створювалися з метою замінити повідомлення про помилку VBA на більш інформативне, що пояснює користувачеві причину збою в роботі Далі виконання процедури закінчувалося

Можливо, в деяких ситуаціях ви захочете замість завершення процедури продовжити її виконання Тоді використовуйте інструкцію Resume або Resume Next Перша передає управління інструкції, що викликала помилку, а друга – наступної за Іструкції – джерелом помилки Якщо ви впевнені, що можливі помилки не є критичними для роботи програми, просто ігноруйте їх за допомогою інструкції On Error Resume Next Перепишемо макрос LakhsCrores з урахуванням викладеного:

Sub LakhsCrores() Dim cell as Object

‘Ігноруємо будь-які помилки виконання

On Error Resume Next

For Each cell In Selection

If Abs(cellValue) &gt 10000000 Then cellNumberFormat = &quot#&quot&quot,&quot&quot##&quot&quot,&quot&quot##&quot&quot,&quot&quot###&quot

Elself Abs(cellValue) &gt 100000 Then cellNumberFormat = &quot#&quot&quot,&quot&quot##&quot&quot,&quot###&quot

End If Next cell End Sub

Тепер, якщо користувач виділить діаграму і запустить макрос, помилка буде проігнорована і макрос взагалі не виконає ніяких дій Якщо ж користувач виділить діапазон, що містить одну або кілька значень # Н / Д, макрос просто пропустить всі осередки, що не зуміє відформатувати Знову ж без будь-яких повідомлень Подібне рішення є ідеальним для програми даного рівня складності

Зрозуміло, при використанні інструкції On Error Resume Next ви повністю відключаєте контроль помилок виконання з боку VBA Відповідно потрібно бути повністю впевненим у бездоганній роботі своєї програми, а єдиний спосіб домогтися такої впевненості – це ретельно і багаторазово протестувати її

•&nbsp&nbsp&nbsp

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*