How to better structure your Excel VBA code for maintenance, clarity and error handling
Suppose I want to run some good VBA code that can best be broken down into four nice little sequential functions.
Call Init()
Call FirstStep()
Call SecondStep()
Call CleanUp()
However, if one of the first three fails with an error or user invalidation, I want to finish neatly and run CleanUp
.
I started with ...
If Init() Then
If FirstStep() Then
If SecondStep() Then
End If
End If
End If
Call CleanUp
But this is ugly.
Further...
Select Case vbCancel
Case Init()
Case FirstStep()
Case SecondStep()
End Select
Call CleanUp
This was an improvement, but I'm still wondering if there is a better way? I know VBA is not meant to be well structured, but I'd like to try it nonetheless.
source to share
@AlexK is the default if errors are caused by runtime errors.
But there are circumstances in which we want:
1- run the usual sequence of operations and checks, and the logic is such that we need to stop at some place when one of these operations reports it, returning a boolean
2- to avoid setting standard error handling (there are situations where we don't want this)
3- to avoid a lot of nested If (too ugly as you said)
The solution Select Case
meets these requirements, but here's another one you might prefer (the goal is clearer):
Do
If Not FirstStep Then Exit Do
If Not SecondStep Then Exit Do
If Not ThirdStep Then Exit Do
If Not FourthStep Then Exit Do
' ...
Loop Until True
Cleanup
source to share
Error handling is suitable for handling errors. Run Err.Raise procedures if there is an error, then you can:
on error goto ERR_IN_PROCESS
Init
FirstStep
SecondStep
ERR_IN_PROCESS:
CleanUp
This also has the advantage of dealing with run-time errors.
If you really don't want to use error handling, you can use the shorter form of your choice
Select Case False
Case Init(), FirstStep(), SecondStep()
End Select
CleanUp
I can't see the point of the loop, I use scope instead:
Sub Foo()
Run
CleanUp
End Sub
Sub Run()
If Not Init Then Exit Sub
If Not FirstStep Then Exit Sub
If Not SecondStep Then Exit Sub
End Sub
source to share