Tag Archives: Sub

vba end

VBA End – Exit or Reset VBA macro

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.00 out of 5)
Loading...

Often we think about beginnings not endings. It is only when we get somewhere it is when we realize we hadn’t thought through our destination. Same goes for writing VBA macros. Fortunately the VBA End statement is here to save the day and help us put that full-stop in the right place. Today I want to share a cool VBA tip for properly ending your VBA macros.

The VBA End statement

The VBA End statement ends the execution of a certain VBA scope.

Syntax

The VBA End statement is used with any scope in VBA.

End [Function | Sub | With | Select | Type | Enum | If ]

You can End a Function, a conditional If statement, mark the End of an VBA Enum or VBA Type.

Exiting VBA Functions or Procedures

Sometimes however we want to end the execution of a function or procedure early, simply by leaving the current execution scope.

Exit Sub

Ending Sub procedures is very useful. Instead of using the End statement we need to use the VBA Exit statement.

Sub SomeSub()
    '...
    'Your code here
    '...
    Exit Sub
    '...
    'This code will not be executed
    '...
End Sub

Exit Function

Similarly for Functions we need to use the Exit statement instead of End.

Function SomeFunction() as Integer
    '...
    'Your code here
    SomeFunction = 1
    Exit Function
    'This code will not be executed
    SomeFunction = 2
End Function

The result of the VBA Function above is 1 not 2.

End VBA macro / Reset VBA macro

Now here is the trick I want to sell you today. The Exit statement above will allow you to leave the scope of your current VBA Function or VBA Sub. It will not however end the execution of your entire macro. A simple example below:

Sub Main()
    Call SomeSub 
    'Code will execute
    Debug.Print "Main: Hello after Exit Sub!"
End Sub

Sub SomeSub()
    Exit Sub
    'Code will not execute
    Debug.Print "SomeSub: Hello after Exit Sub!"
End Sub

The result:

The result of running Exit Sub
The result of running End Sub

So you see Exit Sub exiting only the the current scope of the running VBA Sub procedure and not the entire macro!

End – as simple as that

How to exit the execution of the entire macro (the Main Sub)? A simple End will do…

Sub Main()
    Call SomeSub 
    'Code will not execute
    Debug.Print "Main: Hello after Exit Sub!"
End Sub

Sub SomeSub()
    End
    'Code will not execute
    Debug.Print "SomeSub: Hello after Exit Sub!"
End Sub

The result:

Using the VBA End statement to exit macro execution
Using the VBA End statement to exit macro execution

The End statement ends the execution of the ENTIRE MACRO.

Use End carefully!

As Andy Pope has rightfully corrected me, the End statement needs to be used with care. As there are consequences…

The VBA Reset button
The VBA Reset button
The VBA End statement works a lot like the VBA Reset button. This is what happens:

  • Object events will not be invoked e.g. the Unload, QueryUnload, or Terminate events
  • Any running Visual Basic code will be stopped – that means Timers and other running code
  • Object references held by other programs are invalidated – if any application is holding a reference to a VBA object it will be invalidated and inaccessible
  • Closes any open dialogs / forms (e.g. UserForm or MsgBox)

Using End is a lot like pulling a hand brake in a car. It stops the car, but often so abruptly it might cause it to crash.

VBA Function vs VBA Sub

The VBA Function vs VBA Sub

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 3.33 out of 5)
Loading...

Today back to basics – the explaining the VBA Function and the VBA Sub procedures. We use Subs and Functions often not thinking about their true potential and how much we can squeeze out of a regular VBA Function. Let’s start with a reminder how both differ from each other and then dive into the details.

VBA Function vs. VBA Sub

We often tend to mix up procedures, Subs and Functions in VBA. So let’s get it right this time.

A VBA procedure is a block of VBA statements enclosed by a declaration statement (Function, Sub, Operator, Get, Set) and a matching End declaration. All executable statements in Visual Basic must be within some procedure.

A VBA Sub procedure is a series of VBA statements enclosed by the Sub and End Sub statements. The Sub procedure performs a task and then returns control to the calling code, but it does not return a value to the calling code. Only VBA Subs can be called directly.

Sub NameOfSub()
 Debug.Print "Hello I am a Sub"
