Access VBA Variables

Access VBA is a loosely typed language. Declaring variables is optional unless you use the Option Explicit statement to force explicit declaration of all variables with Dim, Private, Public, or ReDim. Using Option Explicit is strongly recommended to avoid incorrectly typing an existing variable and to avoid any confusion about variable scope.

Undeclared variables are variants. To specifically declare a variant, use:

Dim x As Variant

Dim x 

Common data types include Byte (0..255), Boolean, Integer (2-byte integers), Long (4-byte integers), Currency, Single (32-bit number), Double (64-bit number), Date, String, and variant.

Variables declared with Dim at the module level are available to all procedures within the module. At the procedure level, variables are available only within the procedure.

Require Variable Declaration

It is a good idea to declare variables. Using only declared variables prevents common mistakes. As the first line of code, add the following to force variable declarations with Dim:

Option Explicit

Add Option Explicit Automatically setting

While editing code, click Tools | Options and check the Require Variable Declaration checkbox to have Access automatically add the Option Explicit directive to all newly created code modules (not retroactive).