VBA VARTYPE FUNCTION

What is Excel VBA VarType Function?

In Excel VBA (Visual Basic for Applications), the VarType function is used to determine the data type of a variable or an expression. It returns an integer value that corresponds to a specific data type. The returned integer value corresponds to a specific data type, and you can use constants provided by VBA to interpret the result.

Consider the following example:

A subroutine is created to find the VarType of a string. The result will be 8 for string. This value will be printed in the Immediate tab.

How to Use Excel VBA VarType Function?

You can learn how to use the VBA VarType function by following the steps below.

Step 1: Open the Developer tab once you’re in the Excel Workbook. It is available in the Excel toolbar. In Developer, select “Visual Basic.”

It opens the VBA Editor. Then, select “Insert” in the toolbar, and then in the drop-down, select “Module.”

You can follow the steps to learn the VBA VarType function in Excel VBA. Considering you have a set of values in a table in Excel, you want them to return the variable type. It can be done using the VBA VarType function.

Step 2: Start by creating a subroutine to print the variable type values in the second column.

Step 3: Initialize an Integer and a Variant variable. The variant variable is used to store the values returned by the VarType function.

Step 4: Start a FOR-loop throughout the range.

Step 5: Assign the variable type values to the variant variable.

Using the VBA Cells function in the for-loop, the row index increases, while the column index stays the same in column 1. The values in these cells are put in the VBA VarType function.

Step 6: Assign these values to the adjacent column in the same row and continue the loop.

Code:

Sub FindTheVar()

    Dim i As Integer

    Dim check

    For i = 2 To 9

        check = VarType(Cells(i, 1))

        Cells(i, 2) = check

    Next i

End Sub

Step 7: Click “F5” or the “Run” icon above the VBA Editor. Then, go back to the worksheet to view the results.

Examples

See some more examples of how VBA VarType is being used to write functional codes in Excel VBA.

Example #1

Given a value, find its datatype using the VBA VarType function. It can be used by combining the VBA Select Case function and finding the result.

Step 1: Start by naming the subroutine to print the datatype of a variable.

Step 2: Define a Variant variable. Here, the variable is declared as a Variant data type so that we can type in any value and it will accurately find the type of variable without coding it into a string or getting a type mismatch error in VBA.

Step 3: Provide a value of any type. Here, for the example, a double data type value is inserted.

Step 4: Start a VBA Switch Case function with the variable equated into the VBA VarType function.

Step 5: Declare a case for an empty, uninitialized variable.

The VBA VarType function returns integer values to which VBA can assign datatypes, such as vbEmpty, vbString, etc.

Step 6: Declare a case for a string value. Then print the datatype in MsgBox.

Step 7: Similarly, if the input is an integer, assign a case for integer values.

Step 8: Do the same for Double and boolean.

Step 9: Declare a default value in the Switch case in case it is a user-defined datatype or others. Then, end the Switch case.

Code:

Sub ExampleVarType()

    Dim myVar As Variant

    myVar = 32767.057

    Select Case VarType(myVar)

        Case vbEmpty

            MsgBox “Empty”

        Case vbString

            MsgBox “String”

        Case vbInteger

            MsgBox “Integer”

        Case vbDouble

            MsgBox “Double”

        Case vbBoolean

            MsgBox “Boolean”

        Case Else

            MsgBox “Other”

    End Select

End Sub

Step 10: Run the above subroutine. A VBA message box will pop up with the data type based on the input you’ve given.

Here, the value is a VBA Double data type.

Example #2

With different types of input, print their data types by initializing the VarType function and then printing their values.

Step 1: Start declaring a subroutine to

Step 2: Define a Variant datatype to store the values from the VarType function.

Step 3: Initialize values to check their datatypes.

Here, 5 data types are defined:

• Integer
• String
• Date
• Object
• String Array.

Step 4: Find the variable type of the variables defined above and then print them in the immediate tab.

Step 5: Similarly, assign the variable type of the date variable to assign it to the variant variable and then print it in the immediate tab.

Step 6: Also, find the variable type of the string variable and print the value in the Immediate tab.

Step 7: Find the variable type of the application object variable and then assign it to the variant variable. Then, print it in the Immediate tab.

Despite being an Object variable, it will still give the value of the string variable since the application value is a string.

Step 8: Find the variable type of the array, a variant array and then print its value in the immediate tab.

