Razumijevanje tipova podataka Excel VBA (varijable i konstante)

U Excelu VBA često ćete morati koristiti varijable i konstante.

Prilikom rada s VBA -om, varijabla je mjesto u memoriji vašeg računala gdje možete pohraniti podatke. Vrsta podataka koje možete pohraniti u varijablu ovisit će o vrsti podataka varijable.

Na primjer, ako želite spremiti cijele brojeve u varijablu, vaša vrsta podataka bila bi "Integer", a ako želite pohraniti tekst, onda bi vaša vrsta podataka bila "String".

Više o vrstama podataka kasnije u ovom vodiču.

Dok se vrijednost varijable mijenja dok je kod u tijeku, konstanta ima vrijednost koja se nikada ne mijenja. Kao dobra praksa kodiranja, trebali biste definirati tip podataka i varijabilnog i konstantnog.

Zašto koristiti varijable u VBA?

Kada kodirate u VBA -i, trebat će vam varijable koje možete koristiti za držanje vrijednosti.

Prednost korištenja varijable je ta što možete promijeniti vrijednost varijable unutar koda i nastaviti je koristiti u kodu.

Na primjer, ispod je kôd koji dodaje prvih 10 pozitivnih brojeva, a zatim prikazuje rezultat u okviru za poruku:

Sub AddFirstTenNumbers () Dim Var Kao cijeli broj Dim i Kao cijeli broj Dim k kao cijeli broj Za i = 1 do 10 k = k + i Dalje i MsgBox k Kraj pod

U gornjem kodu postoje tri varijable - Var, i, i k.

Gornji kôd koristi petlju For Next u kojoj se sve tri varijable mijenjaju po završetku petlji.

Korisnost varijable leži u činjenici da se može promijeniti dok je vaš kod u tijeku.

Ispod su neka pravila koja morate imati na umu pri imenovanju varijabli u VBA -i:

