Deze VBA errors los je voortaan in een mum van tijd op

In dit artikel bespreken we drie veelvoorkomende errors in VBA: wat triggert ze en hoe fix je ze?

Welke VBA-ontwikkelaar is er niet bekend met de gevechten in de VBA-frontlinie? Het kan ontzettend moeilijk zijn om VBA naar je hand te zetten, vooral voor beginnende ontwikkelaars. VBA errors zijn vooral vervelend, omdat ze je worden voorgeschoteld in opdringerige pop-upberichten: je moét ze wegklikken om door te kunnen met het schrijven van code. En erger nog, je kunt een werkmap niet opslaan zolang er VBA fouten in staan.

#1 Object variable or With block variable not set

Deze notificatie is misschien wel meteen de ergste; zowel vaag als vervelend. In VBA moet je een type variabele declareren voordat je er een waarde aan toewijst. Als je bijvoorbeeld een getal wilt opslaan, kun je een variabele declareren als Integer of Double:

Dim PI As Double
PI = 3.14

Op dezelfde manier kun je een String voor tekst declareren:

Dim message As String
message = "Hello World"

Het is belangrijk om het woord Set te gebruiken bij het toewijzen van waarden aan variabelen van objecttypen, zoals een Range of een Worksheet. Als je een werkblad wilt toevoegen en toewijzen aan een variabele ws:

Dim ws As Worksheet
ws = Worksheets.Add

…dan krijg je de gevreesde notificatie:

VBA errors in Excel // Run time error 91 // Deze errors in VBA los je voortaan in een mum van tijd op // PerfectXL Academy

Om het op te lossen, maak je gebruik van het woord Set:

Dim ws As Worksheet
Set ws = Worksheets.Add

Merk op dat de notificatie een slechte indicatie is van de locatie waar Set ontbrak. Run je code regelmatig om te weten waar de fout gemaakt is.

#2 Subscript out of range

Deze lijkt logisch. Laten we zeggen dat je een lijst met drie items hebt:

Dim MyFirst(1 To 3) As String
MyFirst(1) = "A"
MyFirst(2) = "B"
MyFirst(3) = "C"

Als je vervolgens een vierde item probeert te kiezen (bijvoorbeeld MsgBox MyFirst(4)), dan krijg je de klacht dat het subscript (in dit voorbeeld “4”) buiten het bereik (1 to 3) ligt:

VBA errors in Excel // Run time error 9 // Deze errors in VBA los je voortaan in een mum van tijd op // PerfectXL Academy

Toch zijn er genoeg gevallen waarin de oorzaak van deze boodschap niet zo duidelijk is. Je krijgt deze fout niet alleen wanneer een index zich buiten het bereik van een lijst bevindt, maar ook wanneer een tijdens runtime opgelost object geen opgegeven eigenschap heeft. Te cryptisch? We geven een voorbeeld. We willen programmatisch een werkblad met de naam “Resultaten” naar de voorgrond brengen:

Worksheets("Results").Activate

Dat werkt – als en alleen als het werkblad “Results” daadwerkelijk bestaat. Als je een typefout hebt gemaakt en het in VBA, of in de werkelijke naam van het werkblad als Result hebt gespeld, dan krijg je dezelfde “Subscript out of range” notificatie. De truc is niet alleen om voorzichtig te zijn met spelling, maar om ook te controleren of er variabelen in het spel zijn.

#3 Handling Run-time errors

Je kunt niet altijd elke mogelijke fout voorkomen, dus laten we eindigen met de juiste manier om VBA errors op te sporen en op de juiste manier te rapporteren.

Binnen elke Sub of Function kun je de regel On Error GoTo MyErrorHandler aan het begin van de methode plaatsen om fouten om te leiden naar een speciale foutafhandelaar:

Sub MyMethod()
On Error GoTo MyErrorHandler
' do your thing
Exit Sub
MyErrorHandler:
MsgBox "Something went wrong"
End Sub

Merk op dat de code onder “MyErrorHandler” zonder Exit Sub altijd zal worden uitgevoerd.

In het bovenstaande voorbeeld houdt het afhandelen van de fout simpelweg in dat de gebruiker wordt verteld dat de procedure niet volgens plan is verlopen. In veel gevallen is een vorm van “reset” ook belangrijk. In VBA is het bijvoorbeeld gebruikelijk om ScreenUpdating uit te schakelen en om Calculation in te stellen op manual aan het begin van het script, om ze vervolgens aan het einde van het script opnieuw in te stellen. Vereenvoudigd voorbeeld:

Sub MyMethod()
Application.Calculation = xlManual
Application.ScreenUpdating = False
' do your thing
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

…als je script vervolgens faalt in de “doe-je-ding”-fase, zal het de eigenschappen voor Calculation en ScreenUpdating nooit opnieuw instellen. Neem ze daarom ook op in de foutafhandeling, om er zeker van te zijn dat ze altijd opnieuw worden ingesteld:

Sub MyMethod()
On Error GoTo Finally
Application.Calculation = xlManual
Application.ScreenUpdating = False
' do your thing
Finally:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Merk op dat “Finally” altijd wordt aangeroepen omdat er geen Exit Sub statement is, en dat is precies wat we willen. Wil je alleen in het geval van een error een MsgBox tonen? Voeg dan simpelweg nog een GoTo statement toe:

Sub MyMethod()
On Error GoTo CatchError
Application.Calculation = xlManual
Application.ScreenUpdating = False
' do your thing
GoTo Finally
CatchError:
MsgBox "Something went wrong"
Finally:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Geef je niet om fouten en wil je gewoon doorgaan met het runnen van code wanneer VBA een fout tegenkomt? Je kunt VBA expliciet instrueren om dit te doen:

On Error Resume Next

Deze situatie is vooral vaak gewenst tijdens het doorlopen van een lijst, wanneer het geen probleem is om de items over te slaan die resulteren in een fout.

Let wel op: deze regel maskeert ook eventuele fouten die niet mogen worden overgeslagen!