Declaring Variables

A variable is a named storage location that can contain data that can be modified during program execution. Each variable has a name that uniquely identifies it within its scope. By giving your variable a suitable name you make the program easier to understand. Also, the name must follow some simple VBA naming rules When declaring a variable, specifying the data type is advised but not mandatory. After having been declared, the variable is available for use, primarily asigning values.

Declaring variables in the procedure body

In the following we will discuss declaring a variable inside a procedure using the Dim statement. Placing the variable inside a procedure makes it only accessible from within the same procedure, which is safer than module-level.

The variable declaration is located somewhere between the start and the end of the procedure after the Sub or Function keyword, as shown below. The variable must be declared before it is used (e.g. given a value). Usually a variable is either located at the top of the procedure body, or it is placed close to where it is used. The variable is released from memory after the procedure is executed. And when starting it again it will obtain a new value.

Sub MyProcedure()
    Dim intAge As Integer
    intAge = 24
    Dim intCounter As Integer
    For intCounter = 1 to intAge
End Sub

Option Explicit

In the coding window you often see 'Option Explicit' declared at the top. With Option Explicit specified, the VBA compiler requires you to declare all variables before they can be used. If you now attempt to use an undeclared variable name, an error occurs at compile time. This is useful because it prevents mistakes such as incorrectly typing the name of an existing variable and so having 2 different variables without being aware of it.

Tip:In the VB Menu: Tools » Options... - check 'Require Variable Declaration' to have 'Option Explicit' automatically added to each new module.

Declaring constants

Constants have fixed values.

Const mintDaysInYear As Integer = 365

Using constants in your code instead of using their values directly has several advantages:

  • not having to change the value in multiple locations if that is needed,
  • the name makes clear the purpose of the value and thus prevent mistakes. The same value in different locations may actually play different roles.

You can declare a constant within a procedure or at the top of a module, in the Declarations section. The objection against using module level declaration that applies to variables does not apply to constants. Module-level constants are private by default. To declare a public module-level constant, precede the Const statement with the Public keyword.

Specifying the data type

All variables and constants have a data type: the characteristic of a variable that determines what kind of data it can hold. It is specified by the optional As ... part of the declaration. If it is not explicitly specified, the type is 'Variant', meaning any value can be stored in it.

Commonly used data types are String for text, Date for date and time, Integer or Long for numbers, Single for floating numerics, Boolean for items that can be either True or False. For a more elaborate description visit the VBA Data type page.