Excel VBA-gegevenstypen begrijpen (variabelen en constanten)

In Excel VBA zou u vaak variabelen en constanten moeten gebruiken.

Wanneer u met VBA werkt, is een variabele een locatie in het geheugen van uw computer waar u gegevens kunt opslaan. Het type gegevens dat u in een variabele kunt opslaan, is afhankelijk van het gegevenstype van de variabele.

Als u bijvoorbeeld gehele getallen in een variabele wilt opslaan, is uw gegevenstype 'Integer' en als u tekst wilt opslaan, is uw gegevenstype 'String'.

Meer over gegevenstypen verderop in deze zelfstudie.

Terwijl de waarde van een variabele verandert wanneer de code wordt uitgevoerd, heeft een constante een waarde die nooit verandert. Als goede coderingspraktijk moet u het gegevenstype van zowel variabel als constant definiëren.

Waarom variabelen gebruiken in VBA?

Wanneer u codeert in VBA, hebt u variabelen nodig die u kunt gebruiken om een ​​waarde vast te houden.

Het voordeel van het gebruik van een variabele is dat u de waarde van de variabele in de code kunt wijzigen en deze in de code kunt blijven gebruiken.

Hieronder staat bijvoorbeeld een code die de eerste 10 positieve getallen optelt en vervolgens het resultaat in een berichtvenster weergeeft:

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

Er zijn drie variabelen in de bovenstaande code - Var, I, en k.

De bovenstaande code gebruikt een For Next-lus waarin al deze drie variabelen worden gewijzigd als de lussen zijn voltooid.

Het nut van een variabele ligt in het feit dat deze kan worden gewijzigd terwijl uw code wordt uitgevoerd.

Hieronder staan ​​enkele regels waarmee u rekening moet houden bij het benoemen van variabelen in VBA:

