WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Variable Types

VBA Variable Types

Excel VBA Variable Types

VBA Variables are like an address for storage of data and data can be in many forms like numerical or string or characters etc. so how does a code know what value or data to be stored in which variable, this is done by different variable types or also known as data types which are used to store the data as per type, for example, a string variable type will store string value while an integer data type will store an integer value and so on.

In order to code efficiently, declaring variables and assigning data types to those declared variables are key to go a long way in VBA coding.  In this article, we will show you what variables is, what data type is, and how to assign a data type to those variables.

As the name itself says, the variable will varies from time to time, and we store some value into those variables. To understand this better, let’s remember our “mathematic” classes, where we assume variable “x = something,” so whenever we use the “x” variable, it would be equal to the value we have assigned.

VBA Variable Types

What is Data Type?

The data type is the restriction we put on hold the variable. For example, for the declared variable, we can restrict to hold only “Date Values,” “Integer Values,” “Long Values,” “String Value,” etc.…

The types of data that a variable may hold is called “Data Type” in VBA.

It has many types. In coding, it is important to understand what each data type can hold. We can classify the data types in two ways i.e.

#1 – Non-Numerical Data Types

These data types can hold only non-numerical data. These are common non-numerical data types, i.e., String, Boolean, Variant, Object.

  • String: This can hold two kinds of string values in it, i.e., String with fixed length and string with variable length.
  • Boolean: Booleans in VBA are logical values, i.e., either TRUE or FALSE.
  • Variant: It can hold both numerical and non-numerical data.
  • Object: Object variables are products of Microsoft. For example, in excel, objects are “Worksheet, Workbook, Range.” Microsoft Objects are “MS Word, MS PowerPoint, and MS Outlook.”

#2 – Numerical Data Types

These data types can hold only numerical data. Below are numerical data types, i.e., Byte, Integer, Long, Single, Double, Date, Currency, Decimal.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Byte: This is a small capacity variable where the declared variable can hold values from 0 to 255.
  • Integer: This is the improved version of the Byte data type. This can hold values from -32768 to 32768. If any decimal values are assigned, then it will convert to the nearest integer value. For example, 5.55 will be converted to 6, and 5.49 will be converted to 5.
  • Long: Where Integer data types limits its value at 32768 LONG can hold very long numbers from -2,147,483,648 to 2,147,483,648.
  • Single: Single data type can hold two decimal places -3.402823E+38 to -1.401298E-45 for negative values and 1.401298E-45 to 3.402823E+38 for positive values.
  • Double: Double data type can hold more than two decimal places i.e. up to 14 decimal places. -1.79769313486232e+308 to -4.94065645841247E-324 for negative values and 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
  • Date: This data type can hold only DATE values.
  • Currency: This data type can hold values from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
  • Decimal: Decimal data types can hold up to 28 decimal places. It can hold from +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335.

How to Define Variable & Assign Data Type in VBA?

The most important thing to know is to define the variable during coding. We can define the variable types in two different ways, i.e., Implicitly and Explicitly.

#1 – Implicitly

We can declare the VBA variable implicitly, i.e., without using the “DIM” word. Dim stands for “Dimension.” For example, look at the below image.

Code:

Sub Data_Type()
  k = 45
End Sub

VBA Variable Types Implicit Example 1.0.1

#2 – Explicitly

This is a proper way of declaring a variable. I would call it an official and professional way. To declare a variable, we have to use the word “DIM” and assign a data type to the variable.

Code:

Sub Data_Type()
  Dim k As Integer
  k = 45
End Sub

Example 1.1.0

We have defined the variable “k” as you can see in the above image and assigned the data type as “Integer.”

Rules to Define Variable

  • Variable cannot contain any space character.
  • The variable should not contain any special characters except “underscore” (_)
  • The variable should not start with numerical character.
  • The variable should not directly contain any VBA keywords.

Examples

You can download this VBA Variable Types Excel Template here – VBA Variable Types Excel Template

Example #1

To define any variable, we need to first use the word “Dim” and followed by a variable name.

Code:

Sub Data_Type()
  Dim var
End Sub

VBA Variable Types Example 1

Next, once the variable name is given, we need to assign a data type. As we discussed above, we can assign any data type.

Code:

Sub Data_Type()
  Dim var As Integer
End Sub

 Example 1.1

I have assigned the data type as an Integer. So now you need to remember the limitations of the Integer variable, i.e., it can hold values between -32768 to 32768.

Code:

Sub Data_Type()
  Dim var As Integer
  var = 25000
End Sub

VBA Variable Types Example 1.2

In the above image, I have assigned 25000, which is well within reach, but the moment you enter the value more than the limit, it will cause an overflow error in VBA.

Code:

Sub Data_Type()
  Dim var As Integer
  var = 35000
End Sub

 Example 1.3

Now you can run this code using shortcut key F5 or manually to see the result.

VBA Variable Types Example1

Overflow is nothing but the assigned value of data type is more than its capacity.

Example #2

Similarly, we cannot assign different values also. For example, we can not assign the “String” value to the integer data type variable. If assigned, we will get “Type Mismatch Error.”

Code:

Sub Data_Type1()
  Dim var As Integer
  var = "Hii"
End Sub

 Example 1.4

Now run this code through shortcut key F5 or manually, to see the result.

VBA Variable Types Example 2

Things to Remember

  • Always use the DIM word to define the variable.
  • Before assigning data type, make sure what kind of data you are going to store in it.
  • Assigning more than the capacity value to the data type causes overflow error, and assigning a different value to the data type causes “Type Mismatch Error.”

Recommended Articles

This has been a guide to VBA Variable Types. Here we discuss how to define the variable and assign data type in Excel VBA with the help of practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • VBA Solver
  • Public Variables in VBA
  • Global Variables in VBA
  • Named Range in VBA
10 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA Variable Types Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More