Facturation clients Excel avec gestion de stocks VBA

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


Sujets que vous pourriez aussi aimer :


Facturation Excel avec gestion de Stock VBA

Dans une précédente formation Excel, nous avons appris à réaliser une facturation client automatisée. Mais cette dernière ne proposait pas de gérer les stocks des articles. Nous allons partir du travail précédent, là où nous nous étions arrêtés.

• Télécharger le classeur facturation-et-stock-excel.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel,

L'extension .xlsm signifie qu'il s'agit d'un classeur permettant de gérer les macros. En effet, nous aurons besoin d'un petit bout de code à l'issue, pour mettre à jour les stocks. Ce classeur est constitué de deux feuilles : une feuille articles dans laquelle figure un tableau des références produits, et une feuille facturation qui permet d'établir la facture d'un client ayant acheté des articles de ce tableau. La sélection d'une référence à l'aide de la liste déroulante dans la colonne Code article permet d'afficher automatiquement la désignation et le prix unitaire hors taxes correspondants. C'est la fonction RechercheV() qui permet de rapatrier ces informations du tableau des références en fonction du choix du code article. Dès lors, l'utilisateur n'a plus qu'à saisir la quantité achetée et les montants hors taxes ainsi que le total TTC, en bas de lafacture, se calculent automatiquement.

Catalogue évolutif
Comme nous devons faire un modèle de cette facturation pour que chaque nouvelle facture vierge puisse se servir dans les références produits du catalogue, et comme ces références sont censées évoluer (Prix, nombre etc...), nous devons le détacher de la facture et le placer dans un classeur indépendant.

• Créer un nouveau classeur Excel,
• Copier et coller le tableau de la feuille articles dans la première feuille de ce nouveau classeur,
• Enregistrer ce nouveau classeur sous le nom catalogue.xlsx par exemple,

Maintenant que les références des articles se trouvent sur un classeur indépendant, nous pourrons le faire évoluer à notre guise. Mais désormais, les fonctions RechercheV() doivent y faire référence. La liste déroulante fait référence à l'ancien catalogue, nous devons la supprimer.

• Activer la feuille facturation du premier classeur,
• Sélectionner les cellules de la colonne Code article,
• Activer le ruban Données,
• Cliquer sur le bouton Validation de données,
• Dans la boîte de dialogue, fixer la zone Autoriser sur Tout,
• Puis, valider par Ok.

De retour sur la feuille, la liste déroulante qui faisait référence à l'ancien catalogue, a disparu. Sur le catalogue du nouveau classeur, nous devons ajouter une colonne pour les stocks.

• Revenir sur la feuille du nouveau classeur,
• Ajouter la colonne Stock sur la droite comme le propose la capture ci-dessous,

Pour plus de visuel, nous allons faire ressortir ces stocks avec une mise en forme conditionnelle pour être facilement alerté lorsqu'ils s'épuisent.

• Dans le catalogue du nouveau classeur, supprimer toutes les lignes au-dessus du tableau de manière à ce qu'il débute en première ligne de la feuille,
• Sélectionner ensuite la colonne entière des stocks en cliquant sur son étiquette, F dans notre cas,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Pointer sur Nuances de couleurs,
• Puis choisir Echelle de couleur Blanc - Rouge,
• Enregistrer le classeur (CTRL + S),

Tous les stocks dont la valeur se rapproche de 0 apparaissent ainsi dans un rouge vif évocateur au premier coup d'oeil comme l'illustre la capture ci-dessous.


Liste déroulante et fonction Decaler()
La formation Excel sur les listes déroulantes apprend à gérer ces dernières. Nous devons reconstruire la liste déroulante pour les codes des articles. Cependant, autant il est possible de construire une liste déroulante faisant référence à une autre feuille, autant il n'est pas possible de la construire en faisant référence à une feuille d'un autre classeur. Mais nous allons utiliser une astuce. De plus, cette liste déroulante doit pouvoir évoluer. Si des références sont ajoutées dans le catalogue, elle doit les intégrer.

