Excel Macros from A to Z using VBA
This course refreshes the skills of Advanced users of Excel on the basics of Macros, then builds on the knowledge of participants to enable them to create, vet and edit Macros effectively and accurately using VBA.
- Duration
- 1 day
- Format
- Instructor-led, in person or live online
What the course covers
Prerequisites
All participants should have a strong working knowledge of Excel.
Overview
- What is VBA
- What can be done with VBA
- Other products containing VBA
- Storing VBA files
Using VBA
- How to get use VBA
- Using the VBA Editor
- Using the Integrated Development Environment
- Toolbars
- Menus
- VBA Windows
- VBA Syntax
Using Code Modules
- Naming Modules
- How to write code
- Editor Options
- Code Options
Excel Macros
- Creating a macro in Visual Basic
- Recording a macro,
- Examining the recorded VBA
- Editing the recorded VBA
The Excel Object Model
- Structure of an Excel Application
- Visual Basic Help
- Excel Object Browser
- Types of Excel Objects
Visual Basic Subroutines and Functions
- Using Windows message boxes
- Using Windows input boxes
- Creating VBA subroutines
- Creating VBA functions
Visual Basic Data Types and Variables
- What is a variable?
- Scope of a variable
- Size of a variable
- What is a DataType?
- String DataTypes
- Numeric DataTypes
- Date DataTypes
VBA Built-In Functions
- String manipulation functions
- Numeric manipulation functions
- Date manipulation functions
- Statistical functions
Controlling Excel Objects from VBA
- using Excel workbooks in VBA
- using Excel worksheets in VBA
- using Excel ranges in VBA
- finding lastrow in range object
VBA Arrays
- dimensioning arrays
- filling arrays
- displaying arrays
- redefining arrays
- multi-demensional arrays
Control of Flow in VBA
- using If statements
- using Case Select statements
- using For loops
- using ForEach loops
- using While loops
- interacting through collections
Excel methods, properties and events
- What are Methods?
- What are Properties?
- What are Events?
- Opening Workbooks with VBA
- Responding to Excel events, Open,Close
Debugging with VBA
- How to debug in VBA?
- using Immediate window
- using the Watch window
- using Breakpoints
- using Single step
- monitoring variables
UserForms in Excel
- What is a UserForm?
- Creating Userforms in Excel
- Using the Toolbox
- using Textboxes and Labels
- Using Comboboxes and Listboxes
- using CommandButtons
- Using Option and Checkbox controls
Practical Excel Forms lab
- Build a project to collect user data
- Design and Build Booking UserForm
- Write VBA code to control the UserForm
- Save the user data in Excel
- Userform opens when Excel loads
Connecting to other Applications
- VBA in other applications
- using Word via Excel VBA
- using Access via Excel VBA
Practical tips when using VBA
Course dates
There are no public dates for this course yet. We also run it on request for teams. Contact us to arrange a date.