Forstå Excel VBA -datatyper (variabler og konstanter)

I Excel VBA vil du ofte bli pålagt å bruke variabler og konstanter.

Når du arbeider med VBA, er en variabel et sted i datamaskinens minne hvor du kan lagre data. Datatypen du kan lagre i en variabel vil avhenge av datatypen til variabelen.

For eksempel, hvis du vil lagre heltall i en variabel, vil datatypen være "Heltall", og hvis du vil lagre tekst, vil datatypen være "streng".

Mer om datatyper senere i denne opplæringen.

Mens en variabels verdi endres når koden pågår, holder en konstant en verdi som aldri endres. Som en god kodingspraksis bør du definere datatypen for begge - variabel og konstant.

Hvorfor bruke variabler i VBA?

Når du koder i VBA, trenger du variabler som du kan bruke til å holde en verdi.

Fordelen med å bruke en variabel er at du kan endre verdien av variabelen i koden og fortsette å bruke den i koden.

For eksempel er en kode som legger til de første 10 positive tallene og deretter viser resultatet i en meldingsboks:

Sub AddFirstTenNumbers () Dim Var As Integer Dim i As Integer Dim k as Integer For i = 1 Til 10 k = k + i Neste i MsgBox k End Sub

Det er tre variabler i koden ovenfor - Var, Jeg, og k.

Koden ovenfor bruker en For Next -løkke der alle disse tre variablene endres etter hvert som løkkene er fullført.

Nyttigheten av en variabel ligger i det faktum at den kan endres mens koden din pågår.

Nedenfor er noen regler du må huske på når du navngir variablene i VBA:

1. Du kan bruke alfabeter, tall og tegnsetting, men det første tallet må være et alfabet.
2. Du kan ikke bruke mellomrom eller punktum i variabelnavnet. Du kan imidlertid bruke et understreketegn for å gjøre variabelnavnene mer lesbare (for eksempel Interest_Rate)
3. Du kan ikke bruke spesialtegn (#, $, %, &, eller!) I variabelnavn
4. VBA skiller ikke mellom saken i variabelnavnet. Så 'InterestRate' og 'interestrate' er det samme for VBA. Du kan bruke mixed case for å gjøre variablene mer lesbare.
5. VBA har noen reserverte navn som du kan bruke for et variabelnavn. For eksempel kan du ikke bruke ordet 'Neste' som et variabelnavn, ettersom det er et reservert navn for For Next -løkken.
6. Ditt variabelnavn kan inneholde opptil 254 tegn.

Datatype av variabler

For å utnytte variabler best, er det en god praksis å spesifisere datatypen til variabelen.

Datatypen du tilordner en variabel, vil være avhengig av typen data du vil at variabelen skal inneholde.

Nedenfor er en tabell som viser alle tilgjengelige datatyper du kan bruke i Excel VBA:

Data-type	Bytes brukt	Verdiområde
Byte	1 byte	0 til 255
Boolsk	2 byte	Sant eller usant
Heltall	2 byte	-32.768 til 32.767
Langt (langt heltall)	4 byte	-2.147.483.648 til 2.147.483.647
Enkelt	4 byte	-3.402823E38 til -1.401298E -45 for negative verdier; 1.401298E-45 til 3.402823E38 for positive verdier
Dobbelt	8 byte	-1.79769313486231E308 til-4.94065645841247E-324 for negative verdier; 4.94065645841247E-324 til 1.79769313486232E308 for positive verdier
Valuta	8 byte	-922 337 203 685 477,5808 til 922 337 203 685 477,5807
Desimal	14 byte	+/- 79.228.162.514.264.337.593.543.950.335 uten desimaltegn; +/- 7.9228162514264337593543950335 med 28 steder til høyre for desimalen
Dato	8 byte	1. januar 100 til 31. desember 9999
Gjenstand	4 byte	Enhver objektreferanse
Streng (variabel lengde)	10 byte + strenglengde	0 til omtrent 2 milliarder
Streng (fast lengde)	Lengde på snor	1 til omtrent 65 400
Variant (med tall)	16 byte	Enhver numerisk verdi opp til området for en dobbel
Variant (med tegn)	22 byte + strenglengde	Samme område som for variabel lengde
Brukerdefinert	Varierer	Området for hvert element er det samme som området for datatypen.

Når du angir en datatype for en variabel i koden, forteller den VBA hvordan denne variabelen skal lagres og hvor mye plass som skal tildeles for den.

For eksempel, hvis du trenger å bruke en variabel som er ment å inneholde månedstallet, kan du bruke BYTE -datatypen (som kan ta imot verdier fra 0 til 255). Siden månedstallet ikke kommer til å være over 12, vil dette fungere fint og også reservere mindre minne for denne variabelen.

Tvert imot, hvis du trenger en variabel for å lagre radnummerene i Excel, må du bruke en datatype som kan inneholde et tall opptil 1048756. Så det er best å bruke den lange datatypen.

Deklarere variable datatyper

Som en god kodingspraksis, bør du deklarere datatypen til variabler (eller konstanter) når du skriver koden. Dette gjør at VBA bare tildeler det spesifiserte minnet til variabelen, og dette kan få koden til å kjøre raskere.

Nedenfor er et eksempel der jeg har erklært forskjellige datatyper til forskjellige variabler:

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

For å deklarere en variabel datatype må du bruke DIM -setningen (som er forkortelse for Dimension).

I ‘Dim X som heltall‘, Jeg har deklarert variabelen X som heltall datatype.

Når jeg bruker den i koden min, ville VBA vite at X bare kan inneholde heltall datatype.

Hvis jeg prøver å tilordne en verdi til den som ikke er et heltall, får jeg en feilmelding (som vist nedenfor):

Merk: Du kan også velge å ikke deklarere datatypen. I så fall vurderer VBA variabelen til variantdatatypen automatisk. En variant datatype kan romme enhver datatype. Selv om dette kan virke praktisk, er det ikke en god praksis å bruke variantdatatype. Det har en tendens til å ta mer minne og kan få VBA -koden til å kjøre saktere.

Gjør variabel erklæring obligatorisk (alternativ eksplisitt)

Selv om du kan kode uten noen gang å erklære variabler, er det en god praksis å gjøre dette.

Bortsett fra å lagre minne og gjøre koden mer effektiv, har deklarering av variabler en annen stor fordel - det hjelper med å fange feil forårsaket av feilstavede variabelnavn.

For å være sikker på at du er tvunget til å deklarere variabler, legger du til følgende linje øverst i modulen.

Alternativ Eksplisitt

Når du legger til ‘Option Explicit’, må du deklarere alle variablene før du kjører koden. Hvis det er en variabel som ikke er deklarert, viser VBA en feil.

Det er en stor fordel ved å bruke Option Explicit.

Noen ganger kan du ende opp med å skrive en feil og skrive inn et variabelnavn som er feil.

Normalt er det ingen måte for VBA å vite om det er en feil eller er forsettlig. Men når du bruker ‘Option Explicit’, vil VBA se navnet på den feilstavede variabelen som en ny variabel som ikke er deklarert og viser deg en feil. Dette vil hjelpe deg med å identifisere disse feilstavede variabelnavnene, som kan være ganske vanskelige å oppdage i en lang kode.

Nedenfor er et eksempel der bruk av 'Option Explicit' identifiserer feilen (som ikke kunne ha blitt fanget hvis jeg ikke hadde brukt '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 Sub

Vær oppmerksom på at jeg har feilstavet ordet 'CommissionRate' en gang i denne koden.

Hvis jeg ikke bruker Option Explicit, vil denne koden kjøre og gi meg feil total provisjonsverdi (i tilfelle verdien i celle A1 er mindre enn 10000).

Men hvis jeg bruker Option Explicit øverst i modulen, lar jeg meg ikke kjøre denne koden før jeg enten retter det feilstavede ordet eller deklarerer det som en annen variabel. Det vil vise en feil som vist nedenfor:

Selv om du kan sette inn linjen ‘Alternativ eksplisitt’ hver gang du koder, er her trinnene for å få den til å vises som standard:

1. Klikk på Verktøy i verktøylinjen i VB Editor.
2. Klikk på Alternativer.
3. I dialogboksen Alternativer klikker du på kategorien Editor.
4. Merk av for alternativet - “Krev variabel erklæring”.
5. Klikk OK.

Når du har aktivert dette alternativet, og når du åpner en ny modul, vil VBA automatisk legge til linjen "Alternativ eksplisitt" til den.

Merk: Dette alternativet påvirker bare alle moduler du oppretter etter at dette alternativet er aktivert. Alle eksisterende moduler påvirkes ikke.

Variabelenes omfang

Så langt har vi sett hvordan vi deklarerer en variabel og tilordner datatyper til den.

I denne delen vil jeg dekke omfanget av variabler og hvordan du kan deklarere at en variabel bare skal brukes i en delprogram, i en hel modul eller i alle modulene.

Omfanget til en variabel bestemmer hvor variabelen kan brukes i VBA,

Det er tre måter å omfatte en variabel i Excel VBA:

1. Innenfor en enkelt underprogram (lokale variabler)
2. Innenfor en modul (variabler på modulnivå)
3. I alle moduler (offentlige variabler)

La oss se på hver av disse i detalj.

Innenfor en enkelt underprogram (lokale variabler)

Når du deklarerer en variabel innenfor en underrutine/prosedyre, er variabelen bare tilgjengelig for den underrutinen.

Du kan ikke bruke den i andre delrutiner i modulen.

Så snart subrutinen slutter, blir variabelen slettet og minnet som brukes av den frigjøres.

I eksemplet nedenfor blir variablene deklarert i underrutinen og vil bli slettet når denne underprogrammet slutter.

Innen en modul (variabler på modulnivå)

Når du vil at en variabel skal være tilgjengelig for alle prosedyrene i en modul, må du deklarere den øverst i modulen (og ikke i noen underprogram).

Når du har erklært det øverst i modulen, kan du bruke den variabelen i alle prosedyrene i den modulen.

I eksemplet ovenfor er variabelen 'i' deklarert øverst i modulen og er tilgjengelig for alle modulene.

Vær oppmerksom på at når underrutinen avsluttes, slettes ikke modulnivåvariablene (den beholder verdien).

Nedenfor er et eksempel, hvor jeg har to koder. Når jeg kjører den første prosedyren og deretter kjører den andre, blir verdien av 'i' 30 (ettersom den bærer verdien 10 fra den første prosedyren)

I alle moduler (offentlige variabler)

Hvis du vil at en variabel skal være tilgjengelig i hele prosedyren i arbeidsboken, må du deklarere den med det offentlige søkeordet (i stedet for DIM).

Kodelinjen nedenfor øverst i modulen vil gjøre variabelen ‘CommissionRate’ tilgjengelig i alle modulene i arbeidsboken.

 Offentlig kommisjonRate As Double

Du kan sette inn variabeldeklarasjonen (ved hjelp av det offentlige søkeordet), i hvilken som helst av modulene (øverst før noen prosedyre).

Statiske variabler (som beholder verdien)

Når du jobber med lokale variabler, så snart prosedyren avsluttes, vil variabelen miste verdien og bli slettet fra VBAs minne.

Hvis du vil at variabelen skal beholde verdien, må du bruke Statisk søkeord.

La meg først vise deg hva som skjer i et normalt tilfelle.

I koden nedenfor, når jeg kjører prosedyren flere ganger, vil den vise verdien 10 hver gang.

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

Hvis jeg nå bruker det statiske søkeordet i stedet for DIM, og kjører prosedyren flere ganger, vil det fortsette å vise verdier i trinn på 10. Dette skjer når variabelen ‘i’ beholder verdien og bruker den i beregningen.

Delprosedyre1 () Statisk i Som heltall i = i + 10 MsgBox i End Sub

Deklarere konstanter i Excel VBA

Selv om variabler kan endres under kjøring av kode, kan du bruke konstanter hvis du vil ha faste verdier.

En konstant lar deg tilordne en verdi til en navngitt streng som du kan bruke i koden din.

Fordelen med å bruke en konstant er at det gjør det enkelt å skrive og forstå kode, og lar deg også kontrollere alle de faste verdiene fra ett sted.

For eksempel, hvis du beregner provisjoner og provisjonsrenten er 10%, kan du opprette en konstant (CommissionRate) og tilordne verdien 0,1 til den.

I fremtiden, hvis provisjonsrenten endres, trenger du bare å gjøre endringen på ett sted i stedet for å endre den manuelt i koden overalt.

Nedenfor er et kodeeksempel hvor jeg har tilordnet en verdi til konstanten:

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

Følgende linje brukes til å deklarere konstanten:

Const CommissionRate As Double = 0.1

Når du erklærer konstanter, må du starte med søkeordet 'Konst', Etterfulgt av navnet på konstanten.

Vær oppmerksom på at jeg har spesifisert datatypen til konstanten som Double i dette eksemplet. Igjen er det en god praksis å spesifisere datatypen for å få koden til å kjøre raskere og bli mer effektiv.

Hvis du ikke deklarerer datatypen, vil det bli sett på som en variantdatatype.

Akkurat som variabler kan konstanter også ha omfang basert på hvor og hvordan disse deklareres:

1. Innenfor en enkelt subrutine (lokale konstanter): Disse er tilgjengelige i delrutinen/prosedyren der disse deklareres. Etter hvert som prosedyren avsluttes, slettes disse konstantene fra systemets minne.
2. Innenfor en modul (modulkonstant): Disse er deklarert øverst i modulen (før noen prosedyre). Disse er tilgjengelige for alle prosedyrene i modulen.
3. I alle moduler (offentlige konstanter): Disse deklareres ved hjelp av det 'offentlige' søkeordet, øverst i en hvilken som helst modul (før noen prosedyre). Disse er tilgjengelige for alle prosedyrer i alle modulene.

Du kan også like følgende VBA -opplæringsprogrammer:

• Hvordan spille inn en makro i Excel
• Arbeider med celler og områder i Excel VBA
• Arbeide med regneark ved hjelp av Excel VBA
• Arbeider med arbeidsbøker i Excel VBA
• VBA -arrangementer
• Excel VBA -sløyfer
• Hvordan kjøre en makro i Excel
• If Then Else Statement i Excel VBA.