Ajouter des articles à la facture 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 :


Construire la commande du client

Dans cette formation VBA Excel, nous poursuivons la construction de l'application de gestion et de facturation des clients. Grâce aux développements précédents, nous pouvons maintenant récupérer les informations d'un client et d'un article spécifiés. Désormais, pour le client désigné, nous souhaitons construire la commande en ajoutant tour à tour les articles achetés avec leurs quantités.

Source et problématique
Pour poursuivre les travaux, il est tout d'abord nécessaire de réceptionner les précédents au dernier indice.

• Télécharger le fichier construire-commande.rar en cliquant sur ce lien,
• Décompresser le fichier et double cliquer sur le classeur pour l'ouvrir dans Excel,
• Cliquer alors sur le bouton Activer la modification du bandeau de sécurité,
• Fermer Excel en enregistrant le classeur puis rouvrir ce dernier,

La sécurité se déclenche en effet constatant qu'il s'agit d'une source externe. De fait, elle neutralise le code VBA prévu pour s'exécuter à l'ouverture. Après enregistrement, le classeur est considéré comme une source reconnue et fiable. Nous pouvons donc poursuivre les développements.



Au choix d'un identifiant dans la première liste déroulante, toutes les informations attachées au client, sont importées dans les zones du premier cadre. Le client à facturer est ainsi désigné. Au choix d'une référence dans la deuxième liste déroulante, toutes les données du produit sont restituées dans les zones du second cadre.

Contrôler la saisie en amont
L'opérateur doit donc indiquer une quantité achetée en cellule I7. Ensuite, il doit cliquer sur le bouton +Com. Un code VBA doit se charger d'alimenter la commande, en fonction de ces informations, à partir de la ligne 11 de la feuille Facture.

Mais encore faut-il que la quantité renseignée soit considérée comme valide. Et nous proposons d'effectuer ce contrôle en amont, par les fonctionnalités Excel.

La quantité est forcément un nombre entier positif. La fonction Excel EstNum est préconisée pour tester la valeur de la cellule. Mais les nombres décimaux sont proscrits. Donc la fonction Trouve doit intervenir pour déceler la présence potentielle d'une virgule. De plus, la quantité achetée doit nécessairement être inférieure à la quantité en stock, rappelée en cellule J5. Un test d'inégalité doit donc être effectué. Et pour recouper et énumérer toutes ces conditions, la fonction Excel ET est nécessaire.

• Sélectionner la case de laquantité, soit la cellule I7,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de la boîte de dialogue qui suit, choisir Personnalisé,
• Dans la zone Formule, taper la syntaxe suivante :

=ET(ESTNUM(I7)=VRAI;I7>0;ESTERREUR(TROUVE(',';I7));I7<=J5)



Comme nous l'avons évoqué, notre règle de validité est plus complexe que celles proposées par défaut. C'est pourquoi une formule est nécessaire pour recouper les conditions multiples.

Nous nous assurons tout d'abord que la quantité est bien une valeur numérique : ESTNUM(I7)=VRAI. Nous vérifions de même que cette donnée est strictement positive : I7>0. Puis, nous nous assurons qu'il ne s'agit pas d'un nombre décimal : ESTERREUR(TROUVE(',';I7)). En effet, lorsque la fonction Trouve renvoie une erreur à la place de la position, elle indique que l'occurrence cherchée (La virgule) n'a pas été trouvée. De plus, nous contrôlons que le stock est suffisant pour honorer la commande : I7<=J5.

• Valider la règle en cliquant sur le bouton Ok de la boîte de dialogue,
• En I7, taper une valeur décimale comme 7,5 et valider,

La sécurité est en place. La saisie est refusée. Elle doit nécessairement être corrigée. Il en va de même pour un texte, une quantité nulle ou supérieure au stock. Bref, le code VBA saura que si une donnée existe en I7, c'est qu'elle a été validée.

Ajouter les articles à la commande
Le traitement d'ajout des articles doit être enclenché au clic sur le bouton +Com. Mais avant cela, une variable publique supplémentaire est nécessaire. Elle est destinée à mémoriser le total hors taxes de la commande consolidée.

• 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),
• En en-tête de sa feuille de code, ajouter la déclaration suivante :

Option Explicit

Dim ligne As Byte: Dim couleur As Boolean
Dim THT As Single

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

Nous déclarons donc la variable publique THT et nous la typons comme un Single. Il s'agit d'un nombre réel simple. Nous devons en effet gérer les décimales.

• Enregistrer les modifications et revenir 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,
• Dès lors, sur la feuille, double cliquer sur le bouton +Com,

De fait, nous basculons de nouveau dans l'éditeur Visual Basic Excel mais cette fois, entre les bornes de la procédure événementielle Ajouter_Click. Son code VBA se déclenchera donc au clic sur le bouton +Com.

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

...
Dim la_ligne As Integer
Dim Puht As Single: Dim la_qte As Byte

If (ID.Value <> '' And Ref.Value <> '' And Range('I7').Value <> '') Then
la_ligne = 3

If (ligne = 0) Then ligne = 11

Do While Sheets('Catalogue').Cells(la_ligne, 2).Value <> Ref.Value

la_ligne = la_ligne + 1
If la_ligne > 1000 Then Exit Do

Loop

End If
...

Nous déclarons la variable la_ligne pour parcourir la base de données à la recherche des informations relatives à la référence désignée. C'est pourquoi, nous l'initialisons ensuite à l'indice 3. Certes, nous pourrions nous contenter de récupérer les valeurs déjà importées dans le cadre supérieur droit. Mais certaines données comme la désignation peuvent apparaître tronquées.

La variable la_qte doit stocker le nombre d'articles commandés. Son typage en entier court est largement suffisant. La variable Puht doit mémoriser le prix de l'article. C'est pourquoi il est typé comme un nombre réel (Single). De fait, la multiplication des deux conduira au total hors taxes par ligne. Ce total consolidé fournira le montant total de la commande (THT).

Grâce à une instruction conditionnelle, nous recoupons trois critères à satisfaire ensemble. En effet, le code ne doit pas être déclenché inutilement. Pour ajouter un article à la commande, un identifiant client, une référence article et une quantité achetée doivent être définis.

Si la variable publique ligne n'a pas encore été incrémentée (If (ligne = 0)), nous en déduisons qu'il s'agit du premier article à ajouter à la commande. Dans ce cas, nous calons son indice sur le point de départ de la facture à construire (Then ligne = 10).

Ensuite, nous enclenchons la boucle parcourant la base de données à la recherche de la référence désignée. Tant qu'elle n'est pas trouvée, la variable la_ligne est incrémentée jusqu'à être correctement positionnée. Nous ajoutons néanmoins une sécurité en cas d'anomalie. Au-delà de mille passages, nous interrompons le traitement récursif.

Ensuite, nous devons commencer à alimenter la commande. Nous l'avions évoqué dans les volets précédents, la ligne 11 propose déjà les formats et la structure. Pour toutes les lignes à suivre, ces attributs doivent être répliqués. D'ailleurs, une macro utilisant le pinceau pour reproduire la mise en forme, livre une bonne portion du code VBA.

• A la suite du code, après le Loop et avant le End If, ajouter les traitements suivants :

...
If (ligne <> 11) Then
Range('J' & ligne).EntireRow.Delete 'Leurre
Range('B11:J11').Select
Selection.Copy
Range('B' & ligne & ':J' & ligne).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range('J' & ligne + 1).EntireRow.Delete
Else
THT = 0
End If
...

Nous le comprendrons par la suite, nous supprimons tout d'abord une ligne qui sert de leurre, située à l'indice ligne, après avoir été préalablement incrémentée. Nous allons réaliser un détachement entre la commande et la ligne de synthèse du total hors taxes. Mais la procédure nettoyer qui se charge de purger l'espace à chaque démarrage a besoin de continuité. Nous y inscrirons donc un texte 'transparent'. Ensuite, nous copions les attributs de la première ligne (B11:J11). Et nous les répliquons sur la ligne suivante à implémenter (Range('B' & ligne & ':J' &ligne)). Nous récupérons ainsi les formats, les alignements et les fusions. Ce code, issu d'une macro automatique , est fourni dans le fichier texte à la racine du dossier de décompression.

Puis, nous supprimons la ligne située encore en-dessous. Elle correspond au précédent total hors taxes consolidé. A chaque article ajouté, il doit en effet être indexé et déplacé.

Dans le cas en revanche où il s'agit bien de la ligne 11 (else), nous initialisons le total hors taxes à zéro. Il s'agit en effet du début de la construction d'une nouvelle commande.

La boucle While précédente nous a fourni l'indice de ligne à partir duquel prélever les informations depuis la base de données de la feuille Catalogue. Nous pouvons donc l'exploiter pour restituer ces données sur la ligne en cours de la commande.

• A la suite du code VBA et toujours avant le End If, ajouter les instructions suivantes :

...
Range('B' & ligne).Value = Ref.Value
Range('C' & ligne).Value = Sheets('Catalogue').Cells(la_ligne, 3).Value

Puht = Sheets('Catalogue').Cells(la_ligne, 4).Value
la_qte = Range('I7').Value

Range('H' & ligne).Value = Puht
Range('I' & ligne).Value = la_qte
Range('J' & ligne).Value = Puht * la_qte
THT = THT + Puht * la_qte
...

Grâce à l'objet Range, nous pointons sur les cellules respectives de la ligne en cours pour la commande. Pour cela, nous indiquons l'indice de colonne statique entre guillemets. Puis, nous le concaténons à l'indice de ligne en cours. Nous réalisons les affectations et inscriptions respectives. C'est comme toujours la propriété Value qui permet de faire référence au contenu d'une cellule. Nous mémorisons bien les valeurs du prix et de la quantité dans les variables dédiées, pour un calcul ultérieur. Celui-ci intervient en effet en colonne J, pour le total HT de l'article acheté. Nous n'oublions de consolider ce montant avec les autres dans la variable publique THT.

Maintenant, nous devons construire la ligne du total hors taxes, deux lignes plus bas et seulement sur les deux dernières colonnes, pour le titre et pour le total. Nous ne devons pas oublier la ligne de leurre. Des formats spécifiques sont à appliquer grâce aux propriétés des objets de type Range.

• A la suite du code VBA et toujours avant le End If, ajouter les instructions suivantes :

...
Range('I' & ligne + 2).Value = 'THT'
Range('J' & ligne + 2).Value = THT
Range('J' & ligne + 1).Value = 'Leurre'
Range('J' & ligne + 1).Font.ColorIndex = 2
Range('I' & ligne + 2 & ':J' & ligne +2).Font.Bold = True
Range('I' & ligne + 2 & ':J' & ligne +2).HorizontalAlignment = xlRight
Range('I' & ligne + 2 & ':J' & ligne +2).Borders.LineStyle = xlContinuous
Range('J' & ligne + 2).Style = 'Currency'
...

La valeur Currency de la propriété Style permet d'appliquer à la cellule le format monétaire. Pour le reste, c'est du grand classique, nous affectons le contenu, grâce à la propriété Value. Nous ajustons les attributs de police grâce à la propriété Font. Puis, nous réglons l'alignement et les bordures.

Pour parachever la construction de la commande et pour plus de clarté, nous souhaitons alterner la couleur de fond, une ligne sur deux. C'est la raison pour laquelle nous avions déclaré la variable publique couleur. Celle-ci doit être initialisée à False à l'ouverture du classeur. Nous y reviendrons. C'est cette valeur qui indique que la ligne ne doit pas posséder d'attributs particuliers.

• A la suite du code VBA et toujours avant le End If, ajouter les instructions suivantes :

...
If (couleur = True) Then
Range('B' & ligne & ':J' & ligne).Interior.ColorIndex = 15
Range('B' & ligne & ':J' & ligne).Font.ColorIndex = 2
couleur = False
Else
couleur = True
End If

Range('B' & ligne).Select

ligne = ligne + 1

Else

MsgBox 'Pour la commande, il faut un identifiant client, un code article et une quantité'
...

Dans le cas d'une ligne alternée (couleur = True), nous changeons la couleur de fond (Interior.ColorIndex), ainsi que celle du texte pour trancher (Font.ColorIndex = 2). Les réglages étant opérés, nous basculons la valeur de la variable (couleur = False), pour la ligne suivante. De fait, à chaque ajout d'article, donc à chaque clic sur le bouton, nous n'oublions d'incrémenter la variable publique de ligne (ligne = ligne + 1), pour la prochaine insertion à réaliser en-dessous.

Nous traitons le cas où le triple critère de notre première instruction conditionnelle n'est pas satisfait. Nous indiquons simplement à l'opérateur qu'une donnée est manquante pour débuter la construction ou la poursuivre.

Comme nous le disions, nous devons initialiser la variable couleur.

• Revenir tout en haut de la feuille de code, entre les bornes de la procédure remplir,

C'est elle qui est effectivement appelée à deux reprises, à l'ouverture du classeur, pour charger les listes déroulantes des identifiants clients et codes articles.

• Entre les bornes de l'instruction conditionnelle, ajouter l'affectation suivante :

...
If depart = True Then
ligne = 11
couleur = False
nettoyer
End If
...

Nous avions déjà calé la valeur de départ pour la variable publique ligne. Désormais la couleur est elle aussi initialisée pour permettre l'alternance des remplissages.

• Basculer sur la feuille Facture,
• Dans le ruban Développeur, cliquer sur le bouton Mode création,
• Enregistrer les modifications et fermer le classeur,
• A la racine du dossier de décompression, double cliquer sur le fichier pour le rouvrir,

Ainsi, nous réinitialisons les variables publiques.

• Avec la première liste déroulante, choisir un identifiant client,
• Avec la seconde liste déroulante, choisir une référence article,
• En I7, taper une quantité entière inférieure au stock importé,
• Puis, cliquer sur le bouton +Com pour ajouter l'article à la commande,

Comme vous pouvez le voir, l'article est parfaitement inséré sur la première ligne de la commande. Le total hors taxes est restitué en bout de ligne. Le montant total de la commande, pour l'instant identique, est inscrit deux lignes plus bas.

• Avec la seconde liste déroulante, choisir un nouvel article,
• Saisir une quantité inférieure à celle du stock annoncé,
• Puis, cliquer sur le bouton +Com,

Le nouveau produit vient se greffer sur la ligne du dessous. Les formats et la structure sont parfaitement répliqués. La couleur est alternée. Le montant total de la commande est consolidé et repoussé deux lignes en-dessous.

Si vous continuez d'ajouter des articles, vous remarquez que la construction de la commande s'opère en bonne et due forme.



Bientôt, nous devrons exploiter ces données intermédiaires pour produire et éditer la facture en PDF, archiver la commande, sans oublier de mettre à jour les stocks en fonction des quantités achetées.

Le code complet de la procédure est le suivant :

Private Sub Ajouter_Click()
Dim la_ligne As Integer
Dim Puht As Single: Dim la_qte As Byte

If (ID.Value <> '' And Ref.Value <> '' And Range('I7').Value <> '') Then
la_ligne = 3

If (ligne = 0) Then ligne = 11

Do While Sheets('Catalogue').Cells(la_ligne, 2).Value <> Ref.Value

la_ligne = la_ligne + 1
If la_ligne > 1000 Then Exit Do

Loop

If (ligne <> 11) Then
Range('J' & ligne).EntireRow.Delete 'Leurre
Range('B11:J11').Select
Selection.Copy
Range('B' & ligne & ':J' & ligne).Select
Selection.PasteSpecial Paste:= xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range('J' & ligne + 1).EntireRow.Delete
Else
THT = 0
End If

Range('B' & ligne).Value = Ref.Value
Range('C' & ligne).Value = Sheets('Catalogue').Cells(la_ligne, 3).Value

Puht = Sheets('Catalogue').Cells(la_ligne, 4).Value
la_qte = Range('I7').Value

Range('H' & ligne).Value = Puht
Range('I' & ligne).Value = la_qte
Range('J' & ligne).Value = Puht * la_qte
THT = THT + Puht * la_qte

Range('I' & ligne + 2).Value = 'THT'
Range('J' & ligne + 2).Value = THT
Range('J' & ligne + 1).Value = 'Leurre'
Range('J' & ligne + 1).Font.ColorIndex = 2
Range('I' & ligne + 2 & ':J' & ligne + 2).Font.Bold = True
Range('I' & ligne + 2 & ':J' & ligne + 2).HorizontalAlignment = xlRight
Range('I' & ligne + 2 & ':J' & ligne + 2).Borders.LineStyle = xlContinuous
Range('J' & ligne + 2).Style = 'Currency'

If (couleur = True) Then
Range('B' & ligne & ':J' & ligne).Interior.ColorIndex = 15
Range('B' & ligne & ':J' & ligne).Font.ColorIndex = 2
couleur = False
Else
couleur = True
End If

Range('B' & ligne).Select

ligne = ligne + 1

Else

MsgBox 'Pour la commande, il faut un identifiant client, un code article et une quantité'

End If
End Sub