• Sur le nouveau classeur, sélectionner la colonne des codes article par son étiquette, C dans notre cas,
• La copier (CTRL + C),
• Sur la feuille Facturation du premier classeur, sélectionner une colonne par son étiquette, sur la droite de la facture, K par exemple,
• Puis coller (CTRL + V),
• Dans la foulée, cliquer sur la petite balise active qui se déclenche à droite de la sélection,
• En bas de la liste, choisir Coller avec liaison,

Ainsi, la liste du classeur Catalogue est rapatriée dans la feuille facturation. Si elle évolue, cette copie évoluera en même temps grâce à la liaison. Une fois que la liste déroulante sera construite dessus, nous pourrons masquer cette colonne pour conserver une facturation professionnelle.

• Sur la feuille facturation, sélectionner toutes les cellules avec un code article, K2:K10 dans notre cas,
• Dans la zone Nom en haut à droite, taper references,

Nous attribuons ainsi un nom à la plage de cellules de codes articles. Nous verrons qu'il sera très utile pour la liste déroulante, pour qu'elle puisse s'adapter aux nouveautés notamment.

• Sélectionner maintenant toutes les cellules de la colonne Code article, C6:C26,
• Activer le ruban Données et cliquer sur le bouton Validation de données,
• Dans la boîte de dialogue, fixer la zone Autoriser sur Liste,
• Cliquer ensuite dans la zone Source et sélectionner les codes article collés avec liaison,
• Valider avec Ok,

La liste déroulante est fonctionnelle. Elle permet bien de sélectionner l'une des références issues du catalogue. Cependant elle n'est pas encore évolutive. Si une référence est ajoutée dans le classeur catalogue, elle apparaîtra bien sur la feuille facturation dans la liste des cellules collées avec liaison mais pas dans la liste déroulante. En effet la liste déroulante a été conçue sur la plage nommée references qui est une plage de cellules aux bornes strictes. Cette plage doit évoluer si son contenu évolue, c'est à dire, si une référence est ajoutée. C'est ce que nous allons faire à l'aide des formules dans le nom de la plage de cellules.

• Activer le ruban Formules et cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue, sélectionner le nom references,
• Dans la zone de saisie en bas, taper la formule suivante :

=DECALER(facturation!$K$2;0;0;NB.SI(facturation!$K:$K;'>'& facturation!$K$2))

Decaler() est la fonction Excel qui permet de déplacer les bornes d'une plage de cellules en fonction de son contenu, s'il varie. Nous lui passons en premier paramètre, la première cellule du code article collé avec liaison, soit K2. Les deux paramètres qui suivent sont définis à 0 car nous conservons comme référence cette colonne sans la déplacer. Le dernier paramètre est celui qui permet de savoir sur combien de lignes vers le bas cette plage de cellules doit se déplacer. Tout cela dépend s'il s'agit bien de codes articles insérés. C'est pourquoi nous utilisons la fonction NB.SI() avec en premier paramètre toute la colonne $K:$K et en deuxième paramètre le critère '>' & $K$2. En d'autres termes, elle compte toutes les cellules dont le contenu est supérieur à la première référence article et permet ainsi à la fonction Decaler() de déplacer la borne inférieure vers le bas si une nouvelle référence est ajoutée.

• Cliquer sur Fermer pour valider la formule associée au nom de la plage,
• Dans le classeur Catalogue, ajouter une référence B010 avec désignation, prix et stock,
• Revenir sur la feuille facturation,

Vous remarquez que la référence B010 est bien présente dans la colonne K.

• Dans la colonne Code article, cliquer sur une cellule pour déclencher la liste déroulante,

Grâce aux fonctions Decaler() et Nb.Si() sur la plage nommée, vous remarquez que la liste déroulante s'est agrandie, son contenu s'est adapté, la référence B010 y figure bien. Cependant à ce stade, si vous la sélectionnez, une erreur s'affiche en colonne Désignation et Prix unitaire car les fonctions rechercheV() quant à elles n'ont pas encore été adaptées. La formation Excel sur l'extraction de données enseigne ces fonctions RechercheV(). Dans la colonne Désignation, la formule posée pour l'instant est la suivante :

