VBA for Office

VBA (Visual Basic for Applications) is the macro language built into Microsoft Office: Excel, Word, Access, Outlook, and PowerPoint. It's based on VB6, not VB.NET, but the syntax is close enough that you can read and write it with what you've learned.

Getting started

1. In Excel, enable the Developer tab (File → Options → Customize Ribbon).
2. Click Visual Basic (or press Alt + F11) to open the VBA editor.
3. Insert → Module, and write a Sub.
4. Run it with F5, or from Developer → Macros.
5. Save the workbook as .xlsm (macro-enabled).

A first macro

' illustrative: VBA in an Excel module
Option Explicit

Sub FormatReport()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sales")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim r As Long
    For r = 2 To lastRow
        If ws.Cells(r, "C").Value > 1000 Then
            ws.Cells(r, "C").Interior.Color = RGB(198, 239, 206)
        End If
    Next r

    ws.Range("A1:C1").Font.Bold = True
    ws.Columns("A:C").AutoFit
    MsgBox "Formatted " & (lastRow - 1) & " rows."
End Sub

Differences from VB.NET

Always put Option Explicit at the top of VBA modules, so undeclared variables are errors instead of silent Variants.

A user-defined worksheet function

' illustrative: VBA in an Excel module, used as =VAT(A2) in a cell
Public Function VAT(net As Double, Optional rate As Double = 0.2) As Double
    VAT = Round(net * rate, 2)
End Function

Public functions in a standard module can be used in cell formulas like any built-in function.

Error handling in VBA

' illustrative: VBA error handling pattern
Sub ImportFile()
    On Error GoTo Failed
    Workbooks.Open "C:\data\import.xlsx"
    ' ... process the data ...
    Exit Sub
Failed:
    MsgBox "Import failed: " & Err.Description, vbExclamation
End Sub

When to go beyond VBA

• For automation outside Office (scheduled jobs, servers), use VB.NET or PowerShell with a library such as ClosedXML or EPPlus to read and write .xlsx files without Excel installed.
• Office Scripts (TypeScript) are Microsoft's newer automation for Excel on the web.
• Macros from unknown sources can contain malware — Office blocks macros in files downloaded from the internet by default. Sign your own macros and only enable trusted ones.