4.9 (575 reviews)
$1435
Learning to program might seem intimidating, but with some patience and with examples in this course many users find that learning VBA makes their work easier and gives them the ability to do things in Office that they did not think were possible.
By the last day of the course, you will be able to write Excel VBA code that interacts with other Office applications such as Word, Access and Outlook.
Learn how to automate daily routine tasks within Excel
Learn best practices when writing code to streamline your VBA code
Understanding the limitations of the Macro Recorder
Learn how to write custom VBA code to create more robust automation
Learn VBA programming concepts to create dynamic, re-usable code
Learn how to write code for real world VBA projects from beginning to end
Learn to write code to automatically send information from Excel using Outlook
Develop code that writes to or merges with Microsoft Word
Learn how to use VBA to incorporate speech in your applications.
We encourage you to bring a USB to take away completed examples.
Course Overview
This course is structured as one day per week over three weeks to give you plenty of time to consolidate the information between sessions.
Audience:
This course had been designed for the non-technical user who needs to design & create applications in Excel.
Prerequisites:
Participants must have a good understanding of Excel spreadsheets in particular working with ranges.
Inclusions:
• Experienced Microsoft Certified Instructor with Excel development experience
• Comprehensive Training Manual
• Course exercise files ... with completed code
• Unlimited post-course online support to the level of the course
• Access to VBA Snippets newsletter
Course Content
Introduction to VBA
- Why use VBA if I can record macros in Excel?
- Recoding a Simple macro
- Reviewing the code
- Familiarisation with the VBA environment
- Running Code from the VB Editor window
- Getting help on code
- Stepping through a procedure
- Using a Break point
- Communicating with the User
VBA Terminology
- Modules and procedures
- Components of your code
- Objects, Collections, Properties and methods
- Using the Excel Object model
Data Types, Variables and Operators
- Working with variables and constants
- Using Data types
- Working with operators and expressions
- Implicit and Explicit Declarations
- Variable Scope
- Passing variables by Value
- Passing variables by Reference
- Using Arrays
Workbooks and Worksheets
- Workbook objects and methods
- Creating a new workbook
- Adding and Renaming Sheets
- Saving and Closing a Workbook
Functions
- Mathematical functions
- Date and time functions
- String functions
- Using Excel functions in VBA code
User Defined Function Procedures
- Creating User Defined functions
- Using user-defined functions in a worksheet
- Declaring Multiple arguments in a function
- Creating a Function Library
Loops
- Do Until and Do While loops
- Looping at least once
- For Next Loop
- For Each Loops with collections
Decision Structures
- Using IF to make decisions
- Testing for multiple conditions
- Establishing Flow control
- Branching
- Call other procedures
Working with Names
- Adding Names
- Deleting Names
- Creating Hidden Names
- Checking for the Existence of a Name
Manipulating data
- Working with the Ranges and Selections
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Columns and Rows Properties
- Using the Union Method to Join Multiple Ranges
- Using the IsEmpty Function
- Using the CurrentRegion Property
- Using the Areas Collection
File management techniques in VBA
- Open and Save files
- Copy, move and delete files
- Changing folders
- Using Excel Open and SaveAs dialog boxes in code
Custom Forms / Dialog boxes
- Creating a user form
- Labels, text boxes, combo boxes and list boxes
- Setting properties for the form and controls
- Assigning data to combo boxes and list boxes
- Option buttons, Groups, checkboxes and buttons
- Creating the event code for controls
- Initialising the form
- Closing the form
- Using RefEdit to allow user interaction
Managing Information with VBA
- Linking Excel to an Access database
- Adding a Record to the Database from Excel
- Retrieving Records from the Database
- Updating an Existing Record
Available Excel VBA courses
Excel VBA courses are conducted one day per week over 3 weeks to allow participants to practice the content before continuing. Practice activities will be assigned at the completion of each session.
| Event | Event Date | Location | Individual Price | Register |
|---|---|---|---|---|
| Excel Programming & Automation VBA | 23-01-2025 | Live Online | $1,495.00 | |
| Excel Programming & Automation VBA | 17-03-2025 | Live Online | $1,495.00 | |
| Excel Programming & Automation VBA | 26-05-2025 | Live Online | $1,495.00 |
Brochure