4 помилки, які ви можете уникнути під час програмування макросів Excel за допомогою VBA

Microsoft Excel вже дуже ефективний інструмент аналізу даних, але з можливістю автоматизації повторюваних завдань за допомогою макросів., написавши простий код на (VBA), це набагато потужніше. Однак при неправильному використанні VBA може викликати багато проблем.

Відкрийте БЕЗКОШТОВНУ шпаргалку «Essential Excel Oneulas» прямо зараз!

Це підпише вас на нашу розсилку

Введіть адресу електронної пошти

[] [] [] [] розблокування

Прочитайте нашу політику конфіденційності

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

Незалежно від того, чи є ви гуру VBA і створюєте інформаційні панелі в Excel, або новачок, який знає тільки, як писати прості скрипти, які виконують базові обчислення комірок, ви можете слідувати простим методам програмування, які допоможуть вам поліпшити шанси написання чистого і безпомилкового коду.

Початок роботи з VBA

Якщо ви раніше не програмували в VBA в Excel, включити інструменти розробника насправді досить просто. Просто зайдіть до Файла > Параметри і потім Налаштуйте подачу. Просто перемістіть групу команд розробника з лівої панелі на праву.

Переконайтеся, що позначено цей пункт, і тепер вкладка Розробник з'явиться у вашому меню Excel.

На даний момент найпростіший спосіб потрапити у вікно редактора коду - просто натиснути кнопку «Переглянути код» у розділі «Елементи керування» в меню «Розробник».

1. Жахливі імена змінних

Тепер, коли ви перебуваєте у вікні коду, прийшов час приступити до написання коду VBA. Створити. Першим важливим кроком у більшості програм, будь то VBA або будь-яка інша мова, є визначення ваших змінних.

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

• Зробіть їх якомога коротшими.
• Зробіть їх якомога більш описовими.
• Передзварюйте їх типом змінної (логічне, ціле тощо).
• Не забудьте використовувати правильну область (див. Нижче).

Ось приклад скріншоту з програми, яку я часто використовую для виконання викликів WMIC Windows з Excel для збору інформації про ПК. інформація

Якщо ви хочете використовувати змінні всередині будь-якої функції всередині додатка або об'єкта (я поясню це нижче), вам потрібно оголосити її як «публічну» змінну, попередньо додавши оголошення в Public. В іншому випадку змінні оголошуються за допомогою слова Dim.

Як ви можете бачити, якщо змінна є цілим числом, їй передує int. Якщо це рядок, то вул. Це допомагає пізніше, коли ви програмуєте, тому що ви завжди будете знати, який тип даних містить змінна, просто поглянувши на ім'я. Ви також помітите, що якщо є щось на зразок рядка, що містить ім'я комп'ютера, то змінна називається strComputerName.

Уникайте створення дуже заплутаних або заплутаних імен змінних, які зрозумілі тільки вам. Зробіть так, щоб іншому програмісту було легше прийти за вами і зрозуміти, що все це означає!

Ще одна помилка, яку допускають люди, - залишити імена аркушів за замовчуванням «Sheet1», «Sheet2» і т. Д. Це додає плутанину в програму. Замість цього назвіть аркуші так, щоб вони мали сенс.

Таким чином, при зверненні до імені аркуша в коді Excel VBA, ви маєте на увазі ім'я, яке має сенс. У наведеному вище прикладі у мене є аркуш, де я отримую інформацію про мережу, тому я називаю аркуш «Мережа». Тепер у коді, коли б я не захотів послатися на аркуш мережі, я можу зробити це швидко, не переглядаючи, який це номер аркуша.

2. Розірвати замість циклу

Одна з найпоширеніших проблем, які є у нового програміста: коли вони починають писати код, який правильно обробляє цикли. А оскільки багато людей, які використовують Excel VBA, є новачками в коді, погане зациклювання - це епідемія.

Цикли дуже поширені в Excel, тому що часто ви обробляєте значення даних по всьому рядку або стовпчику, тому вам потрібно виконати цикл для обробки всіх їх. Нові програмісти часто хочуть просто вирватися з циклу (або цикл For, або погляд while), коли певна умова виконується.

Ви можете ігнорувати складність наведеного вище коду, просто зазначте, що всередині внутрішнього оператора IF є можливість вийти з циклу For, якщо умова істинна. Ось більш простий приклад:

For x = 1 To 20 If x = 6 Then Exit For y = x + intRoomTemp Next i

Нові програмісти використовують цей підхід, тому що це легко. Коли виникає умова, якої ви чекаєте, щоб вийти з циклу, виникає спокуса просто негайно вистрибнути з неї, але не робіть цього.

Найчастіше код, який слід після цієї «перерви», важливий для обробки, навіть востаннє, коли виконується цикл перед виходом. Набагато більш чистий і професійний спосіб обробки умов, в яких ви хочете залишити цикл на півдорозі, полягає в тому, щоб просто включити цю умову виходу в щось на зразок оператора While.

While (x>=1 AND x<=20 AND x<>6) For x = 1 To 20 y = x + intRoomTemp Next i Wend

Це враховує логічний потік вашого коду, з останнім прогоном, коли x дорівнює 5, і потім плавним виходом, коли цикл For рахує до 6. Немає необхідності включати незручні команди EXIT або BREAK в середині циклу.

