Le VBA est un langage de programmation puissant, intégré à l’ensemble des logiciels du pack OFFICE, qui permet d’enchaîner de nombreux calculs. Sous Excel, ces calculs portent fréquemment sur des plages de cellules.
Or, les opérations de lecture et d’écriture sur des plages Excel de grande taille ralentissent de manière significative l’exécution des programmes VBA ; il en est ainsi des traitements sur des écritures comptables qui peuvent représenter des volumes conséquents même pour des PME. Pour s’affranchir de cet inconvénient, il est possible d’utiliser les variables tableaux. L’utilisation des variables tableaux est une des techniques d’optimisation du code.
Comme nous le verrons plus loi, l’usage d’une variable tableau est parfaitement approprié pour stocker une plage de cellules. Principe général : le contenu de la plage de cellules est copié dans une variable tableau, puis les données contenues dans la variable tableau sont retraitées par le programme et enfin retranscrites dans les cellules du tableau Excel.
Nous appuierons notre propos sur l’exemple d’un programme de calcul de remises de fin d’années (RFA). La table des chiffres d’affaires (CA) réalisés avec chaque client est rapprochée de la grille de RFA, la RFA est calculée pour chaque client selon la formule CA x Taux de RFA. Dans notre exemple, la table CA est comprise dans la plage de cellules A6 à C8000 :
- le premier champ (colonne A) étant le code client,
- le deuxième (colonne B), le montant du chiffre d’affaires annuel et
- le dernier (colonne C), le montant de la RFA (que le programme doit calculer).
1. Déclaration de la variable tableau
Les variables tableaux sont une catégorie de variables qui permettent de stocker non pas une seule donnée comme une simple variable (pour mémoire, une variable est un espace mémoire accessible par un programme à l’aide d’un nom ; selon son type, une variable peut stocker une donnée : une chaîne de caractère, un montant chiffré…) mais une des séries de données sur une ou plusieurs dimensions. C’est la possibilité de stockage en deux dimensions qui est mise à profit pour traiter des plages de cellules ; en effet, une plage de cellules Excel forme un tableau à deux dimensions (colonnes et lignes formant chacune une dimension).
Il est tout d’abord nécessaire de déclarer la variable tableau (qui se nommera ici TableauDonnées) :
Dim TableauDonnées As Variant
Le type Variant autorise le stockage de n’importe quelle sorte de données (texte et numérique), c’est pourquoi il est systématiquement retenu pour stocker des plages de données (celles-ci accueillant n’importe quel genre de données).
2. Stockage de la plage de cellules dans la variable tableau
Le stockage de la plage de cellules s’effectue simplement. La lecture des données est opérée par le signe égal disposé entre d’une part la variable tableau et d’autre part la plage de données :
TableauDonnées = ActiveWorkbook.Sheets(1).Range("A6:C8000").Value
L’appel de la plage de données est effectué à l’aide des instructions suivantes :
- ActiveWorkbook : classeur Excel en cours d’utilisation,
- .Sheets(1) : première feuille de calcul Excel en partant de la gauche,
- .Range(“A6:C8000”) : plage de cellules A6 à C8000,
- .Value : le traitement portera sur les valeurs et non sur les formules (.Formula) contenues dans la plage de cellules.
Pour résumer, la variable TableauDonnées stockera les valeurs de la plage de cellules A6 à C8000 de la première feuille de calcul du classeur Excel actif.
3. Traitement des données
Les données étant stockées dans la variable tableau TableauDonnées, le programme n’a plus qu’à lancer les traitements nécessaires.
Dans notre cas, la table de CA (stockée dans la variable nommée TableauDonnées) sera opposée à la grille de RFA suivante :
| CA | Taux de remise |
| > 100 K€ | 15 % |
| > 50 K€ | 10 % |
| > 25 K€ | 5 % |
| < 25 K€ | 0 % |
Le rapprochement entre la table des CA et la grille de RFA est effectué à l’aide de simples tests conditionnels (If… Then… ElseIf… : Si condition réalisée Alors réalise l’action 1 Sinon l’action 2) qui schématiquement se représentent ainsi :
Voici le code VBA de la procédure (Sub… End Sub) CalculRFA :
Sub CalculRFA
Dim Taux As Double
Dim CA As Double
Dim i As Integer
For i = 1 to UBound(TableauDonnées)
Taux = 0
CA = TableauDonnées(i,2)
If CA > 100000 Then Taux = 0.15
ElseIf CA > 50000 Then Taux = 0.1
ElseIf CA > 25000 then Taux = 0.05
End If
TableauDonnées(i,3) = CA * Taux
Next i
End Sub
La procédure CalculRFA effectue les opérations suivantes :
- Définit les variables Taux (taux de RFA à appliquer au client en cours de traitement) et CA (chiffre d’affaires du client en cours) ainsi que la variable i qui correspondra au numéro de la ligne de la table de CA en cours de traitement,
- Fait tourner une boucle compteur For… To… Next qui passe en revue toutes les occurrences de la table CA (TableauDonnées), de la première (1) à la dernière (UBound(TableauDonnées)),
- Le CA du client en cours de traitement est lu à partir de la table CA à l’aide de l’instruction :
CA = TableauDonnées(i,2)
- Le CA est opposé à la grille de RFA à l’aide des tests conditionnels,
- Le montant de la RFA est stocké dans la 3ème colonne de la table CA :
TableauDonnées(i,3) = CA * Taux
- Ceci fait, le programme passe au client suivant (Next i).
4. Restitution des données dans Excel
Le calcul des RFA étant achevé, il est temps de transférer la table de CA dans Excel :
ActiveWorkbook.Sheets(1).Range("A6:C8000").Value = TableauDonnées
Vous noterez que la construction de cette instruction d’écriture d’une plage de données dans Excel est la même que celle de la lecture vue au point 2 ci-avant, les deux termes situés de part et d’autre de l’égalité (=) étant inversés.
Pour conclure :
J’utilise cette technique d’optimisation avec succès dans mes différents projets qui traitent de grandes quantités de données (plusieurs dizaines de milliers de lignes). Les gains de temps sont conséquents.
Approfondir le sujet : programmer en VBA
Derniers articles parBenoît RIVIERE (voir tous)
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
- L’IA dans les cabinets comptables : cas concrets - jeudi 26 septembre 2024
- EXCEL : insérer une image ou un logo dans une cellule - lundi 16 septembre 2024
- Lancer l’exécution d’un script Python à partir d’une macro VBA - lundi 9 septembre 2024
Bonjour.Monsieur Benoît Rivere je suis ravi de visiter votre site parce que ça fait longtemps que je cherchais un tel site. En fait j’ai l’impression que vous m’avez “volé mon projet professionnel”. juste pour dire que c’est GENIAL ! je suis un passionné de l’informatique et Etudiant En Etudes Comptables Superieures ( ECS) , à L’institut national polytechnique Houphouêt-Boigny de yamoussoukro en Côte D’ivoire et je desire embrasser le metier d’auditeur Financier voire auditeur Informatique et me preparer pour l’expertise comptanle par la suite . pour le volet INFORMATIQUE je je suis programmeur amateur en WINDEV et EXCEL et EXCEL VBA , j’ai aussi touché le HTML5/CSS pour tenter de créer des sites web et je tien vraiment à avoir un Diplôme en Informatique de gestion afin d’être un Homme accompli. je cherchais un Mentor . et voila je tombe sur ce site . je désire vraiment correspondre avec vous pour mieux vous connaitre et recevoir des conseils du fait de la similitude de nos projets professionnels. MERCI D’AVANCE voici mon adresse mail: kkbam@live.fr
Pingback: VBA : les types de données | Audit & Systèmes d'Information
Bonjour,
J’étais totalement passé à coté de votre commentaire. Désolé de n’y répondre seulement maintenant, surtout avec les louanges que vous m’adressez ! Mais n’exagérons rien, je n’ai rien d’exceptionnel. Nous sommes nombreux à avoir la même passion pour notre métier. C’est simplement que nous sommes, malheureusement faute de temps, beaucoup moins nombreux à la partager. N’hésitez pas à réagir à mes articles, c’est toujours une base d’échange enrichissante pour tout le monde.
Bien cordialement,
Benoît RIVIERE
Pingback: EXCEL & VBA : convertir une chaîne de caractères en une variable tableau avec la fonction Split – Audit & Systèmes d'Information
Pingback: Langage C : déclaration des variables et types de données – Audit & Systèmes d'Information
Pingback: 10 ans – Audit & Systèmes d'Information
Bonjour,
Merci pour votre contribution.
Toutefois, la plupart du code est faux. Ou avez-vous trouvé code? L’avez-vous compris? L’avez-vous testé? J’en doute… Cela va induire en erreur de nombreuses personnes qui tente de se former sur internet.
Vous trouverez ci-dessous la version corrigée :
Sub tableau()
Dim tableau_premier As Variant
tableau_premier = Range(“A6:C8000”).Value
Dim Taux As Double
Dim CA As Double
Dim i As Integer
For i = 1 To UBound(tableau_premier)
Taux = 0
CA = tableau_premier(i, 2)
If CA > 100000 Then
Taux = 0.15
ElseIf CA > 50000 Then
Taux = 0.1
ElseIf CA > 25000 Then
Taux = 0.05
End If
tableau_premier(i, 3) = CA * Taux
Next i
Range(“A6:C8000”).Value = tableau_premier
End Sub
Bonne soirée.
Bonsoir,
Je vous remercie de l’intérêt que vous portez à mon blog et d’avoir pris le temps de de lire mon article.
J’ai écrit ce code à partir d’un extrait d’un autre programme que j’ai moi-même écrit afin d’illustrer mon propos. J’insiste sur le fait que je l’ai écrit moi-même. Vous pouvez douter de mes compétences en informatique, pourquoi pas, ce n’est pas mon métier ; c’est juste une passion que je partage bénévolement. Mes capacités en la matière n’égalent certainement pas les vôtres mais me permettent dans mon quotidien de résoudre bien des problèmes.
Toutefois, vous avez raison, mon code comporte deux erreurs : la variable TableauDonnées devient par endroit TableDonnées et j’ai interverti lignes et colonnes dans le traitement de la variable TableauDonnées. Au moins, ayant moi-même écrit ce code, je ne peux m’en prendre qu’à moi-même.
J’ai corrigé mon article en conséquence. De ce point de vue, je vous remercie de votre retour qui, je le concède, va éviter à mes lecteurs des recherches inutiles.
J’ai ajouté en téléchargement le classeur Excel avec la macro pour en démontrer le correct fonctionnement.
Bien cordialement,
Benoît RIVIERE
Bonjour Benoît,
Je vous suis avec beaucoup d’intérêt. Je suis un débutant Vba Excel et en recherchant sur les variables tableau je suis tombé sur l’exercice sur la RFA. Jai compris le code mais mon soucis c’est le classeur actif qui contient déjà les données brutes et a la fin également on restitue également le résultat sur la même feuille. Ça reste flou dans ma tête.
Pingback: Exemple de programme en Python : calcul d'un échéancier d'emprunt - Audit & Systèmes d'Information