Make sure this course is right for you.
Get unbiased reviews and personalized recommendations.
Who should attend
The course will suit anyone who finds themselves repeating Excel tasks regularly and / or wants to learn how to design their own bespoke Excel Functions. The only pre-requisite for this course is having a reasonable understanding of Microsoft Excel.
About the course
The purpose of this two day hands-on course is to gain an understanding of how Excel VBA can save you time through automation of repetitive Excel tasks. Day one is a core skills day, delivered with tutor-led mini exercises, designed to build up programming confidence. Here we spend a good amount of time on language structure and syntax. Day two applies the concepts to a real-life business problem, where delegates are given freedom to design a solution with tutor guidance.
Key Learning Outcomes:
Record, Write, Edit and Run Macros and Functions with confidence.
Introduction to Macros
- Recording absolute and relative macros (sub procedures)
- Running Macros with Form Controls, Shapes, Shortcut Keys
Tour of the Visual Basic Editor (VBE)
- Location and uses of the VBE windows:
- Project Explorer
- Customising the look and feel of the VBE, including window docking
Editing and Writing Code
- Language Structure: Objects, Properties and Methods (and Optional Parameters)
- Using the Intellisense List (CTRL+SPACE)
- Testing code with the Immediate Window, including Debug.Print
- With…End With to assign multiple properties
- VBA Functions
- Working with Dates
- Using Comments / Remarks
- Common Error-types: Syntax, Compile, Run-time
- Getting Help: F1, Macro Recording, Web Resources
Using Variables and Data Types
- Declaring and assigning values to variables and constants
- Data Types E.g. Integer, Double, String, Boolean
- Using Option Explicit, Intellisense List to avoid spelling errors and speed up the coding process
*User Interaction *
- Message Boxes - to display messages and progress
- Input Boxes - to collect data or obtain user feedback
Controlling Flow and Making Decisions
- Loop Structures
- Do While / Until…Loop
- For Each…Next (using Object Variables)
- IF, Then, Else Construction
- Counters and Flags
- Calling Excel Functions
- Debugging and Testing Code with Step Through (F8), Breakpoints (F9), Locals window
- Halting Procedures with CTRL+BREAK / PAUSE
- Private and Public Subs, Functions, Variables
- Project, Module and Procedure (Local) Scope
Further IF and Select Case
- IF, Else, Else IF
- Select Case, Case Is, Case Else
*User Defined Functions (UDFs) *
- Creating UDFs with Function Arguments
- Optional Arguments
- Testing / Calling UDFs with Breakpoints (F9)
- Calling UDFs in Excel
Efficient Error Handling
- On Error Resume Next
- On Error Goto
- Handling Error Numbers
- Resume Next and Exit Statements
*Class Guided Project: VBA UserForms *
This project runs in 3-4 hours. We will design a UserForm to select and import CSV files upon which we output some useful calculations.
- Designing UserForms with check boxes, command buttons, labels etc
- Formatting via the Properties Window
- The Power of Hungarian Notation
- Showing and Unloading UserForms
- Setting File path variables
- Toggling Automatic Calculations, Display Alerts, Screen Updating
- Testing with Breakpoints (F9)
- Worksheet events E.g. SelectionChange,, Change
- Workbook Events E.g. AfterSave, Open, BeforeClose, NewSheet
- Testing with Breakpoints (F9)
Class Takeaway Project: Building Loan Schedules
This project takes 1-2 hours. Delegates are tasked with building out automated loan schedules which handle bullet /amortisation structures.
Videos and materials
Because of COVID-19, many providers are cancelling or postponing in-person programs or providing online participation options.
We are happy to help you find a suitable online alternative.