Name: VBA Excel (versions 2024 et Microsoft 365) Programmer sous Excel : macros et langage VBA
Rating: 5 (1 reviews)

Présentation générale

La société MobiBuro, spécialisée dans la vente de matériel de bureau destiné aux professionnels, dispose d’un "show-room" avec un stock de matériel d’exposition qu’elle souhaite proposer en prêt ou à la vente à ses clients.

Pour cela, elle utilise une application Excel qui lui permet :

de générer des fiches produits au format PDF,
d’envoyer ces fiches par mail aux clients,
de générer des fiches de prêt et de gérer les prêts et retours de matériel,
d’obtenir une situation du stock valorisé à une date donnée.

Cette application vous permettra de mettre en pratique la plupart des enseignements de l’ouvrage. Elle inclut notamment des formulaires, des tableaux, un ruban personnalisé, des procédures événementielles, un pilotage d’Outlook (envoi de courriels), des liens hypertexte et une gestion des erreurs.

Tous les fichiers nécessaires à l’application sont livrés avec les exemples de l’ouvrage : le fichier StockShowRoom.xlsm qui contient les feuilles Excel, le code VBA et les formulaires de l’application, les photos de produits situées dans le sous-dossier Photos et le manuel utilisateur Aide.pdf situé dans le sous-dossier Aide.

La lecture du manuel...

Descriptif du contenu de l’application StockShowRoom

Cette application est constituée des éléments suivants :

Un ruban Office personnalisé créé à partir de l’utilitaire Custom UI Editor : vous trouverez une description détaillée de cet utilitaire dans le chapitre Amélioration de l’interface utilisateur de l’ouvrage.
Les feuilles Excel :

Accueil : page d’accueil affichée au lancement de l’application.
Stock : tableau des articles en stock.
Fiche_Produit : modèle de fiche produit.
Bon_Pret : modèle de bon de prêt.
Parametres : liste des utilisateurs, texte du mail adressé aux clients, taux de dépréciation du stock…

Les formulaires suivants :

Stock : tableau des articles en stock.
Fiche_Produit : modèle de fiche produit.
Bon_Pret : modèle de bon de prêt.
Parametres : liste des utilisateurs, texte du mail adressé aux clients, taux de dépréciation du stock…

Les formulaires :

Frm_Envoi_Email : formulaire pour l’envoi de fiches produits à un client.
Frm_Fiche_Pret : formulaire de saisie des coordonnées du client pour la génération des fiches de prêt.
Frm_Fiche_Retour : formulaire de saisie de la date de retour du matériel....

Connexion à l’application

Le code VBA exécuté à l’ouverture du classeur StockShowRoom.xlsm est situé dans la procédure Workbook_Open du module ThisWorkbook.

Private Sub Workbook_Open() 
' Personnalisation de l'affichage Excel  
Application.DisplayFormulaBar = False 
' Formulaire de connexion  
Frm_Identification.Show  
End Sub

Ce code permet de masquer la barre de formule et d’afficher le formulaire Frm_Identification ci-dessous :

1. Présentation du formulaire Frm_Identification

Ce formulaire permet :

de contrôler le login et le mot de passe saisis à partir de la plage de cellules nommée TAB_LOGIN qui contient la liste des utilisateurs ;

de récupérer son profil utilisateur et de masquer et verrouiller certaines cellules en fonction du profil utilisateur.

2. Liste des contrôles du formulaire Frm_Identification

N°	Nom du contrôle	Description
1.	txtLogin	Zone de texte
2.	txtPassword	Zone de texte : la propriété PasswordChar doit avoir la valeur * pour remplacer les caractères saisis par une *
3.	BtOK	Bouton de commande
4.	BtAnnuler	Bouton de commande

3. Code du formulaire Frm_Identification

Option Explicit  
   
Private Sub btAnnuler_Click()  
' Bouton Annuler : ferme le classeur  
ThisWorkbook.Close False  
Unload Me  
End Sub  
 
Private Sub btOK_Click()  
Dim oRngLogin As Range  
Dim bOk As Boolean  
Dim i As Integer  
   
' Bouton OK : contrôle de la saisie  
If txtLogin = "" Then  
   MsgBox "Vous devez saisir votre login utilisateur", vbExclamation 
   txtLogin.SetFocus  
   Exit Sub  
End If  
If txtPassword = "" Then  
   MsgBox "Vous devez renseigner votre mot de passe utilisateur", 
vbExclamation  
  ...

