Pochopenie dátových typov Excel VBA (premenné a konštanty)

V programe Excel VBA by ste často museli používať premenné a konštanty.

Pri práci s VBA je premenná umiestnenie v pamäti počítača, kde môžete ukladať údaje. Typ údajov, ktoré môžete uložiť do premennej, bude závisieť od dátového typu premennej.

Ak napríklad chcete do premennej ukladať celé čísla, váš typ údajov bude „celé číslo“ a ak chcete uložiť text, potom bude typ údajov „reťazec“.

Viac informácií o typoch údajov ďalej v tomto návode.

Kým sa hodnota premennej mení, keď prebieha kód, konštanta uchováva hodnotu, ktorá sa nikdy nemení. Ako osvedčený postup kódovania by ste mali definovať typ údajov - variabilný aj konštantný.

Prečo používať premenné vo VBA?

Pri kódovaní vo VBA by ste potrebovali premenné, ktoré môžete použiť na uchovanie hodnoty.

Výhodou použitia premennej je, že môžete zmeniť hodnotu premennej v rámci kódu a naďalej ju používať v kóde.

Nižšie je napríklad kód, ktorý pridá prvých 10 kladných čísel a potom zobrazí výsledok v okne so správou:

Sub AddFirstTenNumbers () Dim Var ako celé číslo Dim i ako celé číslo Dim k ako celé číslo pre i = 1 až 10 k = k + i Ďalej i MsgBox k End Sub

Vyššie uvedený kód obsahuje tri premenné - Var, ia k.

Vyššie uvedený kód používa slučku For Next, kde sa všetky tieto tri premenné menia, keď sú slučky dokončené.

Užitočnosť premennej spočíva v tom, že ju je možné zmeniť, kým prebieha váš kód.

Nasleduje niekoľko pravidiel, ktoré je potrebné mať na pamäti pri pomenovaní premenných vo VBA:

