Excel 2016 VBA Snelheidstips - dummies

VBA voor Excel 2016 is snel, maar het is niet altijd snel genoeg. (Computerprogramma's zijn nooit snel genoeg.) Blijf lezen om een ​​aantal programmeervoorbeelden te ontdekken die u kunt gebruiken om uw macro's te versnellen.

Schermupdate uitschakelen

Wanneer u een macro uitvoert, kunt u achterover leunen en alle acties op het scherm in de macro bekijken. Hoewel dit heel leerzaam kan zijn, is het vaak irritant nadat je de macro goed hebt laten werken en de prestaties van je macro aanzienlijk kunnen vertragen. Gelukkig kunt u de schermbijwerking uitschakelen die normaal optreedt wanneer u een macro uitvoert. Om het updaten van het scherm uit te schakelen, gebruikt u de volgende verklaring:

Application. ScreenUpdating = False

Als u wilt dat de gebruiker op elk moment tijdens de macro ziet wat er gebeurt, gebruikt u de volgende verklaring om de schermbijwerking weer in te schakelen:

Toepassing. ScreenUpdating = True

Om het snelheidsverschil te demonstreren, voert u deze eenvoudige macro uit, die een bereik met getallen vult:

Sub FillRange () Dim r als Long, c As Long Dim Number as Long Number = 0 For r = 1 tot 50 Voor c = 1 Tot 50 Aantal = Aantal + 1 Cellen (r, c). Selecteer Cellen (r, c). Waarde = Nummer Volgende c Volgende r Einde Sub

U ziet dat elke cel wordt geselecteerd en de waarde wordt ingevoerd in de cellen. Plaats nu de volgende verklaring aan het begin van de procedure en voer deze opnieuw uit:

Application. ScreenUpdating = False

Het bereik wordt veel sneller ingevuld en u ziet het resultaat pas nadat de macro is voltooid en het bijwerken van het scherm (automatisch) is ingesteld op True.

Wanneer u code foutopsporing uitvoert, eindigt het programma soms ergens in het midden zonder dat u de functie Schermupdate opnieuw hebt ingeschakeld. Dit zorgt er soms voor dat het applicatievenster van Excel totaal niet meer reageert. De uitweg uit deze bevroren toestand is eenvoudig: ga terug naar de VBE en voer de volgende verklaring uit in het venster Onmiddellijk:

Application. ScreenUpdating = True

Automatische berekening uitschakelen

Als u een werkblad met veel complexe formules heeft, zult u merken dat u de dingen aanzienlijk kunt versnellen door de berekeningsmodus in te stellen op handmatig terwijl uw macro wordt uitgevoerd. Wanneer de macro is voltooid, stelt u de berekeningsmodus opnieuw in op automatisch.

De volgende instructie stelt de Excel-berekeningsmodus in op handmatig:

Toepassing. Berekening = xlCalculationManual

Voer de volgende instructie uit om de berekeningsmodus in te stellen op automatisch:

Toepassing. Berekening = xlCalculationAutomatic

Als uw code cellen gebruikt met formuleresultaten, betekent het uitschakelen van de berekening dat de cellen niet opnieuw worden berekend, tenzij u Excel expliciet opdracht geeft dit te doen!

Die vervelende waarschuwingsberichten elimineren

Zoals u weet, kan een macro automatisch een reeks acties uitvoeren. In veel gevallen kunt u een macro starten en vervolgens in de pauzeruimte rondhangen terwijl Excel zijn ding doet. Sommige Excel-bewerkingen geven echter berichten weer die een menselijk antwoord vereisen. Dit soort berichten betekent dat je Excel niet onbeheerd kunt laten terwijl het je macro uitvoert - tenzij je de geheime truc kent.

U kunt Excel opdracht geven om dit soort waarschuwingen niet weer te geven tijdens het uitvoeren van een macro.

De geheime truc om deze waarschuwingsberichten te vermijden is het invoegen van de volgende VBA-instructie in uw macro:

toepassing. DisplayAlerts = False

Excel voert de standaardbewerking uit voor dit soort berichten. In het geval van het verwijderen van een blad, is de standaardbewerking Verwijderen. Als u niet zeker weet wat de standaardbewerking is, voert u een test uit om te zien wat er gebeurt.

Wanneer de procedure is voltooid, stelt Excel de eigenschap DisplayAlerts automatisch in op True. Als u de meldingen weer moet inschakelen voordat de procedure is voltooid, gebruikt u deze verklaring:

Toepassing. DisplayAlerts = True

Vereenvoudig objectreferenties

Zoals u waarschijnlijk al weet, kunnen verwijzingen naar objecten erg lang duren. Een volledig gekwalificeerde verwijzing naar een bereikobject kan er bijvoorbeeld als volgt uitzien:

werkmappen ("MyBook. Xlsx"). Werkbladen ("Sheet1") _. Bereik ("InterestRate")

Als uw macro dit bereik vaak gebruikt, wilt u misschien een objectvariabele maken met behulp van de opdracht Set. Met de volgende instructie wordt dit bereikobject bijvoorbeeld toegewezen aan een objectvariabele met de naam Rate:

Set Rate = Workbooks ("MyBook. Xlsx") _. Worksheets (“Sheet1”). Bereik ("Interest Rate")

Nadat u deze objectvariabele hebt gedefinieerd, kunt u de variabele Rate gebruiken in plaats van de lange referentie. U kunt bijvoorbeeld de waarde van de cel met de naam InterestRate wijzigen:

Rate. Waarde =. 085

Dit is veel gemakkelijker te typen dan de volgende verklaring:

Werkmappen ("MyBook. Xlsx"). Worksheets (“Sheet1”). _ Bereik ("InterestRate") =. 085

Naast het vereenvoudigen van uw codering versnelt het gebruik van objectvariabelen uw macro's aanzienlijk.

Variabele types declareren

U hoeft zich over het algemeen geen zorgen te maken over het type gegevens dat u toewijst aan een variabele. Excel verwerkt alle details achter de schermen voor u. Als u bijvoorbeeld een variabele met de naam MyVar hebt, kunt u een nummer van elk type aan die variabele toewijzen. U kunt er later in de procedure zelfs een tekstreeks aan toewijzen.

Als u wilt dat uw procedures zo snel mogelijk worden uitgevoerd, vertel dan aan Excel welk type gegevens aan elk van uw variabelen wordt toegewezen. Dit staat bekend als waarbij het type van een variabele wordt aangegeven .

Over het algemeen moet u het gegevenstype gebruiken dat het kleinste aantal bytes vereist, maar toch alle gegevens kan verwerken die eraan zijn toegewezen. Wanneer VBA met gegevens werkt, is de uitvoersnelheid afhankelijk van het aantal bytes dat VBA tot zijn beschikking heeft. Met andere woorden, hoe minder bytes-gegevens worden gebruikt, hoe sneller VBA toegang heeft tot de gegevens en deze kan manipuleren.Een uitzondering hierop is het gegevenstype Integer. Als snelheid kritiek is, gebruikt u in plaats hiervan het lange gegevenstype.

Als u een objectvariabele gebruikt, kunt u de variabele als een specifiek objecttype declareren. Hier is een voorbeeld:

Dim Rate als Range Set Rate = Workbooks ("MyBook. Xlsx") _. Worksheets (“Sheet1”). Bereik ("InterestRate")

De With-End With-structuur gebruiken

Moet u een aantal eigenschappen voor een object instellen? Uw code werkt sneller als u de With-End With-structuur gebruikt. Een bijkomend voordeel is dat uw code mogelijk gemakkelijker te lezen is.

De volgende code gebruikt With-end with:

niet. HorizontalAlignment = xlCenter Selection. VerticalAlignment = xlCenter Selection. WrapText = echte selectie. Oriëntatie = 0 Selectie. ShrinkToFit = False Selection. MergeCells = False

Hier is dezelfde code, herschreven om With-End With:

met selectie te gebruiken. HorizontalAlignment = xlCenter. VerticalAlignment = xlCenter. WrapText = True. Oriëntatie = 0. ShrinkToFit = False. MergeCells = False End met

Wanneer u With-End With gebruikt, moet u ervoor zorgen dat elke instructie begint met een punt.