Excel Advanced (VBA)

Course Overview

Create macros using the macro recorder and the Visual Basic programming language. Create dialog boxes or elements that interact with the user.

Prerequisites

Microsoft Excel Intermediate, more specifically the workbooks and functions sections are essential prerequisites.

The Databases section is an asset.

Prerequisites

Intermediate Microsoft Excel. Have an idea about what indicators you will use.

Syllabus

Macro Commands:

• Automating recurring tasks;
• Creating and saving macros;
• Running macros;
• Opening a workbook containing macros;
• Running macros;
• Editing macros;
• Editing macros;
• Working with absolute and relative references in Visual Basic;
• Working with the work book containing custom macros and execution errors;
• Defining the work book containing custom macros;
• Debugging errors found in macros;
• Managing macros.

Introduction to VBA

– Part 1: Why use VBA?

• Opening the VBA editor;
• Defining the properties of objects;
• Notions required to learn VBA;
• Creating a procedure;
• Creating a sub-procedure and writing code;
• Using online help;
• Using objects;
• Using the Application, Workbook, Worksheet and Range as well as the Sheets objects;
• Defining variables and constants;
• Defining variables and constants;
• Identifying the span of variables and constants;
• Applying the Explicit option;
• Using information box functions;
• Defining the Function procedures.

Why create custom functions?

Introduction to VBA– Part 2:

• Defining control structures;
• Using conditional instructions, loops and logical operators in expressions;
• Working with the object explorer and managing errors;
• Using the Screen Updating function;
• Using the With instruction;
• Working with the object explorer;
• Managing errors;
• Using Excel and VBA functions in procedures;
• Entering a formula inside a cell;
• Creating and using a custom function;
• Diverse and interesting procedures;
• Adding the file path in the footer;
• Using switching procedures;
• Reaching an address and activating it in the upper left corner of the window;
• Learning procedures to work with a range of cells.

Dialog Boxes:

• Working with dialog boxes;
• Creating and using a user interface;
• Placing controls on a sheet;
• Working with controls on a sheet;
• Formatting controls;
• Using additional controls;
• Working with the code associated to events;
• Identifying events;
• Initializing dialog boxes;
• Writing event-driven procedures for controls inside a dialog box.