Objectives of the training
By the end of the training course, the participant will be able to use ChatGPT, Copilot, and Midjourney, as well as prompting best practices to communicate effectively with AI.Targeted audience
Anyone wishing to discover and use the main generative AIs for text and images (ChatGPT, Copilot, and Midjourney).Prerequisite
NoneTrainers
Course architecture
General Presentation of Macros and VBA Language
• Why create a macro?
• Limitations of recorded macros
• From macro recording to typing in VBE
Macro Recording Mode
• Principle of recording a macro
• Creating a macro in recording mode
• Using relative references when recording a macro
• Managing the debug window
Running a Macro
• Using the menu or a keyboard shortcut
• Creating an icon in the ribbon
• Adding an icon to the Quick Access Toolbar
• Assigning a macro to an object
• Excel file formats containing macros
• Managing security options
Viewing and Modifying the Code of a Recorded Macro
• Displaying the code of a recorded macro
• Understanding macro syntax
• Running step-by-step and identifying unnecessary lines
• Adding comments
• Managing modules and macros
Creating Structured and Efficient Macros (Procedures)
• VBA language structure
• Using the AutoComplete feature
• Application Object – Modifying Excel properties
• Workbook Object – Referencing a workbook
• Worksheet Object – Referencing a worksheet
• Sheets Collection – Referencing all sheets
• Range Object – Referencing a range of cells or a single cell
• Using online help and the Object Browser
Conditional Statements
• Principle of conditional statements
• Using a single condition: If… Then… End if
• Using multiple conditions: If… ElseIf… End If
• Using multiple conditions: Or/And
• Using multiple conditions: Select Case
Variables and Constants
• What is a variable?
• Declaring variables
• Using a variable
• Variable scope
• Using constants
• Intrinsic constants
Communicating with the User: Message Boxes and Dialog Boxes
• Displaying a message to the user: MsgBox function
• Using a message box with multiple buttons to ask a question
• Displaying a dialog box: Application.InputBox
• Using message boxes and dialog boxes: demonstration
Loops
• Principle of using a loop
• Do… Loop loop
• Do… Loop loop: demonstration
• For… Each loop
• For… Each loop: demonstration
• For… To… Next loop (counter loop)
• For… To… Next loop (counter loop): demonstration
Calculation Formulas
• Principle of using calculation formulas in a procedure
• Using calculation functions available in Visual Basic
• Using calculation functions not available in Visual Basic
• Displaying a calculation formula created in a procedure in a cell
• Calculation formulas: demonstration
Array Variables
• Concept of an array
• Using a fixed-size array
• Populating an array using the Array function
• Using an array: demonstration
• Using multidimensional arrays
• Using a dynamic-size array
• Defining array bounds
• Resizing an array while preserving its initial values
• Using a multidimensional array: demonstration
Error Handling
• Identifying and responding to errors
• Implementing local error handling for identified potential errors
• Implementing global error handling for all lines of code
• Resuming code execution after correcting an error
User-Defined Functions
• What is a user-defined function (Function)?
• Creating and using a function without arguments
• Creating and using a function with arguments
Procedure or Function Calls
• Calling a procedure from the same workbook: Call
• Calling a procedure from another workbook: Application.Run
• Calling a function from another workbook
Debugging Tools
• Step-by-step execution and breakpoints
• Displaying values of variables, properties, and functions during execution
• Using the Watch window to run a procedure step-by-step from an reached value
• Using the Immediate window to control results
Automating PivotTable and Chart Creation
• Using the Macro Recorder to create a pivot table
• Using the Macro Recorder to create a chart
Event Procedures
• Principle of using an event procedure
• Creating a workbook-related event procedure (on opening, before closing, etc.)
• Populating the parameters of an event procedure
• Creating an event procedure linked to a specific cell range
Forms
• Creating a form in VBA (UserForm)
• Different types of controls
• Managing control properties and formatting
• Managing control events
• Showing or hiding a UserForm
• Retrieving data from a form into a worksheet
Automation
• Principle of automation
• Creating a Word document and populating it
• Creating and populating an Outlook message
Manipulating Files and Folders
• Searching for a file with the Dir function
• Using the FileSystemObject to search for files
• Managing folders (creating, deleting, moving, etc.)
• Managing files (creating, deleting, moving, etc.)
Supplementary Book
This module offers access to digital course material.
Macros and VBA Language – Discover Programming in Excel (new edition)
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