Tutorials›Excel›VBA Basics
🟢 Free Demo
Excel TutorialTopic 15 of 31

VBA Basics

Write your first VBA code to make Excel Macros smarter, dynamic, and reusable.

✅ What You Will Learn

›How to open the VBA Editor and navigate its windows
›The structure of a VBA Sub (procedure)
›How to declare variables and use basic data types
›How to use If-Then-Else and For loops in VBA
›How to work with Cells, Ranges, and Worksheets in VBA

VBA (Visual Basic for Applications) is the programming language built into Excel. While the Macro Recorder generates VBA code for you, knowing VBA lets you edit that code to handle dynamic data, add logic (IF conditions, loops), create custom functions, and build tools that no Macro Recorder can produce.

You write VBA in the VBA Editor (press Alt+F11 to open it). Code is organised into Modules (where general Macros live) and objects like ThisWorkbook and Sheet1 (where event-driven code lives — e.g. code that runs when a sheet is selected or a cell is changed).

Even basic VBA knowledge — variables, loops, and IF statements — allows you to transform brittle recorded Macros into robust tools that work correctly on any dataset.

Syntax

EXCEL SYNTAX
Sub MacroName()
  ' Declare variables
  Dim i As Integer
  Dim lastRow As Long
  Dim ws As Worksheet

  ' Assign values
  Set ws = ThisWorkbook.Sheets("Data")
  lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

  ' Loop through rows
  For i = 2 To lastRow
    If ws.Cells(i, 3).Value > 10000 Then
      ws.Cells(i, 4).Value = "High"
    Else
      ws.Cells(i, 4).Value = "Low"
    End If
  Next i

  MsgBox "Done! Processed " & lastRow - 1 & " rows."
End Sub

Examples

Example 1Find the last row dynamically — no hardcoded row numbers
Sub ProcessData()
  Dim lastRow As Long
  lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  MsgBox "Data has " & lastRow & " rows (including header)"
End Sub
💡

Cells(Rows.Count, 1).End(xlUp).Row is the VBA equivalent of pressing Ctrl+↑ from the bottom of column A — it finds the last used row. Never hardcode row numbers in production Macros.

Example 2Loop through rows and categorise values
Sub CategoriseSales()
  Dim i As Long
  Dim lastRow As Long
  lastRow = Cells(Rows.Count, 1).End(xlUp).Row

  For i = 2 To lastRow   ' start at 2 to skip header
    If Cells(i, 2).Value >= 50000 Then
      Cells(i, 3).Value = "Premium"
    ElseIf Cells(i, 2).Value >= 20000 Then
      Cells(i, 3).Value = "Standard"
    Else
      Cells(i, 3).Value = "Budget"
    End If
  Next i
End Sub
OUTPUT
Column C is filled with Premium / Standard / Budget for every row in the dataset — works on 10 rows or 10,000 rows.
💡

This is the VBA equivalent of an IFS formula, but it runs as a Macro — it can also format cells, write to other sheets, or trigger further actions.

📌 Key Points to Remember

  • ✓Alt+F11 opens the VBA Editor. Insert → Module creates a new code module.
  • ✓Sub starts a procedure, End Sub closes it — all code goes between them
  • ✓Dim declares a variable. Use Long for row numbers (not Integer — Long handles over 32,767 rows)
  • ✓Cells(row, column) references a cell by row and column numbers. Cells(2,3) = cell C2.
  • ✓Always test Macros on a copy of your data first — VBA actions cannot be undone with Ctrl+Z

🏢 Real-World Application

Reconciliation teams at banks write VBA Macros that loop through thousands of transaction rows, match debits with credits using custom logic, flag mismatches, and write results to a summary sheet — all in under a minute. This level of customisation is impossible with formulas alone and would take hours manually. VBA is what turns Excel into a proper data processing tool for finance and operations.

⚠️ Common Mistakes to Avoid

WRONGUsing Integer instead of Long for row counter variables
FIXInteger maxes out at 32,767. Excel sheets can have over a million rows. Always use Long for anything that could grow beyond 32,767.
WRONGNot using Option Explicit — undefined variables cause silent bugs
FIXAdd Option Explicit at the top of every module. This forces you to declare all variables with Dim, catching typos and undefined variables as errors rather than bugs.
WRONGRunning a loop without End Sub — VBA crashes with an error
FIXEvery Sub must have a matching End Sub. Every For must have Next. Every If must have End If. VBA will not run incomplete code structures.
✏️Test Yourself

In VBA, what does Cells(3, 2).Value refer to?

❓ Frequently Asked Questions

Do I need to know programming before learning VBA?

No prior programming experience is needed. VBA syntax is very readable — it resembles plain English. Start with recording Macros, read the generated code, and modify small parts. That is how most analysts learn VBA.

What is the difference between a Sub and a Function in VBA?

A Sub performs actions (it does not return a value). A Function returns a value and can be used as a custom Excel formula (UDF — User Defined Function). =MyFunction(A1) in a cell calls a VBA Function.

How do I debug a VBA Macro that is not working?

Press F8 in the VBA Editor to step through code one line at a time. Hover over variables to see their current values. Add Debug.Print variable to print values to the Immediate Window (Ctrl+G to open it).

✏️ Practice Exercise

Write a VBA Sub that: (1) Finds the last row of data in column A dynamically, (2) Loops through each row from row 2 to lastRow, (3) If the value in column B is above 50000, writes "Target Met" in column C; otherwise writes "Below Target", (4) After the loop, shows a MsgBox displaying how many rows were processed. Test it on a dataset of at least 20 rows.

← PreviousMacros IntroductionNext →Dashboard Design
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.