Розуміння типів даних Excel VBA (змінних та констант)

У Excel VBA вам часто доведеться використовувати змінні та константи.

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

Наприклад, якщо ви хочете зберігати цілі числа у змінній, ваш тип даних буде "Ціле число", а якщо ви хочете зберігати текст, то тип даних буде "Рядок".

Детальніше про типи даних далі в цьому посібнику.

Хоча значення змінної змінюється під час виконання коду, константа містить значення, яке ніколи не змінюється. Як хороша практика кодування, слід визначити тип даних як змінних, так і постійних.

Навіщо використовувати змінні у VBA?

Коли ви кодуєте у VBA, вам знадобляться змінні, які можна використовувати для утримання значення.

Перевага використання змінної полягає в тому, що ви можете змінити значення змінної у коді та продовжувати використовувати її у коді.

Наприклад, нижче наведено код, який додає перші 10 позитивних чисел, а потім відображає результат у вікні повідомлення:

Sub AddFirstTenNumbers () Dim Var As Integer Dim i As Integer Dim k as Integer For i = 1 To 10 k = k + i Next i MsgBox k End Sub

У наведеному вище коді є три змінні - Var, i, і k.

У наведеному вище коді використовується цикл For Next, де всі ці три змінні змінюються по мірі завершення циклів.

Корисність змінної полягає в тому, що її можна змінити, поки код працює.

Нижче наведено деякі правила, які слід пам’ятати при називанні змінних у VBA:

1. Можна використовувати алфавіти, цифри та розділові знаки, але перше число має бути алфавітом.
2. У назві змінної не можна використовувати пробіл або крапку. Тим не менш, ви можете використовувати символ підкреслення, щоб зробити назви змінних більш читабельними (наприклад, Rate_Rate)
3. Не можна використовувати спеціальні символи (#, $, %, & або!) У іменах змінних
4. VBA не розрізняє регістр у назві змінної. Тож "процентна ставка" та "процентна ставка" однакові для VBA. Ви можете використовувати змішаний регістр, щоб зробити змінні більш читабельними.
5. VBA має деякі зарезервовані імена, які можна використовувати для імені змінної. Наприклад, ви не можете використовувати слово "Далі" як ім'я змінної, оскільки це зарезервоване ім'я для циклу "Далі".
6. Назва вашої змінної може містити до 254 символів.

Тип даних змінних

Для найкращого використання змінних рекомендується вказувати тип даних змінної.

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

Нижче наведено таблицю, яка показує всі доступні типи даних, які можна використовувати в Excel VBA:

Тип даних	Використовувані байти	Діапазон значень
Байт	1 байт	Від 0 до 255
Булеве	2 байти	Правда чи неправда
Ціле число	2 байти	-32,768 до 32,767
Long (довге ціле число)	4 байти	Від 2 147 483 648 до 2 147 483 647
Неодружений	4 байти	Від -3,402823E38 до -1,401298E -45 для від'ємних значень; 1.401298E-45 до 3.402823E38 для позитивних значень
Подвійний	8 байт	-1.79769313486231E308 до-4.94065645841247E-324 для від'ємних значень; 4,94065645841247E-324 до 1,79769313486232E308 для позитивних значень
Валюта	8 байт	-922,337,203,685,477,5808 до 922,337,203,685,477,5807
Десятковий	14 байт	+/- 79,228,162,514,264,337,593,543,950,335 без десяткової коми; +/- 7,9228162514264337593543950335 з 28 місцями праворуч від десяткової
Дата	8 байт	1 січня 100 р. До 31 грудня 9999 р
Об'єкт	4 байти	Будь -яке посилання на об’єкт
Рядок (змінна довжина)	10 байт + довжина рядка	Від 0 до приблизно 2 млрд
Рядок (фіксована довжина)	Довжина струни	1 приблизно до 65 400
Варіант (з числами)	16 байт	Будь -яке числове значення до діапазону Double
Варіант (з символами)	22 байти + довжина рядка	Той самий діапазон, що і для рядка зі змінною довжиною
Визначений користувачем	Змінюється	Діапазон кожного елемента такий самий, як діапазон його типу даних.

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

Наприклад, якщо вам потрібно використати змінну, призначену для утримання номера місяця, ви можете використовувати тип даних BYTE (який може містити значення від 0 до 255). Оскільки число місяця не буде вище 12, це буде нормально працювати, а також збереже менше пам’яті для цієї змінної.

Навпаки, якщо вам потрібна змінна для зберігання номерів рядків у Excel, вам потрібно використовувати тип даних, який може містити число до 1048756. Тому найкраще використовувати тип даних Long.

Оголошення змінних типів даних

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

Нижче наведено приклад, коли я оголосив різні типи даних різним змінним:

Sub DeclaringVariables () Dim X як ціле число Dim Email як рядок Dim FirstName As String Dim RowCount As Long Dim TodayDate As Date End Sub

Щоб оголосити змінний тип даних, вам потрібно використати оператор DIM (що скорочено від Dimension).

В 'Dim X як ціле число', Я оголосив змінну X цілим типом даних.

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

Якщо я спробую присвоїти йому значення, яке не є цілим числом, я отримаю помилку (як показано нижче):

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

Встановлення обов’язкової декларації змінної (опція явна)

Хоча ви можете кодувати, не оголошуючи змінних, це є хорошою практикою.

Окрім економії пам’яті та підвищення ефективності коду, оголошення змінних має ще одну важливу перевагу - це допомагає уловлювати помилки, спричинені неправильно написаними іменами змінних.

Щоб переконатися, що ви змушені оголошувати змінні, додайте наступний рядок у верхній частині модуля.

Опція явна

Коли ви додаєте "Option Explicit", вам потрібно буде оголосити всі змінні перед запуском коду. Якщо є будь -яка змінна, яка не була оголошена, VBA покаже помилку.

Використання Option Explicit має величезну користь.

Іноді ви можете помилитися при введенні тексту та ввести неправильну назву змінної.

Як правило, VBA не може дізнатися, помилка це чи навмисне. Однак, коли ви використовуєте «Option Explicit», VBA побачить неправильно написану назву змінної як нову змінну, яка не була оголошена, і покаже вам помилку. Це допоможе вам визначити ці помилково написані імена змінних, які досить складно помітити у довгому коді.

Нижче наведено приклад, коли використання "Option Explicit" ідентифікує помилку (яка не могла бути заблокована, якби я не використав "Option Explicit")

Sub CommissionCalc () Dim CommissionRate як подвійний, якщо діапазон ("A1"). Значення> 10000 Тоді CommissionRate = 0,1 Інше CommissionRtae = 0,05 End Якщо MsgBox "Total Commission:" & Range ("A1"). Значення * CommissionRate End Sub

Зауважте, що я неправильно написав слово "CommissionRate" одного разу в цьому коді.

Якщо я не використовую Option Explicit, цей код запуститься і надасть мені неправильне загальне значення комісії (у випадку, якщо значення в комірці A1 менше 10000).

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

Хоча ви можете вставляти рядок "Option Explicit" щоразу, коли ви кодуєте, ось кроки, щоб він відображався за замовчуванням:

1. На панелі інструментів VB Editor натисніть Інструменти.
2. Натисніть Параметри.
3. У діалоговому вікні Параметри натисніть вкладку Редактор.
4. Поставте прапорець біля опції «Вимагати декларації змінних».
5. Натисніть OK.

Після того, як ви ввімкнули цю опцію, щоразу, коли ви відкриваєте новий модуль, VBA автоматично додає до нього рядок "Option Explicit".

Примітка: Ця опція вплине лише на будь -який модуль, який ви створите після ввімкнення цієї опції. Це не впливає на всі існуючі модулі.

Область змінних

До цього часу ми бачили, як оголосити змінну та призначити їй типи даних.

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

Область дії змінної визначає, де цю змінну можна використовувати у VBA,

Існує три способи застосування змінної в Excel VBA:

1. Всередині однієї підпрограми (локальні змінні)
2. Усередині модуля (змінні на рівні модуля)
3. У всіх модулях (загальнодоступні змінні)

Давайте розглянемо кожен з них детально.

Всередині однієї підпрограми (локальні змінні)

Коли ви оголошуєте змінну у підпрограмі/процедурі, ця змінна доступна лише для цієї підпрограми.

Ви не можете використовувати його в інших підпрограмах модуля.

Як тільки підпрограма закінчується, змінна видаляється, а пам’ять, яку вона використовує, звільняється.

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

Всередині модуля (змінні на рівні модуля)

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

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

У наведеному вище прикладі змінна ‘i’ оголошена у верхній частині модуля і доступна для використання всіма модулями.

Зауважте, що коли підпрограма закінчується, змінні рівня модуля не видаляються (вона зберігає своє значення).

Нижче наведено приклад, де у мене є два коди. Коли я запускаю першу процедуру, а потім другу, значення "i" стає 30 (оскільки воно несе значення 10 з першої процедури)

У всіх модулях (загальнодоступні змінні)

Якщо ви хочете, щоб змінна була доступною у всіх процедурах у книзі, вам потрібно оголосити її за допомогою ключового слова Public (замість DIM).

Нижній рядок коду у верхній частині модуля зробить змінну "CommissionRate" доступною у всіх модулях робочої книги.

 Громадська комісіяОцінка як подвійна

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

Статичні змінні (які зберігають значення)

Коли ви працюєте з локальними змінними, як тільки процедура закінчується, змінна втратить своє значення і буде видалена з пам'яті VBA.

Якщо ви хочете, щоб змінна зберігала значення, вам потрібно використовувати Статичний ключове слово.

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

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

Додаткова процедура1 () Dim i As Integer i = i + 10 MsgBox i End Sub

Тепер, якщо я використовую ключове слово Static замість DIM, і запускаю процедуру кілька разів, вона продовжуватиме показувати значення з кроком 10. Це відбувається, коли змінна "i" зберігає своє значення і використовує її в обчисленні.

Підпроцедура1 () Статична i Як ціле число i = i + 10 MsgBox i Закінчити підп

Оголошення констант у Excel VBA

Хоча змінні можуть змінюватися під час виконання коду, якщо ви хочете мати фіксовані значення, ви можете використовувати константи.

Константа дозволяє призначити значення іменованому рядку, який ви можете використовувати у своєму коді.

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

Наприклад, якщо ви розраховуєте комісійні, а комісійна ставка становить 10%, ви можете створити константу (CommissionRate) і присвоїти їй значення 0,1.

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

Нижче наведено приклад коду, де я призначив значення константі:

Sub CalculateCommission () Dim CommissionValue As Double Const CommissionRate As Double = 0.1 CommissionValue = Range ("A1") * CommissionRate MsgBox CommissionValue End Sub

Для оголошення константи використовується наступний рядок:

Const CommissionRate As Double = 0,1

При оголошенні констант потрібно починати з ключового слова "Конст‘, За яким слідує назва константи.

Зауважте, що в цьому прикладі я вказав тип даних константи як Double. Знову ж таки, рекомендується вказувати тип даних, щоб ваш код працював швидше та ефективніше.

Якщо ви не декларуєте тип даних, він вважатиметься варіантом типу даних.

Як і змінні, константи також можуть мати область дії залежно від того, де і як вони оголошені:

1. У межах однієї підпрограми (Локальні константи): Вони доступні в підпрограмі/процедурі, в якій вони оголошені. Після завершення процедури ці константи видаляються з пам’яті системи.
2. Всередині модуля (константи на рівні модуля): Вони оголошені у верхній частині модуля (перед будь -якою процедурою). Вони доступні для всіх процедур у модулі.
3. У всіх модулях (загальнодоступні константи): Вони оголошуються за допомогою ключового слова "Public" у верхній частині будь -якого модуля (перед будь -якою процедурою). Вони доступні для всіх процедур у всіх модулях.

Вам також можуть сподобатися такі підручники з VBA:

• Як записати макрос в Excel
• Робота з клітинками та діапазонами в Excel VBA
• Робота з робочими листами за допомогою Excel VBA
• Робота з робочими книгами в Excel VBA
• Події VBA
• Цикли VBA Excel
• Як запустити макрос в Excel
• Якщо тоді інше висловлювання в Excel VBA.