Please note javascript is required for full website functionality.

Blog

VBA Blogs: Going Through the Visual Basics – Part 8

5 October 2018

We thought we’d run an elementary series going through the rudiments of Visual Basic for Applications (VBA) as a springboard for newer users.  This blog looks at the concept of a variable scope at a module level.

 

Variables can also be used in different subroutines and functions.  If there are items that are known to have constant values throughout the entire workbook, they can be declared explicitly in one place for easy reference.

Module Level

Let’s write a new module as follows: 

Option Explicit

Sub ScopeTest()

    Dim myTestString As String

    myTestString = "Hello World!"

End Sub

 

Sub NextScopeTest()

    MsgBox myTestString

End Sub

The declaration for the variable is in ScopeTest but the subroutine NextScopeTest calling it is not the one that defined it.  What happens upon execution?

The ‘Variable not defined’ message pops up again.  Let’s move the declaration OUTSIDE the subroutine, under the ‘Option Explicit’ statement as follows:

 

Option Explicit

Dim myTestString As String

Sub ScopeTest()

    myTestString = "Hello World!"

End Sub

Sub NextScopeTest()

    MsgBox myTestString

End Sub

 

When NextScopeTest is run the following happens:

This is because though the variable has been declared, it has not been initialised. If ScopeTest is run prior to NextScopeTest then:

These shows that the variable is accessible to the entire module and is changed as required. The Dim statement outside any subroutines means the variable is accessible within the module that it is declared in.

Newsletter