Additionner les cellules non barrées avec Excel

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


Sujets que vous pourriez aussi aimer :


Sommer les valeurs non barrées

Logiquement, les éléments barrés révèlent un état obsolète qui peut s'enclencher visuellement par le biais d'une mise en forme conditionnelle. Elle doit simplement scruter l'écart entre la date d'émission et la date du jour par exemple. Il peut aussi s'agir de factures honorées pour les différencier des commandes impayées. Nous souhaitons bâtir une fonction VBA Excel capable de calculer le reste dû sur la reconnaissance de cette mise en valeur particulière.

Classeur Excel à télécharger
Nous suggérons d'appuyer les travaux sur une feuille livrant des montants, payés pour certains et impayés pour d'autres.

• Télécharger le classeur somme-valeurs-non-barrees.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons l'unique feuille de ce classeur. Elle offre un tableau qui présente les commandes de certains clients. Les montants barrés révèlent ceux qui se sont acquittés de leur dette. C'est sur cette base que nous souhaitons exploiter une fonction VBA Excel pour calculer le total du reste dû, sur la droite de la feuille.

Créer la fonction
Nous devons donc commencer par créer cette fonction. Ici, nous choisissons de l'implanter dans un module du projet. Mais comme vous le savez, si vous souhaitez qu'elle soit disponible pour toutes les utilisations, vous devez l'enregistrer dans la bibliothèque d'Excel.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur, déployer le menu Insertion,
• Dans les propositions, choisir l'option Module,

Un nouveau module apparaît dans l'arborescence de l'explorateur de projet et sa feuille de code (vierge pour l'instant) s'affiche au centre de l'écran.

• Dans cette feuille de code, créer la fonction sommeNonBarres, comme suit :

Function sommeNonBarres(plage As Range) As Double
Dim cellule As Range

End Function

Nous la déclarons avec un paramètre en attente, typé comme un Range. En effet, il s'agit de la plage que l'utilisateur devra désigner au moment de la construction de la formule, afin de déterminer où calculer le reste dû. Cette fonction est elle-même typée comme un réel double, pour savoir gérer les décimales. Dans le cas de notre tableau, tous les montants sont ronds. Nous aurions donc pu nous contenter de la typer comme un Integer. Mais qui peut le plus, peut le moins. La variable cellule sera utilisée pour parcourir toutes les cases de la plage désignée afin de déceler celles qui sont barrées.

Parcourir toutes les cellules de la plage
Précisément, il est maintenant question d'enclencher une boucle For Each pour analyser chaque cellule de la plage choisie.

• Après la déclaration de la variable, créer la boucle For Each suivante :

...
Application.Volatile
For Each cellule In plage

Next cellule
...

Tout d'abord, la méthode Volatile de l'objet Application permet d'imposer le recalcul automatique si d'aventure des montants venaient à être modifiés dans la plage de la somme. Ensuite, nous exploitons notre objet cellule dans une boucle For Each pour parcourir toutes les cellules dans la plage (In plage) désignée par l'utilisateur.

Tester si les cellules sont barrées
Désormais, à chaque passage dans cette boucle, donc pour chaque cellule analysée tour à tour, nous devons vérifier sa mise en forme. Plus spécifiquement, nous devons vérifier si son montant est barré, indiquant que sa valeur ne doit pas être considérée dans le calcul du total dû. Nous avons donc besoin de dégainer une instruction conditionnelle.

• Dans les bornes de la boucle, ajouter l'instruction conditionnelle suivante :

...
If (cellule.Font.Strikethrough = False) Then
sommeNonBarres = sommeNonBarres + cellule.Value
End If
...

C'est la propriété enfant Strikethrough de la propriété Font d'un objet de type Range (cellule ici) qui permet de savoir si la valeur de la case analysée est barrée. Si le test renvoie False, nous savons que le montant n'est pas barré. Donc, nous devons l'intégrer dans le cumul. C'est la consolidation que nous entreprenons ensuite, pour chaque cellule non barrée. Au précédent montant (sommeNonBarres), nous ajoutons la valeur portée par la cellule en cours (cellule.Value). Comme vous le constatez, ce total nous le consolidons dans le nom de la fonction. En effet et vous le savez, en VBA, une fonction retourne la valeur de son calcul par son propre nom.

Calculer le reste dû
Le code VBA est déjà terminé. Il ne nous reste plus qu'à tester la fonction que nous venons de créer.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Désigner la cellule du cumul à calculer en cliquant sur sa case G6,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : sommeNonBarres(,
• Désigner tous les montants en sélectionnant la plage de cellules D4:D11,
• Fermer la parenthèse de la fonction,
• Enfin, valider la formule par la touche Entrée du clavier,

Nous obtenons un total logiquement inférieur à celui du dessus puisqu'il concerne le cumul global des montants. Si vous sélectionnez les cellules non barrées et que vous consultez l'information en bas à droite dans la barre d'état, vous avez le plaisir de constater que la somme indiquée vient parfaitement recouper celle de notre calcul.

Remarque : La mise à jour du calcul intervient à chaque modification de valeur dans la feuille mais pas au changement de mise en forme (Barré), comme c'est le cas pour toutes les fonctions Excel.