VBA macros course outline

This 2 day Excel course is designed for participants who want to discover how to harness the power of VBA and use macros to automate spreadsheet routines.

The course assumes familiarity with Excel but no prior knowledge of VBA and serves as a first introduction to macros and programming.

The power of the macro recorder

Delegates use the macro recorder to start to automate spreadsheet routines. They watch the recorder write code in front of their eyes, before testing their macro and playing back the new routine.

Starting with Excel VBA macros

  • Introducing the power of the Excel macro recorder
    • Becoming a programmer without knowing code
    • Working in Excel and watching the code write itself
    • Checking the code
    • Making sense of the VBA code
    • Simplifying the code
    • Running your new macro manually
VBA programming: delegates work with the VBA macro recorder to create a new macro and play it back

Getting started with dialog boxes

Delegates start to discover what they can do with VBA outside of/ in parallel to the macro recorder. Course attendees learn how to build user forms that provide key messages, solicit inputs and display results for the user.

VBA message boxes

  • Becoming a coder
    • Working with dialog boxes
    • Creating your own dialog boxes
    • Getting dialog boxes to display as ‘event markers’ for program flow
Delegates create a macro that contains a simple message box
  • Adding more complex message boxes
    • Other types of message box
    • Directing program flow using a message box
    • Soliciting variables using input boxes
Delegates add other types of message boxes to their macro, using an input box to solicit information from users

Doing work with variables

Delegates get their macro to do some real work, manipulating inputs the user has previously entered into dialog boxes. Course delegates get their macro to branch using if-then-else statements before using message boxes to display different results to the user.

Getting the macro to do some work

  • An introduction to variables
    • Variables as storage spaces
    • Doing ‘work’ on a variable: creating simple formulas in VBA using basic mathematical operators
    • Outputting the results using message boxes
    • Declaring a variable
Delegates continue to develop their macro, using mathematical operators to alter inputs provided by the user and displaying the output
  • VBA’s if-then-else statement
    • Using VBA “if” to alter program flow
    • VBA’s object-properties-method structure
    • Tools to help understand and expand the code you are writing
    • Excel VBA help
    • Error checking and the goto statement
Course delegates combine what they have been learning, soliciting user inputs, using certain of those inputs to direct program flow and displaying outputs dependent on the options chosen by the person running the macro

Custom user forms

Delegates design and draw their own custom user form before creating a macro that incorporates everything they have learned to date.

Custom user forms

  • Creating custom user forms
    • Using the template to draw custom user forms
    • Adding functional elements to the user form
    • Viewing user form code
    • Getting the user form to display as part of the program
Participants draw their own user form and add it to their macro
  • Improving the custom user form
    • The benefit of public variables
    • Making sure the work done by variables is available to the user form
    • Adding functionality to the command button
Course participants continue to refine their user form macro
  • Worksheet functions and loops
    • Beyond basic operators: manipulating variables using the functions we know and love from Excel
    • Processing data using macro loops
    • Different types of macro loop
    • Course summary and conclusion: continuing the learning
Participants create a macro that works through a list of data until it gets to the bottom

Download the outline for the VBA macros course

Here you can download the online Excel VBA macros course outline.