Excel Visual Basic for Applications (VBA) - course outline

This two-day course is aimed at anyone who needs to understand and write code that operates within Excel. You will learn how to automate any repetitive task, add new functionality to Excel and format data into reports, or for upload into another format - at the click of a button.

This hands-on course is led by a highly experienced Excel VBA trainer and involves working on a number of practical exercises, in a workshop environment. Time-permitting, there will be an opportunity to work with the trainer on your specific Excel VBA solutions.

By the end of the two-day course, you will be able to:

• Automate complex or repetitive procedures
• Read, edit and debug Visual Basic code
• Understand the language: Objects/Collections, Properties and Methods
• Create user-interactive macros
• Build new user-defined functions
• Create custom Dialog Boxes/Forms

Main topics covered

• Recorded Macros: incorporating recorded macros in your code, avoiding the common problems, understanding the limitations
• How to run a Macro: Buttons, the quick-access toolbar, assigning shortcut keys
• Finding and reading the code: modules, comments, keywords, and statements
• Understanding the Object model: the rules of grammar and syntax in a programming language, how to apply these, the Object-Browser
• Interacting with the user: Input Boxes and Message Boxes and forms
• Looping: 'For' loops, 'Do' loops and nested loops
• Variables: using and declaring them, types and scope of variables
• Calling Functions/procedures, using Functions within Macros
• Making decisions: the If statement, the Block-If statement, ElseIf, Select-Case
• Custom Functions: creating and using new functions on worksheets and in VBA code
• Debugging: how to trap or respond to an error, the debug window, stepping through your Macro, the Watch window, the Immediate Pane
• Dialog Boxes/Forms: designing and building your own, adding controls, showing, controlling and interacting with your dialog boxes

This course outline is a guide. All our courses can be tailored to customer requirements.

Target audience
Anyone who needs to understand and write code that operates within Excel.

Assumed knowledge
Delegates should be familiar with the basic features of Excel, and should have some experience of the topics covered in our Introduction and Intermediate Courses, either from attending these courses or by knowledge gained from their own experience.