1. Môžete použiť abecedu, čísla a interpunkciu, ale prvé číslo musí byť abeceda.
2. V názve premennej nemôžete používať medzeru ani bodku. Môžete však použiť znak podčiarknutia, aby boli názvy premenných čitateľnejšie (napríklad Úroková sadzba)
3. V názvoch premenných nemôžete používať špeciálne znaky (#, $, %, &, alebo!)
4. VBA v názve premennej nerozlišuje prípad. „Úroková sadzba“ a „úroková sadzba“ sú teda pre VBA rovnaké. Na zvýšenie čitateľnosti premenných môžete použiť kombináciu malých a veľkých písmen.
5. VBA má niekoľko vyhradených názvov, ktoré môžete použiť pre názov premennej. Napríklad nemôžete použiť slovo „Ďalej“ ako názov premennej, pretože je to vyhradený názov pre slučku For Next.
6. Názov premennej môže mať až 254 znakov.

Dátový typ premenných

Na čo najlepšie využitie premenných je vhodné špecifikovať typ údajov premennej.

Typ údajov, ktorý priradíte premennej, bude závisieť od typu údajov, ktoré má daná premenná uchovávať.

Nasleduje tabuľka, ktorá zobrazuje všetky dostupné typy údajov, ktoré môžete použiť v programe Excel VBA:

Dátový typ	Použité bajty	Rozsah hodnôt
Bajt	1 bajt	0 až 255
Booleovský	2 bajty	Pravda alebo lož
Celé číslo	2 bajty	-32 768 až 32 767
Dlhé (dlhé celé číslo)	4 bajty	-2 147 483 648 až 2 147 483 647
Single	4 bajty	-3,402823E38 až -1,401298E -45 pre záporné hodnoty; 1,401298E-45 až 3,402823E38 pre kladné hodnoty
Dvojité	8 bajtov	-1,79769313486231E308 až-4,94065645841247E-324 pre mínusové hodnoty; 4.94065645841247E-324 až 1,79769313486232E308 pre kladné hodnoty
Mena	8 bajtov	-922 337 203 685 477,5808 až 922 337 203 685 477,5807
Desatinné	14 bajtov	+/- 79 228 162 514 264 337 593 543 950 335 bez desatinnej čiarky; +/- 7,9228162514264337593543950335 s 28 miestami vpravo od desatinnej čiarky
Dátum	8 bajtov	1. januára 100 do 31. decembra 9999
Objekt	4 bajty	Akýkoľvek odkaz na objekt
Reťazec (variabilná dĺžka)	10 bajtov + dĺžka reťazca	0 až približne 2 miliardy
Reťazec (s pevnou dĺžkou)	Dĺžka šnúrky	1 až približne 65 400
Variant (s číslami)	16 bajtov	Ľubovoľná číselná hodnota až do rozsahu dvojnásobku
Variant (so znakmi)	22 bajtov + dĺžka reťazca	Rovnaký rozsah ako pre reťazec s premennou dĺžkou
Definované užívateľom	Rôzne	Rozsah každého prvku je rovnaký ako rozsah jeho dátového typu.

Keď v kóde zadáte typ údajov pre premennú, oznámi to VBA, ako má túto premennú uložiť a koľko priestoru pre ňu vyhradiť.

Ak napríklad potrebujete použiť premennú, ktorá má obsahovať číslo mesiaca, môžete použiť dátový typ BYTE (ktorý môže obsahovať hodnoty od 0 do 255). Pretože číslo mesiaca nebude vyššie ako 12, bude to fungovať dobre a taktiež si vyhradíte menej pamäte pre túto premennú.

Naopak, ak potrebujete na uloženie čísel riadkov v programe Excel premennú, musíte použiť dátový typ, ktorý pojme číslo až 1048756. Preto je najlepšie použiť dátový typ Long.

Deklarácia premenných typov údajov

Ako osvedčený postup kódovania by ste pri písaní kódu mali deklarovať dátový typ premenných (alebo konštánt). Tým sa zaistí, že VBA alokuje premennej iba uvedenú pamäť, a preto môže váš kód bežať rýchlejšie.

Nasleduje príklad, kde som deklaroval rôzne typy údajov pre rôzne premenné:

Sub DeclaringVariables () Dim X as Integer Dim Email as String Dim FirstName as String Dim Row Rowount as Long Dim TodayDate As Date End Sub

Ak chcete deklarovať variabilný typ údajov, musíte použiť príkaz DIM (čo je skratka pre dimenziu).

V ‘Dim X ako celé číslo“, Premennú X som deklaroval ako celočíselný údajový typ.

Keď to teraz použijem vo svojom kóde, VBA bude vedieť, že X môže obsahovať iba celočíselný typ údajov.

Ak sa mu pokúsim priradiť hodnotu, ktorá nie je celé číslo, zobrazí sa chyba (ako je uvedené nižšie):

Poznámka: Môžete sa tiež rozhodnúť, že údajový typ nebudete deklarovať. V takom prípade VBA automaticky zváži premennú variantného dátového typu. Variantný typ údajov môže obsahovať akýkoľvek typ údajov. Aj keď sa to môže zdať výhodné, nie je najvhodnejšie používať variantný typ údajov. Má tendenciu zaberať viac pamäte a môže spôsobiť, že váš kód VBA bude bežať pomalšie.

Vyhlásenie o premennej na povinné (explicitná možnosť)

Aj keď môžete kódovať bez toho, aby ste museli deklarovať premenné, je vhodné to urobiť.

Okrem úspory pamäte a zefektívnenia kódu má deklarovanie premenných ešte jednu veľkú výhodu - pomáha zachytiť chyby spôsobené nesprávne napísanými názvami premenných.

Aby ste sa uistili, že ste nútení deklarovať premenné, pridajte nasledujúci riadok do hornej časti modulu.

Možnosť explicitná

Keď pridáte „Možnosť explicitná“, budete musieť pred spustením kódu deklarovať všetky premenné. Ak existuje nejaká premenná, ktorá nebola deklarovaná, VBA zobrazí chybu.

Používanie Option Explicitne prináša obrovské výhody.

Niekedy sa môže stať, že urobíte chybu pri písaní a zadáte nesprávny názov premennej.

VBA za normálnych okolností nemôže zistiť, či je to chyba alebo úmysel. Keď však použijete možnosť „Explicitná voľba“, VBA by videl názov nesprávne zadanej premennej ako novú premennú, ktorá nebola deklarovaná, a zobrazí vám chybu. Pomôže vám to identifikovať tieto nesprávne napísané názvy premenných, ktoré je v dlhom kóde dosť ťažké rozpoznať.

Nasleduje príklad, kde sa pomocou chyby „Explicitná možnosť“ identifikuje chyba (ktorá by nemohla byť zachytená, ak by som nepoužil „explicitnú možnosť“)

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

Všimnite si toho, že som v tomto kódexe nesprávne napísal slovo „CommissionRate“.

Ak nepoužívam explicitnú voľbu, spustí sa tento kód a poskytne mi nesprávnu celkovú hodnotu provízie (v prípade, že hodnota v bunke A1 je menšia ako 10 000).

Ale ak použijem Option Explicit v hornej časti modulu, nedovolí mi to spustiť tento kód skôr, ako opravím nesprávne napísané slovo alebo ho vyhlásim za inú premennú. Zobrazí chybu, ako je uvedené nižšie:

Aj keď môžete pri každom kódovaní vložiť riadok „Explicitná možnosť“, tu sú kroky na to, aby sa zobrazil v predvolenom nastavení:

1. Na paneli s nástrojmi VB Editor kliknite na Nástroje.
2. Kliknite na Možnosti.
3. V dialógovom okne Možnosti kliknite na kartu Editor.
4. Začiarknite možnosť - „Vyžadovať variabilné vyhlásenie“.
5. Kliknite na tlačidlo OK.

Keď povolíte túto možnosť, VBA pri každom otvorení nového modulu automaticky pridá riadok „Explicitná možnosť“.

Poznámka: Táto možnosť ovplyvní iba modul, ktorý vytvoríte po povolení tejto možnosti. Všetky existujúce moduly nie sú ovplyvnené.

Rozsah premenných

Doteraz sme videli, ako premennú deklarovať a priradiť k nej dátové typy.

V tejto časti sa budem zaoberať rozsahom premenných a tým, ako môžete deklarovať premennú, ktorá sa má použiť iba v podprograme, v celom module alebo vo všetkých moduloch.

Rozsah premennej určuje, kde je možné premennú použiť vo VBA,

V programe Excel VBA existujú tri spôsoby, ako nastaviť premennú:

1. V rámci jedného podprogramu (miestne premenné)
2. V rámci modulu (premenné na úrovni modulu)
3. Vo všetkých moduloch (verejné premenné)

Pozrime sa na každú z nich podrobne.

V rámci jedného podprogramu (miestne premenné)

Keď deklarujete premennú v rámci podprogramu/procedúry, potom je táto premenná k dispozícii iba pre tento podprogram.

Nemôžete ho použiť v iných podprogramoch v module.

Hneď ako podprogram skončí, premenná sa vymaže a pamäť, ktorú používa, sa uvoľní.

V nižšie uvedenom príklade sú premenné deklarované v rámci podprogramu a budú vymazané, keď tento podprogram skončí.

V rámci modulu (premenné na úrovni modulu)

Ak chcete, aby bola premenná k dispozícii pre všetky procedúry v module, musíte ju deklarovať v hornej časti modulu (a nie v žiadnom podprograme).

Keď ho deklarujete v hornej časti modulu, môžete túto premennú použiť vo všetkých postupoch v tomto module.

Vo vyššie uvedenom príklade je premenná „i“ deklarovaná v hornej časti modulu a je k dispozícii na použitie pre všetky moduly.

Všimnite si toho, že keď sa podprogram skončí, premenné na úrovni modulu sa neodstránia (zachová si svoju hodnotu).

Nasleduje príklad, kde mám dva kódy. Keď spustím prvý postup a potom spustím druhý, hodnota „i“ sa zmení na 30 (pretože nesie hodnotu 10 z prvého postupu)

Vo všetkých moduloch (verejné premenné)

Ak chcete, aby bola premenná dostupná v celom postupe v zošite, musíte ju deklarovať kľúčovým slovom Public (namiesto DIM).

Nasledujúci riadok kódu v hornej časti modulu by sprístupnil premennú „CommissionRate“ vo všetkých moduloch v zošite.

 Verejná provízia Sadzba ako dvojnásobná

Deklaráciu premennej (pomocou kľúčového slova Public) môžete vložiť do ktoréhokoľvek z modulov (v hornej časti pred akýmkoľvek postupom).

Statické premenné (ktoré zachovávajú hodnotu)

Keď pracujete s lokálnymi premennými, hneď ako sa procedúra skončí, premenná stratí svoju hodnotu a bude odstránená z pamäte VBA.

V prípade, že chcete, aby si premenná zachovala hodnotu, musíte použiť Statický kľúčové slovo.

Najprv vám ukážem, čo sa deje v normálnom prípade.

V nižšie uvedenom kóde, keď spustím postup viackrát, vždy zobrazí hodnotu 10.

Sub Procedúra1 () Dim i As Integer i = i + 10 MsgBox i End Sub

Teraz, keď použijem kľúčové slovo Static namiesto DIM a spustím postup viackrát, bude naďalej zobrazovať hodnoty v prírastkoch po 10. Toto sa stane, keď si premenná „i“ zachová svoju hodnotu a použije ju pri výpočte.

Podpostup1 () Statický i ako celé číslo i = i + 10 MsgBox i Koniec podč

Deklarácia konštánt v programe Excel VBA

Aj keď sa premenné môžu počas vykonávania kódu meniť, ak chcete mať pevné hodnoty, môžete použiť konštanty.

Konštanta vám umožňuje priradiť pomenovanému reťazcu hodnotu, ktorú môžete použiť vo svojom kóde.

Výhodou použitia konštanty je, že uľahčuje písanie a porozumenie kódu a tiež vám umožňuje ovládať všetky pevné hodnoty z jedného miesta.

Ak napríklad počítate provízie a miera provízií je 10%, môžete vytvoriť konštantu (CommissionRate) a priradiť jej hodnotu 0,1.

Ak sa v budúcnosti sadzba provízie zmení, stačí zmenu vykonať na jednom mieste namiesto toho, aby ste ju v kóde manuálne menili všade.

Nasleduje príklad kódu, kde som konštante priradil hodnotu:

Sub CalculateCommission () Dim CommissionValue as Double Const CommissionRate As Double = 0,1 CommissionValue = Range ("A1") * CommissionRate MsgBox CommissionValue End Sub Sub

Na deklaráciu konštanty sa používa nasledujúci riadok:

Konštantná sadzba provízie ako dvojnásobok = 0,1

Pri deklarovaní konštánt musíte začať kľúčovým slovom „Const“, Za ktorým nasleduje názov konštanty.

Všimnite si toho, že som v tomto prípade špecifikoval dátový typ konštanty ako Double. Opäť je osvedčené špecifikovať typ údajov, aby váš kód bežal rýchlejšie a efektívnejšie.

Ak typ údajov nenahlásite, bude považovaný za variantný typ údajov.

Rovnako ako premenné, aj konštanty môžu mať rozsah podľa toho, kde a ako sú deklarované:

1. V rámci jedného podprogramu (lokálne konštanty): Sú k dispozícii v podprograme/postupe, v ktorom sú deklarované. Na konci postupu sa tieto konštanty vymažú z pamäte systému.
2. V rámci modulu (konštanty na úrovni modulu): Tieto sú deklarované v hornej časti modulu (pred akýmkoľvek postupom). Sú k dispozícii pre všetky postupy v module.
3. Vo všetkých moduloch (Verejné konštanty): Tieto sú deklarované pomocou kľúčového slova „Verejné“ v hornej časti akéhokoľvek modulu (pred akýmkoľvek postupom). Tieto sú k dispozícii pre všetky postupy vo všetkých moduloch.

Tiež by sa vám mohli páčiť nasledujúce návody VBA:

• Ako zaznamenať makro v programe Excel
• Práca s bunkami a rozsahmi v programe Excel VBA
• Práca s pracovnými listami pomocou programu Excel VBA
• Práca so zošitmi v programe Excel VBA
• Udalosti VBA
• Slučky Excel VBA
• Ako spustiť makro v programe Excel
• Ak Potom Ostatné Vyhlásenie v programe Excel VBA.