Select a part of the course to start

1. Theory - Introduction to VBA and Macros

What is VBA?

• VBA (Visual Basic for Applications) : Integrated programming language in Microsoft Office, used to automate repetitive tasks in Excel.
• Role in Excel : Automate formatting, calculations, data management, or even create custom interfaces.
• Usage examples : Format tables, generate reports, validate data.

Macros

• Definition : A macro is a series of instructions recorded or written in VBA to execute tasks automatically.
• Recording : Excel can record user actions (e.g., formatting, data entry) to create a macro.
• Run : Macros can be launched via the Developer tab or keyboard shortcuts.

VBA Editor

• Access : Press Alt+F11 (on Windows) to open the VBA editor.
• Components :
  • Project Explorer: List of workbooks and modules.
  • Code window: Where macros are written or modified.
  • Properties window: To handle object names (and some properties).
• Modules : Containers for VBA code (Insert > Module in editor).

Security

• Macros and security : Macros may contain malicious code, so enable macros only for trusted files.
• File format : Save workbooks with macros in . xlsm.
• Settings : Enable macros via File > Options > Privacy Dashboard > Macros settings > Enable all macros (for testing, with caution).

Good practices

• Always save a copy of the file before testing a macro.
• Name macros descriptively (e.g. FormaterTableau instead of Macro1).
• Test macros on dummy data to avoid data loss.

2. Demonstrations with an example

Save a Macro to Format a Book List

Steps:

1. Open a new Excel workbook and save it as Librairie.xlsm.
2. In the sheet "Sheet1", enter the following headers in A1:C1:
   • A1: ID
   • B1: Title
   • C1: Price
3. Enter fictitious data in A2:C3:
   • A2:C2: 1, "The Little Prince", 12.99
   • A3:C3: 2, "1984", 15.99
4. Record a macro:
   • Go to Developer > Save a macro.
   • Name the macro FormaterListeLivres.
   • Perform the following actions:
     • Select A1:C3.
     • Apply bold formatting to A1:C1.
     • Add borders to the range A1:C3.
     • Apply a yellow background color to A1:C1.
   • Click Stop recording.
5. Test the macro:
   • Go to Developer > Macros > FormaterListeLivres > Run.
   • Check that the formatting is applied.

???? Expected outcome:

1. Theory - Basics of VBA Programming

Variables

• Definition : A variable is a named memory space to store data (numbers, text, etc.).
• Declaration : Use Dim to declare a variable (e.g., Dim counter As Integer).
• Common data types :
  • Integer : Integer number (e.g. 1, 10, -5).
  • Double : Decimal number (e.g. 12.99).
  • String : Text (for example, "The Little Prince").
  • Boolean : True or False (e.g. True, False).
• Good practices :
  • Always declare variables before use
  • Use speaking names (e.g., prixLivre instead of x).

Loops

• Role : Allow repeated execution of instructions.
• Loop Types :
  • For... Next : Repeat a fixed number of times (e.g., to cycle through lines).
  • Do... Loop : Repeat until a condition is met.
• Syntax :

Basic instructions

• MsgBox : Displays a dialog box with a message (e.g., MsgBox "Task completed").
• Range : Manipulates cells (e.g., Range("A1").Value = "Text").
• Cells : Accesses cells by their coordinates (e.g., Cells(1, 1).Value for A1).
• Concatenation : Combine text and variables with & (e.g., "Book " & i).

Good practices

