VBA VARTYPE Function

Publication Date :

Blog Author :

Download FREE VBA VARTYPE Function Excel Template and Follow Along!
VBA VARTYPE Function Excel Template

Table Of Contents

arrow

Excel VBA VarType Function

VBA VARTYPE means “Variable Type.” This function helps us to identify the data type assigned to the specific variable. Simply put, it finds what kind of value is stored or assigned to the variable.

Syntax

VarType Syntax

VarName: We 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 Function
You are free to use this image on your website, templates, etc.. Please provide us with an attribution link.

Examples

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.” For this string, we have assigned the value “Hello.”

It 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.

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 variable's data type.

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.

ValueConstantDescription
0vbEmptyVariable is not yet initialized
1vbNullNot valid data is assigned
2vbIntegerThe variable value is "Integer" data type
3vbLongThe variable value is "Long" data type
4vbSingleThe variable value is "Single" data type
5vbDoubleThe variable value is "Double" data type
6vbCurrencyThe variable value is "Currency" data type
7vbDateThe variable value is "Date" data type
8vbStringThe variable value is "String" data type
9vbObjectThe variable value is "Object" data type
10vbErrorThe variable value is Error Value
11vbBooleanThe variable value is "Boolean" data type
12vbVariantThe variable value is “Variant” data type (used only with arrays of variants)
13vbDataObjectThe variable value is the Data Access Object
14vbDecimalThe variable value is “Decimal” data type
17vbByteThe variable value is "Byte" data type
20vbLongLongThe variable value is “LONG LONG” data type (valid on 64-bit platforms only)
36vbUserDefinedTypeThe variable value is “User Defined” data type
8192vbArrayThe variable value is Array.

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., the variable contained the "Object" data type. Yes, this is correct because, for the variable "MyVar," we have set the workbook reference to "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

It will return the result as 2.

VBA VarType Output 3

The number 32500 assigned to the variable is an "Integer" value.

Now, we 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

It will give the result as 3.

VBA VarType Output 4

The Integer value ends at 32767, so anything above that will treat as a VBA LONG data type.

Now, we 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.

Anything supplied within the parenthesis will treat as a String variable.

Things to Remember

  • The VarType stands for "Variable Type."
  • The unique numbers represent Data types, so refer to the table to understand which number represents which variable data type.