Microsoft Excel VBA

Course Overview

Microsoft Excel VBA

Use visual basic code to automate tasks and create procedures to run in Excel spreadsheets. 

Course Content

Macros Revision 
Recording and running macros 
Adding macros to the Excel interface 
Setting macro protection levels 
 
Getting Started with Visual Basic Editor 
Exploring the visual basic editor 
Understanding VBA procedures 
Commenting and editing VBA code 
  
Objects and Object-Oriented Programming 
Understanding objects and dot syntax 
Referencing cells with the range object 
Organising code using “with...end with” 
 
Editing Features 
Running code in the immediate window 
Completing code with auto lists 
 
Message Boxes and Input Boxes 
Creating and customising message boxes  
Receiving information from message boxes 
Creating input boxes to receive user data 
Understanding and customising input boxes 
 
Variables 
Understanding and naming variables 
Methods of declaring variables 
Defining variable data types 
Declaring and using constants 
Assigning values and objects to variables 
 
Writing Procedures and Function Procedures 
Calling procedures from different locations 
Understanding and using intrinsic functions 
Writing and using custom functions in VBA and the Excel interface 
 
Conditional Control Structures 
Controlling program flow using if structures 
Using select case structures 
Looping control structures 
Repeating code using loops 
Definite loops 
Indefinite loops 
 
Controls Dialog Boxes and Forms 
Creating user forms 
Using form controls 
Adding VBA code to user forms 
 
Using Pivot Tables 
Creating and manipulating pivot tables with VBA 
 
Debugging and Error Trapping 
Identifying error types 
Reducing errors in code 
Checking code with debugging tools 
Writing code to handle errors