Skip to content
Tutorials Center
My learning

Topics

Lesson 73 of 93 ยท Automation and Collaboration

VBA Basics

Learn VBA for Excel: the editor, Sub and Function, variables, Range and Cells, loops, If, MsgBox, custom worksheet functions and error handling.

VBA (Visual Basic for Applications) is the programming language built into Excel. A recorded macro is VBA that Excel wrote for you; with a little VBA knowledge you can read it, clean it up and write things the recorder never could: loops over thousands of rows, decisions, custom dialogs and your own worksheet functions. If you have not recorded a macro yet, start with Macros and the Macro Recorder, then come back here.

Excel and VBA cannot run on this site, so every VBA listing below is read-only. To let you experiment anyway, the TryIts use small JavaScript and Python stand-ins that mimic the ideas (an object model with Range and Cells, a user-defined function, error handling). They are not VBA and do not call Excel; they show how the real code behaves, and each one says where the stand-in differs.

What VBA gives you

Loops Repeat work over any number of rows.
Decisions If, Select Case, flags.
Custom functions Your own =FORMULA in cells.
Dialogs MsgBox and InputBox.

The recorder cannot loop or decide. You can.

1 / 2

Opening the Visual Basic Editor

Enable the Developer tab (File > Options > Customize Ribbon) and click Visual Basic, or press Alt+F11. The editor (the VBE) has three panes you use constantly:

  • Project Explorer lists open workbooks, their sheets and modules.
  • Code window is where you type.
  • Immediate window (Ctrl+G) runs a single line at once. Typing ? Range("A1").Value prints the value of A1, which makes it a great calculator and debugging tool.

Code lives in a module: choose Insert > Module. Save the file as an .xlsm workbook, otherwise Excel strips the code on save. To run a procedure, click inside it and press F5; to step through line by line, press F8.

Sub and Function

There are two kinds of procedure:

  • A Sub does something (formats cells, copies data). It returns nothing and is started from a button, the macro list or F5.
  • A Function returns a value. Used in a module it behaves like any function; used in a cell it becomes a custom worksheet function.
VB
Option Explicit

Sub Greet()
    MsgBox "Hello from VBA!"
End Sub

Function PriceWithTax(net As Double, Optional rate As Double = 0.2) As Double
    PriceWithTax = Round(net * (1 + rate), 2)
End Function

A function returns its result by assigning to its own name. After saving, =PriceWithTax(B2) or =PriceWithTax(B2, 0.07) works in any cell of that workbook. Two limits to remember: a function called from a cell can only return a value; it cannot change other cells, formats or the sheet. And VBA's Round uses banker's rounding (halves go to the nearest even number), so Round(2.5, 0) is 2, while the worksheet function ROUND(2.5, 0) gives 3. Use Application.WorksheetFunction.Round if you want Excel's behavior.

Variables and data types

Declare variables with Dim. Put Option Explicit at the top of every module: it makes VBA refuse undeclared names, which turns typos such as totl into errors instead of silent bugs.

Type Holds
Long Whole numbers (use this instead of Integer for row numbers)
Double Decimals
String Text
Boolean True or False
Date Dates and times
Variant Anything (the default; slower and less safe)
Worksheet, Range Objects, assigned with the keyword Set
VB
Dim total As Double
Dim customer As String
Dim ws As Worksheet

total = 0
customer = "Northwind"
Set ws = ThisWorkbook.Worksheets("Sales")   ' objects need Set

More data types

The table above is the short list. VBA has a few more, and choosing the right one saves memory and avoids overflow errors.

Type Range or use Notes
Byte 0 to 255 One byte, for small counters and binary data
Integer -32,768 to 32,767 Overflows easily; a sheet has over a million rows, so prefer Long
Long about ยฑ2.1 billion The standard whole-number type
Single 7 digits of precision Rarely worth using
Double 15 digits of precision The standard decimal type
Currency 4 decimal places, exact Money; avoids binary rounding drift
String Text Fixed-length strings exist but are rare
Object Any object A loose type; prefer Worksheet, Range, etc.

Without a type, Dim x creates a Variant that works for anything but is slower and hides mistakes. Implicit declaration (just using total = 5 with no Dim) creates a Variant on the spot; explicit declaration (Dim total As Double) is what Option Explicit forces on you. Give each variable its own type: Dim a, b As Long makes a a Variant and only b a Long.

Scope and lifetime

Where you declare a variable decides who can see it (scope) and how long it keeps its value (lifetime).

Declared Scope Lifetime
Dim inside a Sub or Function That procedure only Until the procedure ends
Static inside a procedure That procedure only Keeps its value between calls
Dim or Private at the top of a module Every procedure in that module As long as the workbook is open
Public at the top of a module Every module in the project As long as the workbook is open
VB
Option Explicit
Public runCount As Long          ' visible in every module

Sub CountRuns()
    Static calls As Long         ' remembered between runs of this Sub
    calls = calls + 1
    runCount = runCount + 1
    Debug.Print "this Sub ran " & calls & " times"
End Sub

Prefer the narrowest scope that works. Module-level and Public variables are reset when you stop the code with the Reset button or an unhandled error, so do not rely on them to hold data you cannot afford to lose; write it to a cell instead.

Range and Cells

Range takes an address as text; Cells takes a row and a column number, which is ideal inside loops.

VB
Range("A1").Value = "Total"
Range("B1").Formula = "=SUM(B2:B20)"
Range("A1:C1").Font.Bold = True
ws.Cells(2, 3).Value = 450          ' row 2, column 3 = C2
ws.Range("A2:A10").ClearContents

Always qualify with a worksheet (ws.Range) instead of relying on whatever sheet happens to be active. Unqualified Range means "the active sheet", which is a classic source of macros that work on your screen and fail on someone else's.

Find the last used row with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row: it starts at the bottom of column A and jumps up to the last filled cell, like pressing Ctrl+Up.

Run it: an object-model stand-in

This stand-in is a plain JavaScript object with the same shapes: Range("A1"), Cells(r, c), .Value, and a last-row helper. The real objects have hundreds of properties; here there are three.

JS A tiny Range and Cells model

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Loops

VBA has three loops you will use daily.

VB
' Counted loop
For r = 2 To lastRow
    ws.Cells(r, 4).Value = ws.Cells(r, 2).Value * ws.Cells(r, 3).Value
Next r

' Visit every cell of a range
Dim cell As Range
For Each cell In ws.Range("C2:C" & lastRow)
    If cell.Value < 0 Then cell.Font.Color = vbRed
Next cell

' Repeat until a condition changes
r = 2
Do While ws.Cells(r, 1).Value <> ""
    r = r + 1
Loop

Note & joins text: "C2:C" & lastRow becomes C2:C20. Reading and writing cells one at a time is slow for large ranges. For big jobs read a whole range into an array (arr = ws.Range("A1:C5000").Value), loop over the array in memory, and write it back in a single step.

If and Select Case

VB
If score >= 90 Then
    grade = "A"
ElseIf score >= 80 Then
    grade = "B"
Else
    grade = "C or below"
End If

Select Case region
    Case "North", "South"
        rate = 0.05
    Case "East"
        rate = 0.07
    Case Else
        rate = 0.1
End Select

Comparison operators are =, <>, <, >, <=, >=, with And, Or and Not to combine. Text comparisons are case sensitive by default; add Option Compare Text at the top of the module to ignore case.

Talking to the user

MsgBox shows a message; with buttons it returns the choice. InputBox asks for text.

VB
Sub ConfirmClear()
    Dim answer As VbMsgBoxResult
    answer = MsgBox("Clear all data?", vbYesNo + vbQuestion, "Confirm")
    If answer = vbYes Then Range("A2:D100").ClearContents
End Sub

Sub AskName()
    Dim who As String
    who = InputBox("Your name?")
    If who = "" Then Exit Sub        ' Cancel or empty
    Range("F1").Value = "Prepared by " & who
End Sub

MsgBox buttons, icons and results

The second argument of MsgBox is a number built by adding constants from three groups: which buttons to show, which icon to show and which button is the default. The function returns another constant that tells you what the user clicked, so compare it with the names (vbYes), never with a bare number.

Group Constants
Buttons vbOKOnly (0), vbOKCancel (1), vbAbortRetryIgnore (2), vbYesNoCancel (3), vbYesNo (4), vbRetryCancel (5)
Icon vbCritical (16), vbQuestion (32), vbExclamation (48), vbInformation (64)
Default button vbDefaultButton1 (0), vbDefaultButton2 (256), vbDefaultButton3 (512)
Result vbOK 1, vbCancel 2, vbAbort 3, vbRetry 4, vbIgnore 5, vbYes 6, vbNo 7
VB
Dim answer As VbMsgBoxResult
answer = MsgBox("Delete the sheet?", vbYesNo + vbCritical + vbDefaultButton2, "Careful")
If answer = vbNo Then Exit Sub        ' the safe default is No

A dialog blocks the whole program, so do not pop one up inside a loop. MsgBox also accepts helpfile and context arguments that attach a Windows help file; they are rarely used.

InputBox(prompt, title, default, xpos, ypos) takes a message, a window title, a pre-filled default and an optional screen position. It returns a String, and an empty string both for "OK with nothing typed" and for Cancel. If you need a number, a range or a Boolean, use Application.InputBox(prompt, Type:=1) instead: Type 1 is a number, 2 text, 4 logical and 8 a cell range (the user selects it with the mouse; assign it with Set rng = ...). It returns False on Cancel.

Excel cannot run on this page, but the button values are plain arithmetic, so this JavaScript stand-in decodes a value the way VBA does: it reads the bits of each group and lists what MsgBox would return.

JS Decode a MsgBox button value

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The first example prints 36, which is the number you would see if you wrote MsgBox "Sure?", 36. Because the groups use different bits, adding the constants never mixes them up.

Error handling

When a statement fails, VBA stops with a runtime error. On Error GoTo label sends control to a handler instead.

VB
Sub SafeDivide()
    Dim x As Double
    On Error GoTo Fail
    x = 10 / Range("A1").Value        ' error 11 if A1 is 0
    Range("B1").Value = x
    Exit Sub                          ' do not fall into the handler
Fail:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
End Sub

The handler needs Exit Sub above it, or normal runs would walk straight into it. On Error Resume Next ignores errors and carries on, so use it only around one line you can check afterward (test Err.Number), and switch it back with On Error GoTo 0.

Run it: a custom function and an error handler

The second stand-in is Python. It shows the two traps from this lesson: banker's rounding in Round versus the worksheet's half-away-from-zero, and handler-style error handling. Python's built-in round also rounds halves to even, just like VBA's Round.

Python UDF rounding and error handling, in Python

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Debugging and error handling in depth

Three kinds of problem show up in VBA, and they need different tools.

Kind When it appears Example
Syntax / compile error Before running: the editor highlights the line or says "Compile error" Missing Then, undeclared variable with Option Explicit
Runtime error While running, with a number such as 9 (Subscript out of range), 11 (Division by zero), 13 (Type mismatch), 91 (Object variable not set) Reading .Value of a cell holding #N/A into a Double
Logic error The code runs but gives the wrong answer A loop that stops one row early

Compile errors are the easiest: use Debug > Compile VBAProject to find them all at once. Logic errors are the hardest, and they are where the debugging tools earn their keep.

The error-handling statements

VB
On Error GoTo Handler      ' jump to a label when an error occurs
On Error Resume Next       ' ignore the error and run the next line
On Error GoTo 0            ' switch handling off (normal VBA errors again)
Resume Next                ' inside a handler: continue after the failing line
Resume                     ' inside a handler: retry the failing line
Resume Done                ' inside a handler: jump to a label

Inside a handler, the Err object describes the problem: Err.Number, Err.Description and Err.Source. Err.Clear resets it after you have dealt with it, and Err.Raise creates your own error, which is how a called procedure reports a problem to its caller:

VB
Function SafeRatio(a As Double, b As Double) As Double
    If b = 0 Then Err.Raise vbObjectError + 1000, "SafeRatio", "The divisor cannot be zero."
    SafeRatio = a / b
End Function

A reliable template pairs one handler with one exit path, so cleanup always happens:

VB
Sub ProcessAll()
    Dim cell As Range
    On Error GoTo Fail
    Application.ScreenUpdating = False

    For Each cell In Range("B2:B50")
        cell.Offset(0, 1).Value = 100 / cell.Value
    Next cell

Done:
    Application.ScreenUpdating = True      ' runs after success AND after the handler
    Exit Sub
Fail:
    MsgBox "Row " & cell.Row & ": error " & Err.Number & " - " & Err.Description
    Resume Done
End Sub

Handling errors inside a loop

A handler that exits ends the whole loop on the first bad row. To log the problem and keep going, check each risky line instead of using one global handler:

VB
For Each cell In Range("B2:B50")
    On Error Resume Next
    result = 100 / cell.Value
    If Err.Number <> 0 Then
        cell.Offset(0, 1).Value = "bad: " & Err.Description
        Err.Clear
    Else
        cell.Offset(0, 1).Value = result
    End If
    On Error GoTo 0
Next cell

You can often avoid the error altogether: test with IsError(cell.Value), IsNumeric(cell.Value) or IsEmpty(cell) first, the VBA cousins of IFERROR and ISNUMBER (Error-Handling Formulas). Never leave On Error Resume Next active over a large block: it hides every bug in it.

Debugging tools

  • Breakpoint (F9 on a line, or click the left margin): execution pauses there. F8 steps one line at a time, Shift+F8 steps over a call, Ctrl+Shift+F8 steps out, F5 continues. Right-click a line and choose Set Next Statement to re-run it.
  • Immediate window (Ctrl+G): type ? myVariable to print a value, or run a statement such as Range("A1").Value = 0. In code, Debug.Print x writes to it without stopping.
  • Locals window shows every variable of the current procedure and its value. Watch window (Debug > Add Watch) tracks an expression and can break when it becomes True or changes.
  • Call Stack (Ctrl+L) shows which procedure called which.
  • To debug a user-defined function, put a breakpoint in it and change one of its input cells; Excel recalculates and stops inside the function.

Run it: the retry pattern, in JavaScript

The stand-in mirrors the loop template above: bad rows are logged, good rows are processed, and the cleanup line runs either way. It is JavaScript's try/catch, not VBA, but the control flow is the same idea.

JS Log the bad row, keep going, always clean up

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Speed and good habits

  • Turn off screen redrawing in long loops with Application.ScreenUpdating = False and always turn it back to True at the end (also in your error handler).
  • Avoid .Select and .Activate. Write ws.Range("A1").Value = 5 instead of selecting A1 first; it is faster and does not depend on what is on screen.
  • Give procedures verb names (ImportSales, ColorOverdueRows) and comment the why, not the what.
  • Keep numbers like tax rates in cells (or named constants), not buried in code, as you would in a good worksheet (see Performance and best practices).
  • Macros can carry malware. Only enable them from sources you trust, and keep Excel's default of disabling macros with a notification.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does a Function in VBA return its result?

    VBA has no Return statement for functions. You assign the result to the function name, for example PriceWithTax = Round(...), and it comes back when the function ends.
  2. 2. Why put Option Explicit at the top of a module?

    Without Option Explicit, a misspelled variable silently becomes a new empty Variant. With it, VBA reports Variable not defined at once.
  3. 3. You write =MyFunction(A1) in a cell, and MyFunction tries to change the color of B5. What happens?

    Worksheet functions are not allowed to change other cells or formats. Use a Sub for actions and a Function for calculations.
  4. 4. Which line finds the last used row of column A in the usual way?

    The code starts at the very bottom of the column and jumps upward to the last filled cell, like Ctrl+Up. Counting cells would include blanks, and LastRow does not exist.

Summary

  • VBA lives in modules inside the Visual Basic Editor (Alt+F11); save as .xlsm.
  • A Sub performs actions; a Function returns a value and can be used in cells.
  • Declare variables with Dim, use Option Explicit, and use Set for objects.
  • Range("A1") and Cells(row, column) read and write cells; always qualify them with a worksheet.
  • For, For Each and Do While repeat work; If and Select Case decide.
  • On Error GoTo with Exit Sub before the handler is the standard error pattern.
  • VBA Round is banker's rounding, unlike the worksheet function.

Next: VBA Loops, If/ElseIf, Select Case and Procedures.