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

Local variable

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Module level variable

   <source lang="vb">

Option Explicit Dim Sales Sub Scope1()

 Sales = Sales + 1
 MsgBox Sales

End Sub Sub Scope2()

 Sales = Sales + 10
 MsgBox Sales

End Sub

</source>
   
  

module-level variable declared with Private statement

   <source lang="vb">

Option Explicit Private slsTax As Single " module-level variable declared with Private statement Sub CalcCost()

   "...Instructions of the procedure...

End Sub

</source>
   
  

Private module variable

   <source lang="vb">

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

</source>
   
  

Procedure-Level (Local) Variables

   <source lang="vb">

Option Explicit Dim slsTax As Single " module-level variable declared with Dim statement Sub CalcCost()

   "...Instructions of the procedure...

End Sub

</source>
   
  

Public Variables

   <source lang="vb">

Option Explicit Public slsTax As Single Sub PublicCalcCost()

   "...Instructions of the procedure...

End Sub

</source>
   
  

sub module scope

   <source lang="vb">

Sub LifeTime()

 Dim Sales
 Sales = Sales + 1
 MsgBox Sales

End Sub

</source>
   
  

Understanding and Using Static Variables

   <source lang="vb">

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

</source>
   
  

Understanding Module-Level Variables

   <source lang="vb">

Sub ExpenseRep()

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

End Sub

</source>