EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Coding VBA Variant
 

VBA Variant

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Variant

Excel VBA Variant Data Types

In VBA, we have different types of Variable data types. We use them when we need to specify some certain kind of input to be given. Suppose, for the whole number we use Integer, for the text we use String and for lengthy data set we use Long data type. And there are some more data types that we use different types of variable declaration. But what if I tell you that we can define all these variables in a single Data type. For that purpose, we have VBA Variant where we can define any type of variable which we want.

 

 

VBA Variant is as easy as using other data types. For defining any kind of variable use any name or alphabet to name it and then we choose data type which we want. Let’s see an example where we will see how a variable can be declared using Integer data type.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Integer Data Type

As we can see in the above screenshot, for Integer data type variable, we can use number ranging from -32768 to +32767. But if we choose a variant here instead of Integer, then it will work same as Integer but there will not be any limit as data type Variant consists of all kind of variable formation in it.

And Variant can be used as shown below.

Variant Data Type

How to Declare Variant Data Type in Excel VBA?

We will summarize the whole process of declaring variables in VBA by using VBA Variant. Let’s see an example where we will use traditional data types for declaring variables first.

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

Steps to Declare Variant Data Type

Follow the below steps to declare Variant Data Type in Excel by using VBA code.

Step 1: Go to the VBA window, under the Insert menu tab select Module as shown below.

VBA Variant Example 1-1

Step 2: Now write the subprocedure for VBA Variant in any name as you want. We have used the name which can define the process which uses.

Code:

Sub VBA_Variant1()

End Sub

VBA Variant Example 1-2

Step 3: Now define a variable where we can store or print any kind of text or name. For that, we need to use a string data type.

Code:

Sub VBA_Variant1()

Dim Name As String

End Sub

VBA Variant Example 1-3

Step 4: Now define another variable where we can store or print any data. For that, we will again use a String data type.

Code:

Sub VBA_Variant1()

Dim Name As String
Dim DoB As String

End Sub

VBA Variant Example 1-4

Step 5: Now define another variable where we can store some numbers. For that, we will use an integer data type.

Code:

Sub VBA_Variant1()

Dim Name As String
Dim DoB As String
Dim Age As Integer

End Sub

Datatype Integer Example 1-5

Step 6: And at last, we will declare another variable where we will be storing lengthy number using datatype Long

Code:

Sub VBA_Variant1()

Dim Name As String
Dim DoB As String
Dim Age As Integer
Dim RollNo As Long

End Sub

Datatype Long Example 1-6

So basically here, we will be creating a database which will be having the name of a student, Date of Birth, Age, and Roll No. Now to complete this process, we will assign the values to each of the variables which we defined above.

Step 7: So we will declare the name of the student as Ashwani whose date of birth is 02 Sept 1990 and age is 29 and whose roll number is 16238627 in his certification exam as shown below.

Code:

Sub VBA_Variant1()

Dim Name As String
Dim DoB As String
Dim Age As Integer
Dim RollNo As Long

Name = "Ashwani"
DoB = "02-09-1990"
Age = 29
RollNo = 16238627

End Sub

VBA Variant Example 1-7

Please note, the value where we will use String data type are quoted in inverted commas as they as in Text. Now to print these values we can use Msgbox or Debug.Print.
Debug print is the best way here, as we have multiple values for which if we use Msgbox then we need to use separated msgbox to see the output. So, to avoid that, we will use Debug.Print

Step 8: Use Debug.Print function and write all the variable which we defined above separated by Commas as shown below.

Code:

Sub VBA_Variant1()

Dim Name As String
Dim DoB As String
Dim Age As Integer
Dim RollNo As Long

Name = "Ashwani"
DoB = "02-09-1990"
Age = 29
RollNo = 16238627

Debug.Print Name, DoB, Age, RollNo

End Sub

Use Debug Print function

Step 9: To see the output, open the Immediate Window from the View menu list. Or we can use a short cut key as Ctrl + G to get this window.

Immediate Window Example 1-9

Step 10: Run the code by pressing the F5 function key or click on the Play button located below the menu list.

VBA Variant Example 1-10

We will see, all the variable which we have declared above, we are able to see the values stored in each of the variables.

Step 11: Now we will replace each variables String, Integer and Long with Variant data type as shown below.

Code:

Sub VBA_Variant1()

Dim Name As Variant
Dim DoB As Variant
Dim Age As Variant
Dim RollNo As Variant

Name = "Ashwani"
DoB = "02-09-1990"
Age = 29
RollNo = 16238627

Debug.Print Name, DoB, Age, RollNo

End Sub

VBA Variant Example 1-9

Step 12: Again run the code. We will the same output as we got using different variable data type with the Variant data type.

VBA Variant Example 1-11

And if we compare the output, then both outputs are the same.

Pros & Cons of Excel VBA Variant

  • We can replace most of the data types with a single data type Variant.
  • VBA Variant is easy as using Integer or Long or String data types for declaring variables.
  • This saves time in thinking which type of data type we need to choose for variable declaration.
  • For each different data, we will get the same output by using a Variant data type as we could get using traditional variables.
  • We cannot use some certain types of variable data type such Double if we want to replace this with Variant.

Things to Remember

  • Use double quote (Inverted commas) if you want to declare text using Variant or any other data types.
  • We can choose any name to declare variable using Variant, as we used to perform with other types of data types.
  • VBA Variant has the limitation where cannot use IntelliSense list, which has inbuilt functions.
  • VBA always suggests the best possible data types we could declare of any type of data.

Recommended Articles

This is a guide to VBA Variant. Here we discuss how to declare Variant Data Type in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Workbook Open
  2. VBA Get Cell Value
  3. VBA Length of String
  4. VBA XML

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA Variant Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW