CODEWITHSUNDEEP
vbaexcel
CaptainFuzzyFace
CaptainFuzzyFace

Reputation: 339

How best to structure Excel VBA Code for maintenance, clarity and error handling

Let's assume that I want to run some nice VBA code that can be best broken 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 cancellation then I want to exit tidily and run CleanUp.

I started with...

If Init() Then
    If FirstStep() Then
        If SecondStep() Then
        End If
    End If
End If
Call CleanUp

But that is ugly.

Next came...

Select Case vbCancel
    Case Init()
    Case FirstStep()
    Case SecondStep()
End Select
Call CleanUp

That was an improvement but I still wonder if there is a better way? I know that VBA isn't really designed to be well structured, but none the less, I'd like to try.

Upvotes: 3

Views: 497

Answers (2)

A.S.H
A.S.H

Reputation: 29332

@AlexK 's answer is the standard one if the failures are due to runtime errors.

But there are circumstances where we want:

1- to run a normal sequence of operations and checkings, and the logic is such that we have to stop at some place, when one of those operations tells so by returning a boolean

2- to avoid setting up the standard error handling (there are situations where we dont want that)

3- to avoid a big number of nested If's (too ugly as you said)

The Select Case solution answers these requirements, but here's another one that you might prefer (the intent being 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

Upvotes: 3

Alex K.
Alex K.

Reputation: 175846

Error handling is good for error handling. Make you routines Err.Raise if there is an error, then you can:

on error goto ERR_IN_PROCESS
    Init
    FirstStep
    SecondStep

ERR_IN_PROCESS:
    CleanUp

This has the advantage of dealing with runtime errors also.

If you really don't want to use error handling you can use a briefer form of your Select

Select Case False
    Case Init(), FirstStep(), SecondStep()
End Select

CleanUp

I cant see the point of a loop, instead use scope:

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

Upvotes: 2

Related Questions