Porozumění datovým typům aplikace Excel VBA (proměnné a konstanty)

V aplikaci Excel VBA byste často museli používat proměnné a konstanty.

Při práci s VBA je proměnná umístění v paměti vašeho počítače, kam můžete ukládat data. Typ dat, který můžete uložit do proměnné, bude záviset na datovém typu proměnné.

Pokud například chcete ukládat celá čísla do proměnné, bude váš datový typ „Integer“ a pokud chcete ukládat text, pak bude váš datový typ „String“.

Více o datových typech dále v tomto kurzu.

Zatímco hodnota proměnné se mění, když probíhá kód, konstanta uchovává hodnotu, která se nikdy nemění. Jako správný postup kódování byste měli definovat datový typ obou - proměnných i konstant.

Proč používat ve VBA proměnné?

Při kódování ve VBA byste potřebovali proměnné, které můžete použít k uchování hodnoty.

Výhodou použití proměnné je, že můžete změnit hodnotu proměnné v kódu a nadále ji v kódu používat.

Níže je například kód, který přidá prvních 10 kladných čísel a výsledek pak zobrazí v okně se zprávou:

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

Ve výše uvedeném kódu jsou tři proměnné - Var, já, a k.

Výše uvedený kód používá smyčku For Next, kde se všechny tyto tři proměnné mění po dokončení smyček.

Užitečnost proměnné spočívá v tom, že ji lze změnit, zatímco probíhá váš kód.

Při pojmenovávání proměnných ve VBA je třeba mít na paměti některá pravidla:

