VBA for Excel 2007 tutorial-Declaring variables
In VBA, a variable can be declared at two levels—local and global.
-Declaring a variable at Local Level: At local level a variable is declared in Sub-procedure between Sub and End Sub or Function and End Function. By declaring the variable at the local level, the variable scope is limited to only the sub-procedure or function. This means that the variable can’t be used outside the sub-procedure or function. The variable declaration prototype is: Dim variable_name As DataType
Example: To declare a variable called x to store integer value, you can write as shown below:
Dim x As Integer 'Declare a variable called x to store an integer value
End sub Note:
The sign ' is called comment. Comments can used to explain the code or to exclude the code from being executed. -Declaring a variable at Global Level: You can declare a variable at the global level by declaring it in the Form module or the Standard module. +Form module:
In the Form module, you will declare a variable in the general section of the form. By declaring the variable in the Form module, the variable can be accessed from any code block of the form but can’t be accessed from other forms.
Example: To declare a variable called lstName to store string value in the Form module, you can write as seen below:
Dim lstName As String
+Standard module: To declare a variable in the Standard module, you need to create a module by using the Visual Basic Editor:
In the Standard module, you need to declare the variable with public or private keyword instead of Dim keyword. By declaring with the public keyword the variable can be accessed through out your application. However, with private keyword the variable can be accessed only within its module. The variable declaration in the Standard module can be generally written as shown below:
Public Variable_name As Datatype
Private Variable_name As Datatype
Example: To declare a variable called PI to store a float value in the Standard module, you can write:
Public PI As Single