зміст
- Вбудовані функції VBA
- Настроювані процедури «Функція» та «Підрозділ» у VBA
- Аргументи
- Процедура VBA «Функція»
- Процедура VBA «Sub»
- Сфера дії процедури VBA
- Ранній вихід із процедур VBA «Функція» та «Підрозділ»
Вбудовані функції VBA
Перш ніж почати створювати власні функції VBA, корисно знати, що Excel VBA має багату колекцію готових вбудованих функцій, які можна використовувати під час написання коду.
Список цих функцій можна переглянути в редакторі VBA:
- Відкрийте книгу Excel і запустіть редактор VBA (клацніть, щоб зробити це Alt + F11), а потім натисніть F2.
- Виберіть бібліотеку зі спадного списку у верхньому лівому куті екрана VBA.
- З’явиться список вбудованих класів і функцій VBA. Натисніть назву функції, щоб відобразити її короткий опис у нижній частині вікна. пресування F1 відкриє сторінку онлайн-довідки для цієї функції.
Крім того, повний список вбудованих функцій VBA з прикладами можна знайти в Центрі розробників Visual Basic.
Настроювані процедури «Функція» та «Підрозділ» у VBA
В Excel Visual Basic набір команд, які виконують певне завдання, поміщається в процедуру. функція (Функція) або нижче (Підпрограма). Основна відмінність процедур функція и нижче така процедура функція повертає результат, процедуру нижче - Not.
Тому, якщо потрібно виконати дії і отримати якийсь результат (наприклад, підсумувати кілька чисел), то зазвичай використовується процедура функція, а щоб просто виконати деякі дії (наприклад, змінити форматування групи комірок), потрібно вибрати процедуру нижче.
Аргументи
Різні дані можна передавати процедурам VBA за допомогою аргументів. Список аргументів вказується при оголошенні процедури. Наприклад, процедура нижче у VBA додає задане ціле число (Integer) до кожної клітинки у вибраному діапазоні. Ви можете передати це число в процедуру за допомогою такого аргументу:
Sub AddToCells(i As Integer) ... End Sub
Майте на увазі, що мати аргументи для процедур функція и нижче у VBA є необов’язковим. Деякі процедури не потребують аргументів.
Необов’язкові аргументи
Процедури VBA можуть мати необов’язкові аргументи. Це аргументи, які користувач може вказати за бажанням, і якщо вони опущені, процедура використовує для них значення за замовчуванням.
Повертаючись до попереднього прикладу, щоб зробити цілочисельний аргумент функції необов’язковим, його слід оголосити так:
Sub AddToCells (необов’язковий i як ціле = 0)
У цьому випадку цілочисельний аргумент i за замовчуванням буде 0.
У процедурі може бути кілька необов’язкових аргументів, усі вони перераховані в кінці списку аргументів.
Передача аргументів за значенням і за посиланням
Аргументи у VBA можна передати процедурі двома способами:
- ByVal – передача аргументу за значенням. Це означає, що лише значення (тобто копія аргументу) передається до процедури, і тому будь-які зміни, внесені до аргументу всередині процедури, будуть втрачені, коли процедура завершить роботу.
- Автор Ref – передача аргументу за посиланням. Тобто фактична адреса розташування аргументу в пам'яті передається процедурі. Будь-які зміни, внесені до аргументу всередині процедури, будуть збережені після завершення процедури.
Використання ключових слів ByVal or Автор Ref в декларації процедури ви можете вказати, як аргумент передається в процедуру. Це показано на прикладах нижче:
Sub AddToCells(ByVal i As Integer) ... End Sub | У цьому випадку цілочисельний аргумент i передається за значенням. Після виходу з процедури нижче все зроблено з i зміни будуть втрачені. |
Sub AddToCells(ByRef i As Integer) ... End Sub | У цьому випадку цілочисельний аргумент i передається за посиланням. Після виходу з процедури нижче все зроблено з i зміни будуть збережені в змінній, яка була передана процедурі нижче. |
Пам’ятайте, що аргументи у VBA за замовчуванням передаються за посиланням. Іншими словами, якщо ключові слова не використовуються ByVal or Автор Ref, тоді аргумент буде передано за посиланням.
Перш ніж приступити до процедур функція и нижче більш детально, корисно буде ще раз поглянути на особливості та відмінності цих двох видів процедур. Нижче наведено короткий опис процедур VBA функція и нижче і показані прості приклади.
Процедура VBA «Функція»
Редактор VBA розпізнає процедуру функціяколи він зустрічає групу команд, укладених між наступними початковими та кінцевими операторами:
Функція ... Кінцева функція
Як було сказано раніше, процедура функція у VBA (на відміну від нижче) повертає значення. До значень, що повертаються, застосовуються такі правила:
- Тип даних значення, що повертається, має бути оголошено в заголовку процедури функція.
- Змінна, яка містить значення, що повертається, має бути названа так само, як і процедура функція. Цю змінну не потрібно оголошувати окремо, оскільки вона завжди існує як невід’ємна частина процедури. функція.
Це добре показано в наступному прикладі.
Приклад функції VBA: виконання математичної операції над 3 числами
Нижче наведено приклад коду процедури VBA функція, який приймає три аргументи типу подвійний (числа з плаваючою комою подвійної точності). У результаті процедура повертає інший номер типу подвійнийдорівнює сумі перших двох аргументів мінус третій аргумент:
Функція SumMinus (dNum1 як Double, dNum2 як Double, dNum3 як Double) Як подвійна сума SumMinus = dNum1 + dNum2 - dNum3 Кінцева функція
Це дуже проста процедура VBA функція ілюструє, як дані передаються до процедури через аргументи. Ви бачите, що тип даних, який повертає процедура, визначається як подвійний (слова говорять Як подвійний після списку аргументів). Цей приклад також показує, як виглядає результат процедури функція зберігається в змінній з тим самим ім'ям, що й ім'я процедури.
Виклик процедури VBA «Функція»
Якщо описана вище проста процедура функція вставлений у модуль у редакторі Visual Basic, його можна викликати з інших процедур VBA або використовувати на робочому аркуші в книзі Excel.
Виклик процедури VBA «Функція» з іншої процедури
Процедура функція можна викликати з іншої процедури VBA, просто призначивши цю процедуру змінній. У наступному прикладі показано виклик процедури Summinus, який було визначено вище.
Sub main() Dim total як Double total = SumMinus(5, 4, 3) End Sub
Викличте процедуру VBA «Функція» з аркуша
Процедура VBA функція можна викликати з аркуша Excel так само, як і будь-яку іншу вбудовану функцію Excel. Отже, процедура створена в попередньому прикладі функція - Summinus можна викликати, ввівши такий вираз у клітинку аркуша:
=SumMinus(10, 5, 2)
Процедура VBA «Sub»
Редактор VBA розуміє, що перед ним є процедура нижчеколи він зустрічає групу команд, укладених між наступними початковими та кінцевими операторами:
Sub ... End Sub
Процедура VBA «Sub»: Приклад 1. Вирівнювання по центру та зміна розміру шрифту у вибраному діапазоні клітинок
Розглянемо приклад простої процедури VBA нижче, завданням якого є зміна форматування виділеного діапазону клітинок. Комірки вирівнюються по центру (по вертикалі та горизонталі), а розмір шрифту змінюється на вказаний користувачем:
Sub Format_Centered_And_Sized(Необов’язковий iFontSize як ціле число = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub
Ця процедура нижче виконує дії, але не повертає результат.
У цьому прикладі також використовується необов’язковий аргумент Розмір шрифту. Якщо аргумент Розмір шрифту не пройшов процедуру нижче, тоді його значення за умовчанням дорівнює 10. Однак, якщо аргумент Розмір шрифту передано на процедуру нижче, тоді для виділеного діапазону комірок буде встановлено розмір шрифту, вказаний користувачем.
Підпроцедура VBA: приклад 2: вирівнювання по центру та жирний шрифт у вибраному діапазоні клітинок
Наступна процедура подібна до тієї, що щойно обговорювалася, але цього разу замість зміни розміру вона застосовує жирний шрифт до вибраного діапазону клітинок. Це приклад процедури нижче, який не приймає аргументів:
Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub
Виклик «Sub» процедури в Excel VBA
Виклик процедури VBA «Sub» з іншої процедури
Для виклику процедури VBA нижче з іншої процедури VBA, вам потрібно написати ключове слово виклик, назва процедури нижче і далі в дужках аргументи процедури. Це показано на прикладі нижче:
Sub main() Виклик Format_Centered_And_Sized(20) End Sub
Якщо процедура Format_Centered_and_Sized має більше одного аргументу, вони повинні бути розділені комами. Подобається це:
Sub main() Виклик Format_Centered_And_Sized(arg1, arg2, ...) End Sub
Викличте процедуру VBA «Sub» з аркуша
Процедура нижче не можна ввести безпосередньо в клітинку аркуша Excel, як це можна зробити за допомогою процедури функціяоскільки процедура нижче не повертає значення. Проте процедури нижче, які не мають аргументів і оголошуються як громадськості (як показано нижче) буде доступним для користувачів аркуша. Таким чином, якщо прості процедури, розглянуті вище нижче вставлений у модуль у редакторі Visual Basic, процедура Format_Centered_And_Bold буде доступним для використання на робочому аркуші Excel, а процедура Format_Centered_and_Sized – не буде доступний, оскільки має аргументи.
Ось простий спосіб запустити (або виконати) процедуру нижче, доступний з аркуша:
- прес Alt + F8 (натисніть клавішу інший і, утримуючи її, натисніть клавішу F8).
- У списку макросів, що з’явиться, виберіть той, який потрібно запустити.
- прес прогін (бігти)
Щоб виконати процедуру нижче швидко і легко, ви можете призначити йому комбінацію клавіш. Для цього:
- прес Alt + F8.
- У списку макросів, що з’явиться, виберіть той, якому потрібно призначити комбінацію клавіш.
- прес параметри (Параметри) і в діалоговому вікні, що з’явиться, введіть комбінацію клавіш.
- прес OK і закрийте діалогове вікно Macro (Макро).
Увага: Призначаючи комбінацію клавіш для макросу, переконайтеся, що вона не використовується як стандартна в Excel (наприклад, Ctrl + C). Якщо вибрати вже існуючу комбінацію клавіш, вона буде перепризначена для макросу, і в результаті користувач може випадково запустити макрос.
Сфера дії процедури VBA
У другій частині цього підручника обговорювалися область змінних і констант і роль ключових слів. громадськості и приватний. Ці ключові слова також можна використовувати з процедурами VBA:
Public Sub AddToCells(i As Integer) ... End Sub | Якщо декларації процедури передує ключове слово громадськості, тоді процедура буде доступна для всіх модулів у цьому проекті VBA. |
Private Sub AddToCells(i As Integer) ... End Sub | Якщо декларації процедури передує ключове слово приватний, то ця процедура буде доступна лише для поточного модуля. Його не можна викликати в будь-якому іншому модулі або з книги Excel. |
Пам’ятайте, що якщо перед оголошенням процедури VBA функція or нижче ключове слово не вставлено, для процедури встановлено властивість за замовчуванням громадськості (тобто він буде доступний скрізь у цьому проекті VBA). Це на відміну від декларацій змінних, які за замовчуванням є приватний.
Ранній вихід із процедур VBA «Функція» та «Підрозділ»
Якщо потрібно припинити виконання процедури VBA функція or нижче, не чекаючи його природного закінчення, то для цього є оператори Функція виходу и Exit Sub. Використання цих операторів показано нижче на прикладі простої процедури. функціяA, який очікує отримати позитивний аргумент для виконання подальших операцій. Якщо в процедуру передається недодатне значення, подальші операції виконуватися не можуть, тому користувачеві має відобразитися повідомлення про помилку, і процедура повинна негайно завершитися:
Функція VAT_Amount(sVAT_Rate As Single) Як один VAT_Amount = 0 If sVAT_Rate <= 0 Then MsgBox «Очікувалося додатне значення sVAT_Rate, але отримано» & sVAT_Rate Вихід із функції End If ... End Function
Зверніть увагу, що перед завершенням процедури функція - ПДВ_сума, у код вставляється вбудована функція VBA MsgBox, яке відображає спливаюче вікно з попередженням для користувача.