• Add Explicit option at the top of the module to force the declaration of variables.
• Avoid using . Select or . Activate (prioritize Range or Cells directly).
• Test macros on dummy data to anticipate and avoid errors.
• Document the code with comments (') to explain each step.

2. Demonstrations with examples

Example 1: Add Book Titles with a Loop

Steps:

1. Create a new Excel workbook and save it as Librairie_Leçon2.xlsm.
2. Open the VBA editor (Alt+F11), insert a new module (Insert > Module).
3. Write and execute the following code:

Check that:

• Cells A1:A5 contain "Book 1", "Book 2", ..., "Book 5".
• A message appears at the end.

Example 2: Add Prices with a Loop Do... Loop

Check that:

• B1:B5 cells contain 12.5, 15.0, 17.5, 20.0, 22.5.
• A message appears at the end.

1. Theory - Manipulation of Leaves and Cells

Excel Objects

• Workbook : Represents an Excel workbook (e.g., ThisWorkbook for the active workbook).
• Worksheet : Represents a spreadsheet (e.g., ThisWorkbook.Sheets("Sheet1")).
• Range : Represents a range of cells (e.g., Range("A1:B10") or Cells(1, 1)).

Properties

• . Value : Reads or sets the value of a cell (e.g., Range("A1").Value = "Text").
• .Font.Bold : Bolds the text (e.g., Range("A1").Font.Bold = True).
• .Interior.Color : Sets the background color (e.g., Range("A1").Interior.Color = vbYellow).
• . Name : Sheet name (e.g., Sheets("Feuil1").Name = "Books").

Methods

• . Select : Selects a range (avoid if possible for optimal performance).
• . Copy : Copies a range (e.g., Range("A1:A10").Copy).
• . ClearContents : Removes the contents of a range (e.g., Range("A1:A10").ClearContents).
• . Add : Adds a new sheet (e.g., ThisWorkbook.Sheets.Add).

Dynamic Access

• Cells : Accesses a cell via its coordinates (row, column), for example, Cells(1, 2) for B1.
• Rows.Count : Total number of lines in a sheet (e.g., Sheets("Feuil1").Rows.Count).
• End(xlUp) : Find the last non-empty row (e.g., Cells(Rows.Count, 1).End(xlUp).Row).

Good practices

• Use Cells instead of Range for dynamic loops.
• Avoid . Select or . Activate to improve performance.
• Always declare variables with Option Explicit.
• Test macros on file copies to avoid data loss.

2. Demonstrations with examples

Example 1: Create Sheet and Add Headers

Example 2: Add Data with Cells

1. Theory - Conditions and Error Handling

Conditional Structures

• Role : Allow the code to make decisions based on conditions.

There are several conditional structures

• If... Then... Else :
  • Syntax :

• Example usage: Check if a price is greater than 10.

• Select Case :
  • Syntax :

• Use example: Assign a price category (low, medium, high).
• Combined conditions : You can use the operators And, Or, Not for complex conditions (e.g., If price > 10 And disponible = True Then...).

Error Handling

• Role : Anticipate and manage errors to avoid the macro stopping abruptly.
• On Error Resume Next : Ignore errors and continue execution.
  • Example: Attempt to read a cell that might be empty.
• On Error GoTo : Redirects execution to a label in case of an error.
  • Syntax :

• Err Property : Contains error information (Err.Number, Err.Description).
• Good practices :
  • Use On Error sparingly and re-enable normal error handling with On Error GoTo 0.
  • Display clear error messages to the user.

Good practices

• Always test conditions on dummy data.
• Use messages (MsgBox) to inform the user of results or errors.
• Document conditions and error handling with comments.
• Avoid infinite loops in complex conditions.

2. Demonstrations with examples

Example 1: Check the Price of a Book with If... Then

Example 2: Categorize Prices with Select Case

1. Theory - Advanced Loops and Tables

Advanced Loops

• Role : Browse through dynamic data ranges (e.g., all non-empty rows of a column).
• Key Techniques :
  • Find the last line : Use Cells(Rows.Count, column). End(xlUp). Row to find the last non-empty line in a column.
  • Dynamic Loop : Cycle through rows or columns without knowing their size in advance.
  • Nested loop : Use one loop inside another (e.g., traverse rows and columns).
• Example : Browse all the books in a sheet to list their titles.

VBA Tables

• Definition : An array is a variable that can store multiple values (e.g., a list of titles).
• Declaration :
  • Static: Dim tableau(4) As String (5 elements, indices 0 to 4).
  • Dynamic: Dim tableau() As String, resized with ReDim tableau(n).
• Manipulation :
  • Store values: array(0) = "Book 1".
  • Rescale: ReDim Preserve tableau(n) to retain existing data.
  • Joindre: Join(array, ", ") to convert a array into text.
• Application example : Store book titles in a table and display them.

Good practices

• Use Cells with loops to access data dynamically.
• Always check the size of tables before accessing them to avoid errors.
• Combine loops and conditions to filter data (e.g. list only available books).
• Document the code with comments to explain loops and tables.

2. Demonstrations with examples

Example 1: List Book Titles with a Dynamic Loop

Example 2: Store Prices in a Table

1. Theory - User Forms Creation

UserForms

• Definition : A UserForm (user form) is a custom graphical interface created in the VBA editor to interact with the user.
• Access : In the VBA editor (Alt+F11), Insert > UserForm.
• Components :
  • Design window: where controls are placed visually.
  • Toolbox: contains controls like TextBox, Label, Button, ComboBox.
  • Properties window: allows configuring the properties of controls (for example, Name, Caption).

Common controls

• Label : Display static text (e.g. "Book title").
• TextBox : Allows the user to enter text or numbers.
• ComboBox : Drop-down list to select an option from several.
• CommandButton : Button to trigger an action (for example, "Add").
• Key Properties :
  • Name : control name for VBA code.
  • Caption : text displayed on the control (e.g. "Add" for a button).
  • Value : control content (for example, text entered in a TextBox).

Interaction with leaves

• Display a UserForm : UserForm1.Show in a macro.
• Close a UserForm : Unload Me in the form code.
• Write in a sheet : Use Range or Cells to insert the entered data.
• Application example : Add a book via a form with fields for title, author and price.

Good practices

• Name the controls in a speaking manner (e.g., txtTitre, btnAjouter).
• Validate user inputs to avoid errors (e.g. check if a field is empty).
• Use messages (MsgBox) to confirm actions or report errors.
• Test forms with dummy data.

2. Demonstrations with examples

Example 1: Create a UserForm to Add a Book

Configuration:

1. Create a new excel workbook and save it as Lecon6.xlsm.
2. In the "Books" sheet, enter the headers in A1:D1: "ID", "Title", "Author", "Price".
3. Open VBA editor (Alt+F11), Insert > UserForm.
4. In the UserForm (named UserForm1 by default):
   • Add a Label (Caption: "Title"), named lblTitre.
   • Add a TextBox, named txtTitre.
   • Add a Label (Caption: "Author"), named lblAuthor.
   • Add a ComboBox, named cboAutory.
   • Add a Label (Caption: "Price"), named lblPrix.
   • Add a TextBox, named txtPrix.
   • Add a CommandButton (Caption: "Add"), named btnAjouter.

UserForm Code:

Macro to display the form:

1. Theory - VBA Events

Introduction to Events

• Definition : VBA events are automatically triggered procedures in response to actions in Excel (e.g. changing a cell, opening a workbook).
• Event Types :
  • Sheet events: triggered by actions in a specific sheet (e.g., Worksheet_Change for a cell change).
  • Workbook events : triggered by actions at the workbook level (e.g., Workbook_Open upon opening).
• Access : in the VBA editor (Alt+F11), double-click on the ThisWorkbook object or a sheet in the Project Explorer, then select an event from the drop-down list.

Current Events

• Workbook_Open : run on opening the workbook (for example, to initialize settings).
• Worksheet_Change : executed when a cell is modified in a sheet.
• Worksheet_SelectionChange : executed when a cell is selected.
• Syntax :

• Target : Represents the range of cells modified.

Event Management

• Validation : Use conditions to check for changes (e.g. if a changed cell is in a specific range).
• Temporary deactivation of events : Use Application.EnableEvents = False to avoid infinite loops.
• Good practices :
  • Limit the use of events to avoid slowing down Excel.
  • Always reactivate events with Application.EnableEvents = True after deactivating them.
  • Test events on dummy data to avoid unwanted changes.

2. Demonstrations with examples

Example 1: Validate Prices with Worksheet_Change

Example 2: Show Opening Message with Workbook_Open

1. Theory - Automated Reporting

Concepts of Automated Reporting

• Definition : An automated report is a synthesis of data generated by a VBA macro, often presented in a new sheet or external file.
• Components :
  • Data collection : browse sheets with loops to extract information.
  • Treatment : use tables and conditions to calculate statistics (e.g. mean, total).
  • Presentation : write the results in a sheet with formatting or export them to a text file.
• Key Methods :
  • Range and Cells for writing data.
  • Worksheets.Add to create a report sheet.
  • Open and Print to write in a text file.

Advanced techniques

• Automatic formatting : apply borders, colors, and fonts with . Borders, .Interior.Color, . Font.
• Data Export : use VBA to write in a text file with FileSystemObject or simple methods like Open.
• Error handling : anticipate cases where sheets or data are missing.
• User interaction : use UserForms to allow the user to choose report settings.

Good practices

• Structure reports with clear headers and readable formatting.
• Use dynamic loops to adapt to variable data.
• Add messages to confirm report creation.
• Test on copies of workbooks to avoid data loss.

Part 2: Demonstrations with Examples

Example 1: Report of Available Books

Example 2: Client Statistical Report

1. Theory - Custom Functions and Advanced Data Manipulation

Custom Functions (UDF)

• Definition : A User-Defined Function is a VBA procedure that can be used directly in Excel formulas, such as SUM or VLOOKUP.
• Syntax :

• Usage : Enter "=NomFunction(A1, B1)" in an Excel cell.
• Volatile Application : Use Application.Volatile to force recalculation at each sheet modification.
• Example : A function to calculate a discount on the price of a book.

Advanced Data Manipulation

• Dynamic ranges :
  • UsedRange : Selects all cells used in a sheet.
  • Offset : Moves the reference by a range (e.g., Range("A1").Offset(1, 0) returns A2).
  • Resize : Resizes a range (e.g., Range("A1").Resize(2, 3) selects A1:C2).
  • CurrentRegion : Selects a contiguous region around a cell (useful for tables).
• Tables for optimization :
  • Load a range into a VBA array with Range.Value to reduce cell accesses.
  • Write a table in a range in one operation.
• Application example : Extract a unique list of titles or authors from a sheet.

Good practices

• Name the functions in a clear and meaningful way (e.g. CalculerRemise).
• Validate function parameters to avoid errors (e.g. check data types).
• Use tables to process large amounts of data quickly.
• Document functions with comments to explain their role and parameters.

2. Demonstrations with an example

Custom Function to Calculate Discount

Steps:

1. Create a new Excel workbook and save it as Librairie_lecon1_Intermediaire.xlsm.
2. In the "Books" sheet, enter the headers in A1:D1: "ID", "Title", "Author", "Price".
3. Add data in A2:D5:
   • 1, "The Little Prince", "Antoine de Saint-Exupéry", 12.99
   • 2, "1984", "George Orwell", 15.99
   • 3, "The Stranger", "Albert Camus", 10.50
   • 4, "Animal Farm", "George Orwell", 9.99
4. Create a custom function to calculate a discount:

Usage:

5. In the "Books" sheet, enter =CalculerRemise(D2, 10) into E2 to apply a 10% discount to D2’s price.
6. Copy the formula to E3:E5.
7. Check that:
   • E2 displays 11.69 (12.99 * 0.9), E3 displays 14.39, etc.
   • If a price or percentage is invalid, the function returns a #VALUE! error.

1. Theory - Advanced Management of UserForms

Dynamic UserForms

• Definition : Dynamic UserForms allow to add or modify controls (TextBox, ComboBox, etc.) at runtime according to data or needs.
• Adding Controls : Use Controls.Add to create controls dynamically.

• Dynamic properties : Configure Top, Left, Width, Height, and other added control properties.

Advanced Input Validation

• Techniques :
  • Checking formats with regular expressions (e.g. check an ISBN).
  • Uniqueness control (e.g. avoid duplicate titles in a sheet).
  • Real-time validation with event Change of controls.
• Error handling : Display error messages via Labels or MsgBox.

Check Events

• Common events :
  • Change : Triggered when a TextBox or ComboBox is modified.
  • Click : Triggered when a button or checkbox is clicked.
  • MouseMove : Triggered when moving the mouse over a control.
• Usage : Associate dynamic actions, like enable/disable buttons based on inputs.

Good practices

• Name the dynamic controls with descriptive prefixes (e.g. txtDynamique1).
• Validate inputs as they are entered to improve user experience.
• Use events to reduce unnecessary clicks (for example, automatic ComboBox update).
• Test forms with varied data to ensure their robustness.

2. Demonstrations with an example

Dynamic UserForm to Add a Book

Initial setup:

1. Create a new Excel workbook and save it as Librairie_lecon2_Intermediaire.xlsm.
2. In the "Books" sheet, enter the headers in A1:E1: "ID", "Title", "Author", "Price", "Available".
3. Add data in A2:E6:
   • 1, "The Little Prince", "Antoine de Saint-Exupéry", 12.99, True
   • 2, "1984", "George Orwell", 15.99, False
   • 3, "The Stranger", "Albert Camus", 10.50, True
   • 4, "Animal Farm", "George Orwell", 9.99, True
   • 5, "The Plague", "Albert Camus", 14.00, True
4. Create a UserForm (UserForm1) with:
   • A ComboBox (cboChamps) to choose the number of fields to display (1 to 3).
   • A CommandButton (Caption: "Add"), named btnAjouter.
   • A Label (Caption: ""), named lblErreur to display errors.

UserForm Code:

Macro to display the form:

Test:

7. Test by selecting 2 fields in cboChamps, entering a title and an author, then clicking "Add".
8. Check that:
   • The TextBoxes are displayed dynamically based on cboChamps.
   • The data is added in the "Books" sheet.
   • An error message appears if a field is empty.

1. Theory - File Management and Connection with Other Applications

File Management with FileSystemObject

• Definition : FileSystemObject is an object of the Scripting library for manipulating files and folders (read, write, create, delete).
• Activation : Add a reference to Microsoft Scripting Runtime in the VBA editor (Tools > References) or use CreateObject("Scripting.FileSystemObject").
• Key Methods :
  • OpenTextFile : Opens a file for reading or writing.
  • CreateTextFile : Create a new file.
  • FileExists, FolderExists : Checks the existence of a file or folder.
• Example : Read a CSV file, write a report in a text file.

Login with Microsoft Word

• Interaction : Use CreateObject("Word.Application") or GetObject to control Word from VBA.
• Key Objects :
  • Word.Application : Represents the Word application.
  • Documents.Add : Create a new document.
  • Range : Manipulates text in a document.
• Functions : Add text, format (bold, tables), save the document.

Error handling

• Use On Error Resume Next to handle file or Word access errors.
• Check the existence of files/folders before any operation.
• Close files and exit Word cleanly to avoid residual processes.

Good practices

• Use relative paths (e.g., ThisWorkbook.Path) for better portability.
• Validate the imported data to avoid errors (e.g., check digital formats).
• Document interactions with Word to clarify formatting.
• Test macros on temporary files/folders to avoid data loss.

2. Demonstrations with an example

Example 1: Import Books from CSV File

Preparation:

1. Create a new Excel workbook and save it as Librairie_lecon3_Intermediaire.xlsm.
2. Create a "Books" sheet with A1:E1: "ID", "Title", "Author", "Price", "Available".
3. Create a CSV file named Livres.csv in the same folder as the workbook with the following content:

Code to import the CSV:

Test:

5. Run the macro and verify that:
   • The books of the CSV are added to the "Books" sheet starting from the last line.
   • The columns are adjusted automatically.
   • A message confirms the import.

1. Theory - Code Optimization and Performance Management

VBA Code Optimization

• Reduce interactions with leaves :
  • Each access to Range or Cells is costly in execution time.
  • Solution : Load the data into a VBA array with Range.Value, process it, then write the results in one operation.
• Disable updates :
  • Application.ScreenUpdating = False : Disables screen refresh.
  • Application.Calculation = xlCalculationManual : Disables automatic formula calculation.
  • Restore settings to the end with Application.ScreenUpdating = True and Application.Calculation = xlCalculationAutomatic.
• Using effective structures :
  • Prefer For Each to browse collections (e.g. Worksheets).
  • Use Dictionary for quick searches instead of nested loops.

Debugging and Profiling

• Debugging tools :
  • Breakpoints : Click in the margin of the VBA editor to stop execution at one line.
  • Immediate Window : Use Debug.Print to display values during runtime (Ctrl+G to open).
  • Local Variables Window : Displays the values of running variables.
• Profiling :
  • Measure run time with Timer to compare performance.
  • Identify slow loops or repeated accesses to sheets.
• Error handling : Use On Error to capture errors and Debug.Print to log issues.

Good practices

• Always use Option Explicit to avoid variable errors.
• Minimize nested loops using structures like Dictionary.
• Document optimizations with comments to facilitate maintenance.
• Test on copies of workbooks to avoid data loss.

2. Demonstrations with an example

Example 1: Optimization of a Loop with an Array

Preparation:

1. Create a new Excel workbook and save it as Librairie_Semaine4_Intermediaire.xlsm.
2. In the "Sales" sheet, enter the headers in A1:D1: "Sales ID", "Customer", "Book", "Amount".
3. Add data in A2:D6 (or generate 1000 rows for performance testing):
   • 1, "Alice", "The Little Prince", 12.99
   • 2, "Bob," "The Stranger," 10.50
   • 3, "Charlie," "Animal Farm," 9.99
   • 4, "David", "The Plague", 14.00
   • 5, "Emma", "Harry Potter", 19.99

Unoptimized version:

Optimized version:

Performance test:

Test:

5. Run the two macros and compare the execution time (most notable with 1000+ lines).
6. Check that:
   • The total (e.g. €67.47) is displayed in F2 (not optimized) and G2 (optimized).
   • The optimized version is faster thanks to the use of the table and ScreenUpdating.
   • The execution times are displayed in the Immediate window (Ctrl+G).