Excel VBA VarType Function

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 functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more “VarType.”

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA VARTYPE Function (wallstreetmojo.com)

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Code:

Sub VarType_Example()

Dim MyVar As String

MyVar = "Hello"

End Sub

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.

Sub VarType_Example()

Dim MyVar

MyVar = "Hello"

End Sub

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 VBAOpen MSGBOX In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more coding in the above code.

Then, open the VarType function.

Now, enter the variable name as the argument of the VarType function.

Sub VarType_Example()

Dim MyVar

MyVar = "Hello"

MsgBox VarType(MyVar)

End Sub

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

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.

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.

Sub VarType_Example2()

Dim MyVar

Set MyVar = ThisWorkbook

MsgBox VarType(MyVar)

End Sub

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

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

Sub VarType_Example3()

Dim MyVar

MyVar = 32500

MsgBox VarType(MyVar)

End Sub

It will return the result as 2.

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

Now, we will change the value to 40000 and see the result.

Sub VarType_Example4()

Dim MyVar

MyVar = 40000

MsgBox VarType(MyVar)

End Sub

It will give the result as 3.

The Integer value ends at 32767, so anything above that will treat as a VBA LONG data typeVBA LONG Data TypeLong is a data type in VBA that is used to store numeric values. We know that integers also store numeric values, but Long differs from integers in that the range for data storage is much larger in the case of long data type.read more.

Now, we will enclose the number in double quotes.

Sub VarType_Example5()

Dim MyVar

MyVar = "40000"

MsgBox VarType(MyVar)

End Sub

Run the code and see the result.

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.

This article 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 VBAPublic Variables in VBAGlobal Variables in VBAReturn Statement in VBA