VBA

Scripting inside Microsoft Office.

VBA is taught here in 14 lessons, running from The VBA editor and the language through to Distributing macros: add-ins, signatures and security. Each lesson takes one topic, shows the working code, and links onward to the next, so the course can be read straight through in order.

Lessons

1. The VBA editor and the languageFinding the VBE, what a macro-enabled file really is, and the declarations and types that keep a module honest.
2. Macros, worksheets and rangesThe Office object model, reading whole blocks into arrays instead of cell by cell, and where event handlers must live.
3. Error handling and the classic pitfallsOn Error in place of try/catch, a cleanup pattern that always restores state, and the mistakes that bite every VBA author.
4. Control flow: If, Select Case and loopsBranch and loop predictably in VBA, and stop writing the off-by-one and infinite-loop bugs that break real workbooks.
5. Sub, Function and passing argumentsChoose between Sub and Function, understand the ByRef default that bites everybody, and scope procedures across modules.
6. Arrays, collections and dictionariesUse static and dynamic arrays well, ReDim Preserve without quadratic costs, and reach for Scripting.Dictionary for lookups.
7. Strings, dates and number formattingSplit, Join and Replace text, format numbers and dates reliably, and avoid the locale traps that silently corrupt data.
8. Files and folders: FileSystemObject and TextStreamTest and create paths, read and write text files, walk directories, and import CSV without corrupting the data.
9. UserForms, MsgBox and InputBoxPrompt and validate with the built-in dialogs, lay out a UserForm, and pass values back to the caller cleanly.
10. Speed: ScreenUpdating, calculation and array transfersTurn off the right things while working, move data in blocks instead of cells, and stop using Select and Activate.
11. Debugging: breakpoints, Watch and the Immediate windowStep through code, inspect state with the Locals and Watch windows, and use Debug.Print and Stop to find the bug fast.
12. Automating Word, Outlook and other Office appsDrive other applications with CreateObject and GetObject, choose early or late binding, and release object references properly.
13. Calling web APIs and parsing JSON in VBAMake GET and POST requests with MSXML2 and WinHttp, set headers, parse JSON without a library, and handle timeouts and errors.
14. Distributing macros: add-ins, signatures and securityShip macros safely with Trust Center settings, digital signatures and add-ins, and plan the migration path to Office Scripts.

More in Programming Languages

C C++ C# Scala Lua Dart

FAQ