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

Declaring Variables

   <source lang="vb">

Sub DeclareVariable()

   Dim myVar As Integer

End Sub

</source>
   
  

Forcing Declaration of Variables: Option Explicit

   <source lang="vb">

Option Explicit Sub CalcCost() " revised CalcCost procedure

   " declaration of variables
   Dim slsPrice As Currency
   Dim slsTax As Single
   Dim cost As Currency
   Dim strMsg As String
   slsPrice = 35
   slsTax = 0.085
   cost = Format(slsPrice + (slsPrice * slsTax), "0.00")
   strMsg = "The calculator total is " & "$" & cost & "."
   MsgBox strMsg

End Sub

</source>
   
  

If you"re going to declare multiple variables on one line, make sure each variable is specifically declared

   <source lang="vb">

Sub dSub()

   Dim intCounter As Integer, intAge As Integer, intWeight As Integer

End Sub

</source>
   
  

To declare a variable in VBA use the Dim (short for Dimension) statement.

   <source lang="vb">

Sub inttype()

   Dim myVar As Integer

End Sub

</source>
   
  

Variable declaration is required with Option Explicit and a module level variable (answer) is declared.

   <source lang="vb">

Option Explicit Dim answer As Integer Sub Main()

  Dim num1 As Integer
  Dim num2 As Integer
  num1 = Val(InputBox("Please enter the first addend", "First Addend"))
  num2 = Val(InputBox("Please enter the second addend", "Second Addend"))
  Call AddUserInput(num1, num2)
  SendResult

End Sub Private Sub AddUserInput(num1 As Integer, num2 As Integer)

   answer = num1 + num2

End Sub Private Sub SendResult()

   MsgBox ("The answer is " & Str(answer))

End Sub

</source>
   
  

Variables can also be declared in the declarations section at the top of a module

   <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>