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

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 :

Test conditionnelVoici 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

Share Button
Calcul RFA
Calcul RFA
Calcul-RFA.xlsm
18 KiB
577 téléchargements
Détails...
The following two tabs change content below.
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

10 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

  6. Pingback: 10 ans – Audit & Systèmes d'Information

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

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

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

  10. Pingback: Exemple de programme en Python : calcul d'un échéancier d'emprunt - Audit & Systèmes d'Information

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.