Razumevanje podatkovnih tipov Excel VBA (spremenljivke in konstante)

V Excelu VBA boste pogosto morali uporabljati spremenljivke in konstante.

Pri delu z VBA je spremenljivka lokacija v pomnilniku računalnika, kamor lahko shranite podatke. Vrsta podatkov, ki jih lahko shranite v spremenljivko, je odvisna od vrste podatkov spremenljivke.

Na primer, če želite shraniti cela števila v spremenljivko, bi bil vaš podatkovni tip »Integer«, če pa želite shraniti besedilo, pa bi bil vaš podatkovni tip »String«.

Več o vrstah podatkov kasneje v tej vadnici.

Medtem ko se vrednost spremenljivke spreminja, ko je koda v teku, ima konstanta vrednost, ki se nikoli ne spremeni. Kot dobra praksa kodiranja bi morali določiti podatkovni tip obeh - spremenljivih in konstantnih.

Zakaj uporabljati spremenljivke v VBA?

Ko kodirate v VBA, boste potrebovali spremenljivke, ki jih lahko uporabite za shranjevanje vrednosti.

Prednost uporabe spremenljivke je, da lahko spremenite vrednost spremenljivke v kodi in jo še naprej uporabljate v kodi.

Spodaj je na primer koda, ki doda prvih 10 pozitivnih števil in nato prikaže rezultat v polju za sporočila:

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

V zgornji kodi so tri spremenljivke - Var, jaz, in k.

Zgornja koda uporablja zanko For Next, kjer se vse tri spremenljivke spremenijo, ko se zanke zaključijo.

Koristnost spremenljivke je v tem, da jo je mogoče spremeniti, ko je koda v teku.

Spodaj je nekaj pravil, ki jih morate upoštevati pri poimenovanju spremenljivk v VBA:

1. Uporabite lahko abecede, številke in ločila, vendar mora biti prva številka abeceda.
2. V imenu spremenljivke ne morete uporabiti presledka ali pike. Lahko pa uporabite podčrtaj, da bodo imena spremenljivk bolj berljiva (na primer Rate_Rate)
3. V imenih spremenljivk ne morete uporabljati posebnih znakov (#, $, %in & ali!)
4. VBA ne razlikuje med velikimi in malimi črkami v imenu spremenljivke. Tako sta „InterestRate“ in „interest rate“ enaka za VBA. Če želite spremenljivke narediti bolj berljive, lahko uporabite mešane črke.
5. VBA ima nekaj rezerviranih imen, ki jih lahko uporabite za ime spremenljivke. Na primer, besede »Naprej« ne morete uporabiti kot ime spremenljivke, saj je to rezervirano ime za zanko For Next.
6. Ime vaše spremenljivke je lahko dolgo do 254 znakov.

Podatkovni tip spremenljivk

Za čim boljšo uporabo spremenljivk je dobro določiti podatkovni tip spremenljivke.

Vrsta podatkov, ki jo dodelite spremenljivki, bo odvisna od vrste podatkov, za katere želite, da jih hrani.

Spodaj je tabela, ki prikazuje vse razpoložljive vrste podatkov, ki jih lahko uporabite v Excelu VBA:

Vrsta podatkov	Uporabljeni bajti	Obseg vrednosti
Bajt	1 bajt	0 do 255
Logično	2 bajta	Pravilno ali napačno
Celo število	2 bajta	-32,768 do 32,767
Long (dolgo celo število)	4 bajti	-2,147,483,648 do 2,147,483,647
Samski	4 bajti	-3,402823E38 do -1,401298E -45 za negativne vrednosti; 1.401298E-45 do 3.402823E38 za pozitivne vrednosti
Dvojno	8 bajtov	-1.79769313486231E308 do-4.94065645841247E-324 za negativne vrednosti; 4,94065645841247E-324 do 1,79769313486232E308 za pozitivne vrednosti
Valuta	8 bajtov	-922.337.203.685.477.5808 do 922.337.203.685.477.5807
Decimalno	14 bajtov	+/- 79,228,162,514,264,337,593,543,950,335 brez decimalne vejice; +/- 7,9228162514264337593543950335 z 28 mesti desno od decimalke
Datum	8 bajtov	1. januarja 100 do 31. decembra 9999
Objekt	4 bajti	Katera koli referenca predmeta
Niz (spremenljiva dolžina)	10 bajtov + dolžina niza	0 do približno 2 milijardi
Niz (fiksne dolžine)	Dolžina vrvice	1 do približno 65.400
Različica (s številkami)	16 bajtov	Vsaka številska vrednost do območja Double
Različica (z znaki)	22 bajtov + dolžina niza	Isti obseg kot za niz s spremenljivo dolžino
Uporabnik definiran	Varira	Obseg vsakega elementa je enak kot obseg njegovega podatkovnega tipa.

Ko v kodi podate podatkovni tip za spremenljivko, VBA pove, kako shrani to spremenljivko in koliko prostora zanjo nameni.

Na primer, če morate uporabiti spremenljivko, ki naj bi hranila številko meseca, lahko uporabite podatkovni tip BYTE (ki lahko sprejme vrednosti od 0 do 255). Ker številka meseca ne bo višja od 12, bo to delovalo dobro in tudi prihranilo manj pomnilnika za to spremenljivko.

Nasprotno, če potrebujete spremenljivko za shranjevanje številk vrstic v Excelu, morate uporabiti podatkovni tip, ki lahko sprejme številko do 1048756. Zato je najbolje uporabiti podatkovni tip Long.

Razglasitev spremenljivih podatkovnih tipov

Kot dobra praksa kodiranja morate pri pisanju kode navesti podatkovni tip spremenljivk (ali konstant). S tem zagotovite, da VBA spremenljivki dodeli le določen pomnilnik, kar lahko pospeši delovanje vaše kode.

Spodaj je primer, ko sem različnim spremenljivkam razglasil različne vrste podatkov:

Sub DeclaringVariables () Dim X As Integer Dim Dim As String Dim FirstName As String Dim RowCount as Long Dim TodayDate As Date End Sub

Če želite razglasiti spremenljiv podatkovni tip, morate uporabiti stavek DIM (ki je okrajšava za Dimension).

V 'Zatemni X kot celo število', Spremenljivko X sem razglasil za podatkovni tip Integer.

Ko ga uporabim v svoji kodi, bi VBA vedel, da lahko X vsebuje samo celoštevilski tip podatkov.

Če mu poskušam dodeliti vrednost, ki ni celo število, se prikaže napaka (kot je prikazano spodaj):

Opomba: Lahko se tudi odločite, da podatkovnega tipa ne deklarirate. V tem primeru VBA samodejno upošteva spremenljivko različnega podatkovnega tipa. Različen podatkovni tip lahko sprejme kateri koli tip podatkov. Čeprav se to morda zdi priročno, uporaba različnih podatkovnih tipov ni najboljša praksa. Navadno zavzame več pomnilnika in lahko upočasni delovanje vaše kode VBA.

Objava obveznosti deklaracije spremenljivke (možnost izrecno)

Čeprav lahko kodirate, ne da bi kdaj deklarirali spremenljivke, je to dobra praksa.

Poleg varčevanja pomnilnika in učinkovitejše kode ima razglasitev spremenljivk še eno veliko prednost - pomaga ujeti napake, ki jih povzročijo napačno napisana imena spremenljivk.

Če želite zagotoviti, da morate spremeniti spremenljivke, dodajte naslednjo vrstico na vrh modula.

Možnost izrecno

Ko dodate »Option Explicit«, boste morali pred zagonom kode prijaviti vse spremenljivke. Če obstaja kakšna spremenljivka, ki ni bila deklarirana, bi VBA prikazal napako.

Uporaba Option Explicit ima ogromno koristi.

Včasih lahko naredite napako pri tipkanju in vnesete napačno ime spremenljivke.

Običajno VBA ne more vedeti, ali je to napaka ali namerna. Ko pa uporabite "Option Explicit", bo VBA ime napačno črkovane spremenljivke videla kot novo spremenljivko, ki ni bila razglašena, in prikazala vam bo napako. To vam bo pomagalo prepoznati ta napačno črkovana imena spremenljivk, ki jih je v dolgi kodi težko zaznati.

Spodaj je primer, kjer uporaba 'Option Explicit' identificira napako (ki je ne bi mogli ujeti, če ne bi uporabil '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

Upoštevajte, da sem enkrat v tej kodi napačno črkoval besedo „CommissionRate“.

Če ne uporabim možnosti Explicit, bi se ta koda zagnala in mi dala napačno skupno provizijo (če je vrednost v celici A1 manjša od 10000).

Če pa na vrhu modula uporabim Option Explicit, mi te kode ne bo dovolil, preden popravim napačno črkovano besedo ali jo razglasim kot drugo spremenljivko. Prikazal bo napako, kot je prikazano spodaj:

Medtem ko lahko vsakič, ko kodirate, vstavite vrstico "Option Explicit", so privzeto prikazani naslednji koraki:

1. V orodni vrstici VB Editor kliknite Orodja.
2. Kliknite Možnosti.
3. V pogovornem oknu Možnosti kliknite zavihek Urejevalnik.
4. Preverite možnost - »Zahtevaj izjavo spremenljivke«.
5. Kliknite V redu.

Ko omogočite to možnost, bo vsakič, ko odprete nov modul, VBA samodejno dodal vrstico »Option Explicit«.

Opomba: Ta možnost bo vplivala le na vse module, ki jih ustvarite, ko je ta možnost omogočena. To ne vpliva na vse obstoječe module.

Področje uporabe spremenljivk

Doslej smo videli, kako razglasiti spremenljivko in ji dodeliti podatkovne vrste.

V tem razdelku bom obravnaval obseg spremenljivk in kako lahko razglasite spremenljivko za uporabo samo v podprogramu, v celotnem modulu ali v vseh modulih.

Obseg spremenljivke določa, kje se lahko spremenljivka uporablja v VBA,

Spremenljivko v Excelu VBA lahko uporabite na tri načine:

1. Znotraj ene podprograma (lokalne spremenljivke)
2. Znotraj modula (spremenljivke na ravni modula)
3. V vseh modulih (javne spremenljivke)

Oglejmo si vsakega od teh podrobno.

Znotraj ene podprograma (lokalne spremenljivke)

Ko razglasite spremenljivko v podprogramu/postopku, je ta spremenljivka na voljo samo za to podprogram.

Ne morete ga uporabljati v drugih podprogramih v modulu.

Takoj ko se podprogram konča, se spremenljivka izbriše in pomnilnik, ki ga uporablja, se sprosti.

V spodnjem primeru so spremenljivke deklarirane znotraj podprograma in bi bile izbrisane, ko se ta podprogram konča.

Znotraj modula (spremenljivke na ravni modula)

Če želite, da je spremenljivka na voljo za vse postopke v modulu, jo morate razglasiti na vrhu modula (in ne v kateri koli podprogramu).

Ko ga deklarirate na vrhu modula, lahko to spremenljivko uporabite v vseh postopkih v tem modulu.

V zgornjem primeru je spremenljivka 'i' deklarirana na vrhu modula in je na voljo za uporabo vsem modulom.

Upoštevajte, da se ob koncu podprograma spremenljivke na ravni modula ne izbrišejo (ohranijo svojo vrednost).

Spodaj je primer, kjer imam dve kodi. Ko zaženem prvi postopek in nato drugi, vrednost 'i' postane 30 (saj nosi vrednost 10 iz prvega postopka)

V vseh modulih (javne spremenljivke)

Če želite, da je spremenljivka na voljo v vseh postopkih v delovnem zvezku, jo morate razglasiti s ključno besedo Public (namesto DIM).

Spodnja vrstica kode na vrhu modula bi omogočila spremenljivko „CommissionRate“ na voljo v vseh modulih v delovnem zvezku.

 Javna komisijaOceni kot dvojno

Izjavo spremenljivke (z uporabo ključne besede Public) lahko vstavite v kateri koli od modulov (na vrhu pred vsakim postopkom).

Statične spremenljivke (ki ohranijo vrednost)

Ko delate z lokalnimi spremenljivkami, bi takoj, ko se postopek konča, spremenljivka izgubila svojo vrednost in bi bila izbrisana iz pomnilnika VBA.

Če želite, da spremenljivka ohrani vrednost, morate uporabiti datoteko Statično ključna beseda.

Naj vam najprej pokažem, kaj se zgodi v običajnem primeru.

V spodnji kodi, ko postopek izvedem večkrat, bo vsakič prikazala vrednost 10.

Pomožni postopek1 () Dim i Kot celo število i = i + 10 MsgBox i Konec pod

Če uporabim ključno besedo Static namesto DIM in postopek izvedem večkrat, bo še naprej prikazoval vrednosti v korakih po 10. To se zgodi, ko spremenljivka „i“ ohrani svojo vrednost in jo uporabi pri izračunu.

Podpostopek1 () Statično i Kot celo število i = i + 10 MsgBox i Konec pod

Razglasitev konstant v Excelu VBA

Medtem ko se lahko spremenljivke med izvajanjem kode spreminjajo, lahko uporabite konstante, če želite imeti fiksne vrednosti.

Konstanta vam omogoča, da imenovanemu nizu dodelite vrednost, ki jo lahko uporabite v kodi.

Prednost uporabe konstante je v tem, da olajša pisanje in razumevanje kode, poleg tega pa omogoča nadzor vseh fiksnih vrednosti z enega mesta.

Na primer, če izračunate provizije in je provizija 10%, lahko ustvarite konstanto (CommissionRate) in ji dodelite vrednost 0,1.

Če se v prihodnje spremeni stopnja provizije, morate spremembo narediti le na enem mestu, namesto da jo povsod ročno spreminjate v kodi.

Spodaj je primer kode, kjer sem konstanti dodelil vrednost:

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

Naslednja vrstica se uporablja za razglasitev konstante:

Const CommissionRate As Double = 0,1

Pri razglasitvi konstant morate začeti s ključno besedo 'Konst‘, Ki mu sledi ime konstante.

Upoštevajte, da sem v tem primeru tip podatkovne konstante navedel kot Double. Še enkrat je dobra praksa določiti podatkovni tip, da bo vaša koda delovala hitreje in učinkoviteje.

Če podatkovnega tipa ne deklarirate, bi se obravnaval kot različni podatkovni tip.

Tako kot spremenljivke imajo lahko tudi konstante obseg glede na to, kje in kako so te deklarirane:

1. Znotraj ene podprograma (lokalne konstante): Te so na voljo v podprogramu/postopku, v katerem so navedene. Ko se postopek konča, se te konstante izbrišejo iz pomnilnika sistema.
2. Znotraj modula (konstante na ravni modula): Ti so deklarirani na vrhu modula (pred katerim koli postopkom). Ti so na voljo za vse postopke v modulu.
3. V vseh modulih (javne konstante): Ti so deklarirani z uporabo ključne besede „javno“ na vrhu katerega koli modula (pred katerim koli postopkom). Ti so na voljo vsem postopkom v vseh modulih.

Morda vam bodo všeč tudi naslednji VBA vodiči:

• Kako posneti makro v Excelu
• Delo s celicami in obsegi v Excelu VBA
• Delo z delovnimi listi z uporabo Excel VBA
• Delo z delovnimi zvezki v Excelu VBA
• Dogodki VBA
• Zanke Excel VBA
• Kako zagnati makro v Excelu
• Če potem Druga izjava v Excelu VBA.