VBA Dim

VBA Dim is a keyword that is used for the word dimension. It is also used to declare variable names and their data types. VBA Dim is applied at the starting of the macro codes. VBA Dim follows the following syntax, such as:

There are four types of Dim statements, such as:

1. Basic Variables: This type of variable holds only one value. It can be of types such as Long, String, Date, Double, Currency.
2. Variant: VBA decides during the execution which type will be used. We should avoid modifications, but in some instances, it is required to use.
3. Object: Object is a variable that can have multiple methods (subs or functions) and properties (values). There are three kinds, such as:
   • Excel objects (Workbook, Worksheet, and Range).
   • User objects which are created by using the Class modules.
   • And external libraries, i.e., Dictionary.
4. Array: Array is a group of objects or variables.

Why Use Dim

Below are some main reasons to use VBA Dim in our program and declare the variables, such as:

1. Easy to understand the code.
2. It keeps a record of the variables and organized them.
3. It prevents spelling errors in the program.
4. It stops the storage of the wrong type of data.
5. It provides the best practices of standard coding.

Dim Statements Levels

Dim statements can be declared at the three levels, such as:

1. Procedural Level: A local variable is that variable that is declared at the procedural level. If the task is completed, then we can destroy the variable by using the End statement. If the procedure contains the local variable calls another procedure, then the variable value is retained, but the variable is not available to that other procedure. The VBA Dim can also be used in static or private statements within the subordinate and function to declare a local variable. We can use the same variable name in different subordinates without any issue in the case of the local variable.
2. Module Level: This level variables are created by using Dim or Private statement in the declaration section of a code module. These variables are only visible within the module and available for all procedures within that module. But these variables are not accessible from the outsider module. In general, we use Private at the module level and Dim at the procedural level.
3. Project Level: This level variables are the public variables, and it is created by using only public statements in the general declarations section of a code module. By using the Public keyword, we can place these variables at the top of the standard module. It is available for all procedures in all modules, which will be destroyed after closing the workbook.

Example

To declare as the data type, we use Integer. Consider three variables and declare each of them as integers with the help of Dim keyword.

Step 1: Declare the variables A, B, and C as integers and assign their values.

Step 2: Display the value of the C variable with the help of the msgbox function.

Step 3: Run the code using the Run button, and we get the following output.

Private VBA Variable

A variable that is not accessible within the scope in which it was declared. In VBA, Private variables can be declared in ALL ranges except for Subs and Functions. Below is the example of private variables:

Public VBA Variable

A variable that is accessible within all scopes except Private variables. In VBA, Public variables can be declared in all scopes except for Subs and Functions. Let’s go through an example of using Public variables, such as: