VBAStudy Notes

Arrays in VBA

Store and process many values under one name with fixed and dynamic arrays, ReDim, and fast range-to-array techniques.

Automating Outlook and Word from VBA

Driving Outlook and Word from Excel VBA through Automation: creating application objects, composing and sending emails, and generating Word documents from temp…

Building a Report Automation Macro

A step-by-step walkthrough of building a real VBA macro that consolidates raw data, computes summaries, formats output, and exports a monthly report to PDF.

Conditionals in VBA

Learn how VBA evaluates Boolean expressions to branch code using If...Then...Else, ElseIf chains, and Select Case.

Debugging VBA Macros

Learn to find and fix bugs in VBA using breakpoints, stepping, the Immediate and Locals windows, watches, and Debug.Print so you can inspect exactly what your…

Dictionaries and Collections in VBA

Comparing VBA's built-in Collection with the Scripting.Dictionary for storing keyed data, and choosing the right one for lookups, de-duplication, and counting.

Error Handling with On Error

Intercept run-time errors gracefully in VBA using On Error statements, the Err object, and disciplined cleanup patterns.

Error Logging and Robust Macros

Build VBA macros that fail gracefully by combining structured On Error handling, a central error handler, the Err object, and persistent logging to a file or w…

Events in Excel VBA

Understand Excel's event-driven model — workbook, worksheet, and application events — and how to write handlers that respond automatically to user actions like…

Loops in VBA

Master repetition in VBA with counter-driven For...Next, collection-driven For Each, and condition-driven Do loops.

Performance Tips for VBA

Make VBA macros dramatically faster by minimising interaction with the Excel object model, disabling screen and calculation overhead, and moving data through a…

Ranges and Cells in VBA

Master the Range object — the workhorse of Excel VBA — including how to reference cells, read and write values efficiently, and navigate with properties like C…

Reading and Writing Cell Data

How to read from and write to worksheet cells in VBA using the Range and Cells objects, and how to move data efficiently between the sheet and VBA arrays.

Regular Expressions in VBA

Using the VBScript RegExp object to match, extract, and replace text patterns in VBA, including groups, global matching, and common gotchas.

Securing and Protecting VBA Code

Understand how to protect VBA projects and workbook contents, the real limits of VBA password protection, macro security settings, and digital signing to keep…

Subs and Functions in VBA

Understand VBA's two procedure types—action-performing Subs and value-returning Functions—plus arguments, scope, and UDFs.

The VBA Editor and Macro Recorder

A tour of the Visual Basic Editor and the Macro Recorder: the two tools every VBA beginner uses to write, explore, and learn code.

The Workbook and Worksheet Objects

Learn how the Workbook and Worksheet objects sit at the heart of Excel's object model, and how to reference, add, activate, and manage them reliably in VBA.

UserForms and Controls

Build custom dialog boxes in Excel VBA with UserForms and their controls — text boxes, combo boxes, command buttons and more — and wire up their events to crea…

VBA and ADO Database Access

Using ActiveX Data Objects (ADO) from VBA to connect to databases, run parameterized queries, and read results into a worksheet or recordset.

VBA and Python Alternatives

Compare VBA with modern alternatives — Python (openpyxl, pandas, xlwings), Office Scripts, and Power Query — to choose the right automation tool and understand…

VBA Best Practices

Practical conventions for writing maintainable, fast, and robust VBA — from Option Explicit and naming to error handling, performance, and avoiding Select/Acti…

VBA Data Types and Scope

How VBA stores values and controls their visibility: the core data types, variable scope and lifetime, constants, and object variables.

VBA Interview Questions

The concepts VBA interviews probe most — variables and scope, error handling, workbook/worksheet references, performance, and events — with model answers.