Vba variable scope in Excel

The term "Scope" in Excel VBA defines the visibility or accessability 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

  1. Public str As String
  2. Public nbr As Integer
  3. Sub sub1()
  4. str = "I am String Variable 'str'" & vbNewLine & "Am in sub1"
  5. MsgBox str
  6. sub2
  7. End Sub
  8. Sub sub2()
  9. str = "But Now I am in sub2" & vbNewLine & "I am accessible across the Sub" & vbNewLine & "because I am a PUBLIC variable!!"
  10. MsgBox str
  11. 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.

  1. Sub local_var()
  2. Dim var As Integer
  3. Dim str As String
  4. Dim dbl As Double
  5. Dim obj As Object
  6. MsgBox "var, str, dbl and obj are Local variables"
  7. For i = 1 To 1
  8. MsgBox "In for loop 1 : i is " & i
  9. Next i
  10. For i = 4 To 4
  11. MsgBox "In for loop 2 : i is " & i
  12. Next i
  13. 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

excel vba variable scope

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

 

Here the previous and next chapter