VBA/Excel/Access/Word/Language Basics/Variable Scope

Local variable

 
Public Sub Procedure1()
    Dim intVariable1        As Integer
    Dim intVariable2        As Integer
    intVariable1 = 1
    intVariable2 = 2
    Debug.Print intVariable1
    Debug.Print intVariable2
End Sub
Public Sub Procedure2()
    Dim intVariable1        As Integer
    intVariable1 = 100
   Debug.Print intVariable1
End Sub
Public Sub TestLocal()
    Procedure1
    Procedure2
    Procedure1
End Sub

Local variables are available only in the procedure where they are declared

 
Private Sub cmdOkay_Click()
  Dim strAnimal As String
  strAnimal = "Dog"
  Call ChangeAnimal
  Debug.Print strAnimal "Still Dog
End Sub
Private Sub ChangeAnimal()
  strAnimal = "Cat"
End Sub

Module level variable

 
Option Explicit
Dim Sales
Sub Scope1()
  Sales = Sales + 1
  MsgBox Sales
End Sub
Sub Scope2()
  Sales = Sales + 10
  MsgBox Sales
End Sub

module-level variable declared with Private statement

 
Option Explicit
Private slsTax As Single  " module-level variable declared with Private statement
Sub CalcCost()
    "...Instructions of the procedure...
End Sub

Private module variable

 
Option Explicit
Private intModuleVariable As Integer
Public Sub TestProc1()
    intModuleVariable = intModuleVariable + 1
End Sub
Public Sub TestProc2()
    Debug.Print intModuleVariable
End Sub
Public Sub TestProc3()
    Dim intModuleVariable   As Integer
    intModuleVariable = 100
    Debug.Print intModuleVariable
End Sub

Procedure-Level (Local) Variables

 
Option Explicit
Dim slsTax As Single      " module-level variable declared with Dim statement
Sub CalcCost()
    "...Instructions of the procedure...
End Sub

Public Variables

 
Option Explicit
Public slsTax As Single
Sub PublicCalcCost()
    "...Instructions of the procedure...
End Sub

sub module scope

 
Sub LifeTime()
  Dim Sales
  Sales = Sales + 1
  MsgBox Sales
End Sub

Understanding and Using Static Variables

 
Sub CostOfPurchase()
    " declare variables
    Static allPurchase
    Dim newPurchase As String
    Dim purchCost As Single
    newPurchase = InputBox("Enter the cost of a purchase:")
    purchCost = CSng(newPurchase)
    allPurchase = allPurchase + purchCost
    " display results
    MsgBox "The cost of a new purchase is: " & newPurchase
    MsgBox "The running cost is: " & allPurchase
End Sub

Understanding Module-Level Variables

 
Sub ExpenseRep()
          Dim slsPrice As Currency
          Dim cost As Currency
      
          slsPrice = 55.99
          cost = slsPrice + (slsPrice * slsTax)
      
          MsgBox slsTax
          MsgBox cost
End Sub