VBA Dim

In Visual Basic for Applications, the term “dimension” is represented by the VBA Dim. It also helps to set the names and types of variables. The macro codes all begin with a VBA Dim.

Syntax

Dim [Insert Variable Name] as [Insert Variable Type]

The four varieties of Dim statements are as follows:

1. Basic Variables

It keeps only one value. It can be a Long String, Date, Double, or Currency.

2. Variant

VBA determines the data type at runtime. Even if it is best to avoid them, there are times when adaptation is necessary.

3. Object

It is a type of variable with several associated procedures (called “subs” or “functions”) and properties (values).

The Three Types of Objects are:

  • Excel objects (Workbook, Worksheet, and Range).
  • User objects
  • External libraries

4. Array

Array is a group of objects or variables.

Reasons for Using VBS Dim

Following is some of the most compelling evidence in support of declaring our program’s variables using VBA Dim:

  • The code is straightforward to comprehend.
  • It stores and organises data from a variety of sources.
  • This feature ensures that the software is free of typos.
  • It prevents inappropriate kinds of data from being stored and offers recommended coding conventions.
See More: Excel VBA Tutorial

Dim Statements Levels

There are three possible levels for declaring Dim statements, and they are:

1. Procedural Level

A variable declared at the procedural level is called a “local variable.” If the procedure has been finished, the variable can be deleted with the End statement. When a process that uses a local variable calls another method, the value of the local variable is preserved, but the variable is not made accessible to the called procedure. When declaring a local variable in a subordinate or function, the VBA Dim can also be utilised in static or private statements. Local variables might have the same name among subordinates.

2. Module Level

In the declaration section of a code module, variables at this level are created with the Dim or Private statement. These variables are private to the module and accessible only from its processes. However, the external module has no access to these variables. The most common usages of Private and Dim are at the module and procedure levels, respectively.

3. Project Level

It is where you will put all of your public variables and where you should only use public statements in your general declarations. The public keyword allows us to declare these variables at the very start of the default module. It can be used for any module and will be deleted when the workbook is closed.

Example

An integer is a type we employ when declaring the data itself. Take three integer variables that you have declared using the Dim keyword.

Step 1: Declare the integer variables x, y, and z.

Sub Dim_Example1()

Dim x As Integer

Dim y As Integer

Dim z As Integer

x = 15

y = 20

z = x + y

End Sub

Step 2: The msgbox function helps show the value of the z variable.

Sub Dim_Example2()

Dim x As Integer

Dim y As Integer

Dim z As Integer

x = 15

y = 20

z = x + y

MsgBox z

End Sub

Step 3: Hit the Run button, and the code produces the following results.

 

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top