VBA: Automating Microsoft Office

Speed tips: read/write ranges as arrays; set Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual during long macros (restore them afterward).

35.1 Getting started

In Excel, enable the Developer tab (File → Options → Customize Ribbon).

Press Alt+F11 to open the VBA editor; Insert → Module.

Write a Sub, press F5 to run (or assign it to a button / shortcut).

Save as .xlsm (macro-enabled workbook). Macros from untrusted sources are blocked by default — only enable macros you trust.

Record Macro (Developer tab) generates VBA from your actions — a great way to discover object names.

35.2 First macro

Option Explicit

Sub HelloExcel()
    Dim userName As String
    userName = InputBox("What is your name?", "Greeting")
    If userName = "" Then Exit Sub
    Range("A1").Value = "Hello, " & userName & "!"
    Range("A1").Font.Bold = True
    MsgBox "Written to cell A1.", vbInformation
End Sub

35.3 The object model: Workbooks, Worksheets, Range, Cells

Sub ObjectModelTour()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")       ' object variables need Set

    ws.Range("A1").Value = "Product"
    ws.Range("B1").Value = "Price"
    ws.Cells(2, 1).Value = "Pen"                     ' Cells(row, column)
    ws.Cells(2, 2).Value = 1.5
    ws.Range("B2:B10").NumberFormat = "$#,##0.00"
    ws.Range("D1").Formula = "=SUM(B2:B10)"
    ws.Range("A1:B1").Interior.Color = RGB(200, 220, 255)
    ws.Columns("A:B").AutoFit

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   ' last used row in column A
    Debug.Print "Last row: "; lastRow                      ' output to the Immediate window (Ctrl+G)
End Sub

35.4 Looping over data

Speed tips: read/write ranges as arrays; set Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual during long macros (restore them afterward).

Sub HighlightLowStock()
    Dim cell As Range
    For Each cell In Worksheets("Inventory").Range("C2:C200")
        If IsNumeric(cell.Value) And cell.Value <> "" Then
            If cell.Value < 10 Then
                cell.Interior.Color = vbRed
            Else
                cell.Interior.ColorIndex = xlNone
            End If
        End If
    Next cell
End Sub

Sub FastSum()
    Dim data As Variant, r As Long, total As Double
    data = Worksheets("Sales").Range("B2:B10001").Value   ' read once into a 2-D array (fast)
    For r = 1 To UBound(data, 1)
        If IsNumeric(data(r, 1)) Then total = total + data(r, 1)
    Next r
    Worksheets("Sales").Range("E1").Value = total
End Sub

35.5 Functions usable in worksheet formulas

' In a standard module; use in a cell as =VAT(A2, 0.2)
Public Function VAT(amount As Double, Optional rate As Double = 0.2) As Double
    VAT = Round(amount * rate, 2)
End Function

35.6 Error handling and events

Sub SafeImport()
    On Error GoTo Failed                 ' VBA allows local error handlers inside procedures
    Workbooks.Open "C:\Data\import.xlsx"
    ' ...
    Exit Sub
Failed:
    MsgBox "Import failed: " & Err.Description, vbExclamation
End Sub

' In the code module of a worksheet: runs whenever a cell changes
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        Target.Offset(0, 1).Value = Now      ' timestamp next to the edited cell
        Application.EnableEvents = True
    End If
End Sub

35.7 UserForms

In the VBA editor: Insert → UserForm. Drag a TextBox (txtName), a ComboBox (cboDept), and a CommandButton (btnSave) from the Toolbox.

Double-click the button and add code:

Show the form from a macro: Sub ShowStaffForm(): UserForm1.Show: End Sub.

VBA also automates Word (ActiveDocument.Paragraphs), Outlook (CreateItem(olMailItem)), Access (DAO/ADO recordsets), and PowerPoint. For cloud/web Office, Microsoft offers Office Scripts (TypeScript) instead of VBA.

Private Sub UserForm_Initialize()
    cboDept.List = Array("Sales", "Engineering", "Support")
End Sub

Private Sub btnSave_Click()
    Dim ws As Worksheet, r As Long
    If Trim(txtName.Value) = "" Then MsgBox "Name required": Exit Sub
    Set ws = Worksheets("Staff")
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = txtName.Value
    ws.Cells(r, 2).Value = cboDept.Value
    Unload Me
End Sub