Підручник з Excel VBA - Як писати код у електронній таблиці за допомогою Visual Basic

Вступ

Це посібник із написання коду в електронних таблицях Excel за допомогою Visual Basic for Applications (VBA).

Excel - один з найпопулярніших продуктів Microsoft. У 2016 році генеральний директор Microsoft сказав: "Подумайте про світ без Excel. Це просто неможливо для мене". Ну, можливо, світ не може думати без Excel.

  • У 1996 році було понад 30 мільйонів користувачів Microsoft Excel (джерело).
  • Сьогодні, за оцінками, 750 мільйонів користувачів Microsoft Excel. Це трохи більше, ніж населення Європи, і в 25 разів більше користувачів, ніж було в 1996 році.

Ми одна велика щаслива родина!

У цьому підручнику ви дізнаєтеся про VBA та про те, як писати код у таблиці Excel за допомогою Visual Basic.

Передумови

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

  • Основні для середнього знайомства з Microsoft Excel
  • Якщо ви хочете дотримуватися прикладів VBA у цій статті, вам знадобиться доступ до Microsoft Excel, бажано останньої версії (2019), але Excel 2016 та Excel 2013 будуть працювати чудово.
  • Готовність спробувати нові речі

Мета навчання

Протягом цієї статті ви дізнаєтесь:

  1. Що таке VBA
  2. Чому ви використовуєте VBA
  3. Як налаштуватися в Excel для написання VBA
  4. Як вирішити деякі реальні проблеми за допомогою VBA

Важливі поняття

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

Об'єкти : Excel є об'єктно-орієнтованим, що означає, що все є об'єктом - вікно Excel, книга, аркуш, діаграма, комірка. VBA дозволяє користувачам маніпулювати та виконувати дії з об'єктами в Excel.

Якщо у вас немає досвіду з об’єктно-орієнтованим програмуванням, і це абсолютно нова концепція, знайдіть секунду, щоб впустити це!

Процедури : процедура - це частина коду VBA, записана в редакторі Visual Basic, яка виконує завдання. Іноді це також називають макросом (докладніше про макроси нижче). Існує два типи процедур:

  • Підпрограми: група операторів VBA, яка виконує одну або кілька дій
  • Функції: група операторів VBA, яка виконує одну або кілька дій і повертає одне або більше значень

Примітка: Ви можете мати функції, що працюють усередині підпрограм. Ви побачите пізніше.

Макроси : якщо ви витратили якийсь час на вивчення вдосконалених функціональних можливостей Excel, ви, мабуть, стикалися з поняттям "макрос". Користувачі Excel можуть записувати макроси, що складаються з команд користувача / натискань клавіш / клацань, і відтворювати їх з блискавичною швидкістю для виконання повторюваних завдань. Записані макроси генерують код VBA, який ви потім можете перевірити. Насправді дуже цікаво записати простий макрос, а потім поглянути на код VBA.

Будь ласка, майте на увазі, що іноді може бути простіше і швидше записати макрос, а не кодувати процедуру VBA вручну.

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

Що таке VBA?

Visual Basic for Applications - це мова програмування, розроблена корпорацією Майкрософт. Кожна програма в наборі Microsoft Office постачається разом із мовою VBA без додаткових витрат. VBA дозволяє користувачам Microsoft Office створювати невеликі програми, які працюють у програмах Microsoft Office.

Думайте про VBA як про піч для піци в ресторані. Excel - це ресторан. Кухня оснащена стандартними комерційними приладами, такими як великі холодильники, печі та звичайні оле-печі - це всі стандартні функції Excel.

Але що, якщо ви хочете приготувати піцу на дровах? Це неможливо зробити в звичайній комерційній духовці. VBA - піч для піци.

Нім.

Навіщо використовувати VBA в Excel?

Бо піца на дровах найкраща!

Але серйозно.

Багато людей проводять багато часу в Excel як частину своєї роботи. Час у Excel теж рухається по-іншому. Залежно від обставин, 10 хвилин у програмі Excel можуть виглядати як вічність, якщо ви не можете зробити те, що вам потрібно, або 10 годин можуть пройти дуже швидко, якщо все йде чудово. І саме тоді вам слід запитати себе, чому, по суті, я проводжу 10 годин в Excel?

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

Вам слід розглянути можливість використання VBA, якщо вам потрібно:

  • Автоматизуйте повторювані завдання
  • Створіть прості способи взаємодії користувачів із вашими електронними таблицями
  • Маніпулювати великими обсягами даних

Налаштування для написання VBA в Excel

Вкладка розробника

Щоб написати VBA, вам потрібно додати вкладку Розробник на стрічку, щоб ви побачили таку стрічку.

Щоб додати вкладку розробника на стрічку:

  1. На вкладці Файл перейдіть до Параметри> Налаштувати стрічку.
  2. У розділі Налаштування стрічки та в розділі Основні вкладки встановіть прапорець Розробник.

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

