1. Getting Started
  • 1. Course introduction
  • 2. Excel VBA overview
  • 3. The Developer Tab
  • 4. Macro security
  • 5. The Visual Basic Editor and Project Explorer
  • 6. Excel objects and event code
  • 7. Writing a macro in the VBE
  • 8. Using the Immediate window
  • 9. Saving a macro-enabled workbook
  • 10. The correct way to use the macro recorder
  • 11. Analysing a recorded macro
  • 12. Improving a recorded macro
  • 13. Assigning a macro to a button
  • 14. Testing a macro on another worksheet
2. Key components of VBA code
  • 1. The Excel object model
  • 2. VBA classes and functions
  • 3. Properties and methods
  • 4. Data variables
  • 5. Object variables
  • 6. Option explicit
  • 7. Declaring and using constants
  • 8. Using MsgBox for output
  • 9. Using MsgBox for input
  • 10. Using VBA InputBox
  • 11. Using Application.InputBox
3. VBA Control statements
  • 1. If statements
  • 2. If … ElseIf statements
  • 3. Select Case
  • 4. For … Next loops
  • 5. For Each … Next loops
  • 6. Do … While loops
  • 7. Do … Until loops
  • 8. Creating arrays
  • 9. Dynamic arrays
  • 10. Utilising arrays
4. Procedures and Functions
  • 1. Sub Procedures and module level variables
  • 2. Static variables
  • 3. Passing parameters
  • 4. Creating and calling functions
  • 5. Creating user-defined functions
5. Application Object
  • 1. Changing and restoring user settings
  • 2. Application.GetOpenFilename Part 1
  • 3. Application.GetOpenFilename Part 2
  • 4. Application.GetSaveAsFilename
  • 5. Controlling screen updating
  • 6. Suppressing warning messages
6. Code recognition clinic
  • 1. Example 1
  • 2. Example 2
  • 3. Example 3
  • 4. Example 4
  • 5. Example 5
  • 6. Example 6
  • 07. Hyper-disambiguation practice
7. Files and Workbooks
  • 1. Targeting workbooks efficiently
  • 2. Checking whether a workbook is open
  • 3. Checking whether several workbooks are open
  • 4. Checking whether a file exists
  • 5. Checking whether a folder exists
  • 6. Deleting files and folders
  • 7. Processing all files in folder
8. Worksheets and charts
  • 1. Targeting worksheets efficiently
  • 2. Copying worksheets
  • 3. Moving worksheets between workbooks
  • 4. Saving worksheets as separate workbooks
  • 5. Working with the sheets collection
  • 7. Creating an embedded chart
  • 6. Creating a standalone chart
9. The Range object
  • 1. Copying a range of cells
  • 2. Using the Offset property
  • 3. The End property
  • 4. The Resize property
  • 5. Entering formulas with VBA
  • 6. Deleting ranges
  • 7. Inserting ranges
  • 8. Outlining ranges