Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 37 ยท Projects and Practice

VBA Performance and Best Practices

Make VBA macros fast and maintainable: turn off screen updating, use arrays, avoid Select, naming, Option Explicit, modules, constants, comments and security.

A macro that works is a start. A macro that is fast, readable and safe to hand to a colleague is a professional tool. This lesson collects the habits that matter most: speed, structure, naming, defensive coding and security. Each habit is small, but together they decide whether a macro survives a year of real use.

VBA cannot run on this site, so the VBA is read-only. The JavaScript TryIts model the cost of crossing a boundary many times versus once, and a small naming and structure checklist.

Speed: the big four

VB
Sub FastMacro()
    Dim oldCalc As XlCalculation: oldCalc = Application.Calculation
    On Error GoTo CleanUp

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ' ... the real work ...

CleanUp:
    Application.Calculation = oldCalc
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
  1. ScreenUpdating = False stops redrawing the screen.
  2. Calculation = xlCalculationManual avoids recalculating after every cell write; restore the original setting afterwards.
  3. EnableEvents = False stops event handlers firing during your own writes.
  4. Work in memory. Read a range into an array, process it, write it back (see ranges and cells).

Always restore settings in a CleanUp label that also runs after an error. Other speed-ups:

  • Avoid .Select, .Activate and ActiveCell; refer to objects directly.
  • Use With blocks to avoid repeating a long object path.
  • Prefer Range.Value2 = array to many single writes, and one Range.Formula for a whole block.
  • Use Dictionary or Collection lookups instead of nested loops (O(n) rather than O(nยฒ)).
  • Declare variables with real types (Long, String, Worksheet) instead of Variant; use Long rather than Integer.
  • Use Application.StatusBar for progress, but update it sparingly (every 100 rows, not every row).
  • Concatenating huge strings in a loop is slow; build arrays and Join them.

Structure and naming

Habit Example
Option Explicit in every module catches typos at compile time
One job per procedure ReadData, Summarize, WriteReport
Meaningful names lastRow, customerName, not a, x1
Prefix for kinds ws, wb, rng, dict, frm, cmd
Constants instead of magic numbers Const COL_AMOUNT As Long = 4
Public only when needed default to Private
Short procedures roughly 30 to 50 lines; split bigger ones
VB
Private Const COL_REGION As Long = 2
Private Const COL_AMOUNT As Long = 4
Private Const DATA_SHEET As String = "Data"

Constants make changes easy: if the amount moves to column 5, you edit one line instead of hunting 4 through the code.

Organising a project

  • Standard modules for procedures (modReport, modUtils), class modules for objects, forms for dialogs.
  • A Config sheet or named ranges for values users may change (tax rate, folder), instead of hard-coding them.
  • Put reusable helpers (LastRow, SheetExists, CsvEscape) into a utility module and reuse them across projects.
  • Add a short comment header per procedure: purpose, inputs, outputs, side effects. Comment why, not what.

Defensive coding

  • Validate inputs at the start of each public procedure and fail early with a clear message.
  • Check for Nothing after Find, Dir, GetObject and sheet lookups.
  • Never assume a particular sheet is active; qualify objects.
  • Handle errors with a handler that cleans up (see error handling).
  • Avoid hard-coded paths; use ThisWorkbook.Path or a file dialog.
  • Test on small data first, then edge cases: empty data, one row, text in number columns, blank lines.
  • Keep a backup before macros that delete or overwrite. Macros cannot be undone with Ctrl+Z.

Security

Macros can do harm, so treat them carefully:

  • Save as .xlsm; only enable macros in files from trusted sources or trusted locations.
  • Sign your macros with a digital certificate for company-wide use.
  • Never put passwords or API keys in code. VBA project passwords are weak protection and not a security feature.
  • Avoid Shell with text built from user input.
  • Do not run macros that you did not read or understand.

Version control and documentation

Export modules (.bas, .cls, .frm) from the editor and keep them in a Git repository, because a binary workbook shows no useful differences. Keep a short README describing what the workbook does, which sheets it needs and how to run it.

Try it: why one big write beats many small ones

JS Counting boundary crossings

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

No output.

JS Nested loop versus dictionary lookup

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

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why restore Application.Calculation in a clean-up block?

    Settings changed for speed must be restored in every code path, including after errors.
  2. 2. What is the main reason to avoid Select and Activate?

    Direct object references are faster and do not depend on the active sheet or selection.
  3. 3. What is a benefit of named constants like COL_AMOUNT?

    Constants replace scattered magic numbers with one definition.
  4. 4. Is a VBA project password a real security feature?

    Never rely on it to protect secrets; keep secrets out of the workbook.

Summary

  • Speed comes from turning off screen updating, events and automatic calculation, and from working in arrays and dictionaries.
  • Restore every setting in a clean-up block.
  • Use Option Explicit, clear names, constants, small procedures and Private by default.
  • Code defensively, test edge cases, keep backups and never store secrets in a workbook.
  • Export modules to text files for version control.

Next: VBA Practice Exercises