Download Microsoft Excel VBA User Manual

Excel Visual Basic for Applications
VBA Memory Variables and Constants
The role of Option Explicit
You can use implicit variables in VBA by just typing-in an identifier and assigning a value
to it. However you will not be able to do this if the Option Explicit statement is present.
Option Explicit forces you to declare your variables before you can use them. It is used to
improve the execution speed and precision of the code. You can delete Option Explicit if
you wish and continue with implicit variables. Otherwise you must declare.
To include the Option Explicit statement on all future modules:
1. Choose Tools, Options, Editor Tab.
2. Check the Require Variable Declaration checkbox.
Variable Declaration
The Dim statement is used to declare variables either in a single line or listing form.
Explicitly declared variables are available in the Complete Word lists. Dim is short for
Dimension (which makes no particular sense unless the variable is an array, a variable
that can have more than one dimension). You can place the Dim statement anywhere in
the procedure, so long as you declare the variable before you use it. It is a convention to
list declaration statements at the start of the procedure.
Dim x, y, z
or
Dim x
Dim y
Dim z
Option Explicit
Sub Main()
Dim x, y, z
x = 50
y = 100
z = Application.Average(x, y)
MsgBox z
'View variable values in the Locals Window.
End Sub
Data Types
You can also declare the Type of data you intend to store within a Variable or Constant.
This will ensure you use only the memory required to hold the data and validate the
data. It will also cause problems if you do it incorrectly.
If you do not specify a data type, the Variant data type is assigned by default.
The Data Type is declared in the same statement as the variable or constant itself.
Dim MyVar
As String
Const MyNum As Integer = 5
You can also use one declaration statement for several variables:
Dim MyVar As String, MyNum As Integer
However, when using a single declaration statement, you must declare the Data Type for
each variable. In the following example, only one variable has a defined Data Type, the
other is Variant.
Dim MyVar, MyNum As Integer
Page 27