End Sub

A VBA Function procedure is a series of VBA statements enclosed by the Function and End Function statements. The Function procedure performs a task and then returns control to the calling code. When it returns control, it also returns a value to the calling code.

Function NameOfFunction() as String
 Debug.Print "Hello I am a Function"
 NameOfFunction = "Hello I am a Function"
End Sub
In short VBA Functions return values, VBA Sub can’t return any values. Additionally only VBA Subs can be executed directly (VBA Functions can be executed only within other code blocks)

Syntax

VBA Sub procedure syntax

VBA Sub procedureThe code block of a VBA Sub procedure is marked by the Sub and End Sub statements.

VBA Function procedure syntax

VBA FunctionThe code block of a VBA Function is marked by the Function and End Function statements.

Passing arguments ByVal and ByRef

The common knowledge is that only VBA Functions can return values. That indeed is mostly true. However, instead of a value (the result of the Function), we can assign values to variables that can be passed to the procedure by reference. What does that mean?

ByVal vs ByRef
Passing a variable by its Value or its Reference
Variables can either by passed to a procedure (Function, Sub etc.) by their value or their reference.
Passing by value translates to making a copy of the variable. Thus any changes to the copy will not be reflected in the original variable.
Passing by reference is passing the address of the variable to the procedure. This means that any changes to a argument will be reflected in the original variable.

By default all arguments are passed to procedures ByVal, so that no changes can be made to the original variables

ByVal and ByRef examples

Let’s look at some examples now:

Sub SetValueByVal(ByVal someLong As Long)
    someLong = 10
End Sub
Sub SetValueByRef(ByRef someLong As Long)
    someLong = 10
End Sub

Sub Test()
    Dim someLong As Long
    someLong = 1
    
    SetValueByVal someLong
    Debug.Print someLong 'Result: 1 (no change)
    
    SetValueByRef someLong
    Debug.Print someLong 'Result: 10
End Sub

When passing by value the variable someLong is not modified. However, when we pass it by reference its value is changed within the Sub procedure.

Objects and arrays are always passed by reference, because objects are in fact references (e.g. a Collection). Beware in such cases not to modify referenced objects by mistake!

Passing arrays to Subs and Functions

You can also easily pass arrays to Subs or Functions, even redefining their length. See example below:

Sub ChangeLength(arr() As Long)
    ReDim arr(5) As Long
End Sub

Sub Test()
    Dim arr() As Long
    ReDim arr(2) As Long
    Debug.Print UBound(arr) 'Result: 2
    ChangeLength arr
    Debug.Print UBound(arr) 'Result: 5
End Sub

Optional parameters

VBA Functions and Subs permit optional parameters, ones that need not be provided when executing the Function or Sub. It is a good practice to specify the default value for such parameters. See example below:

Sub SayHi(name As String, Optional surname As String = vbNullString)
    Debug.Print "Hi there, " & name & IIf(surname = vbNullString, "", " " & surname)
End Sub

Sub Test()
    Call SayHi("John") 'Result: "Hi there, John"
    
    Call SayHi("John", "Smith") 'Result: "Hi there, John Smith"
End Sub

You can verify if a parameter has not been passed to an Sub or Function by using the IsMissing function. The IsMissing function works however only for Variant type parameters. See the same SayHi procedure as above, this time with the IsMissing function.

Sub SayHi(name As String, Optional surname As Variant)
    Debug.Print "Hi there, " & name & IIf(IsMissing(surname), "", " " & CStr(surname))
End Sub

Sub Test()
    Call SayHi("John") 'Result: "Hi there, John"
    
    Call SayHi("John", "Smith") 'Result: "Hi there, John Smith"
End Sub

Dynamic parameter list

Say you want to create a VBA function like the Excel SUM or AVERAGE formulas – that can be provided with a dynamic list of parameters. VBA extends a neat solution for such a scenario called the ParamArray.

Public Function MySUM(ParamArray args())
    For Each arg In args
        MySUM = MySUM + arg
    Next arg
End Function

Sub Test()
    Debug.Print MySUM(1, 2, 3, 4) 'Result: 10
End Sub

The ParamArray transforms a list of Variant variables passed as parameters into a neat Variant array.