EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Array Functions VBA Dim
Secondary Sidebar
VBA Array Functions
  • VBA Arrays
    • VBA Arrays
    • VBA ReDim
    • VBA Dim
    • VBA Sort
    • VBA Array Length
    • VBA ArrayList
    • VBA UBound
    • VBA Join
    • VBA Collection
    • VBA Declare Array
    • VBA ReDim Array
    • VBA Array Length
    • VBA Dynamic Array
    • VBA Filter
    • VBA Lbound

VBA Dim

By Madhuri ThakurMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

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,

Sample

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.

You can download this VBA Dim Excel Template here – VBA Dim Excel Template

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.

Example 1-1

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

VBA Dim Example 1-2

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

Code:

Sub Sample()

End Sub

VBA Dim Example 1-3

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

VBA Dim Example 1-4

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

VBA Dim Example 1-5

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

VBA Dim Example 1-6

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

Result of Example 1-1

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.

VBA Dim Example 1-8

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

Result of Example 1-2

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.

VBA Dim Example 1-1

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

VBA Dim Example 2-2

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

Code:

Sub Sample1()

End Sub

VBA Dim Example 2-3

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

VBA Dim Example 2-4

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

VBA Dim Example 2-5

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

VBA Dim Example 2-6

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

Result of Example 2

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.

Example 3

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

VBA Dim Example 3-2

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

Code:

Sub Sample2()

End Sub

VBA Dim Example 3-3

Step 4: Declare a variable A as a string,

Code:

Sub Sample2()

Dim A As String

End Sub

VBA Dim Example 3-4

Step 5: Assign any random values to A,

Code:

Sub Sample2()

Dim A As String

A = "Anand"

End Sub

VBA Dim Example 3-5

Step 6: Display the output using the msgbox function.

Code:

Sub Sample2()

Dim A As String

A = "Anand"

MsgBox A

End Sub

VBA Dim Example 3-6

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

Result of Example 3

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. VBA TIMER
  2. VBA Color Index
  3. VBA MsgBox
  4. VBA Select Case
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more