Interface applicative

Une fois connecté à l’application, vous pouvez accéder aux différentes fonctionnalités via un ruban Office personnalisé.

1. Code XML du ruban personnalisé

2. Code VBA des procédures appelées depuis les boutons du ruban

Ces procédures sont stockées dans le module ProcRuban. La procédure fbCtrl_Selection permet de contrôler si des fiches sont sélectionnées dans la feuille Stock.

Option Explicit  
Public objRibon As IRibbonUI  
   
Sub RibbonOnLoad(Ribbon As IRibbonUI)  
' Initialisation du ruban  
Set objRibon = Ribbon  
End Sub  
   
Sub Init_Selection(control As IRibbonControl)  
' Annulation de la sélection en cours  
Supprime_Selection  
End Sub  
   
Sub Fiches_Produits(control As IRibbonControl)  
' Génération des fiches produits  
If Not fbCtrl_Selection(False) Then Exit Sub  
Genere_Fiches_Produits True  
End Sub  
   
Sub Envoi_Mail(control As IRibbonControl)  
' Formulaire pour envoi mail au client  
FRM_ENVOI_EMAIL.Show  
End Sub  
   
Sub Situation_Stock(control As IRibbonControl)  
'   Formulaire pour situation du stock  
FRM_SITUATION_STOCK.Show  
End Sub  
   