3. Не використовувати масиви

Ще одна цікава помилка, що нові програмісти VBA make намагаються обробити все, що знаходиться всередині численних вкладених циклів, які фільтрують рядки і стовпчики в процесі обчислення.

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

Якщо ви ніколи раніше не використовували масив, не бійтеся. Уявіть масив у вигляді лотка для кубиків льоду з певною кількістю «кубиків», в які можна помістити інформацію. Куби пронумеровані від 1 до 12, і саме так ви «поміщаєте» в них дані.

Ви можете легко визначити масив, просто набравши Dim arrMyArray (12) як Integer.

Це створює «лоток» з 12 слотами, які ви можете заповнити.

Ось як може виглядати код циклу рядка без масиву:

Sub Test1() Dim x As Integer intNumRows = Range(""A2"", Range(""A2"").End(xldown)).Rows.Count Range(""A2"").Select For x = 1 To intNumRows If Range(""A"" & str(x)).value < 100 then intTemp = (Range(""A"" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub

У цьому прикладі код обробляє кожну комірку в діапазоні і виконує розрахунок температури.

Пізніше в програмі, якщо ви коли-небудь захочете виконати якийсь інший розрахунок для цих же значень, вам доведеться продублювати цей код, обробити всі ці комірки і виконати новий розрахунок.

Тепер, якщо ви натомість використовуєте масив, ви можете зберегти 12 значень у рядку у вашому зручному масиві зберігання. Потім, коли ви захочете виконати обчислення для цих чисел, вони вже знаходяться в пам'яті і готові до роботи.

Sub Test1() Dim x As Integer intNumRows = Range(""A2"", Range(""A2"").End(xldown)).Rows.Count Range(""A2"").Select For x = 1 To intNumRows arrMyArray(x-1) = Range(""A"" & str(x)).value) ActiveCell.Offset(1, 0).Select Next End Sub

«X-1» для зазначення на елемент масиву необхідний тільки тому, що цикл For починається з 1. Елементи масиву повинні починатися з 0.

Але як тільки ваш масив буде завантажений значеннями з рядка, пізніше в програмі ви можете просто зібрати будь-які обчислення, використовуючи цей масив.

Sub TempCalc() For x = 0 To UBound(arrMyArray) arrMyTemps(y) = arrMyArray(x) * 32 - 100 Next End Sub

Цей приклад переглядає весь масив рядків (UBound дає вам кількість значень даних в масиві), виконує обчислення температури, а потім поміщає його в інший масив з назвою arrMyTemps.

Ви можете побачити, наскільки простішим є другий код розрахунку. І з цього моменту, кожен раз, коли ви хочете виконати більше обчислень для того ж набору чисел, ваш масив вже попередньо завантажений і готовий до роботи.

4. Використання забагато посилань

Незалежно від того, чи програмуєте ви в повнофункціональному Visual Basic або VBA, вам потрібно буде включити «посилання» для доступу до певних функцій, таких як доступ до бази даних Access або запис вихідних даних до текстового файла.

Посилання - це щось на зразок «бібліотек», наповнених функціями, які можна використовувати, якщо ви ввімкнете цей файл. Ви можете знайти «Посилання» у перегляді «Розробник», клацнувши «Інструменти» в меню, а потім натисніть «Посилання».

У цьому вікні ви знайдете всі вибрані посилання для поточного проекту VBA.

З мого досвіду, вибір тут може змінюватися від однієї установки Excel до іншої і з одного комп'ютера на інший. Багато залежить від того, що інші люди зробили з Excel на цьому ПК, чи були додані або включені певні надбудови або функції, або деякі інші програмісти могли використовувати певні посилання в минулих проектах.

Причина, з якої варто перевірити цей список, полягає в тому, що непотрібні посилання витрачають ресурси системи. Якщо ви не використовуєте якісь маніпуляції з XML-файлами, тоді навіщо вибирати Microsoft XML? якщо ви не спілкуєтеся з базою даних, вилучіть Microsoft DAO. Якщо ви не записуєте вихідні дані в текстовий файл, вилучіть Microsoft Scripting Runtime.

Якщо ви не впевнені, що роблять ці вибрані посилання, натисніть F2, і ви побачите переглядач об'єктів. У верхній частині цього вікна ви можете обрати довідкову бібліотеку для перегляду.

Після вибору ви побачите всі об'єкти і доступні функції, на які ви можете натиснути, щоб дізнатися більше.

Наприклад, коли я натискаю на бібліотеку DAO, швидко стає ясно, що це все про підключення до баз даних і зв'язки з ними.

Скорочення кількості посилань, які ви використовуєте у своєму програмному проекті, - це просто здоровий глузд і допоможе підвищити ефективність роботи всього додатку.

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

Сама ідея написання коду в Excel лякає багатьох людей, але цей страх дійсно не потрібен. Visual Basic для програм - це дуже проста мова для вивчення, і якщо ви будете слідувати основним загальним практикам, згаданим вище, ви переконайтеся, що ваш код чистий, ефективний і простий для розуміння.

Ви код у VBA? Які уроки ви отримали за ті роки, якими ви можете поділитися з іншими читачами, які вперше вивчають VBA? Поділіться своїми порадами в розділі коментарів нижче!