WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA VARTYPE Function

VBA VARTYPE Function

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA VarType Function

VBA VARTYPE means “Variable Type”. This function helps us to identify the data type assigned to the specific variable or in simple word we can say it finds what kind of value is stored or assigned to the variable.

Syntax

VarType Syntax

VarName: We just need to supply the variable name to find the data stored in the supplied variable name.

So, it takes the variable name as the syntax or argument, and in the output, it returns the data type assigned to the variable or the kind of data stored in the variable.

So, if you have ever wondered how to find the variable data type or the kind of data assigned to the variable, then here we have a VBA function “VarType.”

VBA-VARTYPE

Examples

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

Example #1

In VBA, while writing the code, we usually declare variables and assign a data type to them. For example, look at the below VBA code.

Code:

Sub VarType_Example()

    Dim MyVar As String

    MyVar = "Hello"

End Sub

Example 1.1

In the above example, we have declared the variable as “String,” and for this string, we have assigned the value as “Hello.”

This is a straightforward case, but it is also possible to declare the variables without assigning the variables to them, so in such cases, the VarType function helps us.

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

Code:

Sub VarType_Example()

    Dim MyVar

    MyVar = "Hello"

End Sub

Example 1.2

In the above code, we have not assigned any data type but straight away assigned the value as “Hello,” so by using the VarType function, we can find the data type of the variable.

Open MSGBOX in VBA coding in the above code.

Example 1 (Open MsgBox)

Then open the VarType function.

Example 1 (Open Vartype)

Now enter the variable name as the argument of the VARTYPE function.

Code:

Sub VarType_Example()

    Dim MyVar

    MyVar = "Hello"

    MsgBox VarType(MyVar)

End Sub

Example 1.3

Now run the code and see what we get in the message box.

VBA VarType Output 1

We got the result as 8 because VBA has certain codes for each kind of variable data type, so below is the detailed list for you.

Value Constant Description
0 vbEmpty Variable is not yet initialized
1 vbNull Not valid data is assigned
2 vbInteger The variable value is “Integer” data type
3 vbLong The variable value is “Long” data type
4 vbSingle The variable value is “Single” data type
5 vbDouble The variable value is “Double” data type
6 vbCurrency The variable value is “Currency” data type
7 vbDate The variable value is “Date” data type
8 vbString The variable value is “String” data type
9 vbObject The variable value is “Object” data type
10 vbError The variable value is Error Value
11 vbBoolean The variable value is “Boolean” data type
12 vbVariant The variable value is “Variant” data type (used only with arrays of variants)
13 vbDataObject The variable value is the Data Access Object
14 vbDecimal The variable value is “Decimal” data type
17 vbByte The variable value is “Byte” data type
20 vbLongLong The variable value is “LONG LONG” data type (valid on 64-bit platforms only)
36 vbUserDefinedType The variable value is “User Defined” data type
8192 vbArray The variable value is Array.

Ok, now our code has retuned the variable data type as 8, i.e., the variable name “MyVar” contains the “String” data type.

Example #2

Now, look at the below code.

Code:

Sub VarType_Example2()

    Dim MyVar

    Set MyVar = ThisWorkbook

    MsgBox VarType(MyVar)

End Sub

Example 2.1

Let’s run this code and see what the result is.

VBA VarType Output 2

The result is 9, i.e., variable contained the “Object” data type. Yes, this is correct because, for the variable “MyVar,” we have set the workbook reference of “This Workbook.”

Example #3

Now, look at the below code.

Code:

Sub VarType_Example3()

    Dim MyVar

    MyVar = 32500

    MsgBox VarType(MyVar)

End Sub

VBA VARTYPE - Example 3

This will return the result as 2.

VBA VarType Output 3

Because the number 32500, which is assigned to the variable, is an “Integer” value.

Now I will change the value to 40000 and see the result.

Code:

Sub VarType_Example4()

    Dim MyVar

    MyVar = 40000

    MsgBox VarType(MyVar)

End Sub

VBA VARTYPE - Example 3.1

This will give the result as 3.

VBA VarType Output 4

Because Integer value is ending at 32767, so anything above that will be treated as a VBA LONG data type.

Now I will enclose the number in double-quotes.

Code:

Sub VarType_Example5()

    Dim MyVar

    MyVar = "40000"

    MsgBox VarType(MyVar)

End Sub

VBA VARTYPE - Example 3.2

Run the code and see the result.

VarType Output 5

We got the result as 8, i.e., String data type.

This is because anything supplied within the parenthesis will be treated as a String variable.

Things to Remember

  • VARTYPE stands for “Variable Type.”
  • Data Type is represented by unique numbers so refer to the table to understand which number represents which variable data type.

Recommended Articles

This has been a guide to VBA VarType. Here we learn how to find the variable data type or the kind of data assigned to the variable using the excel VBA VarType function with examples. You can learn more from the following VBA articles –

  • Variable Declaration in VBA
  • Public Variables in VBA
  • Global Variables in VBA
  • Return Statement in VBA
6 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 VARTYPE Excel Template

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