Excel 2016 VBA Speed ​​Tips - dummies

VBA for Excel 2016 er rask, men det er ikke alltid rask nok. (Dataprogrammer er aldri raske nok.) Fortsett å lese for å oppdage noen programmeringseksempler du kan bruke til å øke hastigheten på makroene dine.

Slå av skjermoppdatering

Når du utfører en makro, kan du lene seg tilbake og se på alle skjermhandlingene som oppstår i makroen. Selv om det å gjøre dette kan være lærerikt, etter at makroen fungerer riktig, er det ofte irriterende og kan bremse ytelsen til makroen betydelig. Heldigvis kan du deaktivere skjermoppdateringen som normalt oppstår når du utfører en makro. For å slå av skjermoppdatering, bruk følgende setning:

Program. ScreenUpdating = False

Hvis du vil at brukeren skal se hva som skjer når som helst under makroen, bruker du følgende setning for å aktivere skjermoppdateringen på nytt:

Program. ScreenUpdating = True

For å demonstrere forskjellen i hastighet, utfør denne enkle makroen, som fyller en rekkevidde med tall:

Sub FillRange () Dim er så lang, c Så lenge Dim nummer som langt tall = 0 For r = 1 til 50 For c = 1 til 50 tall = tall + 1 celler (r, c). Velg celler (r, c). Verdi = Nummer Neste c Neste r End Sub

Du ser at hver celle er valgt og verdien er oppgitt i cellene. Sett inn følgende setning i begynnelsen av prosedyren og utfør den igjen:

Program. ScreenUpdating = False

Området fylles mye raskere, og du ser ikke resultatet før makroen er ferdig å kjøre, og skjermoppdatering er (automatisk) satt til True.

Når du feilsøker kode, slutter programgjennomføringen et sted i midten uten at du har slått på skjermoppdatering igjen. Dette fører noen ganger til at Excel-applikasjonsvinduet blir helt uforsvarlig. Veien ut av denne frosne tilstanden er enkel: Gå tilbake til VBE, og utfør følgende setning i vinduet Umiddelbart:

Program. ScreenUpdating = True

Slå av automatisk beregning

Hvis du har et regneark med mange komplekse formler, kan det hende at du kan øke hastigheten mye ved å sette beregningsmodus til manuell mens makroen din utføres. Når makroen er ferdig, må du sette beregningsmodus tilbake til automatisk.

Følgende setning angir Excel beregningsmodus til manuell:

Program. Beregning = xlCalculationManual

Utfør neste setning for å sette beregningsmodus til automatisk:

Program. Beregning = xlCalculationAutomatic

Hvis koden din bruker celler med formelresultater, slår du av beregning at cellene ikke blir omberegnet med mindre du eksplisitt forteller Excel for å gjøre det!

Eliminere de irriterende varselmeldingene

Som du vet kan en makro automatisk utføre en rekke handlinger. I mange tilfeller kan du starte en makro og deretter gå ut i pauseomrommet mens Excel gjør ting. Noen Excel-operasjoner viser imidlertid meldinger som krever menneskelig respons. Disse typer meldinger betyr at du ikke kan la Excel være uovervåket mens den utfører makroen din, med mindre du kjenner det hemmelige trikset.

Du kan instruere Excel for ikke å vise disse typer varsler mens du kjører en makro.

Det hemmelige trikset for å unngå disse varslingsmeldingene er å sette inn følgende VBA-setning i makroen:

Søknad. DisplayAlerts = False

Excel utfører standardoperasjonen for disse typer meldinger. Ved sletning av 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, nullstiller Excel automatisk egenskapen DisplayAlerts til True. Hvis du må slå på varslene igjen før prosedyren avsluttes, bruk denne setningen:

Søknad. DisplayAlerts = True

Forenkler objektreferanser

Som du sikkert allerede vet, kan referanser til objekter bli svært lange. For eksempel kan en fullt kvalifisert referanse til et områdeobjekt se slik ut:

arbeidsbøker ("MyBook. Xlsx"). Regneark ("Sheet1") _. Område ("InterestRate")

Hvis makroen ofte bruker dette området, kan det hende du vil opprette en objektvariabel ved å bruke kommandoen Set. For eksempel tilordner følgende setning denne Range-objektet til en objektvariabel som heter Rate:

Set Rate = Workbooks ("MyBook. Xlsx") _. Regneark (“Ark1”). Range ("InterestRate")

Når du har definert denne objektvariabelen, kan du bruke variabelen Rate snarere enn den lange referansen. For eksempel kan du endre verdien av cellen som heter InterestRate:

Rate. Verdi =. 085

Dette er mye lettere å skrive enn følgende erklæring:

Arbeidsbøker ("MyBook. Xlsx"). Regneark (“Ark1”). _ Range ("InterestRate") =. 085

I tillegg til å forenkle kodingen, bruker objektvariabler hastighetene dine makroer betydelig.

Deklarere variabeltyper

Du trenger vanligvis ikke å bekymre deg for hvilken type data du tilordner en variabel. Excel håndterer alle detaljene for deg bak kulissene. Hvis du for eksempel har en variabel som heter MyVar, kan du tildele en 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å fort som mulig, fortell Excel hvilken type data som skal tilordnes hver av variablene dine. Dette er kjent som deklarerer en variants type.

Generelt bør du bruke datatypen som krever det minste antall byte, men likevel kan håndtere alle dataene som er tildelt den. Når VBA arbeider med data, avhenger eksekveringshastigheten av antall byter VBA har til disposisjon. Med andre ord, jo færre bytes data bruker, jo raskere VBA kan få tilgang til og manipulere dataene.Et unntak fra dette er integer datatypen. 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 as Range Set Rate = Arbeidsbøker ("MyBook. Xlsx") _. Regneark (“Ark1”). Range ("InterestRate")

Bruke End-End With Structure

Trenger du å angi en rekke egenskaper for et objekt? Koden din kjører raskere hvis du bruker Med-End With-strukturen. En ekstra fordel er at koden din kan være lettere å lese.

Følgende kode bruker ikke Endre med:

Valg. HorizontalAlignment = xlCenter Selection. VerticalAlignment = xlCenter-utvalg. WrapText = True Selection. Orientering = 0 Valg. ShrinkToFit = False Selection. MergeCells = False

Her er den samme koden, omskrivet for å bruke Endre med:

Med valg. HorizontalAlignment = xlCenter. VerticalAlignment = xlCenter. WrapText = True. Orientering = 0. ShrinkToFit = False. MergeCells = False End With

Når du bruker With-End With, må du kontrollere at hver setning begynner med en prikk.