VBA Dim

Excel VBA Dim

DIM in VBA can be termed as it declares variable in different data types such as integer Boolean string or double etc. In any programming language, a variable needs to be declared to a specific data type such as X is a variable and if we define X as an integer which means we can store integer values in X. Now if we declare Y as a string it means we can store string values in Y.

As discussed above DIM in VBA is used to declare variables of different data types. But what is DIM in VBA? DIM means dimension or Declare in Memory in VBA. We declare a variable to a specific data type in VBA we use the DIM keyword to do it. We can use the classes structures already inbuilt in VBA or we can create a new one ourselves. To give a very basic explanation on DIM we can take this as an example, for instance, we need to store logical values in a variable. Logical values mean either they are true or they are false. Now logical values are Boolean in data types as we know, so we need to declare our variable in Boolean data type.

If we say X variable has to store the value in Boolean we need to declare X as a Boolean variable so that it can store the data type we want to.

Use of DIM with syntax in VBA is as follows:

DIM Variable As DataType.

When we try to define a specific data type excel pre types it for us.

Have a look at the screenshot below,

Excel determines from the keywords we type and displays the possible data types we might want to use.

How to Use VBA Dim in Excel?

We will learn how to use VBA Dim with a few examples in excel.

Excel VBA Dim – Example #1

First, let us use Integer as the data type to declare. We will use three variables and declare each of them as integers using DIM keyword. And then we will display the final output.

Tip: To use VBA in excel we need to have developer access enabled from the accounts option in the file tab.

Step 1: Go to the Developer tab and click on Visual Basic to open VBA Editor.

Step 2: In the project window click on sheet 1 to open the code window.

Step 3: When the code window opens up to declare the sub-function to start writing the code.

Code:

Sub Sample()

End Sub

Step 4: Declare three variables A, B and C as integers.

Code:

Sub Sample()

Dim A As Integer
Dim B As Integer
Dim C As Integer

End Sub

Step 5: Assign those three variables any random values.

Code:

Sub Sample()

Dim A As Integer
Dim B As Integer
Dim C As Integer
A = 5
B = 10
C = A + B

End Sub

Step 6: Display the values of C using the msgbox function.

Code:

Sub Sample()

Dim A As Integer
Dim B As Integer
Dim C As Integer
A = 5
B = 10
C = A + B
MsgBox C

End Sub

Step 7: Run the code from the run button, Once we run the code we get the following result as the output.

Step 8: As A, B and C were declared as integer and the sum of A and B is also an integer the value stored in C can be displayed. But if we change the value of C = A / B as shown in the screenshot below.

Step 9: Now let us run the code again and see what we get as the result,

We get zero as the result but 5 /10 is 0.5, not 0. It is because C can store only integer values not the values after the decimal.

Excel VBA Dim – Example #2

In the previous example, we saw that if the value of the result in C is in decimals the values after the decimals are not displayed in the output because we declared C as an integer. Now we will use a different data type to declare from DIM function to display the original value of C.

Step 1: Go to the Developer’s tab and click on Visual Basic to open VBA Editor.

Step 2: In the project window click on Sheet 2 to open the code window.

Step 3: Once the code window is open, create a sub-function to start writing the code.

Code:

Sub Sample1()

End Sub

Step 4: Declare three variables A B and C. Declare A and B as integers while C as double.

Code:

Sub Sample1()

Dim A As Integer
Dim B As Integer
Dim C As Double

End Sub

Step 5: Similar to example 1 assign the values to these variables.

Code:

Sub Sample1()

Dim A As Integer
Dim B As Integer
Dim C As Double
A = 5
B = 10
C = A / B

End Sub

Step 6: Now Display the output using the msgbox function.

Code:

Sub Sample1()

Dim A As Integer
Dim B As Integer
Dim C As Double
A = 5
B = 10
C = A / B
MsgBox C

End Sub

Step 7: Run the code from the run button. Once we run the code we get the following result,

Now we have the exact value of C which is 0.5.

Excel VBA Dim – Example #3

Let us use a DIM function to store characters means strings.

Step 1: Go to the Developer’s tab and click on Visual Basic to open VBA Editor.

Step 2: In the project window click on Sheet 3 to open the code window.

Step 3: Create a sub-function to start writing the code,

Code:

Sub Sample2()

End Sub

Step 4: Declare a variable A as a string,

Code:

Sub Sample2()

Dim A As String

End Sub

Step 5: Assign any random values to A,

Code:

Sub Sample2()

Dim A As String

A = "Anand"

End Sub

Step 6: Display the output using the msgbox function.

Code:

Sub Sample2()

Dim A As String

A = "Anand"

MsgBox A

End Sub

Step 7: Run the code from the run button provided and see the result,

Explanation of VBA DIM

DIM in VBA is used to declare variables as different data types. The variables can be a single variable or an array which we need to keep in mind to declare a proper data type in order to get proper output.

For example, if our output is going to be in String we need to declare the variable in String and if the output is going to be in decimals then declare the variable as double etc.

Things to Remember

There are a few things we need to remember about DIM in VBA:

• Excel pre-types the data type while using the dim function using the keystrokes we use.
• For specific data outputs, proper data types must be declared to get the desired result.
• A dim function is used to declare a single variable or an array.

Recommended Articles

This has been a guide to Excel VBA Dim. Here we discussed how to use Excel VBA Dim along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Use of VBA TIMER in Excel
2. Ways to Use VBA Color Index
3. Excel VBA MsgBox | Excel Trick
4. Examples to Learn VBA Select Case