Objectives of the training
By the end of the training course, the participant will be able to create, modify, and extend macros, and build VBA programs.Targeted audience
Anyone wishing to discover VBA programming with Excel or understand how macros work, those programs that allow you to save considerable time when performing repetitive tasks. Whether you already have some basic knowledge or are a complete beginner in programming.Prerequisite
A good knowledge of ExcelTrainers
Course architecture
General presentation of macros and the VBA language
• Why create a macro?
• The limitations of recorded macros
• From recording the macro to entering it in the VBE
Macro Recording mode
• Principle of recording a macro
• Create a macro in Recording mode
• Use relative references when recording a macro
• Manage the debug window
• Exercise: Macro with relative references
Run a macro
• Use the menu or a keyboard shortcut
• Create an icon in the ribbon
• Add an icon to the Quick Access Toolbar
• Assign a macro to an object
• Excel file formats containing macros
• Manage security options
• Exercise: Run a macro from an object or an icon
View and modify the code of a recorded macro
• Display the code of a recorded macro
• Understand the syntax of a macro
• Run step‑by‑step and identify unnecessary lines
• Add comments
• Manage modules and macros
• Exercise: Manage modules and macros
Create structured and efficient macros (procedures)
• The structure of the VBA language
• Use input assistance
• Application object – Modify Excel properties
• Workbook object – Refer to a workbook
• Worksheet object – Refer to a worksheet
• Sheets collection – Refer to all sheets
• Range object – Refer to a range of cells
• Range object – Refer to a cell
• Refer to cells and ranges: demonstration
• Use online help and the Object Browser
• Exercise 1: Workbook and sheet manipulation
• Exercise 2: Gray and pink formatting
Conditional statements
• Principle of conditional statements
• Use a single condition: If… Then… End If
• Use multiple conditions: If… ElseIf… End If
• Use multiple conditions: Or/And
• Use multiple conditions: Select Case
• Exercise: Grade evaluations
Variables and constants
• What is a variable?
• Declaring variables
• Use a variable
• Variable scope
• Use constants
• Intrinsic constants
• Exercise: Retrieve data in another table
Communicating with the user: message boxes and dialog boxes
• Display a message for the user: MsgBox function
• Use a message box with several buttons to ask a question
• Display a dialog box: Application.InputBox
• Use message boxes and dialog boxes: demonstration
• Exercise: Manage the insertion and position of new sheets
Loops
• Principle of using a loop
• Do… Loop loop
• Do… Loop: demonstration
• For… Each loop
• For… Each: demonstration
• For… To… Next loop (counter loop)
• For… To… Next: demonstration
• Exercise: Country sales summary
Calculation formulas
• Principle of using calculation formulas in a procedure
• Use calculation functions available in Visual Basic
• Use calculation functions not available in Visual Basic
• Display in a cell a calculation formula created in a procedure
• Calculation formulas: demonstration
• Exercise: Data conversion
Array variables
• Concept of an array
• Use a fixed‑size array
• Populate an array using the Array function
• Use an array: demonstration
• Use multidimensional arrays
• Use a dynamic array
• Define the bounds of an array
• Resize an array while preserving initial values
• Use a multidimensional array: demonstration
• Exercise: In‑memory data conversion
Error handling
• Identify and react to errors
• Use local error handling for identified potential errors
• Use global error handling for all lines of code
• Resume code execution after correcting an error
• Exercise: New renamed sheet
Custom functions
• What is a custom function (Function)?
• Create and use a function without arguments
• Create and use a function with arguments
• Exercise: Creating content‑testing functions
Procedure or function calls
• Call a procedure in the same workbook: Call
• Call a procedure in another workbook: Application.Run
• Call a function in another workbook
• Exercise: Called calculation and formatting
Debugging tools
• Step‑by‑step mode and breakpoints
• Display variable, property, and function values during execution
• Use the Watch window to run a procedure step‑by‑step from a reached value
• Use the Immediate window to check results
Automate the creation of PivotTables and charts
• Use the Macro Recorder to create a PivotTable
• Use the Macro Recorder to create a chart
• Exercise: Summary table of average sales revenue
Event procedures
• Principle of using an event procedure
• Create an event procedure linked to the workbook (on open, before closing…)
• Set the parameters of an event procedure
• Create an event procedure linked to a targeted cell range
• Exercise: Creating a dynamic calendar
Forms
• Create a form in VBA (UserForm)
• The different types of controls
• Manage control properties and formatting
• Manage control events
• Show or hide a UserForm
• Retrieve form data in a worksheet
• Exercise: Travel entry form
Automation
• Principle of automation
• Create a Word document and fill it
• Create and populate an Outlook message
• Exercise: Retrieve data from an Excel table in a new Word document
Manage files and folders
• Search for a file using the Dir function
• Use the FileSystemObject to search for files
• Manage folders (create, delete, move…)
• Manage files (create, delete, move…)
• Exercise: Create folders to organize files by country
Book included
This module offers access to a digital course manual.
Macros and VBA language – Discover programming in Excel (new edition)
Training module
This VBA training module, lasting 1 hour and 40 minutes, is composed of 20 interactive VBA exercises for Excel.
Pedagogical details
Type of training
Training Notes
100% remote training. Accessible anytime, anywhere. One year of access to training and digital manual.
Private or personalized training
Do you have several employees interested in the same training course? Whether in person at your offices or remotely in virtual mode, we offer private training courses tailored to your team's needs. Group rates are available. Contact us for more details or request a quote online.
Request a quotePrivate or personalized training
Do you have several employees interested in the same training course? Whether in person at your offices or remotely in virtual mode, we offer private training courses tailored to your team's needs. Group rates are available. Contact us for more details or request a quote online.
Request a quote