EXCEL VBA COURSE • LESSON 5

VBA Variables and Data Types

Learn how to create, declare and use variables in Excel VBA, understand VBA data types, store different kinds of information, and write cleaner, faster and more reliable VBA programs.

Beginner to Intermediate • Complete Guide with Examples

📘 What You Will Learn

Variables are one of the most important concepts in VBA programming. Almost every useful VBA program needs variables to temporarily store information such as names, numbers, dates, worksheet values and calculation results.

In this lesson, you will learn what variables are, how to declare them, how to choose the correct data type, how to assign values and how to use variables in practical Excel automation.

1. What is a Variable in VBA?

A variable is a named location in computer memory that is used to store information while a VBA program is running.

Think of a variable as a labeled box. You give the box a name and then put some information inside it.

Example: A Simple Variable

Dim studentName As String

studentName = "John"

In this example:

studentName is the variable name.
String is the data type.
"John" is the value stored in the variable.

2. Why Do We Need Variables?

Variables allow a VBA program to temporarily store information and use that information later.

For example, suppose you want to calculate the total price of a product. You can store the quantity and price in variables.

Dim quantity As Integer
Dim price As Double
Dim total As Double

quantity = 10
price = 25.5

total = quantity * price

MsgBox total

The result will be:

Total = 255

Variables make the program easier to understand, modify and reuse.

3. Declaring a Variable

Before using a variable, it is good programming practice to declare it.

The most common VBA syntax is:

Dim variableName As DataType

For example:

Dim employeeName As String
Dim age As Integer
Dim salary As Double
Dim joiningDate As Date
Dim isActive As Boolean

4. Using Dim

Dim is the most commonly used VBA keyword for declaring variables.

Dim customerName As String
Dim customerAge As Integer
Dim customerSalary As Double

You can also declare multiple variables:

Dim firstName As String
Dim lastName As String
Dim age As Integer

💡 Tip:

Always choose a meaningful variable name. A name such as employeeSalary is much easier to understand than x.

5. Option Explicit

Option Explicit forces you to declare variables before using them.

It is strongly recommended for VBA projects.

Option Explicit

Sub Example()

    Dim employeeName As String

    employeeName = "John"

    MsgBox employeeName

End Sub

Suppose you accidentally write:

employeeNmae = "John"

The spelling mistake would be detected instead of silently creating another variable.

⚠ Important:

Without Option Explicit, spelling mistakes in variable names can create unexpected bugs.

6. What is a Data Type?

A data type tells VBA what kind of information a variable is designed to store.

For example:

String → Text
Integer → Whole numbers
Double → Decimal numbers
Boolean → True or False
Date → Date and time
Long → Large whole numbers
Variant → Can contain different kinds of values
Object → Excel objects such as Workbook, Worksheet or Range

Important VBA Data Types

Data Type	Used For	Example
`Byte`	Small positive whole numbers	Dim x As Byte
`Integer`	Whole numbers	Dim age As Integer
`Long`	Large whole numbers	Dim rowNumber As Long
`Single`	Decimal numbers	Dim temperature As Single
`Double`	High-precision decimal numbers	Dim salary As Double
`Currency`	Financial values	Dim amount As Currency
`Decimal`	High-precision decimal values	Dim value As Variant
`String`	Text	Dim name As String
`Boolean`	True / False	Dim active As Boolean
`Date`	Date and time	Dim todayDate As Date
`Variant`	Flexible data type	Dim value As Variant
`Object`	Objects	Dim ws As Worksheet

7. Numeric Data Types

7.1 Byte

The Byte data type stores small positive whole numbers.

Dim quantity As Byte

quantity = 100

MsgBox quantity

A Byte can store values from 0 to 255.

7.2 Integer

Integer is used for whole numbers.

Dim age As Integer

age = 35

MsgBox age

Examples of Integer values:

7.3 Long

Long is used for larger whole numbers. It is especially useful when working with Excel row numbers.

Dim lastRow As Long

lastRow = 100000

MsgBox lastRow

A very common Excel VBA pattern is:

Dim lastRow As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox lastRow

💡 Excel VBA Tip:

Use Long for row numbers instead of Integer because Excel worksheets can contain far more rows than an Integer can safely represent.

7.4 Single

Single is used for numbers that contain decimal values.

Dim temperature As Single

temperature = 36.5

MsgBox temperature

7.5 Double

Double is commonly used for decimal calculations where greater precision is needed.

Dim price As Double

price = 125.75

MsgBox price

Example calculation:

Dim quantity As Long
Dim price As Double
Dim total As Double

quantity = 15
price = 125.75

total = quantity * price

MsgBox total

7.6 Currency

The Currency data type is useful for financial values and monetary calculations.

Dim productPrice As Currency

productPrice = 12500.75

MsgBox productPrice

For financial applications, using an appropriate fixed-point type can help avoid some floating-point representation issues.

8. String Data Type

A String variable stores text.

Dim employeeName As String

employeeName = "Rahul"

MsgBox employeeName

A String can contain letters, numbers, spaces and symbols.

Dim employeeID As String

employeeID = "EMP1001"

MsgBox employeeID

Notice that EMP1001 is stored as text.

Example: Full Name

Dim firstName As String
Dim lastName As String
Dim fullName As String

firstName = "Rahul"
lastName = "Sharma"

fullName = firstName & " " & lastName

MsgBox fullName

The & operator joins two or more text values.

9. Boolean Data Type

A Boolean variable stores one of two logical values:

True
False

Dim isEmployeeActive As Boolean

isEmployeeActive = True

MsgBox isEmployeeActive

Example with If Statement

Dim isApproved As Boolean

isApproved = True

If isApproved = True Then

    MsgBox "Application Approved"

Else

    MsgBox "Application Rejected"

End If

10. Date Data Type

The Date data type stores dates and times.

Dim joiningDate As Date

joiningDate = #8/13/2026#

MsgBox joiningDate

Using Current Date

Dim todayDate As Date

todayDate = Date

MsgBox todayDate

Using Current Date and Time

Dim currentDateTime As Date

currentDateTime = Now

MsgBox currentDateTime

Calculating Age of a Date

Dim startDate As Date
Dim endDate As Date
Dim daysDifference As Long

startDate = #8/1/2026#
endDate = #8/13/2026#

daysDifference = endDate - startDate

MsgBox daysDifference

11. Variant Data Type

Variant is a flexible data type that can contain different kinds of values.

Dim value As Variant

value = 100

MsgBox value

value = "Hello"

MsgBox value

value = #8/13/2026#

MsgBox value

Although Variant is flexible, you should not automatically use Variant for every variable. When you know what type of data you need, using an appropriate specific data type makes your code clearer.

💡 Remember:

Excel cells can contain text, numbers, dates, errors and empty values. For that reason, Variant is sometimes useful when reading uncertain worksheet data.

12. Object Variables

VBA can use variables to refer to Excel objects such as:

Workbook
Worksheet
Range
Chart
PivotTable
Other Excel objects

Worksheet Variable

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")

MsgBox ws.Name

Range Variable

Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")

MsgBox rng.Address

⚠ Important:

When assigning an object to an object variable, use the Set keyword.

13. Assigning Values to Variables

After declaring a variable, you can assign a value using the assignment operator =.

Dim name As String

name = "John"

Numeric example:

Dim age As Integer

age = 30

Boolean example:

Dim status As Boolean

status = True

Changing a Variable's Value

Dim score As Integer

score = 50

score = 75

score = 90

MsgBox score

The final value stored in score is 90.

14. Storing Excel Cell Values in Variables

One of the most common uses of variables in Excel VBA is reading information from worksheet cells.

Example 1: Read a Name

Suppose cell A1 contains:

Rahul Sharma

VBA:

Dim employeeName As String

employeeName = Range("A1").Value

MsgBox employeeName

Example 2: Read a Number

Suppose cell B2 contains 50000.

Dim salary As Double

salary = Range("B2").Value

MsgBox salary

Example 3: Read a Date

Dim joiningDate As Date

joiningDate = Range("C2").Value

MsgBox joiningDate

15. Using Variables in Calculations

Variables are extremely useful for calculations.

Example: Calculate Total Sales

Dim quantity As Long
Dim unitPrice As Double
Dim totalSales As Double

quantity = 25
unitPrice = 150.5

totalSales = quantity * unitPrice

MsgBox "Total Sales = " & totalSales

Example: Calculate Discount

Dim price As Double
Dim discount As Double
Dim discountAmount As Double
Dim finalPrice As Double

price = 10000
discount = 10

discountAmount = price * discount / 100

finalPrice = price - discountAmount

MsgBox "Final Price = " & finalPrice

16. Scope of Variables

The scope of a variable determines where that variable can be used.

The main types are:

Procedure-level variables
Module-level variables
Public variables
Static variables

16.1 Procedure-Level Variable

A variable declared inside a Sub or Function is normally available only within that procedure.

Sub Example()

    Dim name As String

    name = "John"

    MsgBox name

End Sub

16.2 Module-Level Variable

A variable declared in the declarations section of a module can be shared by procedures in that module according to its declaration.

Option Explicit

Private companyName As String

Sub SetCompany()

    companyName = "ABC Ltd."

End Sub

Sub ShowCompany()

    MsgBox companyName

End Sub

16.3 Public Variable

A Public variable can be made available more broadly within the VBA project.

Option Explicit

Public companyName As String

Other modules can then access the public variable according to the project's VBA scope rules.

16.4 Static Variable

A Static variable retains its value between calls to the procedure.

Sub Counter()

    Static count As Long

    count = count + 1

    MsgBox count

End Sub

If you run the procedure multiple times, the value can continue from its previous value.

17. Constants in VBA

A constant is a named value that does not change while the program runs.

Use the Const keyword.

Const TAX_RATE As Double = 0.18

Example

Sub CalculateTax()

    Const TAX_RATE As Double = 0.18

    Dim price As Double
    Dim tax As Double

    price = 1000

    tax = price * TAX_RATE

    MsgBox tax

End Sub

💡 Why use constants?

Constants make code easier to maintain. If a fixed business value changes, you can update the constant instead of searching through the entire program for hard-coded numbers.

18. VBA Variable Naming Rules

Follow clear naming conventions when creating variables.

Good Variable Names

employeeName
employeeAge
totalSales
lastRow
invoiceNumber
customerAddress

Bad or Unclear Names

x
abc
a1
temp
thing
data1

Short names may be acceptable for very small loops or temporary calculations, but descriptive names are generally better for maintainable VBA programs.

Basic Naming Guidelines

✔ Start with a letter.
✔ Use meaningful names.
✔ Avoid spaces.
✔ Avoid confusing names.
✔ Do not use VBA reserved keywords as variable names.
✔ Keep naming consistent throughout the project.

Common VBA Naming Style

Some programmers use prefixes to indicate the type of a variable. This is optional, but you may see it in older VBA projects.

Prefix	Data Type	Example
str	String	strName
lng	Long	lngRow
dbl	Double	dblSalary
bln	Boolean	blnActive
dt	Date	dtJoiningDate

19. Declaring Multiple Variables

You can declare multiple variables in separate statements.

Dim firstName As String
Dim lastName As String
Dim age As Integer
Dim salary As Double

Be careful when declaring multiple variables on the same line.

⚠ Important VBA Detail:

In VBA, the data type applies to the variable immediately before the As keyword.

Dim a As Integer, b As Integer, c As Integer

This declares all three as Integer.

20. Practical VBA Examples

Example 1: Employee Information

Suppose an Excel worksheet contains:

A2 = Employee Name
B2 = Employee Age
C2 = Salary
D2 = Joining Date

We can read these values into variables.

Sub EmployeeInformation()

    Dim employeeName As String
    Dim employeeAge As Long
    Dim salary As Double
    Dim joiningDate As Date

    employeeName = Range("A2").Value
    employeeAge = Range("B2").Value
    salary = Range("C2").Value
    joiningDate = Range("D2").Value

    MsgBox "Employee: " & employeeName & vbCrLf & _
           "Age: " & employeeAge & vbCrLf & _
           "Salary: " & salary & vbCrLf & _
           "Joining Date: " & joiningDate

End Sub

Example 2: Calculate Employee Bonus

Sub CalculateBonus()

    Dim salary As Double
    Dim bonusRate As Double
    Dim bonus As Double
    Dim totalSalary As Double

    salary = Range("B2").Value

    bonusRate = 10

    bonus = salary * bonusRate / 100

    totalSalary = salary + bonus

    Range("C2").Value = bonus
    Range("D2").Value = totalSalary

End Sub

This example demonstrates how variables can store intermediate calculation results before writing the final values back to Excel.

Example 3: Find the Last Used Row

Sub FindLastRow()

    Dim lastRow As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    MsgBox "Last Used Row = " & lastRow

End Sub

Here, lastRow is a Long variable because Excel row numbers can be large.

Example 4: Process Multiple Rows

Sub ProcessEmployees()

    Dim lastRow As Long
    Dim i As Long
    Dim employeeName As String
    Dim salary As Double

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow

        employeeName = Cells(i, 1).Value
        salary = Cells(i, 2).Value

        If salary >= 50000 Then

            Cells(i, 3).Value = "High Salary"

        Else

            Cells(i, 3).Value = "Normal Salary"

        End If

    Next i

End Sub

This example shows several variables working together:

lastRow stores the last worksheet row.
i stores the current row number.
employeeName stores employee text.
salary stores a numeric value.

Example 5: Using Worksheet and Range Variables

Sub ObjectVariables()

    Dim ws As Worksheet
    Dim rng As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Set rng = ws.Range("A1:A10")

    rng.Font.Bold = True

End Sub

Object variables make code easier to read and can reduce repeated references to the same worksheet or range.

21. Common Mistakes with Variables

Mistake 1: Not Declaring Variables

Avoid relying on undeclared variables.

total = price * quantity

Better:

Dim total As Double
Dim price As Double
Dim quantity As Long

total = price * quantity

Mistake 2: Wrong Data Type

Choosing an inappropriate data type can cause errors or unexpected results.

For example, using Integer for Excel row numbers is generally not a good choice for modern Excel worksheets.

Prefer:

Dim rowNumber As Long

Mistake 3: Confusing Text and Numbers

Dim age As Integer

age = "Thirty"

The variable is declared as Integer, but the assigned value is text. This is an example of a type mismatch.

Mistake 4: Forgetting Set for Objects

Incorrect:

Dim ws As Worksheet

ws = ThisWorkbook.Worksheets("Sheet1")

Correct:

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")

22. VBA Variable Best Practices

✔ Use Option Explicit.
✔ Declare variables before using them.
✔ Choose the appropriate data type.
✔ Use meaningful variable names.
✔ Use Long for Excel row counters and row numbers.
✔ Use Double when appropriate for decimal calculations.
✔ Use Currency for suitable financial calculations.
✔ Use Boolean for True/False conditions.
✔ Use Date for dates and times.
✔ Use Object variables for Excel objects when appropriate.
✔ Avoid using Variant unnecessarily.
✔ Keep variable scope as narrow as practical.

23. Practice Exercises

📝 Exercise 1: Personal Information

Create variables for:

Name
Age
City
Salary
Joining Date

Display all information using a MsgBox.

📝 Exercise 2: Calculate Total

Create variables for:

Quantity
Unit Price
Total Price

Calculate:

Total Price = Quantity × Unit Price

📝 Exercise 3: Employee Bonus

Create a VBA program that:

Reads salary from cell B2.
Stores the salary in a variable.
Calculates a 10% bonus.
Stores the bonus in a variable.
Writes the bonus into cell C2.

📝 Exercise 4: Last Row

Create a Long variable called lastRow and use VBA to find the last used row in column A.

24. Quick Quiz

1. Which keyword is commonly used to declare a variable?

Answer: Dim

2. Which data type is used for text?

Answer: String

3. Which data type is used for True or False?

Answer: Boolean

4. Which data type is commonly recommended for Excel row numbers?

Answer: Long

5. Which keyword is required when assigning an object to an object variable?

Answer: Set

6. Which keyword creates a value that cannot be changed during normal program execution?

Answer: Const

25. Lesson Summary

In this lesson, you learned that a VBA variable is a named storage location used to hold information while a program runs.

Dim is commonly used to declare variables.
Option Explicit helps ensure variables are declared.
String stores text.
Integer stores whole numbers within its range.
Long stores larger whole numbers and is useful for Excel row numbers.
Single and Double store decimal numbers.
Currency is useful for suitable monetary calculations.
Boolean stores True or False.
Date stores dates and times.
Variant can contain different kinds of values.
Object variables can refer to Excel objects.
Set is used when assigning object references.
Const creates constants.
Good variable names make VBA programs easier to understand.

26. Complete Example: Employee Salary Calculator

Let's combine several concepts from this lesson into one practical VBA program.

Option Explicit

Sub EmployeeSalaryCalculator()

    Dim employeeName As String
    Dim basicSalary As Double
    Dim bonusRate As Double
    Dim bonusAmount As Double
    Dim totalSalary As Double
    Dim isEligible As Boolean

    employeeName = Range("A2").Value
    basicSalary = Range("B2").Value

    bonusRate = 10

    If basicSalary >= 50000 Then

        isEligible = True

    Else

        isEligible = False

    End If

    If isEligible = True Then

        bonusAmount = basicSalary * bonusRate / 100

    Else

        bonusAmount = 0

    End If

    totalSalary = basicSalary + bonusAmount

    Range("C2").Value = bonusAmount
    Range("D2").Value = totalSalary

    MsgBox "Employee: " & employeeName & vbCrLf & _
           "Basic Salary: " & basicSalary & vbCrLf & _
           "Bonus: " & bonusAmount & vbCrLf & _
           "Total Salary: " & totalSalary

End Sub

🔍 What This Example Demonstrates

String → employee name
Double → salary and calculations
Boolean → bonus eligibility
Variables → storing temporary information
If...Then...Else → decision making
Worksheet cells → reading and writing data
MsgBox → displaying the result

VBA Variables and Data Types

VBA Variables and Data Types

📘 What You Will Learn

📑 Table of Contents

1. What is a Variable in VBA?

2. Why Do We Need Variables?

3. Declaring a Variable

4. Using Dim

5. Option Explicit

6. What is a Data Type?

Important VBA Data Types

7. Numeric Data Types

7.1 Byte

7.2 Integer

7.3 Long

7.4 Single

7.5 Double

7.6 Currency

8. String Data Type

9. Boolean Data Type

Example with If Statement

10. Date Data Type

Using Current Date

Using Current Date and Time

Calculating Age of a Date

11. Variant Data Type

12. Object Variables

Worksheet Variable

Range Variable

13. Assigning Values to Variables

Changing a Variable's Value

14. Storing Excel Cell Values in Variables

Example 1: Read a Name

Example 2: Read a Number

Example 3: Read a Date

15. Using Variables in Calculations

Example: Calculate Total Sales

Example: Calculate Discount

16. Scope of Variables

16.1 Procedure-Level Variable

16.2 Module-Level Variable

16.3 Public Variable

16.4 Static Variable

17. Constants in VBA

Example

18. VBA Variable Naming Rules

Good Variable Names

Bad or Unclear Names

Basic Naming Guidelines

Common VBA Naming Style

19. Declaring Multiple Variables

20. Practical VBA Examples

Example 1: Employee Information

Example 2: Calculate Employee Bonus

Example 3: Find the Last Used Row

Example 4: Process Multiple Rows

Example 5: Using Worksheet and Range Variables

21. Common Mistakes with Variables

Mistake 1: Not Declaring Variables

Mistake 2: Wrong Data Type

Mistake 3: Confusing Text and Numbers

Mistake 4: Forgetting Set for Objects

22. VBA Variable Best Practices

23. Practice Exercises

24. Quick Quiz

25. Lesson Summary

26. Complete Example: Employee Salary Calculator

🔍 What This Example Demonstrates

🚀 Keep Learning Excel VBA

Post a Comment

0 Comments

Ads

Youtube

Most Popular

Tags

Labels

Contact Form

Recent Post

Popular Posts

Footer Menu Widget