Ravindra BagaleCourses & study guides मराठी Track your progress

12. Macros and VBA

12.6 Variables and Data Types

By Last updated:

Declare variables with Dim name As Type.

Type Holds Example
Long Whole numbers (use instead of Integer) Dim orders As Long
Double Decimals Dim amount As Double
Currency Money with 4 fixed decimals Dim fee As Currency
String Text Dim city As String
Date Date/time Dim orderDate As Date
Boolean True/False Dim isLate As Boolean
Variant Anything (default if not declared) Dim v As Variant
Object types Worksheets, ranges Dim ws As Worksheet, Dim rng As Range

About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Vikram Rao) are made up for this lesson on variables and data types.

Object variables need Set: Set ws = Worksheets("Orders").

Sub VariablesDemo()
    Dim city As String
    Dim orders As Long
    Dim sales As Double
    Dim aov As Double
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Orders")
    city = "Pune"
    orders = Application.WorksheetFunction.CountIfs(ws.Range("C:C"), city)
    sales = Application.WorksheetFunction.SumIfs(ws.Range("G:G"), ws.Range("C:C"), city)

    If orders > 0 Then aov = sales / orders
    MsgBox city & ": " & orders & " orders, AOV Rs " & Format(aov, "0.00")
End Sub

On the Module 3 mini dataset this shows Pune: 4 orders, AOV Rs 136.00.

Ravindra Bagale's Tip

Many students use Integer for row numbers – Integer's limit is 32,767, and with big data you get an "Overflow" error. Always use Long for rows and counts. If you forget Set for an object variable, you get the "Object variable not set" error – that's very common too.

Worked example — variables and data types

Why

Vikram Rao in Pune and Liam Murphy abroad both need a clear, repeatable way to use variables and data types on a QuickCart Delivery sheet without breaking totals.

What

Variables And Data Types is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for variables and data types.
  2. Enter a small made-up value for Vikram Rao using ₹ where money appears.
  3. Add a second value for Liam Murphy using £ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for QuickCart Delivery's sample row.
Meaning: Once variables and data types is correct on one row, copy the same pattern down the column.

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of variables and data types on a blank sheet for GreenField Dairy.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Priya Deshmukh and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Emma Clarke.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply variables and data types to a 5-row made-up GreenField Dairy table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with variables and data types.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as GreenFieldD_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Last used row (Easy): Which row number is the last filled row? The headings are in row 1.
  2. Late orders (Easy): How many rows have Status "Late"?
  3. Total amount (Easy): Add up the Amount column.
  4. Worst delay (Easy): What is the largest number in the Days late column?
  5. Late orders in Pune (Medium): How many orders are Late and in City "Pune"?
  6. Customer for an order (Medium): Find the Customer for Order ID "DL-704".
  7. Money stuck in late orders (Medium): Add up the Amount column for rows where Status is "Late".
  8. Big orders (Medium): How many rows have an Amount of 2000 or more?
  9. Message text (Medium): Build the text "N late orders", where N is the count of Late rows.
  10. Average delay of late orders (Hard): What is the average Days late for rows with Status "Late"? Round to 1 decimal.
  11. Tag for the first row (Hard): For the first row, return "Chase" if Days late is 3 or more, else "OK".
  12. Warning or all clear (Hard): Return "Warning" if any order is 3 or more days late, else "All clear".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Variables And Data Types?
Card 1: What is Variables And Data Types? · Download PNG
Revision card 2: Tiny example — Variables And Data Types
Card 2: Tiny example — Variables And Data Types · Download PNG
Revision card 3: Checklist — Variables And Data Types
Card 3: Checklist — Variables And Data Types · Download PNG