VBA for Excel 2016 er raskt, men det er ikke alltid raskt nok. (Dataprogrammer er aldri raske nok.) Fortsett å lese for å finne noen programmeringseksempler du kan bruke for å øke hastigheten på makroene dine.
Slår av skjermoppdatering
Når du kjører en makro, kan du lene deg tilbake og se alle handlingene på skjermen som skjer i makroen. Selv om det kan være lærerikt å gjøre dette, er det ofte irriterende etter at du har fått makroen til å fungere ordentlig og kan redusere ytelsen til makroen betraktelig. Heldigvis kan du deaktivere skjermoppdateringen som vanligvis skjer når du kjører en makro. For å slå av skjermoppdatering, bruk følgende setning:
Application.ScreenUpdating = False
Hvis du vil at brukeren skal se hva som skjer når som helst under makroen, bruk følgende setning for å slå på skjermoppdatering igjen:
Application.ScreenUpdating = Sant
For å demonstrere forskjellen i hastighet, utfør denne enkle makroen, som fyller et område med tall:
Sub FillRange()
Dim r som lang, c så lang
Dim nummer så langt
Tall = 0
For r = 1 til 50
For c = 1 til 50
Tall = Tall + 1
Celler(r, c). Velg
Celler(r, c). Verdi = Antall
Neste c
Neste r
End Sub
Du ser at hver celle blir valgt og verdien legges inn i cellene. Sett inn følgende setning i begynnelsen av prosedyren og utfør den på nytt:
Application.ScreenUpdating = False
Området fylles mye raskere, og du ser ikke resultatet før makroen er ferdig kjørt og skjermoppdatering er (automatisk) satt til True.
Når du feilsøker kode, slutter programkjøring noen ganger et sted i midten uten at du har slått på Skjermoppdatering igjen. Dette fører noen ganger til at Excels programvindu ikke svarer helt. Veien ut av denne frosne tilstanden er enkel: Gå tilbake til VBE, og utfør følgende setning i Umiddelbar-vinduet:
Application.ScreenUpdating = Sant
Slå av automatisk beregning
Hvis du har et regneark med mange komplekse formler, kan du finne ut at du kan øke hastigheten betraktelig ved å sette beregningsmodusen til manuell mens makroen kjøres. Når makroen er ferdig, setter du tilbake beregningsmodusen til automatisk.
Følgende setning setter Excel-beregningsmodus til manuell:
Application.Calculation = xlCalculationManual
Utfør neste setning for å sette beregningsmodusen til automatisk:
Application.Calculation = xlCalculationAutomatic
Hvis koden din bruker celler med formelresultater, betyr å slå av beregningen at cellene ikke blir beregnet på nytt med mindre du uttrykkelig ber Excel om å gjøre det!
Eliminerer de irriterende varslingsmeldingene
Som du vet, kan en makro automatisk utføre en rekke handlinger. I mange tilfeller kan du starte en makro og deretter henge på pauserommet mens Excel gjør sitt. Noen Excel-operasjoner viser imidlertid meldinger som krever en menneskelig respons. Denne typen meldinger betyr at du ikke kan la Excel være uten tilsyn mens den kjører makroen din – med mindre du kjenner det hemmelige trikset.
Du kan instruere Excel til å ikke vise disse typene varsler mens du kjører en makro.
Det hemmelige trikset for å unngå disse varselmeldingene er å sette inn følgende VBA-setning i makroen din:
Application.DisplayAlerts = False
Excel utfører standardoperasjonen for denne typen meldinger. Hvis du sletter et ark, er standardoperasjonen Slett. Hvis du ikke er sikker på hva standardoperasjonen er, utfør en test for å se hva som skjer.
Når prosedyren avsluttes, tilbakestiller Excel automatisk DisplayAlerts-egenskapen til True. Hvis du trenger å slå på varslene igjen før prosedyren avsluttes, bruk denne uttalelsen:
Application.DisplayAlerts = Sant
Forenkling av objektreferanser
Som du sikkert allerede vet, kan referanser til objekter bli svært lange. For eksempel kan en fullt kvalifisert referanse til et Range-objekt se slik ut:
Arbeidsbøker(“MyBook.xlsx”). Arbeidsark(“Sheet1”) _
.Range(“Rente”)
Hvis makroen din ofte bruker dette området, kan det være lurt å lage en objektvariabel ved å bruke Set-kommandoen. Følgende setning tildeler for eksempel dette Range-objektet til en objektvariabel kalt Rate:
Set Rate = Workbooks(“MyBook.xlsx”) _
.Worksheets(“Sheet1”). Range(“Rente”)
Etter å ha definert denne objektvariabelen, kan du bruke variabelen Rate i stedet for den lange referansen. Du kan for eksempel endre verdien på cellen som heter Rente:
Rate.Value = 0,085
Dette er mye lettere å skrive enn følgende utsagn:
Arbeidsbøker(“MyBook.xlsx”). Arbeidsark(“Ark1”). _
Range(“Rente”) = .085
I tillegg til å forenkle kodingen, øker bruken av objektvariabler makroene betraktelig.
Erklære variabeltyper
Du trenger vanligvis ikke bekymre deg for typen data du tilordner til en variabel. Excel håndterer alle detaljene for deg bak kulissene. For eksempel, hvis du har en variabel som heter MyVar, kan du tilordne et tall av hvilken som helst type til den variabelen. Du kan til og med tilordne en tekststreng til den senere i prosedyren.
Hvis du vil at prosedyrene dine skal utføres så raskt som mulig, fortell Excel hvilken type data som vil bli tildelt hver av variablene dine. Dette er kjent som å deklarere en variabels type.
Generelt bør du bruke datatypen som krever det minste antallet byte, men likevel kan håndtere alle dataene som er tilordnet den. Når VBA jobber med data, avhenger utførelseshastigheten av antall byte VBA har til rådighet. Med andre ord, jo færre byte data bruker, jo raskere kan VBA få tilgang til og manipulere dataene. Et unntak fra dette er datatypen heltall. Hvis hastigheten er kritisk, bruk den lange datatypen i stedet.
Hvis du bruker en objektvariabel, kan du deklarere variabelen som en bestemt objekttype. Her er et eksempel:
Dim rate som område
Set Rate = Workbooks(“MyBook.xlsx”) _
.Worksheets(“Sheet1”). Range(“Rente”)
Bruke With-End With-strukturen
Trenger du å angi en rekke egenskaper for et objekt? Koden din kjører raskere hvis du bruker With-End With-strukturen. En ekstra fordel er at koden din kan være lettere å lese.
Følgende kode bruker ikke With-End With:
Selection.Horizontal Alignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = Sant
Utvalg.Orientering = 0
Selection.ShrinkToFit = False
Selection.MergeCells = False
Her er den samme koden, skrevet om for å bruke With-End With:
Med Utvalg
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = Sant
.Orientering = 0
.ShrinkToFit = False
.MergeCells = False
Avslutt med
Når du bruker With-End With, sørg for at hver setning begynner med en prikk.