Macros and VBA language – Learn to program in Excel

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.