В Excel VBA често ще се налага да използвате променливи и константи.
Когато работите с VBA, променлива е местоположение в паметта на компютъра, където можете да съхранявате данни. Типът данни, които можете да съхранявате в променлива, ще зависи от типа данни на променливата.
Например, ако искате да съхранявате цели числа в променлива, вашият тип данни ще бъде „Integer“ и ако искате да съхранявате текст, тогава вашият тип данни ще бъде „String“.
Повече за типовете данни по -късно в този урок.
Докато стойността на променлива се променя, когато кодът е в процес, константата съдържа стойност, която никога не се променя. Като добра практика за кодиране трябва да дефинирате типа данни и на двете - променливи и постоянни.
Защо да използвате променливи във 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, и к.
Горният код използва цикъл For Next, където всички тези три променливи се променят при завършване на циклите.
Полезността на променливата се крие във факта, че тя може да бъде променена, докато кодът ви е в ход.
По -долу са някои правила, които трябва да имате предвид, когато назовавате променливите във VBA:
- Можете да използвате азбуки, цифри и препинателни знаци, но първото число трябва да бъде азбука.
- Не можете да използвате интервал или точка в името на променливата. Можете обаче да използвате знак за подчертаване, за да направите имената на променливите по -четими (като Interest_Rate)
- Не можете да използвате специални знаци (#, $, %, & или!) В имената на променливите
- VBA не прави разлика между регистъра в името на променливата. Така че „InterestRate“ и „процент на лихва“ са еднакви за VBA. Можете да използвате смесен регистър, за да направите променливите по -четими.
- VBA има някои запазени имена, които можете да използвате за име на променлива. Например, не можете да използвате думата „Next“ като име на променлива, тъй като това е запазено име за цикъла For Next.
- Името на вашата променлива може да бъде с дължина до 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. Така че най -добре е да използвате дългия тип данни.
Деклариране на променливи типове данни
Като добра практика за кодиране, трябва да декларирате типа данни на променливите (или константи), когато пишете кода. По този начин се гарантира, че VBA разпределя само посочената памет към променливата и това може да направи вашия код да работи по -бързо.
По -долу е даден пример, в който декларирах различни типове данни на различни променливи:
Sub DeclaringVariables () Dim X As Integer Dim Dim As String Dim FirstName As String Dim RowCount As Long Dim TodayDate As Date End Sub
За да декларирате променлив тип данни, трябва да използвате оператора DIM (което е съкращение от Dimension).
В „Dim X като цяло число‘, Декларирах променливата X като целочислен тип данни.
Сега, когато го използвам в кода си, VBA ще знае, че X може да съдържа само целочислен тип данни.
Ако се опитам да му присвоя стойност, която не е цяло число, ще получа грешка (както е показано по -долу):
Обявяване на променливата декларация задължителна (опция явна)
Въпреки че можете да кодирате, без никога да декларирате променливи, това е добра практика.
Освен че спестява памет и прави кода ви по -ефективен, декларирането на променливи има още едно голямо предимство - помага за улавяне на грешки, причинени от грешно написани имена на променливи.
За да сте сигурни, че сте принудени да декларирате променливи, добавете следния ред в горната част на вашия модул.
Опция Изрично
Когато добавите „Option Explicit“, ще трябва да декларирате всички променливи, преди да стартирате кода. Ако има някаква променлива, която не е декларирана, VBA ще покаже грешка.
Използването на Option Explicit има огромна полза.
Понякога може да направите грешка при въвеждане и да въведете име на променлива, което е неправилно.
Обикновено няма начин VBA да разбере дали е грешка или е умишлена. Когато обаче използвате „Option Explicit“, VBA ще види името на променливата с грешка като нова променлива, която не е декларирана и ще ви покаже грешка. Това ще ви помогне да идентифицирате тези грешно написани имена на променливи, които могат да бъдат доста трудни за разпознаване в дълъг код.
По -долу е даден пример, при който използването на „Option Explicit“ идентифицира грешката (която не би могла да бъде уловена, ако не бях използвал „Option Explicit“)
Sub CommissionCalc () Dim CommissionRate As Double If Range ("A1"). Value> 10000 then CommissionRate = 0.1 Else CommissionRtae = 0.05 End If MsgBox "Total Commission:" & Range ("A1"). Value * CommissionRate End Sub
Обърнете внимание, че веднъж съм написал грешно думата „CommissionRate“ в този код.
Ако не използвам Option Explicit, този код ще се изпълни и ще ми даде грешна обща комисионна стойност (в случай, че стойността в клетка A1 е по -малка от 10000).
Но ако използвам Option Explicit в горната част на модула, той няма да ми позволи да стартирам този код, преди да поправя грешно написаната дума или да я обявя като друга променлива. Той ще покаже грешка, както е показано по -долу:
Въпреки че можете да вмъквате реда „Option Explicit“ всеки път, когато кодирате, ето стъпките, за да го направите по подразбиране:
- В лентата с инструменти на VB Editor щракнете върху Инструменти.
- Кликнете върху Опции.
- В диалоговия прозорец Опции щракнете върху раздела Редактор.
- Проверете опцията - „Изискване на променлива декларация“.
- Щракнете върху OK.
След като активирате тази опция, всеки път, когато отворите нов модул, VBA автоматично ще добави реда „Option Explicit“ към него.
Забележка: Тази опция ще се отрази само на всеки модул, който създавате, след като тази опция е активирана. Всички съществуващи модули не са засегнати.
Обхват на променливите
Досега видяхме как да декларираме променлива и да й присвоим типове данни.
В този раздел ще разгледам обхвата на променливите и как можете да декларирате променлива да се използва само в подпрограма, в цял модул или във всички модули.
Обхватът на променлива определя къде може да се използва променливата във VBA,
Има три начина за обхват на променлива в Excel VBA:
- В рамките на една подпрограма (локални променливи)
- В рамките на модул (променливи на ниво модул)
- Във всички модули (публични променливи)
Нека разгледаме всеки от тях подробно.
В рамките на една подпрограма (локални променливи)
Когато декларирате променлива в рамките на подпрограма/процедура, тази променлива е достъпна само за тази подпрограма.
Не можете да го използвате в други подпрограми в модула.
Веднага след като подпрограмата приключи, променливата се изтрива и използваната от нея памет се освобождава.
В примера по -долу променливите се декларират в рамките на подпрограмата и ще бъдат изтрити, когато тази подпрограма приключи.
В рамките на модул (променливи на ниво модул)
Когато искате променлива да е достъпна за всички процедури в модул, трябва да я декларирате в горната част на модула (а не във всяка подпрограма).
След като го декларирате в горната част на модула, можете да използвате тази променлива във всички процедури в този модул.
В горния пример променливата „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 Комисионна стойност End End Sub
Следният ред се използва за деклариране на константата:
Const CommissionRate As Double = 0.1
Когато декларирате константи, трябва да започнете с ключовата дума „Съст‘, Последвано от името на константата.
Имайте предвид, че в този пример съм посочил типа данни на константата като Double. Отново е добра практика да посочите типа данни, за да направите кода си по -бърз и по -ефективен.
Ако не декларирате типа данни, той ще се счита за вариант тип данни.
Точно като променливите, константите също могат да имат обхват в зависимост от това къде и как те са декларирани:
- В рамките на една подпрограма (Локални константи): Те са налични в подпрограмата/процедурата, в която те са декларирани. Когато процедурата приключи, тези константи се изтриват от паметта на системата.
- В рамките на модул (Константи на ниво модул): Те са декларирани в горната част на модула (преди всяка процедура). Те са достъпни за всички процедури в модула.
- Във всички модули (публични константи): Те се декларират с помощта на ключовата дума „Public“ в горната част на всеки модул (преди всяка процедура). Те са достъпни за всички процедури във всички модули.
Може да харесате и следните уроци по VBA:
- Как да запишете макрос в Excel
- Работа с клетки и диапазони в Excel VBA
- Работа с работни листове с помощта на Excel VBA
- Работа с работни книги в Excel VBA
- Събития във VBA
- Цикли на Excel VBA
- Как да стартирате макрос в Excel
- Ако тогава друго изявление в Excel VBA.