Sub Fiches_Pret(control...

Génération des fiches produits au format PDF

1. Description du traitement

Le module FichesProduit contient la procédure Genere_Fiches_Produits qui effectue les traitements suivants :

Création d’un nouveau classeur Excel permettant de stocker les différentes fiches générées.
Parcours des fiches sélectionnées dans la feuille Stock et pour chaque fiche :

recopie de la feuille modèle Fiche_Produit dans le nouveau classeur Excel ;
mise à jour des cellules nommées de la fiche à partir des données de la feuille Stock ;
insertion de la photo, redimensionnement et centrage horizontal et vertical.

Export au format PDF de l’ensemble des fiches générées.
Affichage du fichier PDF généré si argument bAffichage.

Cet argument a la valeur false si la procédure est appelée depuis le formulaire d’envoi du mail au client, la valeur true si la procédure est appelée par le bouton Fiches Produits du ruban.

2. Code VBA de la procédure Genere_Fiches_Produits

Option Explicit  
   
Public Function Genere_Fiches_Produits(bAffichage As Boolean) As Boolean 
Dim oRngStock As Range  
Dim sTabStock() As Variant  
Dim iNbFiches As Integer  
Dim iNbSelection As Integer  
Dim i As Integer  
Dim oWbkFiches As Workbook  
Dim oShtFiche As Worksheet  
Dim oShtFicheModele As Worksheet  
Dim iFiche1 As Integer  
Dim sPath As String  
Dim sFile As String  
Dim sMessage As String  
Const iHauteurPhoto = 220  
Const iLargeurPhoto = 440  
   
' Initialisation  
Genere_Fiches_Produits = False  
On Error GoTo Err  
   
' Modèle de prix  
Set oShtFicheModele = ThisWorkbook.Sheets("Fiche_Produit")  
   
' Met le tableau Excel dans une variable tableau  
On Error GoTo Err  
Application.ScreenUpdating = False  
sTabStock = Range("TAB_STOCK").value  
iNbFiches = UBound(sTabStock)  
   
' Génération des fiches produits dans un nouveau classeur  
Set oWbkFiches = Application.Workbooks.Add  
iFiche1 = oWbkFiches.Sheets.Count...

Envoi des fiches produits par mail aux clients

1. Formulaire Frm_Envoi_Email

L’envoi des fiches produits par mail aux clients se fait via le formulaire Frm_Envoi_Email.

N°	Nom du contrôle	Description
1.	txtCient	Zone de texte
2.	txtEmail	Zone de texte
3.	btEmail	Bouton de commande

2. Code VBA du formulaire Frm_Envoi_Email

Le code VBA s’exécute après le clic sur le bouton btEmail et effectue les traitements suivants :

Contrôle de la saisie : le nom du client et l’adresse électronique doivent être renseignés.
Contrôle de validité de l’adresse électronique via la fonction IsEmailValide.
Appel de la procédure de génération des fiches produits Genere_Fiches_Produits.
Envoi du mail au client via Outlook avec le fichier PDF des fiches produits en pièce jointe.

Le contenu du corps du mail est stocké dans l’onglet Paramètres, cellules nommées OBJET_MAIL et CORPS_MAIL. Le texte [CLIENT] sera remplacé par le nom du client.

Option Explicit  
   
Private Sub btEmail_Click()  
   
' Contrôle saisie  
If txtClient = "" Then  
   MsgBox "Nom du client obligatoire", vbExclamation  
   txtClient.SetFocus  
   Exit Sub  
End If  
If txtEmail = "" Then  
   MsgBox "Adresse Email obligatoire", vbExclamation  
   Exit Sub  
Else  
   ' Vérification adresse email  
   If Not IsEmailValide(txtEmail)...

Génération des fiches de prêt

1. Formulaire Frm_Fiche_Pret

Le formulaire Frm_Fiche_Pret permet :

de renseigner la date du prêt et les coordonnées du client emprunteur,
de générer les fiches de prêt au format PDF (traitement similaire à celui des fiches produits, mais basé sur un modèle différent),
d’indiquer la date du prêt et les coordonnées du client pour les produits sélectionnés dans la feuille Stock.

N°	Nom du contrôle	Description
1.	txtDate	Zone de texte
2.	txtClient	Zone de texte
3.	txtAdresse1	Zone de texte
4.	txtAdresse2	Zone de texte
5.	txtCpVille	Zone de texte
6.	btFichesPret	Bouton de commande

2. Code VBA du formulaire Frm_Fiche_Pret

Option Explicit  
   
' Saisie de la date  
Private Sub txtDate_Change()  
   txtDate = FormateDate(txtDate)  
End Sub  
   
Private Sub txtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 
    KeyAscii = IntChar(KeyAscii)  
End Sub  
   
Private Sub btFichesPret_Click()  
   
' Contrôle saisie  
If txtDate & Space(0) = "" Then  
   MsgBox "Date du Prêt obligatoire", vbExclamation  
   txtDate.SetFocus  
   Exit Sub  
End If  
If txtClient & Space(0) = "" Then  
   MsgBox "Nom du client obligatoire", vbExclamation  
   txtClient.SetFocus  
   Exit Sub  
End If  
If txtAdresse1 & Space(0) = "" Or txtCpVille & Space(0) = "" Then  
   MsgBox "Adresse du client obligatoire", vbExclamation  
   txtClient.SetFocus  
   Exit Sub  
End If  
   
' Génération des fiches de prêt  
pb_sClient = txtClient  
If Not fMaj_Tableau Then Exit Sub  
Genere_Fiches_Pret_Retour (False)  
Unload Me  
   
End Sub  
 
Private Function fMaj_Tableau() As Boolean  
Dim sTabStock() As Variant  
Dim oShtStock As Worksheet  
Dim iNbFiches As Integer  
Dim i As Integer  
   
' Met le tableau Excel dans une variable tableau  
fMaj_Tableau = False  
On Error GoTo Err  
Set oShtStock...

Gestion des retours de prêt

1. Formulaire Frm_Fiche_Retour

N°	Nom du contrôle	Description
1.	txtDate	Zone de texte
2.	btFichesRetour	Bouton de commande

Le formulaire Frm_Fiche_Retour permet :

de contrôler les fiches sélectionnées via la fonction fbCtrl_Selection (avec l’argument bFichesRetour à True) : toutes les fiches sélectionnées doivent avoir le même nom de client emprunteur,
de renseigner la date de retour des produits sélectionnés dans la feuille Stock,
de générer les fiches de retour au format PDF : appel de la procédure Genere_Fiches_Pret_Retour,
d’indiquer la date du retour pour les produits sélectionnés dans la feuille Stock.

2. Code VBA du formulaire Frm_Fiche_Retour

Option Explicit  
   
Private Sub btFichesRetour_Click()  
'   Contrôle saisie  
If txtDate & Space(0) = "" Then  
   MsgBox "Date du retour obligatoire", vbExclamation  
   txtDate.SetFocus  
   Exit Sub  
End If  
   
' Génération des fiches de retour  
If Not fMaj_Tableau Then Exit Sub  
Genere_Fiches_Pret_Retour (True)  
Unload Me  
End Sub  
   
' Saisie de la date de retour  
Private Sub txtDate_Change()  
   txtDate = FormateDate(txtDate)...

Situation du stock à une date donnée

1. Formulaire Frm_Situation_Stock

Ce formulaire permet :

de saisir une date de référence : date à laquelle on souhaite calculer la valeur du stock,
de recopier l’ensemble des fiches de l’onglet Stock dans un nouvel onglet,
de supprimer les fiches sans date de vente ou avec une date d’achat antérieure à la date saisie et de mettre en forme le tableau,
de calculer la valeur du stock correspondant à la date de référence (en modifiant la formule de calcul du nombre d’années de dépréciation) et de l’afficher en bas du tableau.

N°	Nom du contrôle	Description
1.	txtDate	Zone de texte
2.	btSituStock	Bouton de commande

2. Code VBA du formulaire Frm_Situation_Stock

Option Explicit  
   
' Saisie de la date de référence  
Private Sub txtDate_Change()  
   txtDate = FormateDate(txtDate)  
End Sub  
   
Private Sub txtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 
    KeyAscii = IntChar(KeyAscii)  
End Sub  
   
Private Sub btSituStock_Click()  
' Contrôle de la date  
If Not IsDate(txtDate) Then  
   MsgBox "Date incorrecte"  
   txtDate.SetFocus  
   Exit Sub  
End If  
   
' Génération tableau de situation  
Tableau_Situation_Stock (txtDate)  
End Sub  
   
Private Sub Tableau_Situation_Stock(zDate As Date)  
Dim oShtStock As Worksheet  
Dim oShtSitu As Worksheet  
Dim i, j As Integer  
Dim bOk As Boolean  
Dim dDateRef As Date  
Dim sTitre As String  
Dim sTabName As String  
   
' Génération tableau situation du stock  
  
' Date de référence  
On Error GoTo Err  
dDateRef = DateValue(zDate)  
   
' Situation déjà existante  ...

Mise à jour des liens hypertexte sur les photos

1. Présentation

La procédure Maj_LiensPhotos du module LiensHyperTexte permet d’associer automatiquement un lien hypertexte sur le fichier de la photo du produit.

Le nom du fichier image est inscrit dans la colonne F de la feuille Stock.

2. Code de la procédure Maj_LiensPhotos

Option Explicit  
   
Public Sub Maj_Liens()  
Dim oRngStock As Range  
Dim iNbFiches As Integer  
Dim i As Integer  
Dim sPathPhotos As String  
Dim sPathSAV As String  
Dim sLien As String  
   
' Chemin  
sPathPhotos = ThisWorkbook.Path & "\photos\"  
   
' Mise à jour des liens hypertexte  
On Error GoTo Err  
Application.ScreenUpdating = False  
Set oRngStock = Range("TAB_STOCK")  
iNbFiches = oRngStock.Rows.Count  
   
' Liens sur les photos et le SAV  
For i = 1 To iNbFiches  
    ' Liens sur les photos  
    If oRngStock(i, iColPhoto) <> "" And oRngStock(i, iColPhoto) 
<> "PRENDRE PHOTO" Then  
       sLien = sPathPhotos & oRngStock(i, iColPhoto)  
       If oRngStock(i, iColPhoto).Hyperlinks.Count = 0 Then  
          Sheets("Stock").Hyperlinks.Add...

Procédures générales

1. Le module ProcGene

Le module ProcGene contient les procédures et variables publiques pouvant être appelées par les différents modules de l’application.

Les colonnes du tableau de la feuille Stock sont des constantes publiques. Par exemple, la déclaration "iColPrixVente = 15" indique que le prix de vente est en colonne 15. Ainsi, s’il s’avère nécessaire d’insérer des colonnes dans le tableau, il suffit de changer la valeur des constantes, aucune ligne de code n’est à modifier.

2. Liste des procédures de ProcGene

Procédure ou fonction	Description
Attente	Permet d’interrompre le traitement pendant un nombre de secondes transmis en paramètre
Protection	Protège la feuille Stock en fonction du profil utilisateur
OuvreFichier	Ouvre un fichier avec l’application associée à son extension
IntChar	Fonction appelée lors de la saisie d’une date : empêche la saisie de valeurs non numériques
FormateNom	Fonction utilisée pour les noms de fiches : supprime les caractères "/" contenus dans un texte
FormateDate	Fonction appelée lors de la saisie d’une date : affiche automatiquement des "/" après la saisie du jour ou du mois

3. Code VBA du module ProcGene

'   Cette fonction API recherche un fichier exécutable  
Private Declare PtrSafe Function FindExecutable Lib "shell32.dll" _  
Alias "FindExecutableA" (ByVal lpFile As String, _  
ByVal lpDirectory...

Exemple d’application VBA Excel