Az Excel VBA adattípusainak (változók és állandók) megértése

Az Excel VBA -ban gyakran változókat és állandókat kell használnia.

Amikor a VBA -val dolgozik, a változó az a hely a számítógép memóriájában, ahol adatokat tárolhat. A változóban tárolható adatok típusa a változó adattípusától függ.

Például, ha egész számokat szeretne tárolni egy változóban, akkor az adattípusa „Egész”, és ha szöveget szeretne tárolni, akkor az adattípus „Karakterlánc”.

Az adattípusokról bővebben ebben az oktatóanyagban olvashat.

Míg a változó értéke megváltozik, amikor a kód folyamatban van, egy állandó értéket tart, amely soha nem változik. Jó kódolási gyakorlatként meg kell határoznia a változó és a konstans adattípust.

Miért használjunk változókat a VBA -ban?

Amikor kódol a VBA -ban, szüksége lesz változókra, amelyekkel értéket tarthat.

A változó használatának előnye, hogy megváltoztathatja a változó értékét a kódon belül, és továbbra is használhatja azt a kódban.

Például az alábbiakban egy kód található, amely hozzáadja az első 10 pozitív számot, majd megjeleníti az eredményt egy üzenetmezőben:

Sub AddFirstTenNumbers () Dim Változó Integerként Dim i Mint Integer Dim k mint Integer For i = 1 to 10 k = k + i Next i MsgBox k End Sub

A fenti kódban három változó található - Var, én, és k.

A fenti kód egy For Next cikket használ, ahol mindhárom változó megváltozik a ciklusok befejezésekor.

A változó hasznossága abban rejlik, hogy megváltoztatható, amíg a kód folyamatban van.

Az alábbiakban néhány szabályt kell szem előtt tartani a VBA változóinak elnevezésekor:

1. Használhat ábécét, számokat és írásjeleket, de az első számnak ábécének kell lennie.
2. A változó nevében nem használhat szóközt vagy pontot. Használhat azonban aláhúzás karaktert a változók nevének olvashatóbbá tételéhez (például Kamatláb)
3. A változónevekben nem használhat speciális karaktereket (#, $, %, &, vagy!)
4. A VBA nem tesz különbséget az eset között a változó nevében. Tehát az „InterestRate” és a „interestrate” ugyanaz a VBA esetében. A változó olvashatóbbá tétele érdekében használhat vegyes kis- és nagybetűket.
5. A VBA rendelkezik néhány fenntartott névvel, amelyeket egy változónévhez használhat. Például nem használhatja a „Next” szót változónévként, mivel a For Next ciklus fenntartott neve.
6. A változó neve legfeljebb 254 karakter hosszú lehet.

A változók adattípusa

A változók legjobb kihasználása érdekében célszerű megadni a változó adattípusát.

A változóhoz rendelt adattípus attól függ, hogy milyen típusú adatokat szeretne tárolni a változó.

Az alábbi táblázat az Excel VBA -ban használható összes elérhető adattípust mutatja:

Adattípus	Bájtok használt	Értékek tartománya
Byte	1 bájt	0 és 255 között
Boolean	2 bájt	Igaz vagy hamis
Egész szám	2 bájt	-32 768 és 32 767 között
Hosszú (hosszú egész szám)	4 bájt	-2 147 483 648 és 2 147 483 647 között
Egyetlen	4 bájt	-3.402823E38 --1,401298E -45 negatív értékek esetén; 1.401298E-45-3.402823E38 a pozitív értékekhez
Kettős	8 bájt	-1,79769313486231E308--4,94065645841247E-324 negatív értékek esetén; 4.94065645841247E-324-1,79769313486232E308 a pozitív értékekhez
Valuta	8 bájt	-922,337,203,685,477.5808 -922,337,203,685,477.5807
Decimális	14 bájt	+/- 79,228,162,514,264,337,593,543,950,335 tizedesvessző nélkül; +/- 7,9228162514264337593543950335, 28 tizedessel jobbra
Dátum	8 bájt	100. január 1. és 9999. december 31. között
Tárgy	4 bájt	Bármilyen objektum hivatkozás
Karakterlánc (változó hosszúságú)	10 bájt + karakterlánc hossza	0 és körülbelül 2 milliárd között
Karakterlánc (rögzített hosszúságú)	A húr hossza	1 és körülbelül 65 400 között
Változat (számokkal)	16 bájt	Bármilyen számérték a Double tartományig
Változat (karakterekkel)	22 bájt + karakterlánc hossza	Ugyanaz a tartomány, mint a változó hosszúságú karakterlánc esetében
Felhasználó által meghatározott	Váltakozik	Az egyes elemek tartománya megegyezik az adattípusok tartományával.

Amikor megad egy adattípust egy változóhoz a kódban, az megmondja a VBA -nak, hogyan kell tárolni ezt a változót, és mennyi helyet kell lefoglalni hozzá.

Például, ha olyan változót kell használnia, amely a hónap számát hivatott tárolni, akkor használhatja a BYTE adattípust (amely 0 és 255 közötti értékeket tud befogadni). Mivel a hónap száma nem haladja meg a 12 -et, ez jól fog működni, és kevesebb memóriát is lefoglal erre a változóra.

Éppen ellenkezőleg, ha egy változóra van szüksége a sorszámok Excelben történő tárolásához, akkor olyan adattípust kell használnia, amely 1048756 -ig terjedő számot képes befogadni. Ezért a legjobb a hosszú adattípus használata.

Változó adattípusok deklarálása

A helyes kódolási gyakorlat szerint a kód írásakor deklarálnia kell a változók (vagy állandók) adattípusát. Ezzel biztosíthatja, hogy a VBA csak a megadott memóriát foglalja le a változóhoz, és ez gyorsíthatja a kód futását.

Az alábbiakban egy példa látható, ahol különböző adattípusokat deklaráltam különböző változóknak:

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

A változó adattípus deklarálásához a DIM utasítást kell használnia (ami a Dimenzió rövidítése).

Ban ben 'Dim X egész számként', Az X változót egész adattípusnak nyilvánítottam.

Most, amikor a kódomban használom, a VBA tudná, hogy az X csak egész adattípust képes tárolni.

Ha olyan értéket próbálok hozzárendelni, amely nem egész szám, akkor hibaüzenetet kapok (az alábbiak szerint):

Megjegyzés: Azt is választhatja, hogy nem deklarálja az adattípust, ebben az esetben a VBA automatikusan figyelembe veszi a változatos adattípus változóját. Egy változat adattípus bármilyen adattípust befogadhat. Bár ez kényelmesnek tűnhet, nem a legjobb gyakorlat a változatos adattípus használata. Általában több memóriát foglal el, és lassabbá teheti a VBA -kódot.

Kötelezővé tenni a változó nyilatkozatot (explicit opció)

Bár kódolhat anélkül, hogy valaha deklarálna változókat, jó gyakorlat ezt megtenni.

A memória megtakarításán és a kód hatékonyabbá tételén kívül a változók deklarálása egy másik fontos előnnyel is jár - segít a hibásan elírt változónevek okozta hibák csapdázásában.

Annak érdekében, hogy kénytelen legyen változókat deklarálni, adja hozzá a következő sort a modul tetejéhez.

Explicit lehetőség

Az „Option Explicit” hozzáadásakor a kód futtatása előtt be kell jelentenie az összes változót. Ha van olyan változó, amelyet nem deklaráltak, a VBA hibát mutat.

Óriási előnye van az Option Explicit használatának.

Előfordulhat, hogy gépelési hibát követ el, és megad egy hibás változónevet.

Általában a VBA nem tudja, hogy hiba, vagy szándékos. Ha azonban az „Option Explicit” opciót használja, a VBA a hibásan írt változó nevét új változónak fogja tekinteni, amelyet még nem jelentettek be, és hibaüzenetet jelenít meg. Ez segít azonosítani ezeket a hibásan írott változóneveket, amelyeket nehéz lehet észrevenni egy hosszú kódban.

Az alábbiakban egy példa látható, ahol az „Option Explicit” használata azonosítja a hibát (amely nem eshetett volna csapdába, ha nem használtam volna az „Option Explicit” lehetőséget)

Sub CommissionCalc () Dim CommissionRate as Double If Range ("A1"). Érték> 10000 Akkor CommissionRate = 0,1 Else CommissionRtae = 0,05 End If MsgBox "Total Commission:" & Range ("A1"). Érték * CommissionRate End Sub

Ne feledje, hogy ebben a kódban egyszer rosszul írtam be a „CommissionRate” szót.

Ha nem használom az Explicit opciót, akkor ez a kód futna, és rossz teljes jutalékértéket adna (ha az A1 cella értéke kevesebb, mint 10000).

De ha az Option Explicit -et használom a modul tetején, akkor nem engedi, hogy futtassam ezt a kódot, mielőtt vagy kijavítanám a hibásan írt szót, vagy más változónak nyilvánítanám. Hiba jelenik meg az alábbiak szerint:

Bár minden kódoláskor beillesztheti az „Option Explicit” sort, az alábbiakban bemutatjuk, hogy alapértelmezés szerint hogyan jelenjen meg:

1. A VB Editor eszköztárán kattintson az Eszközök elemre.
2. Kattintson a Beállítások lehetőségre.
3. A Beállítások párbeszédpanelen kattintson a Szerkesztő fülre.
4. Jelölje be a „Változó nyilatkozat megkövetelése” lehetőséget.
5. Kattintson az OK gombra.

Ha engedélyezte ezt az opciót, amikor új modult nyit, a VBA automatikusan hozzáadja az „Option Explicit” sort.

Megjegyzés: Ez az opció csak a modul engedélyezése után létrehozott modulokra lesz hatással. Az összes meglévő modult nem érinti.

A változók köre

Eddig láttuk, hogyan kell deklarálni egy változót és hozzárendelni hozzá adattípusokat.

Ebben a részben a változók hatókörét fogom bemutatni, és azt, hogy miként deklarálhatja a változót, hogy csak egy alprogramban, egy teljes modulban vagy az összes modulban használható legyen.

A változó hatóköre határozza meg, hogy hol használható a változó a VBA -ban,

Az Excel VBA -ban három módja van a változó hatókörének:

1. Egyetlen alprogramon belül (helyi változók)
2. Egy modulon belül (modulszintű változók)
3. Minden modulban (nyilvános változók)

Nézzük meg mindegyiket részletesen.

Egyetlen szubrutinon belül (helyi változók)

Ha egy változót deklarál egy szubrutinon/eljáráson belül, akkor ez a változó csak az adott alprogramhoz érhető el.

A modul más alprogramjaiban nem használható.

Amint az alprogram befejeződik, a változó törlődik, és az általa használt memória felszabadul.

Az alábbi példában a változókat az alprogramon belül deklaráljuk, és törlésre kerülnek, amikor ez az alprogram befejeződik.

Egy modulon belül (modulszintű változók)

Ha azt szeretné, hogy egy változó elérhető legyen a modul összes eljárásához, akkor azt a modul tetején kell deklarálnia (és nem bármely alprogramban).

Miután deklarálta a modul tetején, használhatja ezt a változót az adott modul összes eljárásában.

A fenti példában az „i” változó a modul tetején van deklarálva, és az összes modul használható.

Ne feledje, hogy amikor az alprogram befejeződik, a modul szintű változók nem törlődnek (megőrzi értékét).

Az alábbiakban egy példa látható, ahol két kódom van. Amikor futtatom az első eljárást, majd a másodikat, az „i” értéke 30 lesz (mivel az első eljárás 10 -es értékét hordozza)

Minden modulban (nyilvános változók)

Ha azt szeretné, hogy a változó a munkafüzet összes eljárásában elérhető legyen, akkor azt nyilvános kulcsszóval kell deklarálnia (DIM helyett).

Az alábbi kódsor a modul tetején elérhetővé tenné a „CommissionRate” változót a munkafüzet összes moduljában.

 Public CommissionRate As Double

Beillesztheti a változó deklarációt (a Public kulcsszó használatával) bármelyik modulba (felül minden eljárás előtt).

Statikus változók (megőrzi az értéket)

Ha helyi változókkal dolgozik, amint az eljárás véget ér, a változó elveszíti értékét, és törlődik a VBA memóriájából.

Ha azt szeretné, hogy a változó megtartsa az értéket, akkor a Statikus kulcsszó.

Először hadd mutassam meg, mi történik normális esetben.

Az alábbi kódban, ha többször futtatom az eljárást, minden alkalommal megjelenik a 10 érték.

1. eljárás () Dim i As Integer i = i + 10 MsgBox i End Sub

Ha most a Statikus kulcsszót használom a DIM helyett, és többször futtatom az eljárást, akkor továbbra is 10 -es lépésekben jeleníti meg az értékeket. Ez akkor történik, amikor az „i” változó megtartja értékét, és felhasználja a számításban.

1. eljárás () Statikus i Mint egész szám i = i + 10 MsgBox i End Sub

Konstansok deklarálása Excel VBA -ban

Bár a változók változhatnak a kód végrehajtása során, ha fix értékeket szeretne, használhat konstansokat.

Az állandó lehetővé teszi, hogy értéket rendeljen egy elnevezett karakterlánchoz, amelyet felhasználhat a kódjában.

Az állandó használatának előnye, hogy megkönnyíti a kód írását és megértését, valamint lehetővé teszi az összes rögzített érték egy helyről történő vezérlését.

Például, ha jutalékot számít, és a jutalék mértéke 10%, létrehozhat egy konstansot (CommissionRate), és hozzárendelheti a 0,1 értéket.

A jövőben, ha a jutalék mértéke megváltozik, csak egy helyen kell elvégeznie a módosítást, ahelyett, hogy manuálisan módosítaná a kódon mindenhol.

Az alábbiakban egy kódpéldát mutatok be, ahol értéket rendeltem az állandóhoz:

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

A konstans deklarálásához a következő sort kell használni:

Const CommissionRate As Double = 0.1

Az állandók deklarálásakor a „kulcsszóval” kell kezdeniConst’, Majd az állandó neve.

Ne feledje, hogy ebben a példában kettősnek adtam meg az állandó adattípusát. Ismét jó gyakorlat az adattípus megadása, hogy a kód gyorsabban és hatékonyabban működjön.

Ha nem deklarálja az adattípust, akkor azt egy alternatív adattípusnak kell tekinteni.

A változókhoz hasonlóan az állandóknak is lehet hatókörük attól függően, hogy hol és hogyan deklarálják ezeket:

1. Egyetlen alprogramon belül (helyi állandók): Ezek abban az alprogramban/eljárásban érhetők el, amelyben ezeket deklarálják. Az eljárás végén ezek az állandók törlődnek a rendszer memóriájából.
2. Egy modulon belül (modulszintű állandók): Ezek a modul tetején találhatók (minden eljárás előtt). Ezek a modul összes eljárásához rendelkezésre állnak.
3. Minden modulban (nyilvános állandók): Ezeket a „Nyilvános” kulcsszó használatával deklarálják, bármely modul tetején (minden eljárás előtt). Ezek az összes modul összes eljárásához rendelkezésre állnak.

A következő VBA oktatóanyagok is tetszhetnek:

• Makró rögzítése Excelben
• Munka cellákkal és tartományokkal az Excel VBA programban
• Munkalapok használata Excel VBA segítségével
• Munkafüzetek használata Excel VBA -ban
• VBA események
• Excel VBA hurkok
• Makró futtatása Excelben
• Ha akkor más nyilatkozat az Excel VBA -ban.