=SI(C6='';'';RECHERCHEV(C6;articles!$C$6:$E$15;2;FAUX))

La fonction Si() est utilisée pour enclencher la recherche seulement si un code article est bien inscrit dans la colonne. La recherche du code article (C6) est réalisé sur un tableau aux bornes fixes $C$6:$E$15. Donc toute nouvelle référence en dehors de ces bornes, ne peut pas être trouvée. D'une part, nous devons là encore exploiter la fonction Decaler() de manière à ce que la fonction RechercheV() adapte ses bornes, et d'autres part, nous devons faire en sorte que la fonction RechercheV() recherche désormais dans le tableau du classeur Catalogue.xlsx. La seule chose à modifier dans cette formule est le deuxième argument de la fonction RechercheV().

• Sélectionner toutes les cellules de la colonne Désignation,
• Enfoncer la touche F2 du clavier pour passer en saisie de la formule de la première cellule,
• Modifier la formule comme suit :

=SI(C6='';'';RECHERCHEV(C6;DECALER([catalogue.xlsx]Feuil1!$C$1:$F$1;0;0; NBVAL([catalogue.xlsx]Feuil1!$C:$F)-1);2;FAUX))

Nous utilisons la fonction Decaler() pour adapter les bornes du tableau de recherche. En premier paramètre, nous sélectionnons sa ligne d'entête comme référence : [catalogue.xlsx]Feuil1!$C$1:$F$1. Comme toute à l'heure nous poursuivons par ;0;0. Puis nous indiquons que le tableau s'étend tant qu'il y a bien des cellules non vides, grâce à la fonction NBVAL() cette fois, en lui passant les étiquettes de colonne comme référence : [catalogue.xlsx]Feuil1!$C:$F. Du coup, pour la colonne P.U - H.T, c'est très simple, il suffit de reproduire la formule à l'identique en modifiant simplement le troisième paramètre de la fonction RechercheV(), qui de 2 passe à 3.

• Cliquer sur l'étiquette de colonne K de la feuille facturation,
• Dans le menu contextuel, choisir Masquer,

La colonne est simplement masquée et non supprimée ce qui permet à la liste déroulante de continuer de fonctionner. Le résultat à ce stade est tout à fait satisfaisant car il fait référence à un catalogue qui peut évoluer et dont les nouvelles références seront prises en compte, que ce soit par la liste déroulante ou les rechercheV().


Néanmoins, il subsiste quelques problèmes. Si le stock d'un article est à 0, rien ne nous empêche de l'ajouter en amont. De plus, en aval, si la quantité commandée est supérieure à la quantité disponible en stock, aucun contrôle n'empêche la facture d'être validée.

La gestion des stocks
En plus du contrôle en amont et en aval sur lesquels nous allons travailler, nous devons être en mesure de mettre à jour les stocks en fonction des quantités commandées une fois la facture validée. Pour commencer, une désignation et son prix unitaire ne doivent pas être affichés si l'article n'est plus en stock, afin d'en avertir l'utilisateur. Le critère à poser est le suivant : Si la rechercheV de la référence, pour la colonne 4 (Stock) dans le tableau des articles renvoie 0, alors il ne faut pas effectuer la rechercheV sur la désignation, mais afficher un message à l'utilisateur.

• Sélectionner toutes les cellules de la colonne Désignation,
• Enfoncer la touche F2 pour passer en mode saisie de la formule,
• Dans le Sinon de la fonction Si, après le second point-virgule, ajouter la formule suivante :

SI(RECHERCHEV(C6;DECALER([catalogue.xlsx]Feuil1!$C$1:$F$1;0;0; NBVAL([catalogue.xlsx]Feuil1!$C:$F)-1);4;FAUX)=0; 'Cetarticle n'est plus en Stock';

• Puis ne pas oublier de fermer une autre parenthèse à la toute fin de la fonction du fait de l'imbrication de la nouvelle fonction Si,

C'est ce qu'illustre la capture ci-dessus. Certes la formule devient complexe et longue et il est nécessaire de déployer la barre de formule avec sa flèche pour pouvoir travailler en intégralité dessus. Mais le résultat est tout à fait intéressant puisqu'un message d'alerte s'affiche dans la colonne Désignation à la place du libellé de l'article. Pour la colonne du prix unitaire, il faut faire de même, mais au lieu du message d'alerte, afficher un simple tiret (-), ce qui empêchera le calcul du montant HT.

• De la même façon, adapter la formule pour la colonne P.U - H.T,

La désignation et le prix unitaire ne sont plus affichés, comme l'illustre la capture ci-dessus. C'est presque parfait. En effet, cette insertion de texte provoque une erreur de calcul dans la colonne du montant HT. C'est tout à fait logique car une multiplication entre un nombre et un texte est impossible. Le calcul à ce stade est le suivant :

=SI(F6='';'';E6*F6)

Il faut remplacer le critère de la fonction Si() et lui indiquer que désormais, si la cellule du montant HT n'est pas un nombre, le calcul ne doit pas être fait. C'est la fonction booléenne ESTNUM() d'Excel qui permet de réaliser ce test.

• Remplacer le critère de cette fonction Si() par le suivant :

ESTNUM(F6)=FAUX



Le problème est corrigé. Désormais nous pouvons estimer que le contrôle en amont est parfaitement réalisé. Nous allons maintenant nous soucier du contrôle en Aval et c'est VBA Excel qui va nous le permettre. Nous devons réaliser plusieurs tests. A l'issue, le bouton doit permettre de mettre à jour les stocks et de lancer l'impression de la facture. Mais avant cela, le code doit contrôler si aucun produit hors stock n'y figure, sinon il doit en avertir l'utilisateur. Ensuite, il doit afficher un message à l'utilisateur pour lui demander s'il valide la facture, ce qui conduit à la mise à jour des stocks et à l'impression.

• Enfoncer les touches Alt et F11 pour basculer dans l'éditeur Visual Basic,
• Dans le volet de gauche, sélectionner Feuil1 (Facturation) pour afficher sa page de code,
• Créer la procédure verification_stock(),

Sub verification_stock()
End Sub

• Enregistrer le travail et revenir sur la feuille facturation,
• Activer le ruban Développeur et cliquer sur le bouton Insérer,
• Choisir le premier bouton de Formulaire et le tracer sur la feuille,
• Dans la boîte de dialogue qui suit, choisir la procédure verification_stock et valider,
• Changer son texte en : Valider Facture,

Si le ruban Développeur n'est pas affiché sur votre interface, le support de formation pour débuter en VBA Excel vous explique comment le greffer. Sachez qu'un contrôle de formulaire ne s'imprime pas par défaut. A ce titre, nous allons définir la zone d'impression afin qu'Excel n'imprime que le tableau demandé.

• Sélectionner tout le tableau de facturation, soit de C2 à G35,
• Activer le ruban Mise en page,
• Cliquer sur le bouton ZoneImpr puis choisir Définir dans la liste,
• Commander l'aperçu avant impression par CTRL + P pour contrôler,

Seule la zone définie s'imprimera en effet.

• Revenir sur la feuille en cliquant sur la flèche en haut à gauche de la fenêtre,
• Puis basculer de nouveau dans l'éditeur de code ALT + F11,

Nous avons besoin d'une variable à définir en tant que Range pour parcourir la plage des prix unitaires à l'aide d'une boucle For Each. La formation sur les variables en VBA explique ces déclarations et dimensionnements.

• Déclarer la variable cellule en tant que Range,
• De même déclarer la variable test en tant que Boolean,
• Initialiser cette variable test à False,

Nous utiliserons cette variable pour savoir si la colonne contient ou non un produit hors stock.

Dim cellule As Range: Dim test As Boolean
test = False

Il faut parcourir la plage de cellules F6:F26 à l'aide d'une boucle For Each et de l'objet cellule pour vérifier le contenu de chacune par le biais d'un test. La formation VBA Excel sur les boucles For Each enseigne leur syntaxe et fonctionnement.

• Ecrire les bornes de la boucle For Each,

For Each cellule In Range('F6:F26')
Next cellule

A l'intérieur de cette boucle, il faut réaliser un test à l'aide de l'instruction If() pour savoir si le contenu de la cellule est un tiret, signifiant que le produit est hors stock.

• Ajouter les bornes de l'instruction If avec le test dans la boucle,

If(cellule.Value = '-') Then
End If

Si ce critère est vérifié, alors la variable Test doit basculer à True et on doit sortir de la boucle. Le test sera exploité en dehors pour en avertir l'utilisateur et stopper la procédure.

• Dans l'instruction If, ajouter le code suivant :

test = True
Exit For

Après la boucle, si la variable test est passée à True, nous devons en avertir l'utilisateur par une boîte de dialogue puis stopper le processus.

• Ajouter le code suivant après la boucle :

If (test = True) Then
MsgBox ('Des articles hors stock figurent dans la facture, il n'est pas possible de continuer')
Exit Sub
End If

Le Exit Sub permet de sortir du programme et donc, de ne pas poursuivre après le If.

• Enregistrer le travail,

• Revenir sur la feuille facturation,
• Cliquer sur le bouton pour tester le code,

Dans notre cas, un produit est en rupture de stock. Donc le programme nous en informe puis met fin à l'exécution. Si nous changeons la référence par une autre en stock et que nous cliquons de nouveau sur le bouton, le programme se déroule sans alerte. Il nous faut maintenant vérifier que la facture est conforme, selon les stocks disponibles et les quantités demandées. Nous imbriquerons pour cela deux boucles pour parcourir le catalogue et comparer aux quantités demandées dans la facture. Si tout est conforme, une nouvelle double boucle réalisera l'effet inverse, elle parcourra la facture pour mettre à jour les stocks du catalogue en fonction des quantités achetées. Tout d'abord, nous avons besoin de variables :

• A la suite du code, réaliser les déclarations et affectations suivantes :

Dim ligne As Integer: ligne = 2
Dim valeur_stock As Integer: valeur_stock = 0
Dim valeur_demandee As Integer: valeur_demandee = 0
Dim ref_cat As String: Dim ref_facture As String
Dim choix_utilisateur As Byte

La variable ligne va nous permettre de parcourir les cellules du catalogue. Les variables valeur_stock et valeur_demandee vont permettre de stocker respectivement les quantités en stock et les quantités demandées dans la facture. Les variables ref_cat et ref_facture déclarées en tant que textes, permettent de stocker les références du catalogue et celles utilisées dans la facture pour les comparer. La variable choix_utilisateur permet de mémoriser le bouton cliqué par l'utilisateur sur la boîte de dialogue pour savoir si le programme doit se poursuivre ou stopper.

• Reproduire le code suivant de la première double boucle afin de vérifier si les quantités demandées sont conformes aux stocks :

While(Workbooks('catalogue.xlsx').Worksheets('Feuil1').Cells(ligne,6).Value <> '')
valeur_stock = Workbooks('catalogue.xlsx').Worksheets('Feuil1').Cells(ligne,6).Value
ref_cat = Workbooks('catalogue.xlsx').Worksheets('Feuil1').Cells(ligne,3).Value

For Each cellule In ThisWorkbook.Worksheets('facturation').Range('C6:C26')

If (cellule.Value = ref_cat) Then
valeur_demandee = ThisWorkbook.Worksheets('facturation').Cells(cellule.Row, 5)
If (valeur_demandee > valeur_stock) Then
MsgBox ('La référence ' & cellule.Value & ' ne possède pas assez de stock')
test = True
End If
End If

Next cellule

ligne = ligne + 1
Wend

La boucle While permet de parcourir la colonne Stock du catalogue tant que la condition est vérifiée, c'est à dire tant qu'il y a quelque chose dans la cellule. Comme la variable ligne est utilisée pour parcourir les cellules, elle doit être incrémentée en fin de boucle, avant le Wend (ligne = ligne + 1). Sinon on risque une boucle infinie. Les objets Workbooks et Worksheets permettent respectivement de faire référence au classeur et à la feuille par leur nom. Comme les deux classeurs sont dans le même dossier, il n'est en effet pas nécessaire de désigner catalogue.xlsx avec son chemin. Nous entamons ensuite une boucle For Each à l'intérieur du While. Elle permet donc de comparer tous les éléments de la facture avec celui en cours de lecture dans la boucle While. C'est ainsi que le critère est posé, si les mêmes références sont trouvées (If(cellule.Value = ref_cat)), alors si la quantité demandée est supérieure à la valeur en stock (If (valeur_demandee > valeur_stock)), l'utilisateur en est averti par MsgBox et le booléen test est basculé à True pour pouvoir ensuite sortir du programme. Il nous reste à faire la double boucle dans l'autre sens pour mettre à jour le catalogue et au final lancer l'aperçu avant impression.

• Ajouter les lignes de code suivantes :

If (test = True) Then
Exit Sub
Else
choix_utilisateur = MsgBox('La facture semble correcte, souhaitez-vous l'imprimer et mettre à jour les stocks ?', vbYesNo)
If (choix_utilisateur = 6) Then
For Each cellule In ThisWorkbook.Worksheets('facturation').Range('C6:C26')
ligne = 2
While(Workbooks('catalogue.xlsx').Worksheets('Feuil1').Cells(ligne,6).Value <> '')
If (cellule.Value = Workbooks('catalogue.xlsx').Worksheets('Feuil1').Cells(ligne,3).Value) Then
Workbooks('catalogue.xlsx').Worksheets('Feuil1').Cells(ligne,6).Value = Workbooks('catalogue.xlsx').Worksheets('Feuil1').Cells(ligne,6).Value - ThisWorkbook.Worksheets('facturation').Cells(cellule.Row,5).Value
End If
ligne = ligne + 1
Wend
Next cellule
Else
Exit Sub
End If
End If

ThisWorkbook.Worksheets('facturation').PrintPreview

Nous commençons par arrêter le programme si les stocks ne sont pas suffisants par rapport aux quantités achetées (If (test = True) Then Exit Sub). Nous mémorisons la réponse de l'utilisateur dans la variable (choix_utilisateur = MsgBox()). S'il souhaite finaliser la facture (If (choix_utilisateur = 6) Then), alors nous repartons sur une double boucle en sens inverse. Nous parcourons les références de la feuille facturation (ForEach). Et dans le même temps, nous parcourons les références du catalogue pour les comparer (While (Workbooks('catalogue.xlsx')). Si elles sont identiques (If ()) alors nous soustrayons la quantité en stock par la quantité achetée. Nous n'oublions pas d'incrémenter la variable ligne (ligne = ligne + 1) pour poursuivre la boucle While. A l'issue, une fois tous les stocks mis à jour, nous commandons l'aperçu avant impression de la feuille facturation (ThisWorkbook.Worksheets('facturation').PrintPreview). Et comme nous avions défini une zone d'impression, seule la feuille de la facture s'affichera. Si vous cliquez sur le bouton de la feuille et que des quantités demandées dépassent les quantités en stock, un message s'affiche vous en informant.


Lorsque vous validez, l'exécution s'arrête. Aucune mise à jour des stocks n'est entreprise. Le programme vous demande de corriger la facture. Si au contraire, les quantités demandées ne dépassent pas les stocks, un message vous demande si vous souhaitez valider la facture et mettre à jour les stocks. Si vous cliquez sur Oui, l'aperçu avant impression apparaît, vous n'avez plus qu'à le valider pour éditer la facture. Si vous consultez le catalogue, vous remarquez que les quantités en stock ont été affectées. Le programme fonctionne donc parfaitement.