Excel 2016 VBA Speed ​​Tips - dummies

VBA til Excel 2016 er hurtig, men det er ikke altid hurtigt nok. (Computerprogrammer er aldrig hurtige nok.) Fortsæt læsning for at opdage nogle programmeringseksempler, du kan bruge til at fremskynde dine makroer.

Slukning af skærmopdatering

Når du udfører en makro, kan du læne dig tilbage og se alle de skærmhandlinger, der forekommer i makroen. Selvom det kan være lærerigt at gøre det, efter at makroen fungerer korrekt, er det ofte irriterende og kan bremse ydeevnen af ​​din makro betydeligt. Heldigvis kan du deaktivere skærmopdateringen, der normalt opstår, når du udfører en makro. For at slukke for skærmopdatering skal du bruge følgende sætning:

Program. ScreenUpdating = False

Hvis du vil have brugeren at se, hvad der sker på et hvilket som helst tidspunkt under makroen, skal du bruge følgende sætning til at aktivere skærmopdateringen igen:

Application. ScreenUpdating = True

For at demonstrere forskellen i hastighed, udfør denne enkle makro, som fylder en rækkevidde med tal:

Sub FillRange () Dim er så lang, c Som lang Dim Number som Long Number = 0 For r = 1 til 50 For c = 1 til 50 nummer = antal + 1 celler (r, c). Vælg celler (r, c). Værdi = Antal Næste c Næste r Slut Sub

Du ser, at hver celle er valgt, og den værdi, der indtastes i cellerne. Indsæt nu følgende sætning i begyndelsen af ​​proceduren og udfør den igen:

Program. ScreenUpdating = False

Sortimentet er fyldt meget hurtigere, og du kan ikke se resultatet, før makroen er færdig med at køre, og skærmopdatering er (automatisk) indstillet til True.

Når du fejler kode, slutter programgennemførelsen et eller andet sted i midten, uden at du har slået Skærmopdatering tilbage. Dette forårsager undertiden Excel's applikationsvindue til at blive fuldstændig uresponsiv. Vejen ud af denne frosne tilstand er enkel: Gå tilbage til VBE, og udfør følgende sætning i vinduet Omgående:

Program. ScreenUpdating = True

Slukning af automatisk beregning

Hvis du har et regneark med mange komplekse formler, kan du opleve, at du kan fremskynde tingene betydeligt ved at indstille beregningsmodus til manuel mens makroen udføres. Når makroen er færdig, skal du indstille beregningsmodus tilbage til automatisk.

I følgende sætning indstilles Excel-beregningsmodus til manuel:

Application. Beregning = xlCalculationManual

Udfør den næste sætning for at indstille beregningsmodus til automatisk:

Application. Beregning = xlCalculationAutomatic

Hvis din kode bruger celler med formelresultater, betyder afbrydelse af beregning, at cellerne ikke beregnes igen, medmindre du udtrykkeligt fortæller Excel for at gøre det!

Eliminere de irriterende beskedmeddelelser

Som du ved kan en makro automatisk udføre en række handlinger. I mange tilfælde kan du starte en makro og derefter gå hænge ud i pause rummet, mens Excel gør dens ting. Nogle Excel-operationer viser dog meddelelser, der kræver et menneskeligt svar. Disse typer af meddelelser betyder, at du ikke kan forlade Excel uovervåget, mens den udfører din makro - medmindre du kender det hemmelige trick.

Du kan instruere Excel for ikke at vise disse typer advarsler, mens du kører en makro.

Det hemmelige trick for at undgå disse advarselsmeddelelser er at indsætte følgende VBA-sætning i din makro:

Application. DisplayAlerts = False

Excel udfører standardoperationen for disse typer meddelelser. Ved sletning af et ark er standardoperationen Slet. Hvis du ikke er sikker på, hvad standardoperationen er, skal du udføre en test for at se, hvad der sker.

Når proceduren slutter, nulstiller Excel automatisk egenskaben DisplayAlerts til True. Hvis du har brug for at slå alarmerne på igen, før proceduren slutter, skal du bruge denne erklæring:

Application. DisplayAlerts = True

Forenkler objektreferencer

Som du sikkert allerede ved, kan henvisninger til objekter blive meget lange. For eksempel kan en fuldt kvalificeret reference til et Range objekt se sådan ud:

Workbooks ("MyBook. Xlsx"). Regneark ("Sheet1") _. Område ("InterestRate")

Hvis din makro ofte anvender dette interval, kan du oprette en objektvariabel ved at bruge kommandoen Set. For eksempel tildeler følgende sætning dette Range objekt til en objektvariabel med navnet Rate:

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

Når du har defineret denne objektvariabel, kan du bruge variablen Rate snarere end den lange reference. Du kan for eksempel ændre værdien af ​​cellen med navnet InterestRate:

Rate. Værdi =. 085

Dette er meget lettere at skrive end følgende erklæring:

Workbooks ("MyBook. Xlsx"). Arbejdsark (”Ark1”). _ Range ("InterestRate") =. 085

Ud over at forenkle din kodning øger hastigheden af ​​dine makroer markant ved hjælp af objektvariabler.

Deklarere variabeltyper

Du behøver normalt ikke at bekymre sig om den type data, du tildeler en variabel. Excel håndterer alle detaljer for dig bag kulisserne. Hvis du for eksempel har en variabel med navnet MyVar, kan du tildele et nummer af en hvilken som helst type til den variabel. Du kan endda tildele en tekststreng til den senere i proceduren.

Hvis du vil have dine procedurer udført så hurtigt som muligt, skal du fortælle Excel, hvilken type data der vil blive tildelt til hver af dine variabler. Dette er kendt som erklærer en variants type.

Generelt skal du bruge datatypen, der kræver det mindste antal bytes, men alligevel kan du håndtere alle de data, der er tildelt det. Når VBA arbejder med data, afhænger udførelseshastigheden af ​​det antal byte, som VBA har til rådighed. Med andre ord, jo færre byte data bruger, jo hurtigere VBA kan få adgang til og manipulere dataene.En undtagelse til dette er integer datatypen. Hvis hastigheden er kritisk, skal du bruge den lange datatype i stedet.

Hvis du bruger en objektvariabel, kan du erklære variablen som en bestemt objekttype. Her er et eksempel:

Dim Rate as Range Set Rate = Arbejdsbøger ("MyBook. Xlsx") _. Arbejdsark (”Ark1”). Område ("InterestRate")

Brug af med-slut med struktur

Har du brug for at indstille et antal egenskaber for en genstand? Din kode kører hurtigere, hvis du bruger strukturen With-End With. En ekstra fordel er, at din kode kan være lettere at læse.

Følgende kode bruger ikke With-End With:

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

Her er den samme kode, der er omskrevet for at bruge With-End With:

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

Når du bruger With-End With, skal du sørge for, at hver sætning begynder med en prik.