Excel Made Easy

VBA variable scope in Excel

The term "Scope" in Excel VBA defines the visibility or accessibility of a variable or procedure within the project

This feature is most important in VBA as it defines the rule to declare a variable or a procedure depending on how to access those members.It is similar to Object Oriented Programming concept.

Some times a variable has to be accessed across different subroutines and sometimes, the variables and subroutines has to be confined and restricted access from other members within the program. This is done by assigning appropriate scope to them.

There are broadly two scopes:

1.Public

As the name implies, a variable or subroutine declared as public is accessible across the entire project, that is, from different functions or subroutines.One example is when a program is working on attendance statement of a single person, here, the person name is stored in a public variable so as to enable all the subroutines to access that variable.

The following example illustrtates this

Public str As String
Public nbr As Integer
Sub sub1()
str = "I am String Variable 'str'" & vbNewLine & "Am in sub1"
MsgBox str
sub2
End Sub
Sub sub2()
str = "But Now I am in sub2" & vbNewLine & "I am accessible across the Sub" & vbNewLine & "because I am a PUBLIC variable!!"
MsgBox str
End Sub

2.Local Variables

As the name indicates, these are accessible within the sub or function where they are declared.

All Variables declared within the sub are local variables and cannot be accessed from any other sub.

Also, there is a provision in VBA where you can use a named variable in a for loop without declaring it even as local variable.

By this, that variable scope is confined within that for loop alone and the same name can be assigned to any other variable.

The following example illustrates this.

Sub local_var()
Dim var As Integer
Dim str As String
Dim dbl As Double
Dim obj As Object
MsgBox "var, str, dbl and obj are Local variables"
For i = 1 To 1
MsgBox "In for loop 1 : i is " & i
Next i
For i = 4 To 4
MsgBox "In for loop 2 : i is " & i
Next i
End Sub

Note from the above example that the variable "I" is treated as two different variables in the for loop.

excel vba variable scope

The outtput Is as shown below:

excel vba variable scope

You can find similar Excel Questions and Answer hereunder

1) How to use logical operators in VBA

2) How to format variable with VBA with the very useful format function. Define the number of decimal, the way a date is formated

3) Various important math functions in VBA (trigonometry, algebra, exponential, �)

4) How can worksheet functions be accessed in VBA?

5) Here we show you advanced VBA array functions, like splitting arrays, Lbound, Ubound

6) How to modify strings in VBA, what are the functions you can manipulate strings with

7) Declaring variable and datatypes in Excel

8) What is a function in VBA. How to create a function and how to call a function

9) What are the various functions of the worksheet in Excel VBA

10) What are the date and time function in Excel VBA

VBA variable scope in Excel

You can find similar Excel Questions and Answer hereunder

Here the previous and next chapter