1. U kunt alfabetten, cijfers en leestekens gebruiken, maar het eerste cijfer moet een alfabet zijn.
2. U kunt geen spatie of punt gebruiken in de naam van de variabele. U kunt echter een onderstrepingsteken gebruiken om de variabelenamen leesbaarder te maken (zoals Interest_Rate)
3. U kunt geen speciale tekens (#, $, %, &, of !) gebruiken in variabelenamen
4. VBA maakt geen onderscheid tussen het geval in de naam van de variabele. Dus 'InterestRate' en 'interestrate' zijn hetzelfde voor VBA. U kunt gemengde hoofdletters gebruiken om de variabelen leesbaarder te maken.
5. VBA heeft enkele gereserveerde namen die u kunt gebruiken voor een variabelenaam. U kunt bijvoorbeeld het woord 'Volgende' niet als variabelenaam gebruiken, omdat het een gereserveerde naam is voor For Next-lus.
6. Uw variabelenaam kan maximaal 254 tekens lang zijn.

Gegevenstype van variabelen

Om optimaal gebruik te maken van variabelen, is het een goede gewoonte om het gegevenstype van de variabele op te geven.

Het gegevenstype dat u aan een variabele toewijst, is afhankelijk van het type gegevens dat u wilt dat die variabele bevat.

Hieronder vindt u een tabel met alle beschikbare gegevenstypen die u in Excel VBA kunt gebruiken:

Data type	Gebruikte bytes	Bereik van waarden
Byte	1 byte	0 tot 255
Booleaans	2 bytes	Waar of niet waar
Geheel getal	2 bytes	-32.768 tot 32.767
Lang (lang geheel getal)	4 bytes	-2.147.483.648 tot 2.147.483.647
Enkel	4 bytes	-3.402823E38 tot -1.401298E-45 voor negatieve waarden; 1.401298E-45 tot 3.402823E38 voor positieve waarden
Dubbele	8 bytes	-1.79769313486231E308 tot-4.94065645841247E-324 voor negatieve waarden; 4.94065645841247E-324 tot 1.79769313486232E308 voor positieve waarden
Munteenheid	8 bytes	-922.337.203.685.477.5808 tot 922.337.203.685.477.5807
Decimale	14 bytes	+/-79.228.162.514.264.337.593.543.950.335 zonder komma;+/-7.922816251426437593543950335 met 28 plaatsen rechts van de komma
Datum	8 bytes	1 januari 100 tot 31 december 9999
Object	4 bytes	Elke objectreferentie
String (variabele lengte)	10 bytes + stringlengte	0 tot ongeveer 2 miljard
String (vaste lengte)	Lengte van string	1 tot ongeveer 65.400
Variant (met cijfers)	16 bytes	Elke numerieke waarde tot het bereik van een Double
Variant (met karakters)	22 bytes + stringlengte	Hetzelfde bereik als voor String met variabele lengte
Gebruiker gedefinieerde	Varieert	Het bereik van elk element is hetzelfde als het bereik van het gegevenstype.

Wanneer u een gegevenstype voor een variabele in uw code opgeeft, vertelt het VBA hoe deze variabele moet worden opgeslagen en hoeveel ruimte ervoor moet worden toegewezen.

Als u bijvoorbeeld een variabele moet gebruiken die bedoeld is om het maandnummer te bevatten, kunt u het gegevenstype BYTE gebruiken (dat waarden van 0 tot 255 kan bevatten). Aangezien het maandnummer niet boven de 12 komt, werkt dit prima en reserveer je ook minder geheugen voor deze variabele.

Integendeel, als u een variabele nodig hebt om de rijnummers in Excel op te slaan, moet u een gegevenstype gebruiken dat een nummer tot 1048756 kan bevatten. U kunt dus het beste het gegevenstype Lang gebruiken.

Variabele gegevenstypen declareren

Als een goede coderingspraktijk, moet u het gegevenstype van variabelen (of constanten) declareren bij het schrijven van de code. Als u dit doet, zorgt u ervoor dat VBA alleen het opgegeven geheugen aan de variabele toewijst en dit kan uw code sneller laten werken.

Hieronder ziet u een voorbeeld waarin ik verschillende gegevenstypen heb gedeclareerd voor verschillende variabelen:

Sub DeclaringVariables() Dim X As Integer Dim E-mail As String Dim Voornaam As String Dim RowCount As Long Dim VandaagDatum Als Datum Einde Sub

Om een ​​variabel gegevenstype te declareren, moet u de DIM-instructie gebruiken (wat een afkorting is voor Dimension).

In 'Dim X als geheel getal', Ik heb de variabele X gedeclareerd als Integer-gegevenstype.

Als ik het nu in mijn code gebruik, zou VBA weten dat X alleen het gegevenstype integer kan bevatten.

Als ik er een waarde aan probeer toe te kennen die geen geheel getal is, krijg ik een foutmelding (zoals hieronder weergegeven):

Opmerking: u kunt er ook voor kiezen om het gegevenstype niet te declareren. In dat geval houdt VBA automatisch rekening met de variabele van het variantgegevenstype. Een variant gegevenstype is geschikt voor elk gegevenstype. Hoewel dit misschien handig lijkt, is het geen best practice om variantgegevenstype te gebruiken. Het heeft de neiging om meer geheugen in beslag te nemen en kan ervoor zorgen dat uw VBA-code langzamer werkt.

Variabele declaratie verplicht maken (optie expliciet)

Hoewel u kunt coderen zonder ooit variabelen te declareren, is het een goede gewoonte om dit te doen.

Afgezien van het besparen van geheugen en het efficiënter maken van uw code, heeft het declareren van variabelen nog een ander groot voordeel: het helpt fouten op te sporen die worden veroorzaakt door verkeerd gespelde namen van variabelen.

Om er zeker van te zijn dat u variabelen moet declareren, voegt u de volgende regel toe aan de bovenkant van uw module.

Optie expliciet

Wanneer u 'Option Explicit' toevoegt, moet u alle variabelen declareren voordat u de code uitvoert. Als er een variabele is die niet is gedeclareerd, geeft VBA een fout weer.

Het gebruik van Option Explicit heeft een enorm voordeel.

Soms maakt u een typefout en voert u een variabelenaam in die onjuist is.

Normaal gesproken kan VBA op geen enkele manier weten of het een fout is of opzettelijk. Wanneer u echter 'Option Explicit' gebruikt, zou VBA de verkeerd gespelde variabelenaam zien als een nieuwe variabele die niet is gedeclareerd en u een foutmelding geven. Dit zal u helpen deze verkeerd gespelde variabelenamen te identificeren, die vrij moeilijk te herkennen zijn in een lange code.

Hieronder ziet u een voorbeeld waarbij het gebruik van 'Option Explicit' de fout identificeert (die niet gevangen had kunnen worden als ik 'Option Explicit' niet had gebruikt)

Sub CommissionCalc() Dim CommissionRate As Double If Range ("A1"). Waarde > 10000 Dan CommissionRate = 0,1 Else CommissionRtae = 0,05 End If MsgBox "Totale commissie: " & Range ("A1"). Waarde * CommissionRate End Sub

Merk op dat ik het woord 'CommissionRate' een keer verkeerd heb gespeld in deze code.

Als ik Option Explicit niet gebruik, wordt deze code uitgevoerd en krijg ik de verkeerde totale commissiewaarde (in het geval dat de waarde in cel A1 kleiner is dan 10000).

Maar als ik Option Explicit bovenaan de module gebruik, kan ik deze code niet uitvoeren voordat ik het verkeerd gespelde woord corrigeer of het als een andere variabele declareer. Het zal een fout tonen zoals hieronder getoond:

Hoewel u elke keer dat u codeert de regel 'Option Explicit' kunt invoegen, zijn hier de stappen om deze standaard te laten verschijnen:

1. Klik in de werkbalk van VB Editor op Extra.
2. Klik op Opties.
3. Klik in het dialoogvenster Opties op het tabblad Editor.
4. Vink de optie - "Variabele declaratie vereist" aan.
5. Klik OK.

Zodra u deze optie hebt ingeschakeld, voegt VBA automatisch de regel 'Option Explicit' toe wanneer u een nieuwe module opent.

Opmerking: deze optie is alleen van invloed op elke module die u maakt nadat deze optie is ingeschakeld. Alle bestaande modules worden niet beïnvloed.

Bereik van variabelen

Tot nu toe hebben we gezien hoe een variabele gedeclareerd kan worden en er datatypes aan kunnen worden toegewezen.

In deze sectie bespreek ik de reikwijdte van variabelen en hoe je een variabele kunt declareren om alleen in een subroutine, in een hele module of in alle modules te gebruiken.

Het bereik van een variabele bepaalt waar de variabele kan worden gebruikt in VBA,

Er zijn drie manieren om een ​​variabele te bereiken in Excel VBA:

1. Binnen een enkele subroutine (lokale variabelen)
2. Binnen een module (variabelen op moduleniveau)
3. In alle modules (openbare variabelen)

Laten we elk van deze in detail bekijken.

Binnen een enkele subroutine (lokale variabelen)

Als je een variabele declareert binnen een subroutine/procedure, dan is die variabele alleen beschikbaar voor die subroutine.

U kunt het niet gebruiken in andere subroutines in de module.

Zodra de subroutine eindigt, wordt de variabele verwijderd en wordt het gebruikte geheugen vrijgemaakt.

In het onderstaande voorbeeld worden de variabelen gedeclareerd binnen de subroutine en worden ze verwijderd wanneer deze subroutine eindigt.

Binnen een module (variabelen op moduleniveau)

Als u wilt dat een variabele beschikbaar is voor alle procedures in een module, moet u deze bovenaan de module declareren (en niet in een subroutine).

Zodra u het bovenaan de module declareert, kunt u die variabele gebruiken in alle procedures in die module.

In het bovenstaande voorbeeld wordt de variabele 'i' bovenaan de module gedeclareerd en kan deze door alle modules worden gebruikt.

Merk op dat wanneer de subroutine eindigt, de variabelen op moduleniveau niet worden verwijderd (het behoudt zijn waarde).

Hieronder is een voorbeeld, waar ik twee codes heb. Wanneer ik de eerste procedure uitvoer en vervolgens de tweede uitvoer, wordt de waarde van 'i' 30 (omdat deze de waarde 10 heeft van de eerste procedure)

In alle modules (openbare variabelen)

Als u wilt dat een variabele beschikbaar is in alle procedures in de werkmap, moet u deze declareren met het trefwoord Public (in plaats van DIM).

De onderstaande regel code bovenaan de module zou de variabele 'CommissionRate' beschikbaar maken in alle modules in de werkmap.

 Openbare commissieBeoordeel als dubbel

U kunt de variabele declaratie (met behulp van het trefwoord Public) in een van de modules invoegen (bovenaan vóór elke procedure).

Statische variabelen (die de waarde behouden)

Wanneer u met lokale variabelen werkt, verliest de variabele zodra de procedure eindigt zijn waarde en wordt uit het geheugen van VBA verwijderd.

Als u wilt dat de variabele de waarde behoudt, moet u de gebruiken Statisch trefwoord.

Laat me je eerst laten zien wat er gebeurt in een normaal geval.

In de onderstaande code, wanneer ik de procedure meerdere keren uitvoer, wordt telkens de waarde 10 weergegeven.

Subprocedure1() Dim i As Integer i = i + 10 MsgBox i End Sub

Als ik nu het Static-sleutelwoord gebruik in plaats van DIM en de procedure meerdere keren uitvoer, blijft het waarden weergeven in stappen van 10. Dit gebeurt omdat de variabele 'i' zijn waarde behoudt en deze in de berekening gebruikt.

Subprocedure1() Statisch i Als geheel getal i = i + 10 MsgBox i End Sub

Constanten declareren in Excel VBA

Hoewel variabelen tijdens het uitvoeren van de code kunnen veranderen, kunt u constanten gebruiken als u vaste waarden wilt hebben.

Met een constante kunt u een waarde toewijzen aan een benoemde tekenreeks die u in uw code kunt gebruiken.

Het voordeel van het gebruik van een constante is dat het gemakkelijk is om code te schrijven en te begrijpen, en dat je ook alle vaste waarden vanaf één plek kunt beheren.

Als u bijvoorbeeld commissies berekent en het commissiepercentage 10% is, kunt u een constante (CommissionRate) maken en hieraan de waarde 0.1 toekennen.

Als het commissiepercentage in de toekomst verandert, hoeft u de wijziging alleen maar op één plaats door te voeren in plaats van deze overal handmatig in de code te wijzigen.

Hieronder staat een codevoorbeeld waarbij ik een waarde aan de constante heb toegewezen:

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

De volgende regel wordt gebruikt om de constante te declareren:

Const CommissionRate als dubbel = 0.1

Bij het declareren van constanten moet u beginnen met het trefwoord 'Const', gevolgd door de naam van de constante.

Merk op dat ik in dit voorbeeld het gegevenstype van de constante als Double heb opgegeven. Nogmaals, het is een goede gewoonte om het gegevenstype op te geven om uw code sneller en efficiënter te laten werken.

Als u het gegevenstype niet declareert, wordt het beschouwd als een variantgegevenstype.

Net als variabelen kunnen constanten ook een bereik hebben op basis van waar en hoe deze worden gedeclareerd:

1. Binnen een enkele subroutine (lokale constanten): Deze zijn beschikbaar in de subroutine/procedure waarin deze zijn gedeclareerd. Als de procedure eindigt, worden deze constanten uit het systeemgeheugen verwijderd.
2. Binnen een module (constanten op moduleniveau): Deze worden bovenaan de module gedeclareerd (vóór een procedure). Deze zijn beschikbaar voor alle procedures in de module.
3. In alle modules (Publieke constanten): Deze worden gedeclareerd met het trefwoord 'Public', bovenaan elke module (vóór elke procedure). Deze zijn beschikbaar voor alle procedures in alle modules.

Misschien vind je de volgende VBA-zelfstudies misschien ook leuk:

• Een macro opnemen in Excel
• Werken met cellen en bereiken in Excel VBA
• Werken met werkbladen met Excel VBA
• Werken met werkmappen in Excel VBA
• VBA-evenementen
• Excel VBA-lussen
• Een macro uitvoeren in Excel
• Als Dan Anders Verklaring in Excel VBA.