EXCEL et VBA : Manipuler des plages de cellules dans des variables tableaux

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.

Liste de clients : calcul des remises de fin d'année

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) :

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 :

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 TableDonné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 :

Test conditionnelVoici le code VBA de la procédure (Sub… End Sub) CalculRFA :

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 (TableDonné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 :

Le CA est égal à la données de la deuxième colonne (CA) de la ligne en cours (i). A noter, les données de la plage Excel sont stockées :

– dans la table en colonnes et en lignes : TableDonnées(colonne,ligne),

– dans Excel en lignes et en colonnes : .Cells(ligne,colonne).

Gare aux confusions !

  • 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 :

  • 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 :

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

Share Button
The following two tabs change content below.
Benoît RIVIERE
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter
Benoît RIVIERE

Derniers articles parBenoît RIVIERE (voir tous)

5 commentaires

  1. 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

  2. Pingback: VBA : les types de données | Audit & Systèmes d'Information

  3. 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

  4. Pingback: EXCEL & VBA : convertir une chaîne de caractères en une variable tableau avec la fonction Split – Audit & Systèmes d'Information

  5. Pingback: Langage C : déclaration des variables et types de données – Audit & Systèmes d'Information

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *