Gestion des clients et produits en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Clients et produits à facturer

Dans cette formation VBA Excel, nous débutons les travaux pour créer une application complète de facturation des clients avec gestion des stocks. Dans ce premier volet, nous concentrons l'étude sur les clients et produits à désigner, mais aussi sur les nouveaux clients à créer. Bref, l'idée est de gérer tous les intervenants de façon transparente, depuis une feuille Excel, à l'aide du code VBA.

Classeur source et problématique
Nous proposons de débuter les travaux depuis un classeur existant et offrant les données et la structure de l'application à construire.

• Télécharger le classeur construire-commande-client.xlsm en cliquant sur ce lien,
• Double cliquer alors sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons ainsi un classeur composé de cinq feuilles. A ce stade, il est totalement dénué de code Visual Basic. Etape par étape, nous devons tout développer. La feuille active par défaut est celle qui nous intéresse pour ce premier développement. Il s'agit de la feuille Facture. A terme, elle doit permettre de consolider les commandes des clients en fonction des produits achetés.



Deux cadres sont prévus en haut de la facture, vierges pour l'instant. Le premier doit permettre de désigner un client existant, au choix de son identifiant dans la liste déroulante. Toutes les informations correspondantes à rapatrier sont archivées dans la feuille Clients. Mais il doit aussi être permis de facturer un nouveau client. C'est la raison de la présence du bouton Créer. Sa mission sera d'ajouter les nouvelles informations à la suite, dans la base de données de la feuille Clients. Le deuxième cadre doit permettre de désigner les articles achetés à ajouter tour à tour à la facture. C'est la raison de la présence du bouton Ajouter. Ces produits doivent être choisis grâce à la liste déroulante suggérée. Toutes les informations attachées sont archivées dans la feuille Catalogue.

Ces deux listes déroulantes ne sont pas des listes de choix classiques.



Il s'agit d'un composant ActiveX et plus précisément d'une zone de liste déroulante(ComboBox). C'est ainsi que nous pourrons les paramétrer et piloter à notre guise par le code VBA Excel.

Remplir les listes déroulantes
Les deux listes déroulantes doivent se remplir automatiquement à l'ouverture du classeur de l'application de facturation. Mais nous le verrons, il sera parfois nécessaire d'actualiser leur contenu. C'est pourquoi, nous suggérons de créer une procédure indépendante. Elle pourra être appelée à souhait. Et comme le traitement est identique dans les deux cas, nous proposons d'optimiser le code VBA dans une seule procédure. C'est un paramètre à déclarer qui devra déterminer la feuille à pointer.

• En haut de la fenêtre Excel, cliquer sur l'onglet Révision pour activer son ruban,
• Dans la section Protéger du ruban, cliquer sur le bouton Ôter la protection de la feuille,

Pour une application saine, il s'agira de penser à la rétablir. En attendant, nous avons besoin d'accéder aux objets.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour l'activer,

S'il n'est pas visible dans votre environnement, la formation pour débuter la programmation VBA Excel, rappelle comment l'afficher.

• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Puis, dans la même section, cliquer sur le bouton Propriétés,

Aussitôt, la fenêtre Propriétés de l'éditeur Visual Basic apparaît. Elle énumère les attributs qu'il est possible de régler pour l'objet sélectionné. Par défaut, il s'agit de la feuille.

• Cliquer sur la première liste déroulante pour la sélectionner,

En consultant la valeur de sa propriété Name, vous constatez qu'elle se nomme ID. Ce nom est prépondérant. C'est grâce à lui que nous pourrons piloter la liste déroulante par le code VBA Excel. En cliquant sur la seconde liste, vous remarquez qu'elle se nomme Ref.

• Fermer la feuille de propriétés en cliquant sur sa croix,
• Dans le ruban Développeur, cliquer de nouveau sur le bouton Mode création,

Il s'agit d'un bouton bascule. Nous venons de désactiver le mode conception au profit du mode exécution.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1 (Facture),

Ainsi, nous affichons la feuille de code qui lui est associée au centre de l'écran.

• Sur cette feuille, saisir la déclarative et déclarations suivantes :

Option Explicit

Dim ligne As Byte: Dim couleur As Boolean
...

Par anticipation, nous effectuons quelques réglages essentiels. Tout d'abord et comme vous le savez, la déclarative Option Explicit impose la déclaration de variables et scrute celles qui sont utilisées. Et précisément dans la foulée, nous déclarons deux variables publiques. La première doit mémoriser la dernière ligne utilisée pour l'insertion d'un article dans la construction de la commande. C'est grâce à elle que nous saurons où nous positionner pour le prochain produit. La seconde est déclarée comme un booléen. Sa vocation est de permettre l'alternance de couleur dans l'énumération de la facture.

• A la suite, créer la procédure remplir, comme suit :

Sub remplir(feuille As String, liste As Object, depart As Boolean)
Dim la_ligne As Integer

la_ligne = 3: liste.Clear

If depart = True Then
ligne = 11
nettoyer
End If

While Sheets(feuille).Cells(la_ligne, 2).Value <> ''
liste.AddItem Sheets(feuille).Cells(la_ligne, 2).Value
la_ligne = la_ligne + 1
Wend

End Sub

Nous créons donc la procédure remplir avec trois paramètres en attente. C'est ainsi qu'elle doit pourvoir s'adapter au contexte. Le premier de ces arguments est le nom de la feuille, que nous typons fort logiquement comme un String, soit une chaîne de caractères. Le deuxième est typé comme un objet. Nous devons donc lui passer le nom de la liste déroulante (ID ou Ref) à remplir. Le dernier est un booléen. Il peut donc être soit vrai, soit faux. Cet indicateur doit être utilisé pour réinitialiser la facture s'il s'agit du premier chargement, en cas de données résiduelles.

Nous déclarons ensuite la variable la_ligne comme un entier. Nous l'initialisons sur la valeur 3. En effet, le contenu des deux bases de données, débute à cet indice. Grâce à la méthode Clear d'un objet ComboBox, nous purgeons le contenu de la liste déroulante dont le nom a été passé en deuxième paramètre.

Ensuite et par anticipation, dans le cas où il s'agit d'un chargement (depart = True), nous appelons la procédure nettoyer, après avoir initialisé la variable publique ligne à 11. Il s'agit de la première ligne à implémenter pour la construction de la facture. Cette procédure n'existe pas encore. Nous devons donc la créer.

Puis, nous engageons une boucle while destinée à parcourir l'intégralité du tableau, tant que des données sont trouvées (<> ''). Dans ce cas, nous incrémentons la valeur de la variable la_ligne, jusqu'à pointer sur la borne inférieure de la base de données située sur la feuille passée en premier paramètre. A chaque fois qu'une valeur est trouvée, l'identifiant ou la référence est ajouté dans la liste correspondante grâce à la méthode AddItem de l'objet. Bien sûr, à chaque passage, nous déplaçons le pointeur de lecture la_ligne. Cette incrémentation prend fin lorsque le critère de la boucle n'est plus vérifié, soit lorsque la première cellule vide est trouvée.

Comme elle est appelée, avant de pouvoir réaliser quelconque test, nous devons créer cette procédure nettoyer.

• Sous la procédure remplir, créer la procédure nettoyer, comme suit :

Private Sub nettoyer()

While Cells(ligne, 10).Value <> ''

If (ligne = 11) Then
Range('B11:J11').Value = ''
ligne = ligne + 1
Else
Range('B' & ligne & ':J' & ligne).EntireRow.Delete
End If

Wend

ligne = 11

End Sub

Nous engageons exactement le même type de boucle while que précédemment. Le traitement est destiné à purger la facture de toutes les informations potentiellement encore inscrites. Donc, le test consiste à vérifier que la cellule de la ligne en cours n'est pas vide. Et pour cela, une fois n'est pas coutume, nous partons de l'extrémité droite de la facture (Cells(ligne, 10).Value <> ''). C'est dans cette colonne en effet que nous placerons les données de synthèse, comme le montant total de la commande.

Nous faisons néanmoins une distinction entre la ligne 11 et les suivantes, d'où l'emploi de l'instruction conditionnelle. Cette ligne est la première de la facture. Elle propose des attributs de mise en forme que nous répliquerons sur les autres lignes, durant chaque construction de la commande. Nous ne devons donc pas la supprimer. Nous vidons le contenu de la plage. Pour les autres en revanche, nous exploitons la propriété EntireRow de l'objet Range. Cette dernière désigne la ligne entière de la cellule ou des cellules sélectionnées. De fait, nous n'avons plus qu'à exploiter la méthode Delete pour supprimer la ligne ainsi spécifiée.

Deux remarques sont nécessaires. Tout d'abord, l'incrémentation de la variable ligne n'intervient que dans la première branche du If. En effet, en cas de suppression de ligne, le pointeur est automatiquement placé sur la suivante. Si nous poursuivions l'incrémentation, nous nous retrouverions deux lignes plus bas. Ensuite, vous notez la construction dynamique du paramètre de l'objet Range. Celui-ci permet de définir une cellule ou une plage de cellules, grâce au symbole deux points (:), comme ici. Les colonnes B et J des extrémités de la plage sont connues. Nous les spécifions statiquement entre guillemets. Puis, nous les concaténons (&) à l'indice de ligne variable.

Cette procédure remplir doit être exécutée à l'ouverture du classeur. Elle doit donc être appelée sur l'évènement Open de l'objet Workbook.

• Dans l'explorateur de projet, double cliquer sur l'élément ThisWorkbook,
• En haut de la feuille de code, choisir l'objet Workbook à l'aide de la première liste déroulante,

Nous créons ainsi la procédure évènementielle Workbook_Open :

Private Sub Workbook_Open()

End Sub



Elle déclenche les instructions codées à l'ouverture du classeur. Il est possible de gérer d'autres évènements du classeur. Ils sont énumérés dans la seconde liste déroulante, en haut de la feuille de code.

• Entre les bornes de la procédure évènementielle, ajouter les instructions suivantes :

Private Sub Workbook_Open()

Call Feuil1.remplir('Clients',Feuil1.ID, True)
Call Feuil1.remplir('Catalogue', Feuil1.Ref, True)
Sheets('Facture').Activate

End Sub

Comme la procédure remplir est située dans une autre feuille et non dans un module, nous avons l'obligation d'exploiter l'instruction Call pour l'appeler. Et pour cela, nous devons la localiser précisément. C'est pourquoi, nous la préfixons du nom d'objet de la feuille qui l'héberge. Il est important de comprendre que nous avons volontairement créé cette procédure comme publique et non privée (Private). Dans ce second cas en effet, nous n'aurions pas pu l'atteindre.

Ensuite, nous lui passons à deux reprises les arguments nécessaires. Comme les objets ID et Ref des listes déroulantes appartiennent à la feuille Facture (Feuil1), nous devons là encore les préfixer pour les atteindre et les désigner. Enfin, nous donnons le focus à la feuille Facture grâce à la méthode Activate de l'objet Sheets.

Pour les démonstrations, nous n'allons pas fermer le classeur pour le rouvrir. Une simulation fera l'affaire.

• Enregistrer les modifications (CTRL + S) puis enfoncer la touche F5 du clavier,

Le code s'est bien exécuté. Mais depuis l'éditeur, son traitement est transparent pour l'utilisateur.

• Basculer sur la feuille Facture puis, déployer l'une et l'autre liste déroulante,

Comme vous le remarquez, elles sont toutes deux précisément remplies des références, issues des feuilles respectives.

Le code VBA que nous avons développé est relativement simple et sommaire, sachant que la procédure nettoyer n'a aucune incidence sur le chargement de ces listes :

Option Explicit

Dim ligne As Byte: Dim couleur As Boolean

Sub remplir(feuille As String, liste As Object, depart As Boolean)
Dim la_ligne As Integer

la_ligne = 3: liste.Clear

If depart = True Then
ligne = 11
nettoyer
End If

While Sheets(feuille).Cells(la_ligne, 2).Value <> ''
liste.AddItem Sheets(feuille).Cells(la_ligne, 2).Value
la_ligne = la_ligne + 1
Wend

End Sub

Private Sub nettoyer()

While Cells(ligne, 10).Value <> ''

If (ligne = 11) Then
Range('B11:J11').Value = ''
ligne = ligne + 1
Else
Range('B' & ligne & ':J' & ligne).EntireRow.Delete
End If

Wend

ligne = 11

End Sub

Importer les données attachées
Désormais, au choix dans l'une et l'autre liste, nous devons rapatrier les informations attachées, dans les cellules prévues à cet effet. Un code VBA doit donc être déclenché au changement de valeur détecté dans l'une et l'autre liste déroulante. Là encore, nous proposons de réunir les traitements communs dans une seule procédure. Elle doit recevoir la référence cherchée et le nom de la feuille de traitement.

• Basculer dans l'éditeur VBA Excel,
• Sous la procédure nettoyer, créer la procédure chercher, comme suit :

Private Sub chercher(code As String, feuille As String)

End Sub

Classiquement, nous déclarons la procédure en lui affectant un nom et en typant les deux paramètres attendus. Elle est caractérisée comme une procédure privée (Private). Il s'agit d'une protection. Elle ne peut être appelée que par les procédures de la même feuille.

• Entre ses bornes, ajouter le code VBA suivant :

Dim la_ligne As Integer

la_ligne = 3

Do While Sheets(feuille).Cells(la_ligne, 2).Value <> code
la_ligne = la_ligne + 1
If la_ligne > 1000 Then Exit Do
Loop

If (feuille = 'Clients') Then
Range('D5').Value = Sheets(feuille).Cells(la_ligne, 6).Value
Range('E5').Value = Left(Sheets(feuille).Cells(la_ligne, 7).Value,11) & '...'
Range('B7').Value = Sheets(feuille).Cells(la_ligne, 4).Value
Range('D7').Value = Sheets(feuille).Cells(la_ligne, 5).Value
Else
Range('I5').Value = Sheets(feuille).Cells(la_ligne, 4).Value
Range('J5').Value = Sheets(feuille).Cells(la_ligne, 7).Value
Range('G7').Value = Left(Sheets(feuille).Cells(la_ligne, 3).Value,20) & '...'
End If

Nous déclarons la variable nécessaire pour parcourir les lignes de l'une ou l'autre feuille. Nous l'affectons sur le premier indice utile (3). Nous enclenchons une boucle While destinée à parcourir la base de données, tant que la référence passée en paramètre, n'est pas trouvée (Cells(la_ligne, 2).Value <> code). A chaque passage donc, tant que la correspondance n'est pas établie, nous incrémentons la variable la_ligne, pour poursuivre la recherche sur la référence suivante. Nous glissons une petite sécurité grâce à une instruction conditionnelle. Nous considérons qu'au-delà d'un certain nombre, une anomalie se présente. Nous choisissons donc de mettre fin au traitement de la boucle, par l'instruction Exit Do.

Au sortir de la boucle, l'indice de ligne doit s'être arrêté sur celui de la référence concordante. Nous l'exploitons selon le nom de la feuille passé en paramètre. C'est pourquoi, nous enclenchons une nouvelle instruction conditionnelle. Et puis, nous établissons l'affectation des cellules de la facture, en fonction des données correspondantes de la base de données. Concernant cette dernière, ne s'agissant pas de la feuille active, nous devons préfixer les instructions par son nom. Notez enfin que nous exploitons à deux reprises la fonction Left. Cette dernière permet de prélever seulement une partie de l'information. En effet, les zones de réception sont relativement étroites.

Pour tester le bon fonctionnement de ce code VBA, encore faut-il que la procédure soit appelée. Nous l'avons dit, elle doit être déclenchée en actionnant l'une ou l'autre liste déroulante.

• Enregistrer les modifications et basculer sur la feuille Facture,
• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,

Nous pouvons ainsi manipuler les objets ActiveX placés sur la feuille Excel.

• Double cliquer sur la première liste déroulante, celle des identifiants clients,

Nous sommes aussitôt redirigés dans l'éditeur de code Visual Basic Excel. Cette action a pour effet de créer la procédure évènementielle attachée à la liste déroulante :

Private Sub ID_Change()

End Sub

Son code se déclenchera au changement de valeur dans la liste. Mais avant d'implémenter cette procédure, nous devons créer la seconde.

• Revenir sur la feuille Facture,
• Puis, double cliquer sur la seconde liste déroulante, celle des articles,

Nous basculons de nouveau dans l'éditeur de code VBA Excel, entre les bornes de la procédure Ref_change:

Private Sub Ref_Change()

End Sub

Son code se déclenchera au choix d'une nouvelle référence produit.

• Entre les bornes de la première procédure, ajouter l'appel VBA suivant :

chercher ID.Value, 'Clients'

Nous appelons la procédure chercher en lui passant l'identifiant client à trouver et en lui indiquant le nom de la feuille de recherche.

• Entre les bornes de la seconde procédure, ajouter l'appel VBA suivant :

chercher Ref.Value, 'Catalogue'

L'action est la même mais les paramètres sont bien évidemment adaptés.

• Enregistrer les modifications et revenir sur la feuille Facture,
• Dans le ruban Développeur, cliquer sur le bouton Mode création,

Nous activons ainsi le mode exécution.

• Choisir un identifiant client à l'aide de la première liste déroulante,

Comme vous le constatez, les références du client désigné sont rapatriées automatiquement. Et si vous consultez la base de données de la feuille Clients, vous remarquez que la correspondance est parfaitement exacte. Dans le cas d'un code postal inférieur à 10000, le zéro en préfixe disparaît. Excel le juge inutile puisqu'il manipule des nombres.

• Sélectionner la cellule D5 du code postal,
• Dans la liste déroulante de la section Nombre du ruban Accueil, choisir le format Texte,

De cette manière, à la prochaine réception, le zéro en préfixe s'il existe, sera conservé.

• Déployer la seconde liste déroulante,
• Puis, choisir une référence article,

Le constat est le même et c'est heureux. Toutes les informations attachées à la référence du produit sont parfaitement importées. Si vous recherchez le code article dans la base de données de la feuille Catalogue, vous remarquez que la correspondance est parfaitement établie.



Créer un nouveau client
Avant de penser à facturer, il faut être capable de construire la commande pour un client pas encore référencé dans la base de données de l'entreprise. Ce référencement est primordial pour les archives, les études statistiques et commerciales mais aussi pour le suivi des clients. C'est le bouton Créer du premier cadre qui doit insérer dans la base de données, les informations saisies dans les zones de texte. Mais ce code VBA doit préalablement s'assurer que le client n'existe pas déjà. Et cette recherche doit s'opérer sur le recoupement du nom et du prénom fournis.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Double cliquer alors sur le bouton Créer de la zone Client à facturer,

Nous basculons de nouveau dans l'éditeur de code VBA Excel, entre les bornes de la procédure évènementielle Creer_Click.

• Entre les bornes de la procédure, ajouter les déclarations et affectations suivantes :

...
Dim nom As String: Dim prenom As String
Dim la_ligne As Integer : Dim ident As Integer

nom = Range('B7').Value: prenom = Range('D7').Value
la_ligne = 3
...

Nous déclarons les variables nécessaires pour trouver les informations cherchées. D'ailleurs, nous les affectons dans la foulée sur les données du nom et prénom renseignés. La variable la_ligne est usuelle pour parcourir le tableau. Nous l'initialisons donc sur l'indice 3. La variable ident doit servir à mémoriser le dernier identifiant client, pour l'incrémenter et l'affecter au nouveau.

Nous devons désormais parcourir le tableau des clients à la recherche d'une correspondance potentielle.

• A la suite du code, ajouter la boucle While suivante :

...
Do While Sheets('Clients').Cells(la_ligne, 4).Value <> nom And Sheets('Clients').Cells(la_ligne, 5).Value <> prenom And Sheets('Clients').Cells(la_ligne, 2).Value <> ''
ident = Sheets('Clients').Cells(la_ligne, 2).Value
la_ligne = la_ligne + 1
Loop
...

C'est un triple critère qui conditionne la poursuite du traitement récursif. Tant que la correspondance sur le nom et le prénom n'est pas trouvée et tant que la cellule de l'identifiant n'est pas vide, nous poursuivons la recherche. Au cas où il s'agit du dernier enregistrement du tableau, nous prélevons à chaque passage, l'identifiant du client. Du fait, si une nouvelle ligne existe, il est remplacé lors du passage suivant. Il en résultera le dernier numéro. Nous n'oublions pas d'incrémenter la variable de boucle.

Au sortir de la boucle, deux cas sont possibles. Soit le client a été trouvé et nous devons en informer l'utilisateur. Soit la correspondance n'a pas été établie et nous devons enregistrer le nouveau client, à la suite des données.

• A la suite du code, ajouter l'instruction conditionnelle suivante :

...
If (Sheets('Clients').Cells(la_ligne, 4).Value = nom And Sheets('Clients').Cells(la_ligne, 5).Value = prenom) Then
MsgBox 'Le client existe déjà'
Else
Sheets('Clients').Cells(la_ligne, 6).Value = Range('D5').Value
Sheets('Clients').Cells(la_ligne, 7).Value = Range('E5').Value
Sheets('Clients').Cells(la_ligne, 4).Value = Range('B7').Value
Sheets('Clients').Cells(la_ligne, 5).Value = Range('D7').Value
Sheets('Clients').Cells(la_ligne, 2).Value = ident + 1
Sheets('Clients').Cells(la_ligne, 3).Value = 'ND'
Sheets('Clients').Cells(la_ligne, 8).Value = 'ND'
remplir 'Clients', ID, False
ID.ListIndex = ID.ListCount - 1
MsgBox 'Le client a été créé avec succès'
End If
...

Dans le cas où la paire a été identifiée sur le dernier indice de ligne livré par la boucle, nous affichons un message à l'utilisateur, grâce à la fonction VBA MsgBox. Dans le cas contraire, nous prélevons les informations issues de la feuille Facture pour les insérer sur la nouvelle ligne de la feuille Clients. Pour cela, le dernier identifiant mémorisé est incrémenté. Certaines données sont manquantes d'où l'inscription de la mention ND. Libre à vous de prévoir une zone plus riche dans la feuille Facture. Nous nous concentrons ici sur les techniques.

Ensuite, nous appelons la procédure remplir en lui passant l'argument booléen False. De cette manière, la liste déroulante des identifiants est rechargée sans engager la procédure nettoyer. Puis, grâce à la propriété ListIndex du contrôle liste déroulante, nous affichons sa dernière valeur, celle du client fraîchement créé. Pour cela, nous exploitons sa propriété ListCount qui retourne le nombre d'éléments qu'elle contient.

Il est de temps de tester le code VBA.

• Enregistrer les modifications et basculer sur la feuille Facture,
• Dans le ruban Développeur, cliquer sur le bouton Mode création pour le désactiver,
• Choisir un identifiant client à l'aide de la première liste déroulante,
• Puis, cliquer sur le bouton Créer,

Fort logiquement, le client a été reconnu. Sa création est donc refusée.

• Valider le message en cliquant sur le bouton Ok,
• Puis, saisir les quatre informations d'un nouveau client,
• Ensuite, cliquer de nouveau sur le bouton Créer,

Ce dernier n'ayant pas été reconnu, semble avoir été inséré dans la base de données. D'ailleurs, à validation du message, vous constatez que la liste déroulante se cale automatiquement sur le dernier identifiant. Ce numéro n'existait pas jusqu'alors. Si vous affichez la feuille Clients, vous le trouvez en effet en dernière ligne du tableau.

Dans les prochaines formations, nous poursuivrons les travaux consistant à monter cette application de gestion et de facturation.