Introduction

In this article we'll be discussing Excel VBA global variables, including what they are, how to declare them, and in what cases you'll utilize them. The prerequisites for this tutorial are very basic but check them out below.

Prerequisites

You should have a computer or a laptop with MS Excel installed and a basic knowledge working with Excel.

The Excel VBA Global Variable

Before we start our macro, the values declared in it are called VBA Global Variables. We can see this at the first line of our code, and by using public or global on your subprocedure the code becomes global variables to which sub procedure variables cannot use anywhere.

Variable on Sub Function/Sub Procedure

See the sample code:

    Sub example1()

        Dim NB As Integer
        NB = 10
        MsgBox NB

    End Sub

Code explanation:

• the below declares our subprocedure code:

  Sub example1()
  End Sub
  

• Dim NB As Integer - this defines as our integer. NB stands for Number.
• NB = 10 - this declares the value of our integer as ten.
• Msgbox NB - this tells VBA to give us a return in the message box for our integer (NB).

Let's take a look at the result after running the code:

Note: Since we use the code under the sub procedure, we used Dim as our keyword and that method is DeclaringAProcedureLevelVariable.

Variable on Module Level

Sample code number 1:

Option Explicit

Dim NB As Integer


    Sub Example2()

        NB = 10
        MsgBox NB

    End Sub

Let's take a look at the result after running the code:

Sample code number 2:

    Option Explicit

    Dim NB As Integer

    Sub example2()

        NB = 10
        MsgBox = NB

    End Sub

    Sub anotherexample2()

        NB = 20
        Range("A1").Value = NB

    End Sub

Let's take a look at the result:

On the above code, we have used a module-level variable together with our sub-procedure code:

Our variable declaration is located on the top on the declaration portion together with Option Explicit. And we have placed the subprocedure code under the option explicit section.

After running the sample code number 1, we got the result on the message box with number 10 as integer. While running sample code number 2 (if you have noticed we have two sub procedure code in the module) we have a result of our text shown on the cell A1 with the number 20 on it.

Variable on Global Level

Sample code number 1:

Option Explicit

Public theString As String

Sub example3()

    theString = ("Variable on Global Level Example")
    MsgBox "Variable on Global Level Example"

End Sub

The result after running the code:

Sample code number 2:

    Option Explicit

    Public theString As String

    Sub example3()

        theString = ("Variable on Global Level Example")
        MsgBox "Variable on Global Level Example"

    End Sub

    Sub anotherexample3()
        theString = "Hello"
        Range("A1").Value = "Hello"

    End Sub

The result of the code:

Sample code number 3:

Option Explicit

Public theString As String

Sub example3()

    theString = ("Variable on Global Level Example")
    MsgBox "Variable on Global Level Example"

End Sub

Sub anotherexample3()

    theString = "Hello"
    Range("A1").Value = "Hello"

End Sub

Sub anotherexampleusingglobalmodule()

    theString = "Hi and Hello"
    MsgBox theString

End Sub

The result after running the code:

If you have noticed, we have encoded three different sub-procedure code under one Option Explicit and by using module-level variables we can run any of the three codes encoded in it.

Do not forget that you need to put your variable declaration on top, under-declarations section with option explicit.

Conclusion

In this article we've showed you the basic usage of VBA global variables. We demonstrated Option Explicit, Public, and using variables within subroutines. Thank for joining us for another tutorial on Excel and VBA.