Редактор VBA

Перейдіть на вкладку розробника та натисніть кнопку Visual Basic. З'явиться нове вікно - це редактор Visual Basic. Для цілей цього підручника вам просто потрібно ознайомитися з панеллю Провідник проекту та панеллю Властивості властивостей.

Приклади Excel VBA

По-перше, давайте створимо файл, в який ми зможемо пограти.

  1. Відкрийте новий файл Excel
  2. Збережіть його як книгу з підтримкою макросів (. Xlsm)
  3. Виберіть вкладку Розробник
  4. Відкрийте редактор VBA

Давайте розкачаємо кілька простих прикладів, щоб ви писали код у таблиці за допомогою Visual Basic.

Приклад №1: Відображення повідомлення, коли користувачі відкривають книгу Excel

У редакторі VBA виберіть Вставити -> Новий модуль

Напишіть цей код у вікні модуля (не вставляйте!):

Sub Auto_Open ()

MsgBox ("Ласкаво просимо до книги XYZ".)

Кінець Sub

Збережіть, закрийте книгу та знову відкрийте книгу. Це діалогове вікно має відобразитися.

Та да!

Як це робиться?

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

  • Суб (скорочення від «Підпрограма»): пам’ятайте з самого початку, «група операторів VBA, яка виконує одну або кілька дій»
  • Auto_Open: це конкретна підпрограма. Він автоматично запускає ваш код, коли файл Excel відкривається - це подія, яка запускає процедуру. Auto_Open буде працювати лише тоді, коли книгу відкрито вручну; він не працюватиме, якщо книгу відкрито за допомогою коду з іншої книги (Workbook_Open зробить це, дізнайтеся більше про різницю між ними).
  • За замовчуванням доступ до підпрограми є загальнодоступним. Це означає, що будь-який інший модуль може використовувати цю підпрограму. Усі приклади цього посібника будуть загальнодоступними. Якщо потрібно, ви можете оголосити підпрограми приватними. Це може знадобитися в деяких ситуаціях. Дізнайтеся більше про модифікатори доступу підпрограми.
  • msgBox: це функція - група операторів VBA, яка виконує одну або кілька дій і повертає значення. Повернуте значення - це повідомлення “Ласкаво просимо до книги XYZ”.

Коротше кажучи, це проста підпрограма, яка містить функцію.

Коли я можу цим скористатися?

Можливо, у вас є дуже важливий файл, до якого ви отримуєте доступ нечасто (скажімо, раз на квартал), але щодня автоматично оновлюється за допомогою іншої процедури VBA. Коли до нього звертаються, це багато людей у ​​багатьох відділах по всій компанії.

  • Проблема: Більшу частину часу, коли користувачі отримують доступ до файлу, їх бентежить ціль цього файлу (чому він існує), як часто його оновлюють, хто його підтримує та як вони повинні з ним взаємодіяти. У нових працівників завжди виникає маса запитань, і вам доводиться надсилати ці питання знову і знову і знову.
  • Рішення: створіть повідомлення користувача, яке містить стислу відповідь на кожне з цих запитань, на які часто відповідають.

Приклади з реального світу

  • Використовуйте функцію MsgBox для відображення повідомлення, коли є якась подія: користувач закриває книгу Excel, користувач друкує, новий аркуш додається до книги тощо.
  • Використовуйте функцію MsgBox для відображення повідомлення, коли користувач повинен виконати умову перед закриттям книги Excel
  • Використовуйте функцію InputBox для отримання інформації від користувача

Приклад №2: Дозволити користувачеві виконати іншу процедуру

У редакторі VBA виберіть Вставити -> Новий модуль

Напишіть цей код у вікні модуля (не вставляйте!):

Sub UserReportQuery ()

Помітно вводити користувач як довго

Приглушити відповідь як ціле число

UserInput = vbYesNo

Відповідь = MsgBox ("Обробити звіт XYZ?", UserInput)

Якщо відповідь = vbТак, тоді ProcessReport

Кінець Sub

Підпроцес звіту ()

MsgBox ("Дякуємо за обробку звіту XYZ.")

Кінець Sub

Збережіть і перейдіть назад на вкладку Розробник Excel та виберіть опцію “Кнопка”. Клацніть на комірку і призначте кнопці макрос UserReportQuery.

Тепер натисніть кнопку. Це повідомлення повинно відображатись:

Натисніть "так" або натисніть Enter.

Ще раз, тада!

Зверніть увагу, що вторинною підпрограмою, ProcessReport, може бути що завгодно . Я продемонструю більше можливостей у прикладі №3. Але спочатку ...

Як це робиться?

