Макрореєстратор Excel має багато потужності, але має свої обмеження. Як висвітлено в іншій статті, макрореєстратор часто записує непотрібний код і не може записувати такі речі, як логіка або взаємодія з іншими програмами. Його також може бути важко використовувати для довших макросів - ви можете заздалегідь описувати свої дії, щоб уникнути дорогих помилок.
Ця стаття має на меті допомогти вам почати кодування макросів з нуля у VBA. Ви дізнаєтесь, де зберігаються макроси, напишете базовий макрос та вивчите основи програмування у VBA за допомогою змінних, логіки та циклів.
Починаємо
VBA та редактор Visual Basic
VBA або Visual Basic for Applications-це мова, на якій написані макроси. Усі макроси зберігаються у вигляді коду VBA, незалежно від того, кодуються вони вручну або створені за допомогою записувача макросів.
Ви можете отримати доступ до всього коду VBA у книзі за допомогою редактора Visual Basic. Це спеціальний текстовий редактор та налагоджувач, який вбудований у всі офісні програми, включаючи Excel. Як правило, ви відкриваєте цей редактор за допомогою ALT+F11 сполучення клавіш в Excel, але ви також можете отримати до нього доступ із Excel Розробник вкладку, якщо вона включена.
Провідник проектів
The Провідник проектів - це вікно всередині редактора VB, яке показує всі елементи, які можуть містити код VBA. Якщо це вікно не відображається, натисніть F5 щоб він з’явився або вибрав Провідник проектів від Перегляд меню.
Двічі клацнувши елемент у Провіднику проектів, буде показано код цього елемента. У Провіднику проектів можуть з'являтися кілька типів елементів:
- Робочі зошити
- Робочі листи
- UserForms
- Класні модулі
- Модулі (макроси зберігаються в цих елементах)
Хоча всі ці типи елементів можуть включати код VBA, найкращою практикою є кодування макросів у модулях.
Створення першого макросу
Використання списку макросів
У списку макросів відображаються всі макроси у вашій книзі. З цього списку можна редагувати наявний макрос або створити новий.
Щоб створити новий макрос за допомогою списку Макроси:
- Виберіть вкладку Розробник і натисніть Макроси (або натисніть ALT+F8)
- Введіть нову назву вашого макросу, а потім натисніть «Створити»
Після натискання кнопки «Створити» з’явиться редактор VB, де буде показано новостворений макрос. Excel за необхідності створить новий модуль для макросу.
Вручну в редакторі VB
Ви можете додати новий макрос вручну без списку макросів. Це кращий варіант, якщо ви хочете вказати модуль, у якому зберігається макрос.
Щоб додати макрос вручну:
- Відкрийте редактор VB (ALT+F11)
- Або:
- Додайте новий модуль, натиснувши Вставка> Модуль у меню (модуль автоматично відкриється)
-
- АБО двічі клацніть наявний модуль у Провіднику проектів, щоб відкрити його
- У модулі введіть код нового макросу
Sub MyMacro () Кінець Sub
Ці два рядки вказують на початок і кінець макросу з назвою “MyMacro” (зверніть увагу на дужки, які обов’язкові). Це відобразиться у діалоговому вікні "Перегляд макросів" у Excel і може бути призначено кнопці (хоча вона ще нічого не робить).
Додайте деякий код до макросу
Тепер давайте додамо деякий код між рядками “Sub” та “End Sub”, щоб цей макрос дійсно щось зробив:
Діапазон Sub MyMacro () ("A1"). Значення = "Привіт, Світ!" End Sub
Основні структури коду
Об'єкт діапазону
Excel VBA використовує об’єкт діапазону для представлення клітинок на аркуші. У наведеному вище прикладі об’єкт Range створюється з кодом Діапазон ("A1") для доступу до значення комірки A1.
Об'єкти діапазону в основному використовуються для встановлення значень комірок:
Діапазон ("A1"). Значення = 1
Діапазон ("A1"). Значення = "Перша клітина"
Зверніть увагу, що, визначаючи значення клітинки як числа, ви просто вводите число, але при введенні тексту ви повинні оточити текст лапками.
Діапазони також можна використовувати для доступу до багатьох властивостей клітинок, таких як їх шрифт, рамки, формули тощо.
Наприклад, ви можете встановити шрифт клітинки напівжирним таким чином:
Діапазон (“A1”). Font.Bold = True
Ви також можете встановити формулу комірки:
Діапазон ("A1"). Формула = "= Сума (A2: A10)"
В Excel ви можете виділити блок клітинок курсором (скажімо, від A1 до D10) і встановити всі їх жирним шрифтом. Об'єкти діапазону можуть отримати доступ до блоків комірок таким чином:
Діапазон (“A1: D10”). Font.Bold = True
Ви також можете звернутися до кількох клітинок/блоків одночасно:
Діапазон (“A1: D10, A12: D12, G1”). Font.Bold = True
Формат для цього такий самий, як і для формату, який би ви використовували при виборі комірок для формули SUM () в Excel. Кожен блок відокремлюється комою, а блоки позначаються верхньо-лівою та правою нижньою клітинками, розділеними двокрапкою.
Нарешті, об’єкти Range мають вбудовані методи виконання загальних операцій на аркуші. Наприклад, ви можете скопіювати деякі дані з одного місця в інше. Ось приклад:
Діапазон ("A1: D10"). Діапазон копіювання ("F1"). Діапазон PasteSpecial xlPasteValues ("F1"). PasteSpecial xlPasteFormats
Це копіює клітинки A1: D10 у буфер обміну, а потім робить PasteSpecial (), починаючи з клітинки C1 - так само, як ви це зробили б вручну в Excel. Зверніть увагу, що в цьому прикладі показано, як за допомогою PasteSpecial () вставляти лише значення та формати - є параметри для всіх параметрів, які ви побачите у діалоговому вікні "Спеціальна вставка".
Ось приклад вставлення "Все" на інший аркуш:
Діапазон ("A1: D10"). Копіювати аркуші ("Аркуш2"). Діапазон ("A1"). Вставити Спеціальне xlPasteAll
Якщо Заяви
З Якщо заява, Ви можете зробити розділ коду запущеним лише "якщо" певне твердження відповідає дійсності.
Наприклад, ви можете зробити клітинку жирною і пофарбувати її в червоний колір, але лише "якщо" значення в комірці менше 100.
Якщо діапазон ("A4"). Значення <100 Тоді діапазон ("A4"). Font.Bold = Істинний діапазон ("A4"). Interior.Color = vb Червоний кінець Якщо
Правильна структура оператора If виглядає наступним чином (квадратні дужки вказують на необов’язкові компоненти):
Якщо тоді
[В іншому випадку]
[Інакше]
Закінчити Якщо
Ви можете включити стільки В іншому випадку блокує, як вам подобається перевіряти кілька умов. Ви також можете додати Інакше блок, який виконується, тільки якщо жодна з інших умов у операторі If не виконується.
Ось ще один приклад, заснований на попередньому, де клітинка форматується кількома різними способами залежно від значення:
Якщо Діапазон ("A4"). Значення <100 Тоді Діапазон ("A4"). Font.Bold = Дійсний діапазон ("A4"). Interior.Color = vbRed ElseIf Діапазон ("A4"). Значення <200 Тоді Діапазон ( "A4"). Font.Bold = False Range ("A4"). Interior.Color = vb Жовтий діапазон ("A4"). Font.Bold = False Range ("A4"). Interior.Color = vbGreen End Якщо
У наведеному вище прикладі клітинка не виділяється жирним шрифтом у блоках ElseIf, де значення не менше 100. Ви можете гніздо Якщо оператори уникають дублювання коду, наприклад:
Якщо діапазон ("A4"). Значення <100 Тоді діапазон ("A4"). Font.Bold = Істинний діапазон ("A4"). Interior.Color = vb Червоний діапазон ("A4"). Font.Bold = False ' відміняти шрифт лише один раз, якщо діапазон ("A4"). Значення <200 Тоді діапазон ("A4"). Інтер'єр. Колір = vb Жовтий діапазон ("A4"). Інтер'єр. Колір = vbЗелеве закінчення, якщо кінець Якщо
Змінні
А. Змінна - це частина пам'яті, яка використовується для зберігання тимчасової інформації під час виконання макросу. Вони часто використовуються в циклах як ітератори або для утримання результату операції, яку потрібно використати кілька разів у макросі.
Ось приклад змінної та спосіб її використання:
Sub ExtractSerialNumber () Dim strSerial As String 'це оголошення змінної' 'As String' 'означає, що ця змінна призначена для утримання тексту' встановлення прикидного серійного номера: Діапазон ("A4"). Значення = "serial# 804567-88 ”'Аналізуйте серійний номер із комірки А4 та присвойте його змінній strSerial = Mid (Діапазон (“ A4 ”). Значення, 9)“ тепер використовуйте змінну двічі, замість того, щоб розбирати серійний номер двічі Range (“ B4 ”). Значення = strSerial MsgBox strSerial End Sub
У цьому базовому прикладі змінна ‘strSerial’ використовується для вилучення серійного номера з комірки A4 за допомогою функції Mid (), а потім використовується у двох інших місцях.
Стандартний спосіб заявляти змінна виглядає наступним чином:
Dim будь -яке ім'я [Як тип]
- будь -яке ім'я - це ім'я, яке ви вирішили дати своїй змінній
- тип - це тип даних змінної
Документ «[Як тип] ”Частину можна пропустити - якщо так, змінна оголошується як тип Variant, який може містити будь -які дані. Хоча цілком допустимі, типи варіантів слід уникати, оскільки вони можуть призвести до несподіваних результатів, якщо ви не будете обережні.
Існує правила для імен змінних. Вони повинні починатися з літери чи символу підкреслення, не можуть містити пробілів, крапок, ком, лапок або символів «! @ & $ #».
Ось кілька прикладів оголошень змінних:
Dim strFilename As String 'стиль хорошого імені - описовий і використовує префікс Dim i As Long' стиль поганого імені - прийнятний лише для деяких ітераторів Dim SalePrice As Double 'добре назва стилю - описовий, але не використовує префікс Dim iCounter' добре ім'я - не дуже описовий, використовує префікс, немає типу даних
У всіх цих прикладах використовуються дещо різні схеми іменування, але всі дійсні. Не погана ідея додати до імені змінної префікс короткої форми типу її даних (згідно з деякими з цих прикладів), оскільки це робить ваш код більш читабельним на перший погляд.
VBA містить багато основних типи даних. Найпопулярніші з них:
- Рядок (використовується для зберігання текстових даних)
- Довго (використовується для утримання цілих чисел, тобто без десяткових знаків)
- Подвійний (використовується для зберігання чисел із плаваючою комою, тобто десяткових знаків)
Повний перелік внутрішніх типів даних VBA можна знайти тут: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Змінні об'єкта діапазону
Можна також створювати змінні, які посилаються на об’єкти діапазону. Це корисно, якщо ви хочете посилатися на певний діапазон у своєму коді в декількох місцях - таким чином, якщо вам потрібно змінити діапазон, вам потрібно змінити його лише в одному місці.
Коли ви створюєте об'єктну змінну Range, вам потрібно "встановити" її на екземпляр діапазону. Наприклад:
Dim rMyRange As Range Set rMyRange = Діапазон (“A1: A10; D1: J10”)
Якщо залишити оператор “Set” при призначенні змінної Range, це призведе до помилки.
Петлі
Цикли - це блоки, які повторюють код всередині них певну кількість разів. Вони корисні, щоб зменшити кількість коду, який ви повинні написати, і дозволяють вам написати один фрагмент коду, який виконує однакові дії над багатьма різними пов'язаними елементами.
For-Next
А. For-Next блок - це цикл, який повторюється певну кількість разів. Він використовує змінну як ітератор підрахувати, скільки разів він був запущений, і цю змінну -ітератор можна використовувати всередині циклу. Це робить цикли For-Next дуже корисними для ітерації по комірках або масивах.
Ось приклад, який перебирає клітинки у рядках 1-100, стовпці 1 і встановлює їх значення до значення змінної -ітератора:
Dim i As Long For i = 1 To 100 Cells (i, 1). Значення = i Next i
Рядок “For i = 1 To 100” означає, що цикл починається з 1 і закінчується після 100. Ви можете встановити будь -які початкові та кінцеві числа, які вам подобаються; Ви також можете використовувати змінні для цих чисел.
За замовчуванням цикли For-Next підраховуються на 1. Якщо ви хочете рахувати на інше число, ви можете записати цикл з явним Крок пункт:
Для i = 5 до 100 Крок 5
Цей цикл почнеться з 5, потім додайте 5 до "i" кожного разу, коли цикл повторюється (так "i" буде 10 у другому повторі, 15 у третьому тощо).
Використання Крок, Ви також можете повернути підрахунок циклу назад:
Для i = 100 до 1 Крок -1
Ви також можете гніздо Цикли For-Next. Кожен блок вимагає власної змінної для підрахунку, але ви можете використовувати ці змінні в будь -якому місці. Ось приклад того, як це корисно в Excel VBA:
Dim i As Long, j As Long For i = 1 To 100 For j = 1 To 100 Cells (i, j). Значення = i * j Next j Next i
Це дозволяє перебирати як рядки, так і стовпці.
УВАГА: хоча це дозволено, НІКОЛИ НЕ ЗМІНЮЙТЕ змінну-ітератор всередині блоку For-Next, оскільки вона використовує цей ітератор для відстеження циклу. Зміна ітератора може призвести до нескінченного циклу та зависання вашого макросу. Наприклад:
Для i = 1 До 100 i = 1 Далі i
У цьому циклі "I" ніколи не вийде за межі 2 перед скиданням на 1, і цикл буде повторюватися вічно.
Для кожного
Для кожного блоки дуже схожі на блоки For-Next, за винятком того, що вони не використовують лічильник, щоб вказати, скільки разів вони циклуються. Натомість блок For-Each бере "колекцію" об'єктів (наприклад, Діапазон клітинок) і запускається стільки разів, скільки об'єктів у цій колекції.
Ось приклад:
Dim r як діапазон для кожного r в діапазоні ("A15: J54") Якщо r.Value> 0 Тоді r.Font.Bold = True End, якщо Next r
Зверніть увагу на використання об’єктної змінної Range ‘r’. Це змінна -ітератор, що використовується у циклі For -Each - кожного разу, коли цикл проходить через цикл, "r" отримує посилання на наступну клітинку в діапазоні.
Перевагою використання циклів For-Each в Excel VBA є те, що ви можете перебирати всі клітинки в діапазоні без вкладених циклів. Це може бути зручним, якщо вам потрібно прокрутити всі клітинки у складному діапазоні, наприклад Діапазон (“A1: D12, J13, M1: Y12”).
Один недолік циклів For-Each полягає в тому, що у вас немає контролю над порядком обробки клітинок. Хоча на практиці Excel буде перебирати клітинки по порядку, в теорії він міг обробляти клітини в абсолютно випадковому порядку. Якщо вам потрібно обробити комірки в певному порядку, замість цього слід використовувати цикли For-Next.
Do-Loop
Хоча блоки For-Next використовують лічильники, щоб знати, коли зупинитися, Do-Loop блоки виконуються доти, поки не буде виконана умова. Для цього ви використовуєте Поки пропозицію на початку або в кінці блоку, що перевіряє умову і призводить до зупинки циклу, коли ця умова виконується.
Приклад:
Dim str As String str = "Buffalo" Do Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Значення = str
У цьому циклі "Буффало" щоразу проходить через цикл, що з'єднується зі "str", поки він не збігається з очікуваним реченням. У цьому випадку перевірка виконується на початку циклу - якщо 'str' вже було очікуваним реченням (а це не тому, що ми не починали його так, але якщо) цикл навіть не запускався .
Ви можете примусити цикл виконуватися принаймні один раз, перемістивши пропозицію До кінця, наприклад:
Do str = str & "" & "Buffalo" Loop Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Ви можете використовувати ту версію, яка має сенс у вашому макросі.
УВАГА: Ви можете викликати нескінченний цикл з блоком Do-Loop, якщо умова До ніколи не виконується. Завжди пишіть свій код так, щоб умова До обов'язково виконувалася під час використання цього типу циклу.
Що далі?
Як тільки ви освоїли основи, чому б не спробувати вивчити деякі більш просунуті техніки? Наш підручник за адресою https://easyexcel.net/excel/learn-vba-tutorial/ буде спиратися на все, що ви тут вивчили, і розширить ваші навички за допомогою подій, UserForms, оптимізації коду та багато іншого!