1. Možete koristiti abecede, brojeve i interpunkciju, ali prvi broj mora biti abeceda.
2. U nazivu varijable ne možete koristiti razmak ili točku. Međutim, možete upotrijebiti znak podcrtavanja kako biste imena varijabli učinili čitljivijima (kao što je Rate_Rate)
3. Ne možete koristiti posebne znakove (#, $, %, & ili!) U nazivima varijabli
4. VBA ne pravi razliku između slova u nazivu varijable. Dakle, "RateRate" i "interest rate" isti su za VBA. Možete koristiti mješovita slova kako biste varijable učinili čitljivijima.
5. VBA ima neke rezervirane nazive koje možete koristiti za naziv varijable. Na primjer, ne možete koristiti riječ 'Dalje' kao naziv varijable jer je to rezervirani naziv za petlju Za sljedeće.
6. Naziv vaše varijable može imati najviše 254 znaka.

Tip podataka varijabli

Kako biste najbolje iskoristili varijable, dobra je praksa navesti tip podataka varijable.

Vrsta podataka koju dodijelite varijabli ovisit će o vrsti podataka koje želite da ta varijabla sadrži.

U nastavku je tablica koja prikazuje sve dostupne tipove podataka koje možete koristiti u programu Excel VBA:

Tip podataka	Korišteni bajtovi	Raspon vrijednosti
Bajt	1 bajt	0 do 255
Booleov	2 bajta	Istina ili laž
Cijeli broj	2 bajta	-32,768 do 32,767
Long (dugi cijeli broj)	4 bajta	-2,147,483,648 do 2,147,483,647
Singl	4 bajta	-3,402823E38 do -1,401298E -45 za negativne vrijednosti; 1.401298E-45 do 3.402823E38 za pozitivne vrijednosti
Dvostruko	8 bajtova	-1.79769313486231E308 do-4.94065645841247E-324 za negativne vrijednosti; 4.94065645841247E-324 do 1.79769313486232E308 za pozitivne vrijednosti
Valuta	8 bajtova	-922.337.203.685.477.5808 do 922.337.203.685.477.5807
Decimal	14 bajtova	+/- 79,228,162,514,264,337,593,543,950,335 bez decimalne točke; +/- 7,9228162514264337593543950335 s 28 mjesta desno od decimalnog mjesta
Datum	8 bajtova	1. siječnja 100. do 31. prosinca 9999. godine
Objekt	4 bajta	Bilo koja referenca objekta
Niz (promjenjive duljine)	10 bajtova + duljina niza	0 do približno 2 milijarde
Žica (fiksne duljine)	Duljina žice	1 do približno 65.400
Varijanta (s brojevima)	16 bajtova	Bilo koja brojčana vrijednost do raspona Double
Varijanta (sa znakovima)	22 bajta + duljina niza	Isti raspon kao i za nizove promjenjive duljine
Korisnik definiran	Varira	Raspon svakog elementa jednak je rasponu njegove vrste podataka.

Kada u kodu navedete vrstu podataka za varijablu, VBA govori kako pohraniti tu varijablu i koliko prostora za nju treba dodijeliti.

Na primjer, ako trebate koristiti varijablu koja ima za cilj zadržati broj mjeseca, možete koristiti vrstu podataka BYTE (koja može prihvatiti vrijednosti od 0 do 255). Budući da broj mjeseca neće biti veći od 12, to će dobro funkcionirati i također će rezervirati manje memorije za ovu varijablu.

Naprotiv, ako vam je potrebna varijabla za spremanje brojeva redaka u Excel, morate upotrijebiti vrstu podataka koja može primiti broj do 1048756. Zato je najbolje koristiti vrstu podataka Long.

Deklariranje promjenjivih tipova podataka

Kao dobra praksa kodiranja, trebali biste deklarirati tip podataka varijabli (ili konstanti) prilikom pisanja koda. Time ćete osigurati da VBA dodjeljuje samo navedenu memoriju varijabli, što može ubrzati rad vašeg koda.

Ispod je primjer gdje sam različite tipove podataka proglasio različitim varijablama:

Sub DeclaringVariables () Dim X kao cijeli broj Dim Email Kao niz Dim Dim Ime

Da biste deklarirali varijabilni tip podataka, morate upotrijebiti DIM izraz (koji je skraćenica od Dimension).

U ‘Dim X kao cijeli broj‘, Promenio sam varijablu X kao integer tip podataka.

Kad ga koristim u kodu, VBA bi znao da X može sadržavati samo cijeli broj podataka.

Ako mu pokušam dodijeliti vrijednost koja nije cijeli broj, dobit ću pogrešku (kao što je prikazano u nastavku):

Napomena: Također možete izabrati da ne deklarirate tip podataka, u tom slučaju VBA automatski razmatra varijablu varijantnog tipa podataka. Varijantni tip podataka može prihvatiti bilo koji tip podataka. Iako se ovo može činiti prikladnim, nije najbolja praksa koristiti varijantni tip podataka. Obično zauzima više memorije i može usporiti rad vašeg VBA koda.

Postavljanje obvezne deklaracije varijable (opcija eksplicitna)

Iako možete kodirati bez da deklarirate varijable, to je dobra praksa.

Osim što štedi memoriju i čini vaš kod učinkovitijim, deklariranje varijabli ima još jednu veliku prednost - pomaže u hvatanju pogrešaka uzrokovanih pogrešno napisanim nazivima varijabli.

Da biste bili sigurni da ste prisiljeni deklarirati varijable, dodajte sljedeći redak na vrh modula.

Opcija Eksplicitno

Kada dodate "Option Explicit", morat ćete deklarirati sve varijable prije pokretanja koda. Ako postoji bilo koja varijabla koja nije deklarirana, VBA će pokazati pogrešku.

Postoji velika korist od korištenja opcije Explicit.

Ponekad ćete možda pogriješiti pri upisu i unijeti naziv varijable koji je netočan.

Normalno, ne postoji način na koji VBA može znati je li to greška ili je namjerna. Međutim, kada koristite "Option Explicit", VBA će vidjeti pogrešno napisano ime varijable kao novu varijablu koja nije deklarirana i prikazat će vam pogrešku. To će vam pomoći da identificirate ove pogrešno napisane nazive varijabli, koje je prilično teško uočiti u dugom kodu.

Ispod je primjer gdje se pomoću opcije "Option Explicit" identificira pogreška (koja se ne bi mogla zarobiti da nisam upotrijebila "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

Imajte na umu da sam jednom u ovom kodu pogrešno napisao riječ 'CommissionRate'.

Ako ne koristim opciju Explicit, ovaj bi se kôd pokrenuo i dao bi mi krivu ukupnu vrijednost provizije (u slučaju da je vrijednost u ćeliji A1 manja od 10000).

Ali ako koristim Option Explicit na vrhu modula, neće mi dopustiti da pokrenem ovaj kôd prije nego što ispravim pogrešno napisanu riječ ili je deklariram kao drugu varijablu. Pokazat će grešku kao što je prikazano u nastavku:

Iako možete umetnuti redak "Option Explicit" svaki put kada kodirate, evo koraka kako bi se prikazao prema zadanim postavkama:

1. Na alatnoj traci VB Editor kliknite Alati.
2. Pritisnite Opcije.
3. U dijaloškom okviru Mogućnosti kliknite karticu Uređivač.
4. Provjerite opciju - "Zahtijevaj promjenjivu deklaraciju".
5. Pritisnite U redu.

Nakon što ste omogućili ovu opciju, kad god otvorite novi modul, VBA bi mu automatski dodao redak "Option Explicit".

Napomena: Ova će opcija utjecati samo na sve module koje kreirate nakon što je ova opcija omogućena. To ne utječe na sve postojeće module.

Opseg varijabli

Do sada smo vidjeli kako proglasiti varijablu i dodijeliti joj tipove podataka.

U ovom odjeljku pokazat ću opseg varijabli i način na koji možete proglasiti varijablu za upotrebu samo u potprogramu, u cijelom modulu ili u svim modulima.

Opseg varijable određuje gdje se varijabla može koristiti u VBA,

Postoje tri načina za uključivanje varijable u Excel VBA:

1. Unutar jedne potprograma (lokalne varijable)
2. Unutar modula (varijable na razini modula)
3. U svim modulima (javne varijable)

Pogledajmo svaki od ovih detalja detaljno.

Unutar jedne potprograma (lokalne varijable)

Kada deklarirate varijablu unutar potprograma/procedure, tada je ta varijabla dostupna samo za tu potprogram.

Ne možete ga koristiti u drugim potprogramima u modulu.

Čim potprogram završi, varijabla se briše i memorija koju koristi oslobađa.

U donjem primjeru varijable su deklarirane unutar potprograma i bile bi izbrisane kad ta potprogram završi.

Unutar modula (varijable na razini modula)

Kada želite da varijabla bude dostupna za sve procedure u modulu, morate je deklarirati na vrhu modula (a ne u bilo kojoj potprogrami).

Nakon što je deklarirate na vrhu modula, možete koristiti tu varijablu u svim postupcima u tom modulu.

U gornjem primjeru, varijabla 'i' je deklarirana pri vrhu modula i dostupna je za upotrebu svim modulima.

Imajte na umu da se po završetku potprograma varijable na razini modula ne brišu (zadržava svoju vrijednost).

Ispod je primjer gdje imam dva koda. Kad pokrenem prvi postupak, a zatim pokrenem drugi, vrijednost 'i' postaje 30 (jer nosi vrijednost 10 iz prvog postupka)

U svim modulima (javne varijable)

Ako želite da varijabla bude dostupna u svim postupcima u radnoj knjizi, morate je objaviti ključnom riječi Public (umjesto DIM -om).

Donji redak koda na vrhu modula učinio bi varijablu "CommissionRate" dostupnom u svim modulima u radnoj knjizi.

 Javna komisijaOcijenite kao dvostruku

Deklaraciju varijable možete umetnuti (koristeći ključnu riječ Public) u bilo koji od modula (na vrhu prije bilo koje procedure).

Statičke varijable (koja zadržava vrijednost)

Kad radite s lokalnim varijablama, čim postupak završi, varijabla bi izgubila svoju vrijednost i izbrisala bi se iz memorije VBA -e.

U slučaju da želite da varijabla zadrži vrijednost, morate koristiti Statički ključna riječ.

Najprije ću vam pokazati što se događa u normalnom slučaju.

U donjem kodu, kad pokrenem postupak više puta, svaki put će pokazati vrijednost 10.

Podpostupak1 () Dim i Kao cijeli broj i = i + 10 MsgBox i Završi sub

Sada, ako koristim ključnu riječ Static umjesto DIM -a, i pokrenem postupak više puta, nastavit će prikazivati ​​vrijednosti u koracima od 10. To se događa jer varijabla 'i' zadržava svoju vrijednost i koristi je u izračunu.

Podpostupak1 () Statički i Kao cijeli broj i = i + 10 MsgBox i Kraj pod

Deklariranje konstanti u Excelu VBA

Dok se varijable mogu mijenjati tijekom izvođenja koda, ako želite imati fiksne vrijednosti, možete koristiti konstante.

Konstanta vam omogućuje da dodijelite vrijednost imenovanom nizu koji možete koristiti u svom kodu.

Prednost korištenja konstante je u tome što olakšava pisanje i razumijevanje koda, a također vam omogućuje kontrolu svih fiksnih vrijednosti s jednog mjesta.

Na primjer, ako izračunate provizije i stopa provizije je 10%, možete stvoriti konstantu (CommissionRate) i dodijeliti joj vrijednost 0,1.

U budućnosti, ako se promijeni stopa provizije, samo trebate unijeti promjenu na jednom mjestu umjesto da je ručno mijenjate u kodu posvuda.

Ispod je primjer koda u kojem sam konstanti dodijelio vrijednost:

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

Sljedeći redak koristi se za deklaraciju konstante:

Const CommissionRate As Double = 0,1

Prilikom deklariranja konstanti morate početi s ključnom riječi 'Konst‘, Nakon čega slijedi naziv konstante.

Imajte na umu da sam u ovom primjeru naveo tip podataka konstante kao Double. Opet, dobra je praksa navesti tip podataka kako bi vaš kôd bio brži i učinkovitiji.

Ako ne deklarirate vrstu podataka, smatrat će se varijantnom vrstom podataka.

Baš kao i varijable, konstante također mogu imati opseg ovisno o tome gdje i kako se one deklariraju:

1. Unutar jedne podrutine (lokalne konstante): Oni su dostupni u potprogramu/postupku u kojem su deklarirani. Na kraju postupka ove se konstante brišu iz memorije sustava.
2. Unutar modula (konstante na razini modula): Oni su deklarirani na vrhu modula (prije bilo kakvog postupka). Oni su dostupni za sve postupke u modulu.
3. U svim modulima (javne konstante): Oni se deklariraju pomoću ključne riječi "Javno" pri vrhu bilo kojeg modula (prije bilo koje procedure). Oni su dostupni svim postupcima u svim modulima.

Možda će vam se svidjeti i sljedeći VBA vodiči:

• Kako snimiti makro u Excelu
• Rad s ćelijama i rasponima u Excelu VBA
• Rad s radnim listovima pomoću programa Excel VBA
• Rad s radnim bilježnicama u Excelu VBA
• VBA događaji
• Excel VBA petlje
• Kako pokrenuti makro u Excelu
• Ako onda Druga izjava u Excelu VBA.