Development using macros
- Defining and identifying the limits of a macro: Need to use VBA.
- Recording a macro and displaying the code.
- Assigning a macro to a button.
- Using variable dimension ranges.
Hands-on work
Overview of the TOSA® test, and head count of participants when going around the room. Recording macros to learn about VBA code.
Getting started with Excel VBA language
- Viewing a macro’s code and making simple changes in an editor.
- Identifying what sets different versions of Excel apart.
- Understanding the VBA environment: Modules, projects, objects, and their properties.
- Knowing the main objects of Excel (Workbook, Worksheet, Range), their properties and essential methods.
Hands-on work
Working on variable dimension ranges. Consolidating information from multiple tabs.
Language structure
- Knowing conditionals: IF ...END IF, SELECT CASE. FOR NEXT, DO WHILE, UNTIL, FOR EACH NEXT loops.
- Understanding variables and constants. Documenting the code.
- Managing runtime errors, debugging.
- Working with procedures and functions. Calls, branches.
Hands-on work
Analyzing and then creating code to act conditionally on cell ranges.
Dialog box and forms
- Using the message box, input box. Excel dialogs.
- Defining and understanding the role of a UserForm in Excel.
- Describing a form’s different objects, control properties in a form.
- Retrieving, controlling, and using data entered in the form.
Hands-on work
Creating a UserForm to use the entered data.
Event procedures
- Understanding the event procedure. The first workbook and sheet events (Open, Activate).
- Initializing and configuring a control: Control events in UserForm.
Hands-on work
Defining actions to be run when a workbook is opened, when a UserForm is displayed.
Communicating with Word or PowerPoint
- Using automation, referencing a library.
- Working with the Application object.
Hands-on work
With VBA, transfer an Excel table into a Word document or PowerPoint presentation. Optional: Certifying your skills with TOSA®.
4d
- 28h00 
