Skip to content
Tutorials Center
My learning

Topics

Lesson 78 of 93 ยท Automation and Collaboration

VBA for Charts, PivotTables and User-Defined Functions

Automate Excel charts and PivotTables with VBA, write user-defined functions (UDFs) with volatile behavior, speed up macros, and protect or share code with the Personal Macro Workbook.

Charts and PivotTables are where reports spend most of their time, and they are also easy to automate: build once, refresh forever. This lesson shows the VBA objects behind them, then moves to user-defined functions (UDFs), your own =FORMULA() for the grid, and finishes with the settings that make macros fast and the places macros can live.

Excel and VBA cannot run on this site, so the VBA listings are read-only. The TryIts use JavaScript stand-ins: a pivot-by-code summary and a UDF that behaves like a worksheet function (including volatile recalculation). They are teaching models, not Excel's own code.

Charts from code

A chart sits either on its own chart sheet or inside a ChartObject floating on a worksheet. Most macros create a ChartObject, point it at a source range and set its look:

VB
Sub BuildSalesChart()
    Dim ws As Worksheet, co As ChartObject
    Set ws = ThisWorkbook.Worksheets("Sales")

    ' remove an old copy so reruns do not stack charts
    On Error Resume Next
    ws.ChartObjects("SalesChart").Delete
    On Error GoTo 0

    Set co = ws.ChartObjects.Add(Left:=ws.Range("F2").Left, Top:=ws.Range("F2").Top, _
                                 Width:=420, Height:=260)
    co.Name = "SalesChart"
    With co.Chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=ws.Range("A1:B13")
        .HasTitle = True
        .ChartTitle.Text = "Monthly sales"
        .HasLegend = False
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Units"
        .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(46, 117, 182)
    End With
End Sub

Three details matter. ChartObjects("name") finds an existing chart, so naming charts makes them easy to update. SetSourceData takes the header row and data together, exactly as when you select cells and press Insert. And ChartType accepts the same constants you see in the dialog (xlLine, xlPie, xlBarStacked, xlXYScatter).

Dynamic charts

A chart is dynamic when its source follows the data. Without code you use a Table or named ranges (Dynamic and Interactive Charts). With code, recompute the range each run:

VB
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
co.Chart.SetSourceData ws.Range("A1:B" & lastRow)

Hook that into Worksheet_Change (events) and the chart redraws itself when a row is added. To save a chart as a picture for an email or web page, use co.Chart.Export "C:\Temp\sales.png".

PivotTables from code

PivotTables use two linked objects: a PivotCache (a snapshot of the source data) and a PivotTable that reads from it. Building from scratch is verbose, so many people record the first version and then clean it up.

VB
Sub BuildPivot()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim pc As PivotCache, pt As PivotTable

    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsPivot = ThisWorkbook.Worksheets("Pivot")
    wsPivot.Cells.Clear

    Set pc = ThisWorkbook.PivotCaches.Create( _
                 SourceType:=xlDatabase, _
                 SourceData:=wsData.Range("A1").CurrentRegion)
    Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range("A3"), _
                                 TableName:="SalesPivot")

    With pt
        .PivotFields("Region").Orientation = xlRowField
        .PivotFields("Product").Orientation = xlColumnField
        With .AddDataField(.PivotFields("Amount"), "Total Amount", xlSum)
            .NumberFormat = "#,##0"
        End With
    End With
End Sub

To refresh pivots after the data changes, you do not rebuild; you refresh:

VB
ThisWorkbook.RefreshAll                                   ' every pivot and query
ThisWorkbook.Worksheets("Pivot").PivotTables("SalesPivot").PivotCache.Refresh

If the source range grows, set a new source with pt.ChangePivotCache or base the pivot on an Excel Table so it grows automatically (see PivotTable Basics). A common trap: a PivotCache.Create inside a loop makes a new cache each time and bloats the file. Reuse one cache for several pivots.

Run it: summarizing by code

A pivot is a group-and-total. This stand-in does the same job as the BuildPivot macro, with rows by region, columns by product and a sum of the amount, and then "refreshes" after new data arrives.

JS What BuildPivot produces, and what a refresh changes

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

No output.

User-defined functions (UDFs)

A UDF is a Function in a standard module that you call from a cell. It must return a value and cannot change other cells, formats or the window (those statements simply fail or are ignored when called from the grid). For the basics see VBA Basics.

VB
' Standard module
Function NetPrice(gross As Double, Optional vatRate As Double = 0.2) As Double
    NetPrice = Round(gross / (1 + vatRate), 2)
End Function

Function CountWords(cell As Range) As Long
    Dim t As String
    t = Trim(cell.Value)
    If t = "" Then
        CountWords = 0
    Else
        CountWords = UBound(Split(t, " ")) + 1
    End If
End Function

Function RegionTotal(region As String, data As Range) As Double
    Dim c As Range
    For Each c In data.Columns(1).Cells
        If c.Value = region Then RegionTotal = RegionTotal + c.Offset(0, 1).Value
    Next c
End Function

Typing = followed by the first letters of the name in a cell offers it in the formula drop-down, under the User Defined category in Insert Function. To describe a UDF there, use Application.MacroOptions Macro:="NetPrice", Description:="Removes VAT", Category:=3. To list every UDF you wrote in a workbook, scan the Object Browser (F2) or search the VBE for Function. Errors in a UDF show as #VALUE!; to debug one, put a breakpoint in it and change an input cell (see debugging VBA).

Volatile functions

Ordinary UDFs recalculate only when their inputs change. Mark a function volatile with Application.Volatile as its first line and Excel recalculates it on every calculation, like NOW() or RAND():

VB
Function SheetName() As String
    Application.Volatile
    SheetName = Application.Caller.Parent.Name
End Function

Use it sparingly: volatile functions slow big workbooks. The usual reason to need one is reading something that is not a parameter, such as a cell's format, the sheet name or the time. Better still, pass such inputs as arguments so Excel sees the dependency.

Run it: a UDF that is and is not volatile

This stand-in models Excel's recalculation with a small dependency list. The plain function is only recomputed when its input cell changes; the volatile one is recomputed every time.

JS Volatile versus normal recalculation

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

No output.

Speed: the settings that matter

Large macros are slow for two reasons: too many round trips to the sheet, and Excel recalculating and redrawing after every one. A tidy skeleton fixes both.

VB
Sub FastMacro()
    Dim oldCalc As XlCalculation
    oldCalc = Application.Calculation
    On Error GoTo Done
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ' ... the real work: read ranges into arrays, process, write back ...

Done:
    Application.Calculation = oldCalc
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub

Remember to restore everything even when an error occurs. Beyond that, avoid .Select, read and write whole ranges through arrays (arrays), and use With blocks to avoid repeating long object paths.

Where macros live

  • This workbook (.xlsm): the macros travel with the file. Best for macros tied to one report.
  • Personal Macro Workbook (PERSONAL.XLSB): a hidden workbook that opens with Excel, so its macros work on any file. Choose "Personal Macro Workbook" when you record, or drop modules into it from the VBE. Good for your private utilities.
  • Add-in (.xlam): save a workbook of macros and UDFs as an add-in and load it from File > Options > Add-ins; share one file with a team.
  • Password-protecting code: in the VBE choose Tools > VBAProject Properties > Protection, tick "Lock project for viewing" and set a password. This keeps casual users out but is not real security; password-cracking tools are common. Never store secrets in VBA.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A pivot built by code shows old numbers after the source data changed. What fixes it?

    A PivotTable reads from a cache snapshot. PivotCache.Refresh or Workbook.RefreshAll updates it; rebuilding is unnecessary.
  2. 2. Which statement is true about a UDF called from a worksheet cell?

    Functions called from cells are restricted to returning a value. Use a Sub, run from a button or event, to change cells or formats.
  3. 3. What does Application.Volatile do at the top of a UDF?

    A volatile function is recalculated whenever Excel calculates, like NOW. It is needed when the result depends on something other than the arguments, but it can slow large workbooks.
  4. 4. Where do macros that should be available in every workbook on your computer best live?

    The Personal Macro Workbook is a hidden file that opens with Excel, and an .xlam add-in can be shared. Both make macros available regardless of the active file.

Summary

  • ChartObjects.Add plus .Chart.SetSourceData, .ChartType and .HasTitle build charts; name them so reruns can replace them.
  • Pivots need a PivotCache and a PivotTable; refresh with PivotCache.Refresh or RefreshAll and reuse one cache.
  • A UDF is a Function in a standard module that returns a value; mark it Application.Volatile only when it truly needs to recalculate every time.
  • Turn off ScreenUpdating, events and automatic calculation in long macros, and restore them in the exit path.
  • Store macros in the workbook, PERSONAL.XLSB or an .xlam add-in; VBA project passwords are not real security.

Next: Office Scripts, Power Automate and Python in Excel.