1. Můžete použít abecedu, čísla a interpunkci, ale první číslo musí být abeceda.
2. V názvu proměnné nelze použít mezeru ani tečku. Můžete však použít znak podtržítka, aby byly názvy proměnných čitelnější (například Interest_Rate)
3. V názvech proměnných nelze použít speciální znaky (#, $, %, &, nebo!)
4. VBA v názvu proměnné nerozlišuje případ. „Úroková sazba“ a „úroková sazba“ jsou tedy pro VBA stejné. Aby byly proměnné čitelnější, můžete použít velká a malá písmena.
5. VBA má některá vyhrazená jména, která můžete použít pro název proměnné. Například nemůžete použít slovo „Další“ jako název proměnné, protože je to vyhrazený název pro smyčku For Next.
6. Název vaší proměnné může mít až 254 znaků.

Datový typ proměnných

Abyste co nejlépe využili proměnné, je vhodné specifikovat datový typ proměnné.

Datový typ, který proměnné přiřadíte, bude záviset na typu dat, které má tato proměnná uchovávat.

Níže je tabulka, která ukazuje všechny dostupné datové typy, které můžete použít v aplikaci Excel VBA:

Datový typ	Použité bajty	Rozsah hodnot
Byte	1 bajt	0 až 255
Boolean	2 bajty	Pravda nebo lež
Celé číslo	2 bajty	-32 768 až 32 767
Long (dlouhé celé číslo)	4 byty	-2 147 483 648 až 2 147 483 647
Singl	4 byty	-3,402823E38 až -1,401298E -45 pro záporné hodnoty; 1.401298E-45 až 3,402823E38 pro kladné hodnoty
Dvojnásobek	8 bajtů	-1,79769313486231E308 až-4,94065645841247E-324 pro záporné hodnoty; 4.94065645841247E-324 až 1,79769313486232E308 pro kladné hodnoty
Měna	8 bajtů	-922 337 203 685 477,5808 až 922 337 203 685 477,5807
Desetinný	14 bajtů	+/- 79,228,162,514,264,337,593,543,950,335 bez desetinné čárky; +/- 7,9228162514264337593543950335 s 28 místy vpravo od desetinné čárky
datum	8 bajtů	1. ledna 100 do 31. prosince 9999
Objekt	4 byty	Jakýkoli odkaz na objekt
Řetězec (proměnná délka)	10 bajtů + délka řetězce	0 až přibližně 2 miliardy
Řetězec (pevná délka)	Délka provázku	1 až přibližně 65 400
Varianta (s čísly)	16 bajtů	Libovolná číselná hodnota až do rozsahu Double
Varianta (se znaky)	22 bajtů + délka řetězce	Stejný rozsah jako pro řetězec s proměnnou délkou
Definováno uživatelem	Liší se	Rozsah každého prvku je stejný jako rozsah jeho datového typu.

Když v kódu zadáte datový typ pro proměnnou, řekne VBA, jak tuto proměnnou uložit a kolik místa jí přidělit.

Pokud například potřebujete použít proměnnou, která má obsahovat číslo měsíce, můžete použít datový typ BYTE (který může obsahovat hodnoty od 0 do 255). Protože číslo měsíce nebude vyšší než 12, bude to fungovat dobře a také vyhradit méně paměti pro tuto proměnnou.

Naopak, pokud potřebujete proměnnou k ukládání čísel řádků do Excelu, musíte použít datový typ, který pojme číslo až 1048756. Nejlepší je tedy použít datový typ Long.

Deklarace proměnných datových typů

Jako správný postup kódování byste při psaní kódu měli deklarovat datový typ proměnných (nebo konstant). Tím zajistíte, že VBA přiděluje proměnné pouze zadanou paměť, a tím může váš kód běžet rychleji.

Níže je uveden příklad, kde jsem deklaroval různé datové typy pro různé proměnné:

Sub DeclaringVariables () Dim X jako celé číslo Dim Email jako řetězec Dim FirstName jako řetězec Dim RowCount jako dlouhý Dim TodayDate As Date End Sub

Chcete -li deklarovat proměnný datový typ, musíte použít příkaz DIM (což je zkratka pro Dimension).

V 'Dim X jako celé číslo‘, Proměnnou X jsem deklaroval jako celočíselný datový typ.

Když to nyní použiji ve svém kódu, VBA by věděl, že X může obsahovat pouze celočíselný datový typ.

Pokud se pokusím přiřadit hodnotu, která není celé číslo, zobrazí se chyba (jak je uvedeno níže):

Poznámka: Můžete se také rozhodnout nedeklarovat datový typ, v takovém případě VBA automaticky zohlední proměnnou datového typu varianty. Variantní datový typ může pojmout jakýkoli datový typ. I když se to může zdát výhodné, není vhodné používat variantní datový typ. Má tendenci zabírat více paměti a může zpomalit běh kódu VBA.

Povinná deklarace proměnných (explicitní možnost)

I když můžete kódovat, aniž byste museli deklarovat proměnné, je dobré to udělat.

Kromě úspory paměti a zefektivnění kódu má deklarace proměnných ještě jednu velkou výhodu - pomáhá zachytit chyby způsobené chybně napsanými názvy proměnných.

Abyste se ujistili, že jste nuceni deklarovat proměnné, přidejte do horní části modulu následující řádek.

Možnost Explicitní

Když přidáte „Možnost explicitní“, budete muset před spuštěním kódu deklarovat všechny proměnné. Pokud existuje nějaká proměnná, která nebyla deklarována, VBA zobrazí chybu.

Používání Option Explicit má obrovskou výhodu.

Někdy se může stát, že uděláte chybu při psaní a zadáte nesprávný název proměnné.

Za normálních okolností neexistuje způsob, jakým by VBA věděla, zda jde o omyl nebo úmysl. Když však použijete „Možnost explicitní“, VBA uvidí název chybně napsané proměnné jako novou proměnnou, která nebyla deklarována, a zobrazí vám chybu. To vám pomůže identifikovat tyto chybně napsané názvy proměnných, což může být v dlouhém kódu docela těžké odhalit.

Níže je uveden příklad, kde použití ‘Option Explicit’ identifikuje chybu (která by nemohla být zachycena, kdybych nepoužil ‘Option Explicit’)

Sub CommissionCalc () Dim CommissionRate as Double If Range ("A1"). Hodnota> 10000 Then CommissionRate = 0,1 Else CommissionRtae = 0,05 End If MsgBox "Total Commission:" & Range ("A1"). Value * CommissionRate End Sub

Všimněte si, že jsem v tomto kódu jednou překlepl slovo „CommissionRate“.

Pokud nepoužiji Option Explicit, tento kód se spustí a poskytne mi špatnou celkovou hodnotu provize (v případě, že hodnota v buňce A1 je menší než 10 000).

Ale pokud použiji Option Explicit v horní části modulu, nedovolí mi spustit tento kód, než opravím chybně napsané slovo nebo ho prohlásím za jinou proměnnou. Zobrazí se chyba, jak je uvedeno níže:

Řádek „Option Explicit“ můžete vložit pokaždé, když kódujete, ale ve výchozím nastavení ho zobrazíte takto:

1. Na panelu nástrojů editoru VB klikněte na Nástroje.
2. Klikněte na Možnosti.
3. V dialogovém okně Možnosti klikněte na kartu Editor.
4. Zaškrtněte možnost - „Vyžadovat proměnné prohlášení“.
5. Klikněte na OK.

Jakmile tuto možnost povolíte, kdykoli otevřete nový modul, VBA do něj automaticky přidá řádek „Možnost explicitní“.

Poznámka: Tato možnost bude mít vliv pouze na jakýkoli modul, který vytvoříte, až bude tato možnost povolena. Všechny stávající moduly nejsou ovlivněny.

Rozsah proměnných

Doposud jsme viděli, jak deklarovat proměnnou a přiřadit jí datové typy.

V této části se budu zabývat rozsahem proměnných a tím, jak můžete deklarovat proměnnou, která má být použita pouze v podprogramu, v celém modulu nebo ve všech modulech.

Rozsah proměnné určuje, kde lze proměnnou použít ve VBA,

V aplikaci Excel VBA existují tři způsoby, jak upravit proměnnou:

1. V rámci jednoho podprogramu (místní proměnné)
2. V rámci modulu (proměnné na úrovni modulu)
3. Ve všech modulech (veřejné proměnné)

Podívejme se na každý z nich podrobně.

V rámci jednoho podprogramu (místní proměnné)

Když deklarujete proměnnou v rámci podprogramu/procedury, pak je tato proměnná k dispozici pouze pro tento podprogram.

Nelze jej použít v jiných podprogramech v modulu.

Jakmile podprogram skončí, proměnná se odstraní a paměť, kterou používá, se uvolní.

V níže uvedeném příkladu jsou proměnné deklarovány v rámci podprogramu a budou odstraněny, když tento podprogram skončí.

V rámci modulu (proměnné na úrovni modulu)

Pokud chcete, aby byla proměnná k dispozici pro všechny procedury v modulu, musíte ji deklarovat v horní části modulu (a ne v jakémkoli podprogramu).

Jakmile ji deklarujete v horní části modulu, můžete tuto proměnnou použít ve všech postupech v tomto modulu.

Ve výše uvedeném příkladu je proměnná „i“ deklarována v horní části modulu a je k dispozici pro použití všemi moduly.

Všimněte si, že když podprogram skončí, proměnné na úrovni modulu nebudou odstraněny (zachová si svou hodnotu).

Níže je příklad, kde mám dva kódy. Když spustím první proceduru a poté spustím druhou, hodnota „i“ se stane 30 (protože nese hodnotu 10 z první procedury)

Ve všech modulech (veřejné proměnné)

Pokud chcete, aby proměnná byla k dispozici ve všech procedurách v sešitu, musíte ji deklarovat klíčovým slovem Public (namísto DIM).

Níže uvedený řádek kódu v horní části modulu by zpřístupnil proměnnou „CommissionRate“ ve všech modulech v sešitu.

 Veřejná provize Míra jako dvojnásobek

Deklaraci proměnné (pomocí klíčového slova Public) můžete vložit do kteréhokoli z modulů (v horní části před jakýmkoli postupem).

Statické proměnné (které zachovávají hodnotu)

Když pracujete s lokálními proměnnými, jakmile procedura skončí, proměnná ztratí svoji hodnotu a bude vymazána z paměti VBA.

V případě, že chcete, aby si proměnná zachovala hodnotu, musíte použít Statický klíčové slovo.

Nejprve vám ukážu, co se děje v normálním případě.

V níže uvedeném kódu, když spustím proceduru vícekrát, zobrazí vždy hodnotu 10.

Dílčí postup1 () Dim i As Integer i = i + 10 MsgBox i End Sub

Nyní, když místo DIM použiji klíčové slovo Static a spustím proceduru vícekrát, bude nadále zobrazovat hodnoty v přírůstcích po 10. To se stává, protože proměnná „i“ si zachovává svoji hodnotu a používá ji při výpočtu.

Dílčí postup1 () Statický i jako celé číslo i = i + 10 MsgBox i Koncový dílčí

Deklarace konstant v aplikaci Excel VBA

Zatímco proměnné se mohou během provádění kódu měnit, pokud chcete mít pevné hodnoty, můžete použít konstanty.

Konstanta vám umožňuje přiřadit hodnotu pojmenovanému řetězci, kterou můžete použít v kódu.

Výhodou použití konstanty je, že usnadňuje psaní a porozumění kódu a také umožňuje ovládat všechny pevné hodnoty z jednoho místa.

Pokud například počítáte provize a sazba provize je 10%, můžete vytvořit konstantu (CommissionRate) a přiřadit jí hodnotu 0,1.

Pokud se v budoucnu změní sazba provize, stačí provést změnu na jednom místě místo toho, abyste ji všude ručně měnili v kódu.

Níže je příklad kódu, kde jsem konstantě přiřadil hodnotu:

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

K deklarování konstanty slouží následující řádek:

Const CommissionRate As Double = 0,1

Při deklaraci konstant musíte začít klíčovým slovem „Const“, Za nímž následuje název konstanty.

Všimněte si, že jsem v tomto příkladu zadal datový typ konstanty jako Double. Opět je vhodné zadat typ dat, aby váš kód běžel rychleji a byl efektivnější.

Pokud nedeklarujete datový typ, bude považován za variantní datový typ.

Stejně jako proměnné mohou mít konstanty také rozsah podle toho, kde a jak jsou deklarovány:

1. V rámci jednoho podprogramu (místní konstanty): Jsou k dispozici v podprogramu/postupu, ve kterém jsou deklarovány. Jak procedura končí, tyto konstanty jsou vymazány z paměti systému.
2. V rámci modulu (konstanty na úrovni modulu): Jsou deklarovány v horní části modulu (před jakýmkoli postupem). Ty jsou k dispozici pro všechny postupy v modulu.
3. Ve všech modulech (veřejné konstanty): Jsou deklarována pomocí klíčového slova „Public“ v horní části libovolného modulu (před jakýmkoli postupem). Ty jsou k dispozici všem postupům ve všech modulech.

Také by se vám mohly líbit následující výukové programy VBA:

• Jak zaznamenat makro v aplikaci Excel
• Práce s buňkami a rozsahy v aplikaci Excel VBA
• Práce s listy pomocí Excelu VBA
• Práce se sešity v aplikaci Excel VBA
• Akce VBA
• Smyčky Excel VBA
• Jak spustit makro v aplikaci Excel
• If Then Else Statement in Excel VBA.