Цей приклад ґрунтується на попередньому прикладі та має досить багато нових елементів. Давайте розглянемо нові речі:

  • Dim UserInput As Long: Dim - це скорочення від “dimension” і дозволяє оголошувати імена змінних. У цьому випадку UserInput - це ім’я змінної, а Long - тип даних. Простою англійською мовою цей рядок означає "Ось змінна під назвою" UserInput ", і це тип змінної Long."
  • Приглушити відповідь як ціле: оголошує іншу змінну, яка називається "Відповідь", із типом даних "Ціле число". Дізнайтеся більше про типи даних тут.
  • UserInput = vbYesNo: присвоює значення змінній. У цьому випадку vbYesNo, який відображає кнопки Так і Ні. Існує безліч типів кнопок. Докладніше тут.
  • Answer = MsgBox (“Обробляти звіт XYZ?”, UserInput): присвоює значення змінної Answer функції MsgBox та змінної UserInput. Так, змінна всередині змінної.
  • Якщо відповідь = vbТак, тоді ProcessReport: це оператор “If” - умовне твердження, яке дозволяє нам сказати, якщо x істинне, то зробіть y. У цьому випадку, якщо користувач вибрав “Так”, виконайте підпрограму ProcessReport.

Коли я можу цим скористатися?

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

Наприклад, можливо, у вас є файл, який використовується для створення 3 різних щотижневих звітів. Ці звіти відформатовані різними способами.

  • Проблема. Кожного разу, коли потрібно створити один із цих звітів, користувач відкриває файл і змінює форматування та діаграми; так далі і так далі. Цей файл широко редагується щонайменше 3 рази на тиждень, і кожен раз це займає щонайменше 30 хвилин.
  • Рішення: створіть 1 кнопку для кожного типу звіту, яка автоматично переформатує необхідні компоненти звітів та сформує необхідні діаграми.

Приклади з реального світу

  • Створіть діалогове вікно для користувача для автоматичного заповнення певної інформації на декількох аркушах
  • Використовуйте функцію InputBox, щоб отримати інформацію від користувача, яка потім заповнюється на декількох аркушах

Приклад # 3: Додавання чисел до діапазону з циклом For-Next

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

У редакторі VBA виберіть Вставити -> Новий модуль

Напишіть цей код у вікні модуля (не вставляйте!):

Sub LoopExample ()

Затемнити X як ціле число

Для Х = від 1 до 100

Діапазон ("A" & X). Значення = X

Далі X

Кінець Sub

Збережіть і перейдіть назад на вкладку Розробник Excel та натисніть кнопку Макроси. Запустіть макрос LoopExample.

Це має статися:

І т. Д., До 100-го ряду.

Як це робиться?

  • Dim X As Integer: оголошує змінну X як тип даних Integer.
  • Для X = 1 До 100: це початок циклу For. Простіше кажучи, це вказує циклу продовжувати повторюватись до X = 100. X - лічильник . Цикл буде продовжувати виконуватися до X = 100, виконати останній раз, а потім зупинитися.
  • Діапазон ("A" & X). Значення = X: це декларує діапазон циклу та те, що ввести в цей діапазон. Оскільки X = 1 спочатку, першою коміркою буде A1, після чого цикл помістить X у цю комірку.
  • Далі X: це повідомляє, що цикл запускається знову

Коли я можу цим скористатися?

Цикл For-Next - одна з найпотужніших функціональних можливостей VBA; існує безліч потенційних випадків використання. Це більш складний приклад, який потребує декількох рівнів логіки, але він передає світ можливостей у циклах For-Next.

Можливо, у вас є перелік усіх продуктів, що продаються у вашій пекарні в колонці A, тип продукту у колонці B (тістечка, пончики або булочки), вартість інгредієнтів у колонці C та середня ринкова вартість кожного виду продукту в інший аркуш.

Потрібно з’ясувати, якою має бути роздрібна ціна кожного товару. Ви думаєте, що це повинна бути вартість інгредієнтів плюс 20%, а також 1,2% нижче середнього рівня ринку, якщо можливо. Цикл For-Next дозволить вам зробити такий тип обчислення.

Приклади з реального світу

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

Висновок

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

  • Що таке VBA?
  • Як налаштуватися на початок використання VBA в Excel?
  • Чому і коли ви використовуєте VBA?
  • Які проблеми я можу вирішити за допомогою VBA?

Якщо у вас є прекрасне уявлення про те, як ви могли б відповісти на ці запитання, то це вдалося.

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

Щасливого кодування!

Навчальні ресурси

  • Програмування Excel VBA для чайників, Джон Уокенбах
  • Початок роботи з VBA, документацією Microsoft
  • Вивчення VBA в Excel, Lynda

Трохи про мене

Я Хлоя Такер, художник і розробник у Портленді, штат Орегон. Як колишній педагог, я постійно шукаю стику навчання та викладання, або технологій та мистецтва. Зв’яжіться зі мною у Twitter @_chloetucker та перевірте мій веб-сайт за адресою chloe.dev.