Here, the value of the variant array is added to get the value equal to 8192 (vbArray) + 12 (vbVariant) = 8204 (Variant Array).

Code:

Sub Example_VarType()

    Dim MyCheck

    IntVar = 123

    StrVar = “Hello World”

    DateVar = #12/12/2023#

    AppVar = Excel.Application

    ArrayVar = Array(“x”, “y”, “Welcome to Excel VBA Tutorial”)

    MyCheck = VarType(IntVar)

    Debug.Print “Value of the Integer is: ” & MyCheck

    MyCheck = VarType(DateVar)

    Debug.Print “Value of the Date variable is: ” & MyCheck

    MyCheck = VarType(StrVar)

    Debug.Print “Value of the String variable is: ” & MyCheck

    MyCheck = VarType(AppVar)

    Debug.Print “Value of the Object variable is: ” & MyCheck

    MyCheck = VarType(ArrayVar)

    Debug.Print “Value of the Array variable is: ” & MyCheck

End Sub

Step 9: Click the green arrow button to run the above subroutine. The values will be printed in the Immediate tab as shown.

Example #3

You can do a fun activity to randomly find whether a randomly generated variable is of different data types. Using the VBA Randomize function you can generate random values and then equate them with a select case to check whether the given values are integer, double, string, and so on.

Step 1: Name the subroutine to randomly generate variables.

Step 2: Initialize variant and integer variables.

Step 3: Start the randomize function. This will generate random values which can be accessed with the “Rnd” variable. The Rnd variable generates a random number between 0 and 1.

Step 4: To generate five values of random data types, initialize a FOR loop from 1 to 5.

Step 5: Start with declaring a select case in VBA.

Using the Rnd variable, it is scaled from 0 to 3 by multiplying 3 by the random variable. Add 1 to the resulting product to generate a random value between 1 and 3.

The Int function in VBA converts the resulting value into a whole number, with which you can generate numbers from 1 to 3.

Step 6: Declare a case when the value is generated as 1.

In this case, the Int function is called again, and the random value is multiplied by 100.

Step 7: Declare another case when the value is generated as 2.

The random value generated is converted to a double variable using the VBA CDbl function. Multiply this value by 100.

Step 8: In case the value is returned as 3 declare another case for it.

In this case, concatenate the word “String” and multiply 10 by the random variable. Then, end the switch case.

Step 9: Print the value and its corresponding data type and variable type in the immediate tab.

A newline character sequence is represented in VBA using the vbCrLf constant. When you wish to construct multiline text or structure the output in a way that includes line breaks, you use it to insert a line break or carriage return in strings.

“CrLf” refers to Carriage Return and Line Feed, which are control characters used to mark the end of a line and advance the cursor to the start of the following line in text files and communication protocols.

Then, continue the FOR-loop.

Code:

Sub RandomDataTypeExample()

    Dim randomValue As Variant

    Dim i As Integer

    Randomize

    For i = 1 To 5

        Select Case Int((3 * Rnd) + 1) ‘ Randomly select a data type (1 to 3)

            Case 1

                randomValue = Int(100 * Rnd)

            Case 2

                randomValue = CDbl(Rnd) * 100

            Case 3

                randomValue = “String” & Int(10 * Rnd)

        End Select

        Debug.Print “Random Value: ” & randomValue & vbCrLf & _

               “Data Type: ” & TypeName(randomValue) & vbCrLf & _

               “VarType: ” & VarType(randomValue) & vbCrLf

    Next i

End Sub

Step 10: Run the subroutine to generate values in the Immediate tab as shown.

With this, you can use VBA VarType function to generate random values and random data types.

Important Things To Note

• Use VarType when you need a quick assessment of a variable’s data type, especially in scenarios where explicit type conversion is not necessary.
• When dealing with custom data types or objects, be aware that the VBA VarType function returns vbObject.
• Use VarType in combination with IsEmpty or check for the default value of the specific data type to handle uninitialized variables.
• Do not neglect error handling in VBA when using VarType. Unexpected values or scenarios might arise, so it’s crucial to handle errors gracefully.
• While the VBA VarType function returns vbNull for null values, nulls are not commonly encountered in standard VBA programming. Be cautious and verify null handling separately if needed.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA VarType Function. Here we learn How to use Excel VBA VarType Function, with step-by-step examples & points to remember. You can learn more from the following articles –

• VBA Variable Range
• VBA Variable Types
• VBA Public Variables