Conceptualisation des variables tableau en VBA et Application à l'optimisation du code sous Excel

L'objectif de ce tutoriel est de proposer une approche de conceptualisation (visualisation) simple des Arrays ou variables tableau à 1,2 et 3 dimensions.
Il est constitué de deux parties pratiquement indépendantes, concernant les Arrays ou variables tableau dans :

  • Le VBA en général chapitres III à VII.
  • L'environnement Excel chapitres VIII à XI.

Une vidéo en 3D explicite les Arrays ou variables tableau à 3 dimensions.

6 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Mon ami, Benoît Sansarray, lors d'une discussion sur le VBA, m'a révélé qu'il ne touchait jamais aux variables tableau, car elles lui paraissaient très compliquées et dès qu'il y pensait il avait un blocage comme si, pour lui, on appuyait sur la touche des neurones : Image non disponible
Il a reconnu que c'était bien dommage car il avait déjà copié-collé des morceaux de codes qui s'avéraient bien utiles, mais il n'arrivait pas à les manier ne pouvant se les représenter. Je lui ai répliqué que ce n'était pas si complexe que ça et lui ai fait un croquis vite fait qui l'a laissé perplexe… Il n'empêche que ça a dû me perturber car cette nuit là, j'ai rêvé que j'étais tranquillement en train de coder sur mon portable assis dans le désert quand Benoît surgissait et me demandait : dessines-moi une touche Image non disponible !

Je lui ai donc proposé de servir de cobaye pour la rédaction d'un tutoriel qui lui permettrait de conceptualiser (et d'apprivoiser) la chose sous une approche graphique puisque telle était sa méthode de pensée, que nous avions d'ailleurs en commun.
Nous avons convenu qu'une animation vidéo en 3D lui permettrait une approche visuelle plus probante de la conceptualisation proposée.

En premier lieu, nous avons décidé de construire le cadre de notre réflexion.

A la fin de ce tutoriel, "la fiche Array de Benoît" vous permettra d'imprimer un résumé synthétique de la conceptualisation pour une remise en mémoire rapide le moment voulu…

II. Préambule

  • Ceci étant un tutoriel sur la conceptualisation des variables tableaux en VBA, nous n'aborderons donc pas ici leur construction et leur manipulation en dehors de ce qui sera indispensable à notre rédaction. Pour tout le côté technique, reportez-vous au tutoriel très complet de Silkyroad et à l'aide VBA.
  • Nous n'aborderons ici que la conceptualisation des Arrays ou variables tableau de dimensions 1, 2 et 3, car la question de la pertinence de l'utilisation d'Arrays ou variables tableau à plus de deux dimensions sera posée. En effet alors que la dimension 3 ouvre des possibilités importantes d'analogies avec la structure des classeurs Excel, à partir de la quatrième dimension, à part les mathématiciens habitués à manier des espaces à N dimensions (qui n'ont d'ailleurs pas besoin de ce tutoriel), une représentation graphique devient inutilisable et nuirait sûrement à la faculté d'abstraction propre à ces raisonnements.
  • La notion de conceptualisation appliquée a forcément un côté arbitraire, d'autres personnes que Benoît ou moi verraient les choses de façon totalement différente, dans le cadre de ce tutoriel nous nous plaçons dans la classe des adeptes de "fais-moi un dessin".
  • Il ne s'agit pas non plus d'adopter un mode représentatif de la façon dont le langage stocke ces variables, ce serait une hérésie. Car, pour le gestionnaire de mémoire, il s'agit simplement d'adresses mémoire et il n'a aucune notion (à ce jour) de dimensions, il fonctionne en base deux. C'est l'esprit humain qui a besoin de conceptualisation.
  • Pour mettre le focus sur certains points, nous serons appelés, à employer des concepts contextuellement erronés, pour justement expliciter les cheminements à ne pas suivre ; ce qui paraît, à juste titre, antinomique mais jugé pédagogiquement nécessaire. C'est pourquoi, il est toujours nécessaire de bien évaluer le contexte décrit afin de ne pas créer d'inférence non valide.
  • Nous prendrons pour principe que la représentation de la dimension 1 se fait en empilant les cases verticalement. Un mode horizontal serait tout aussi juste, mais moins pratique pour construire les analogies avec Excel.
  • Il est conseillé en premier lieu, de se rafraîchir la mémoire avec les chapitres I, II A et II C du tutoriel de Sylkyroad. C'est court et suffisant pour le départ.
  • Les définitions en italique des mots clefs VBA sont les définitions édulcorées issues de l'aide VBA adaptées à notre contexte. N'hésitez pas à consulter l'aide pour plus de détails.

Nous emploierons, par défaut, dans notre module VBA l'instruction Option Base 1 (instruction utilisée au niveau module pour déclarer la limite inférieure par défaut des indices d'un tableau.)

III. Les Arrays en général

Le terme Array(s), dans sa signification informatique, est ambigu en lui-même. En effet Array est la traduction littérale de tableau en anglais qui peut donc se mettre au pluriel, mais c'est aussi le nom d'une fonction qui crée des tableaux dans divers langages dont le VBA :

Array, fonction
Syntaxe : Array(arglist) L'argument arglist est une liste de valeurs délimitée par des virgules attribuées aux éléments du tableau contenu dans la variable de type Variant. Si aucun argument n'est spécifié, un tableau de longueur nulle est créé.

C'est pourquoi, emploierons par la suite : variable(s) tableau pour les variables tableau afin d'éviter toutes confusions avec la fonction et avec le terme tableau qui a plusieurs autres significations dans notre environnement. Pour la fonction, nous emploierons le terme Array en italique.

Dans les codes vba, nous noterons par exemple "Sub lesArrayCestSimple()". L'éditeur étant, lui en anglais, il faut s'habituer à une interpétation contextuelle des termes, les notations devant éviter, pour les noms d'objets divers, un nom qui peut porter à confusion avec les mots clefs. On utilise souvent un langage phonétique illustré propre à ces contraintes et précieux au niveau de la relecture et de la maintenance.

IV. Les variables tableau selon l'aide VBA

Vous pouvez déclarer un tableau pour traiter un ensemble de valeurs du même type de données. Un tableau est une variable simple comportant de nombreux compartiments permettant de stocker autant de valeurs, par opposition à une variable classique dotée d'un compartiment destiné à une seule valeur. Vous pouvez faire référence au tableau globalement lorsque vous souhaitez faire référence à toutes les valeurs qu'il contient, ou faire référence à ses éléments individuels.

Selon le mode de conceptualisation adopté pour ce tutoriel, nous aurons donc ceci :

Image non disponible

Nous sommes bien dans un phénomène de conceptualisation pure en nous représentant ici 3 concepts différents à partir d'une même représentation graphique d'une cellule.

V. Les variables tableau à une dimension

Un variables tableau est donc une variable qui a la particularité de renfermer une série de valeurs du même type et de posséder une limite inférieure et une limite supérieure.
Pour accéder à une valeur donnée, il suffira de préciser son index (avec un nombre pour la dimension 1).
Nous allons donner comme nom à notre variable tableau : tabArrayOrmonth et le déclarer de la façon suivante : Dim tabArrayOrmonth(7), 7 étant la limite supérieure.
Schématiquement, on va donc occuper 7 cases de la façon suivante (qu'on y mette ou non des données, on réserve nos 7 cases du fait de notre déclaration).
Image non disponible Image non disponible
On a placé dans notre tableau quatre valeurs qui seront employées selon le nom de la variable tableau et leur index, que ce soit en lecture ou en écriture.
La conceptualisation à ce niveau est assez simple, notre variable tableau n'a que des cases empilées sur une seule dimension.

VI. Les variables tableau à deux dimensions

Nous allons déclarer notre tableau de la façon suivante : : Dim tabArrayOrmonth(7,4), 7 étant la limite supérieure de la dimension 1 et 4 celle de la dimension 2 (les dessins vont commencer à être bien utiles…)
Schématiquement, on va donc occuper 7 * 4 = 28 cases de la façon suivante (qu'on y mette ou non des données, on réserve nos 28 cases du fait de notre déclaration).
Pour accéder à une valeur donnée, il suffira de préciser son index (avec 2 nombres pour la dimension 2).
Image non disponible Image non disponible
On a placé dans ce tableau nos quatre valeurs qui seront employées selon le nom de la variable tableau et de leur index, que ce soit en lecture ou en écriture.

Comme nous avions décidé que notre représentation de la première dimension était verticale, la représentation de la deuxième dimension est logiquement horizontale.

La conceptualisation à ce niveau est encore assez simple, notre variable tableau n'a que des cases sur deux dimensions et donc pas de profondeur.

Si nous déclarons un autre tableau : Dim tabArrayOrmonth(5,29), nous obtiendrons un tableau de 5 lignes sur 29 colonnes.


Image non disponible

VII. Les variables tableau à trois dimensions

Nous allons déclarer notre tableau de la façon suivante : : Dim tabArrayOrmonth(7,4,5)

  • 7 étant la limite supérieure de la dimension 1.
  • 4 celle de la dimension 2.
  • 5 celle de la dimension 3.

Schématiquement, on va donc occuper 7 * 4 * 5 = 140 cases de la façon suivante (qu'on y mette ou non des données, on réserve nos 140 cases du fait de notre déclaration).

Pour accéder à une valeur donnée, il suffira de préciser son index (avec 3 nombres pour la dimension 3).

Image non disponible

La donnée bleue est bien en 5,2,4 comme le montre les figures suivantes et la vidéo en fin de paragraphe. Elle est non localisable sur la vue en 3D fixe.

Image non disponible      Image non disponible     Image non disponible

On a de nouveau placé, dans ce tableau nos quatre valeurs qui seront employées selon le nom de la variable tableau et leur index, que ce soit en lecture ou en écriture.
Comme nous avions décidé que notre représentation de la première dimension était verticale et que la représentation de la deuxième dimension était horizontale la représentation de la troisième dimension est logiquement en profondeur.

L'animation vidéo proposée vous permettra de mieux visualiser le concept    Image non disponiblevoir la Vidéo

VIII. Analogies et applications à Excel.

Si bien sûr, de base, les choses sont équivalentes, l'utilisation des particularités d'Excel peut faire gagner beaucoup en puissance, mais aussi provoquer pas mal d'ambiguïtés.

En effet, Excel lui-même passe par des variables tableau pour certains traitements au niveau des Range notamment.

Nous avons à notre disposition des manipulations trop souvent négligées alors qu'elles apportent des gains de rapidité très importants et une simplification impressionnante du code.

Nous ne prendrons ici que les exemples les plus simples dans le but, répétons-le, de faire ressortir certaines subtilités d'emploi qui, non maîtrisées, peuvent faire paraître complexe un outil simple par ailleurs.

VIII-A. Création d'une variable tableau en VBA Excel

Pour ce faire nous utiliserons la fonction Array :

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple()
Dim tabArrayOrmonth() As Variant
  tabExemp = Array("Toto", "Zaza", "Mimi", "Nono", "Babu")
End Sub

VIII-B. Alimentation d'une plage via une variable tableau

VIII-B-1. Via une variable tableau à une dimension

Nous voulons maintenant affecter les données de notre tableau aux cellules A1 à A5 et C5 à G5 de notre feuille test. ; la première idée qui peut venir à l'esprit serait de parcourir la collection des cellules de notre plage en affectant à chaque itération la valeur d'une cellule à un index de la variable tableau, comme ceci :

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple()
Dim tabArrayOrmonth() As Variant
Dim I As Byte

tabArrayOrmonth = Array("Toto", "Zaza", "Mimi", "Nono", "Babu")

For I = 1 To UBound(tabArrayOrmonth, 1) 'UBound retourne la limite supérieure 
'de la dimension voulue d'un tableau
   Cells(I, 1) = tabArrayOrmonth(I) 'on remplit en colonne
   Cells(5, I + 2) = tabArrayOrmonth(I) 'on remplit en ligne
Next I
End Sub
 

Image non disponible
Cette approche fonctionne parfaitement, mais parcourir une collection est à éviter quand c'est possible car cela devient vite très lourd si le nombre d'éléments est important (un exemple sur cinq données est explicite au niveau approche, mais absolument non représentatif d'une application moyenne).

Or qu'est-ce qu'une feuille Excel si ce n'est un tableau ? Le VBA le sait bien et nous permet de l'exploiter au mieux. Dans le cas présent nous pouvons réaliser la même chose en beaucoup plus rapide.

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple()
Dim tabArrayOrmonth() As Variant
Dim I As Byte

tabArrayOrmonth = Array("Toto", "Zaza", "Mimi", "Nono", "Babu")
For I = 1 To UBound(tabArrayOrmonth, 1) 'UBound retourne la limite supérieure 
'de la dimension voulue d'un tableau
   Cells(I, 1) = tabArrayOrmonth(I) 'on remplit en colonne
   Cells(5, I + 2) = tabArrayOrmonth(I) 'on remplit en ligne
Next I
Stop
Range("C8:G8") = tabArrayOrmonth 'on remplit en ligne. Cette ligne fait 
'la même chose que celle avant le next I
'mais en un bloc, sans les itérations d'une boucle.
End Sub

Image non disponible
On fait de même en colonne.

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple()
Dim tabArrayOrmonth() As Variant
Dim I As Byte

tabArrayOrmonth = Array("Toto", "Zaza", "Mimi", "Nono", "Babu")
For I = 1 To UBound(tabArrayOrmonth, 1) 'UBound retourne la limite 
'supérieure de la dimension voulue d'un tableau
   Cells(I, 1) = tabArrayOrmonth(I) 'on remplit en colonne
   Cells(5, I + 2) = tabArrayOrmonth(I) 'on remplit en ligne
Next I
Stop
Range("C8:G8") = tabArrayOrmonth 'on remplit en ligne
Stop
Range("I1:I5") = tabArrayOrmonth 'on veut remplir en colonne
End Sub

Image non disponible
Nous n'avons pas le résultat attendu : que se passe-t-il ?
Sous VBA, les tableaux à une dimension sont en ligne par défaut, donc pour Excel, le code que nous venons d'écrire équivaut à : Range("I1:I5") = "Toto" (soit le premier index).
Pour s'en convaincre, il suffit d'appliquer à tabArrayOrmonth sur la ligne suivante la fonction Transpose (de Feuille), pour que tout rentre dans l'ordre !

 
Sélectionnez

Range("J1:J5") = Application.WorksheetFunction.Transpose(tabArrayOrmonth)
'

Image non disponible

Il ne s'agit absolument pas de bogue ou de comportement erratique. Il faut comprendre comment fonctionne Excel avec les variables tableau , et c'est pourquoi nous avons mis en évidence ce comportement normal mais peut-être inattendu...

VIII-B-2. Via une variable tableau à deux dimensions

Pour rester dans l'environnement des variables tableau , nous allons nous servir d'une première variable tableau pour établir la liste de nos données et nous les ventilerons ensuite dans les deux dimensions de notre tableau. Puis, à l'aide d'une boucle, nous ventilerons nos données dans les cellules.

Attention, nous choisissons exprès de placer la limite supérieure de notre première dimension à 2, afin de bien souligner que cette donnée est complètement indépendante du nombre de dimensions de la variable tableau. C'est un facteur de confusion très fréquent lorsqu'on manipule les variables tableau.

Là encore, nous pouvons choisir de construire notre tableau en ligne ou en colonne.

VIII-B-2-a. En ligne

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple2()
Dim tboArrayOrmonth() As Variant
Dim tboArrayOrmonthEnLigne(2, 5) As Variant
Dim I As Byte
Dim J As Byte
Dim K As Byte

tboArrayOrmonth = Array("Toto", "Zaza", "Mimi", "Nono", _
"Babu", "Ella", "Emma", "Eliz", "Edda", "Edme")
K = 1
For I = 1 To UBound(tboArrayOrmonthEnLigne, 1)
   For J = 1 To UBound(tboArrayOrmonthEnLigne, 2)
      tboArrayOrmonthEnLigne(I, J) = tboArrayOrmonth(K)
      K = K + 1
   Next J
Next I

For I = 1 To UBound(tboArrayOrmonthEnLigne, 1)
   For J = 1 To UBound(tboArrayOrmonthEnLigne, 2)
      Cells(I, J) = tboArrayOrmonthEnLigne(I, J)
   Next J
Next I
End Sub 

Image non disponible

VIII-B-2-b. En colonne

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple3()
Dim tboArrayOrmonth() As Variant
Dim tboArrayOrmonthEnColonne(5, 2) As Variant
Dim I As Byte
Dim J As Byte
Dim K As Byte

tboArrayOrmonth = Array("Toto", "Zaza", "Mimi", "Nono", _
"Babu", "Ella", "Emma", "Eliz", "Edda", "Edme")
K = 1
For I = 1 To UBound(tboArrayOrmonthEnColonne, 2)
   For J = 1 To UBound(tboArrayOrmonthEnColonne, 1)
      tboArrayOrmonthEnColonne(J, I) = tboArrayOrmonth(K)
      K = K + 1
   Next J
Next I

For I = 1 To UBound(tboArrayOrmonthEnColonne, 2)
   For J = 1 To UBound(tboArrayOrmonthEnColonne, 1)
      Cells(J + 4, I) = tboArrayOrmonthEnColonne(J, I)
   Next J
Next I
End Sub

Image non disponible
Il suffit donc d'agencer nos boucles d'alimentation en fonction de la déclaration de notre variable tableau : Dim tboArrayOrmonthEnLigne(2, 5) As Variant ou Dim tboArrayOrmonthEnColonne(5, 2).

Là encore, nous pouvons remarquer : qu'est-ce qu'une feuille Excel si ce n'est un tableau ? Le VBA le sait bien et nous permet de l'exploiter au mieux et dans le cas présent de réaliser la même chose en beaucoup plus rapide qu'avec des boucles pour itérer sur chaque cellule de la plage cible.

VIII-B-2-c. En ligne optimisé

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple4()
Dim tboArrayOrmonth() As Variant
Dim tboArrayOrmonthEnLigne(2, 5) As Variant
Dim I As Byte
Dim J As Byte
Dim K As Byte

tboArrayOrmonth = Array("Toto", "Zaza", "Mimi", "Nono", _
"Babu", "Ella", "Emma", "Eliz", "Edda", "Edme")
K = 1
For I = 1 To UBound(tboArrayOrmonthEnLigne, 1)
   For J = 1 To UBound(tboArrayOrmonthEnLigne, 2)
      tboArrayOrmonthEnLigne(I, J) = tboArrayOrmonth(K)
      K = K + 1
   Next J
Next I
Range("A1:E2") = tboArrayOrmonthEnLigne
End Sub

Image non disponible

VIII-B-2-d. En colonne optimisé

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple5()
Dim tboArrayOrmonth() As Variant
Dim tboArrayOrmonthEnColonne(5, 2) As Variant
Dim I As Byte
Dim J As Byte
Dim K As Byte

tboArrayOrmonth = Array("Toto", "Zaza", "Mimi", "Nono", _
"Babu", "Ella", "Emma", "Eliz", "Edda", "Edme")
K = 1
For I = 1 To UBound(tboArrayOrmonthEnColonne, 2)
   For J = 1 To UBound(tboArrayOrmonthEnColonne, 1)
      tboArrayOrmonthEnColonne(J, I) = tboArrayOrmonth(K)
      K = K + 1
   Next J
Next I
Range("A1:B5") = tboArrayOrmonthEnColonne
End Sub

Image non disponible

VIII-C. Alimentation d'une variable tableau via une plage

Là aussi Excel permet d'exploiter au mieux ses possibilités. Au lieu d'employer de longues boucles pour parcourir une collection de cellules, on peut dans certains cas alimenter directement la variable tableau par les références de la plage source.

VIII-C-1. Tentative d'alimentation d'une variable tableau à une dimension via une plage

On peut s'attendre à obtenir un tableau à une dimension (visualisation du chapitre 1) comme celui construit au chapitre VIII-B-1 en employant le code suivant :

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple6()
Dim tboArrayOrmonthViaCellEnCol() As Variant
Dim I As Byte

tboArrayOrmonthViaCellEnCol = Range("A1:A5")

Range("C1:C5") = tboArrayOrmonthViaCellEnCol 'on réécrit sur une colonne adjacente
Stop
For I = 1 To UBound(tboArrayOrmonthViaCellEnCol)
   MsgBox tboArrayOrmonthViaCellEnCol(I)
Next I
End Sub

Le résultat voulu est obtenu. Par contre la boucle d'affichage des données au moyen d'une MsgBox provoque une erreur "9".

Image non disponible
Que se passe-t-il ?

Pour simplifier, on peut considérer qu'il n'existe que des feuilles à 2 dimensions, Excel utilise donc toujours des coordonnées (ligne, colonne) ou (colonne, ligne) pour désigner une cellule.
En fait dans l'écriture précédente, on crée donc un tableau à 2 dimensions, alors que la boucle tente de parcourir un tableau à une dimension.

On vérifie ce fait en ajoutant à notre code un appel à une fonction qui nous retourne les dimensions du tableau et à une procédure qui nous affiche ses données via un MsgBox.

 
Sélectionnez

Option Explicit
Option Base 1

Sub lesArrayCestSimple7()
Dim tboArrayOrmonthViaCellEnCol() As Variant
Dim tboPass As Variant
Dim I As Byte

tboArrayOrmonthViaCellEnCol = Range("A1:A5")

Range("C1:C5") = tboArrayOrmonthViaCellEnCol 'on réécrit sur une colonne adjacente
Stop
tboPass = tboArrayOrmonthViaCellEnCol
 
MsgBox NombreDimensions(tboPass) ' un tboPass permet d'appeller notre fonction
                                 'à partir de divers array sans souci des noms.
Call AffichMessageBox2 (tboPass)
End Sub

Function NombreDimensions(tboPass As Variant) As Integer '(code SilkyRoad sur DVP)
Dim Compteur As Integer
  
If Not IsArray(tboPass) Then Exit Function
On Error GoTo Fin
Do
   Compteur = Compteur + 1
   Debug.Print UBound(tboPass, Compteur)
Loop
Fin:
    NombreDimensions = Compteur - 1
End Function

Sub AffichMessageBox2(ByRef tboPass As Variant)
Dim I As Byte
Dim J As Byte
Dim affich As String

For I = 1 To UBound(tboPass, 1)
   For J = 1 To UBound(tboPass, 2)
   affich = affich & tboPass(I, J) & " (" & I & "," & J & ")"
   Next J
   affich = affich & vbCrLf
Next I
MsgBox affich, , "Tutoriels sur DVP"
End Sub

VIII-C-2. Conceptualisation graphique

Si on reprend notre approche graphique du premier chapitre, une variable tableau obtenu avec un code de ce genre : tboArrayOrmonthViaCellEnCol = Range("A1:A7") sera représenté comme ci-dessous.

Image non disponible

Et non :

Image non disponible

L'alimentation d'une variable tableau via une plage, de la manière employée ici, retournera donc toujours un tableau à 2 dimensions.

VIII-D. Représentation rapide lors de la saisie du code VBA

Ce qui arrive le plus couramment lorsqu'on saisit des donnée de variables tableau dans les boucles qu'on engendre est qu'on cherche trop souvent qui est quoi.

L'avantage de construire ses tableaux en 2 et 3 dimensions par analogie à une feuille Excel est que l'on s'y retrouve plus rapidement.
En effet il suffit de penser à sa notation comme pour la propriété Cells.
La notation de la propriété Cells pour renvoyer un objet Range est celle-ci : Cells(Row, Column). Donc en voulant adresser dans une boucle une case qui correspondrait à Range("C5") plutôt que de penser Range, je pense Cells(5,3) et donc je noterai tabArrayOrmonth(5,3).

La représentation en notation Range(coordonnées) dans ce contexte est en fait finalement une mauvaise habitude. Sans regarder mon tableur, si sous Excel 2007, je vous parle de Cells(5, 15782), vous traduisez immédiatement ligne 5, colonne 15782. Maintenant si je vous demande de me donner la correspondance en notation Range, vous aurez sans doute du mal à répondre Range("WHZ5").

Pour preuve, si sous Excel, vous passez en Style de référence L1C1, Excel travaille bien en notation (Row, Column) et ne propose pas une notation C1L1…

Donc, si je veux parcourir toutes les valeurs d'une colonne qui correspond à la colonne 6, il suffit de boucler sur tabArrayOrmonth(I,6).

Pour une variable tableau à 3 dimensions, on visualisera de façon identique en pensant aux Cases (Row,Column,Feuille).

IX. Optimisation du code avec les variables tableau sous Excel

L'utilisation des variables tableau peut permettre des gains de vitesse impressionnants quand le contexte s'y prête. Comme nous l'avons signalé, nos exemples sur une dizaine de cellules ne sont pas probants à ce niveau, mais dans de nombreux cas l'écart se creuse !
Pour nos besoins, nous construisons donc une feuille de 55 000 lignes sur 256 colonnes soit 14 080 000 cellules à laquelle nous appliquons les codes suivants, en mesurant les temps d'exécution.

Construction Feuille Exemple
Sélectionnez

Option Explicit
Option Base 1

Sub JeRempli_Feuille_14080000()
Dim I
Dim J
Dim pass1 As Single, pass2 As Single

pass1 = Timer
Application.ScreenUpdating = False
For I = 1 To 55000
For J = 1 To 256
Cells(I, J) = "TotoTataTutu"
Next J
Next I
Application.ScreenUpdating = True
pass2 = Timer
MsgBox pass2 - pass1 & " secondes pour " & ActiveCell.CurrentRegion.Cells.Count & " cellules"
End Sub

Image non disponible

Ensuite, on rempli notre variable tableau en parcourant la collection de notre plage via deux boucles et on en note la durée.

Remplissage de l'Array via une boucle
Sélectionnez

Option Explicit
Option Base 1

Sub RempliArray_viaFeuille()
Dim tboMonArray(55000, 256) As Variant
Dim I As Long, J As Long
Dim pass1 As Single, pass2 As Single

pass1 = Timer
For I = 1 To 55000
   For J = 1 To 256
      tboMonArray(I, J) = Cells(I, J)
   Next J
Next I
pass2 = Timer
MsgBox pass2 - pass1 & " secondes pour " & Format(ActiveCell.CurrentRegion.Cells.Count, "#,##0") & " cellules"
Stop
End Sub

Image non disponible

On fait de même, mais en alimentant notre variable tableau directement.

Remplissage de l'Array via une égalité
Sélectionnez

Option Explicit
Option Base 1

Sub MetFeuilleEnArrayDirect()
Dim tboMonArray As Variant
Dim I As Long, J As Long
Dim pass1 As Single, pass2 As Single

pass1 = Timer
tboMonArray = Range("A1:IV55000")
pass2 = Timer
MsgBox pass2 - pass1 & " secondes pour " & Format(ActiveCell.CurrentRegion.Cells.Count, "#,##0") & " cellules"
Stop
End Sub

Image non disponible

La deuxième méthode va donc pratiquement douze fois plus vite...
Le gain s'il est important, le devient encore plus si on se sert de notre variable tableau pour opérer des traitement de données, ce qui est généralement le but. On va donc reprendre notre variable tableau et concaténer chaque donnée avec deux lettres. D'abord en parcourant la collection de cellules cibles.

Concaténation par parcours de collection de cellules feuilles
Sélectionnez

Sub Concatène_viaCell()
Option Explicit
Option Base 1

Dim cell As Range
Dim pass1 As Single, pass2 As Single

Application.ScreenUpdating = False
pass1 = Timer
For Each cell In Range("A1:IV55000")
   cell = cell & "zz"
Next cell
pass2 = Timer
Application.ScreenUpdating = True
MsgBox pass2 - pass1 & " secondes pour " & Format(ActiveCell.CurrentRegion.Cells.Count, "#,##0") & " cellules"
Stop
End Sub

Image non disponible

Puis en faisant la concaténation sur toutes les données de notre variable tableau, et en assignant directement le résultat à une feuille.

 
Sélectionnez

Option Explicit
Option Base 1

Sub Concatène_viaArray_et_colleFeuille2()
Dim tboMonArray As Variant
Dim I As Long, J As Long
Dim pass1 As Single, pass2 As Single

pass1 = Timer
tboMonArray = Range("A1:IV55000")
For I = 1 To 55000
   For J = 1 To 256
   'pour le besoin de l'exemple, on concatène zz à chaque valeur de cellule
   tboMonArray(I, J) = tboMonArray(I, J) & "zz"
   Next J
Next I
Worksheets(2).Select
Worksheets(2).Range("A1:IV55000") = tboMonArray
pass2 = Timer
MsgBox pass2 - pass1 & " secondes pour " & Format(ActiveCell.CurrentRegion.Cells.Count, "#,##0") & " cellules"
Stop
End Sub

Image non disponible

La deuxième méthode est donc 46 fois plus rapide avec 1 minute 41 secondes contre 1 heure 17 minutes et 41 secondes !


IX-A. A l'inverse passer par une feuille pour une saisie d'une longue Arglist (cf chapitre III) peut se révéler intéressant

Le fait de passer par une feuille Excel, même temporaire, pour la saisie est très ergonomique, facilite la clarté, la correction, le comptage et la visualisation des données.
On peut même étendre le raisonnement à l'alimentation d'une variable tableau de Strings à 1 dimension avec des items nombreux via une colonne temporaire d'une feuille car :

- L'éditeur est bien plus souple qu'en vba.
- Il y a une correction orthographique.
- On peut modifier la liste facilement.
- On peut ordonner (trier) la liste facilement.
- On peut dénombrer les items de la liste en un passage de souris.
- On corrigera une erreur de saisie 10 fois plus facilement.

Une fois la saisie et l'ordonnancement effectués, on applique le code suivant :

 
Sélectionnez

Option Explicit
Option Base 1

Sub ConstructArglist()
Dim tboPass() As Variant
Dim tboPass2(50) As Variant
Dim tboFinal()
Dim I
Dim pp
 pp = ""","""
tboPass = Range("A1:A50")
For I = 1 To 50
   tboPass2(I) = tboPass(I, 1)
Next I
strMaString = """" & Join(tboPass2(), pp) & """"
Debug.Print "tboFinal = Array(" & strMaString & ")"

'*****
' et on copie colle le résultat de la fenêtre Exécution à l'endroit voulu
' pour obtenir ceci (sans les retours et les ' ajoutés ici pour la mise en page...)
'*****
'tboFinal = Array("Paris", "Marseille", "Lyon", "Toulouse", "Nice", "Nantes", "Strasbourg", _
'"Montpellier", "Bordeaux", "Lille", "Rennes", "Htnomro", "Le Havre", "Saint-Étienne", _
'"Toulon", "Grenoble", "Angers", "Dijon", "Brest", "Le Mans", "Nîmes", "Aix-en-Provence", _
'"Clermont-Ferrand", "Tours", "Saint-Denis", "Amiens", "Limoges", "Villeurbanne", "Metz", _
'"Ormonth", "Perpignan", "Orléans", "Caen", "Mulhouse", "Boulogne-Billancourt", "Rouen", _
'"Nancy", "Argenteuil", "Montreuil", "Saint-Paul", "Saint-Denis", "Roubaix", "Avignon", _
'"Tourcoing", "Fort-de-France", "Poitiers", "Nanterre", "Créteil", "Versailles", "Pau")
End Sub 

X. Est-il utile de construire des variables tableau à plus de 2 dimensions

X-A. Oui

On peut effectivement se poser la question. Certaines fois, par manque de réflexion, on aurait tendance à imputer une dimension par catégorie de données. Mais si on y réfléchit bien, au final c'est la même analogie que pour une feuille de données sous Excel, elle est en deux dimensions et la mise en forme des données fait l'objet d'un autre traitement pour son exploitation..

Si, à chaque fois qu'on y réfléchit, il n'y a pas nécessité, mieux vaut éviter, surtout que le VBA n'est pas très équipé pour les modifications au-delà d'une seule dimension. Un tableau à 2 dimensions dont on augmente les nombre d'éléments selon le besoin suffit le plus souvent. Passer à 3 dimensions complique souvent la conceptualisation, la lecture et la maintenance. C'est d'autant plus vrai avec le nombre de colonnes et de lignes disponibles dans feuilles depuis Excel 2007.

Aller au delà de 3 dimensions est, nous l'avons vu, réservé à ceux qui manient ce genre de structures mathématiques selon leur besoins.

À notre niveau, la question de l'intérêt des variables tableau à trois dimensions se pose donc clairement. Justement, une variable tableau à trois dimensions répond le plus souvent à une approche conceptuelle, à partir du moment où on définit, tout naturellement 3 axes.

Prenons l'exemple d'une application calendaire. Certains voudront la représenter sous 3 axes ; lignes, colonnes et feuilles :

  • Jour, N° de semaine / 52, an.
  • Jour, mois, an.
  • Heure, jour, N° semaine / 52.
  • etc.

Image non disponible
Il sera plus simple pour eux d'en calquer la représentation au niveau de leur classeur plutôt que de raisonner sur une table de données en 2 dimensions.

On utilise donc souvent un tableau à 3 dimensions, comme on multiplie les feuilles d'un classeur, plus par une conceptualisation graphique de l'approche que par une analyse de gestion informatique des données.

Si on prend une approche de gestion simpliste cette fois-ci où l'on imagine une entreprise qui comprend :
- 4 services.
- 5 centres de frais.
- 12 mois gérés dans un classeur.


Une approche application VBA en gestion de données mènera à la création :
- D'une feuille de données, non accessible à l'utilisateur et d'interfaces de saisies permettant :
   ° D'alimenter les données.
   ° De les rectifier.
   ° D'en lancer un traitement d'impressions formatées, de rapports, d'extractions etc.

La Feuille comprendrait 4 colonnes : Mois; Services; Frais; Montant

Une approche, possible, utilisateur premier niveau Excel et VBA sera d'effectuer une saisie avec une feuille pour chaque mois et de construire un Userform qui servira d'interface de consolidation qui comprendra :
- Le choix du Service.
- Le choix du Centre de Frais.
- Le choix du mois ou de l'année entière.
- La Case Montant ou s'affiche le résultat désiré.

Pour ce faire, par analogie avec son classeur, il va alimenter une variable tableau sur 3 axes, donc en 3 dimensions avec des boucles imbriquées, puis construire une fonction qui selon les arguments passés renverra le montant voulu.

À partir de feuilles de saisies comme ceci :
Image non disponible
Avec un code de ce genre :

 
Sélectionnez

Option Explicit
Option Base 1

Sub ChargerFeuillesMensuellesDansTableau()
  Dim Tableau(1 To 4, 1 To 5, 1 To 12)
  Dim y As Integer, x As Integer, z As Integer
  
  For y = 1 To 4
    For x = 1 To 5
      For z = 1 To 12
        Tableau(y, x, z) = Worksheets(z).Cells(y + 1, x + 1)
      Next z
    Next x
  Next y
  
 ' MsgBox "Montant des frais Téléphones du service Commercial pour le mois de Mai :"  _ 
 ' & SommeTableau(Tableau, 3, 4, 5)
  MsgBox "Montant des frais Téléphones du service Commercial pour l'année :  " & SommeTableau(Tableau, 3, 4)
  'MsgBox "Montant de tous les frais du service commercial en Mai  :  " & SommeTableau(Tableau, 3, , 5)
  MsgBox "Montant de tous les frais du service commercial à l'année  :  " & SommeTableau(Tableau, 3)
  'MsgBox "Montant des frais Téléphones de tous les services en Mai :  " & SommeTableau(Tableau, , 4, 5)
  MsgBox "Montant des frais Téléphones de tous les services à l'année :  " & SommeTableau(Tableau, , 4)
  'MsgBox "Montant des frais de tous les services en Mai :  " & SommeTableau(Tableau, , , 5)
  MsgBox "Montant des frais de tous les services à l'année:  " & SommeTableau(Tableau)

 'Function SommeTableau (Function SommeTableau(Tableau, Optional Service As Integer, _ 
'Optional Frais As Integer,Optional Mois As Integer) As Double) est une fonction (non listée ici) 
'qui retourne les valeurs voulues selon les paramètres passés…
End Sub

Qui donnera la représentation axiale suivante :
Image non disponible
On met en avant, sur la figure suivante, l'analogie avec un Tableau Croisé Dynamique (TCD) qui remplirait largement la fonction, avec une seule feuille de données bien construite et en générant les résultats voulus.
Les 4 MsgBox sont générées par le code ci-dessus et les flèches montrent les similitudes dans l'approche qu'on peut faire avec le TCD correspondant.
Image non disponible

X-B. Non

Imaginons que Benoît Sansarray soit employé chez la Compagnie des Taxis Jaunes de la ville d' Arraytown.

La compagnie possède 300 Taxis qui se ravitaillent aux pompes de la société qui enregistrent automatiquement :
- Le numéro du taxi.
- Son kilométrage.
- Le volume de carburant.
- Le date et l'heure du ravitaillement.

Tout ceci est compilé dans un fichier CSV transmis au département de Benoît, pour qu'entre autres choses, il fournisse pour chaque taxi un état de sa consommation et de son kilométrage mensuels.

Le premier réflexe de Benoît, après son expérience de ce tutoriel, est de penser construire une variable tableau à 3 dimensions avec :
- Le numéro du taxi en lignes.
- Les kilométrages et la consommation en colonnes.
- Une feuille pour chaque mois.

À bien y réfléchir, il a pensé à juste raison que s'il se cantonnait à une variable tableau à 2 dimensions, il pourrait avoir des gains de programmation intéressants :
- Transferts de données variable tableau/feuille et inverse très rapides.
- Gestion des données de la variable tableau allégée puisqu'une dimension de moins à gérer pour les même données.

Et nous avons procédé en construisant le code suivant (le Timer vous donne la durée de traitement):

 
Sélectionnez

Option Explicit
Option Base 1

Sub TaxisSoluce()
Dim tbopass() As Variant
Dim I As Long, J As Long, K As Long
Dim rezultat() As Variant
Dim tboTaxis() As Variant
Dim blnExiste As Boolean
Dim Chrono1 As Single, Chrono2 As Single
Dim objWshCible As Worksheet

'**** Attention pour générer les 20 000 dates, il a été fait appel à une boucle / 30 mn****
'**** les dates s'échelonnent donc sur 2 ans, mais le code ne se base ici que sur les mois***
'*** notre analyse court donc du 01/01/2009 au 21/02/2010 sans distinction d'année ***
'***  Il serait aisé, dans la même logique, de créer un tableau de résultat ****
'*** qui préciserait les mois et les années en rajoutant 12 colonnes par an. ***
'*** les km et consommations n'ont aucune réalité ayant été générés au hazard...

Worksheets(2).Range("A1:AA301").Clear

Chrono1 = Timer
Worksheets(1).Select
tbopass = Range("A2:D20000")
'*** liste Taxis **** 'on la connaît, mais on fait comme si ce n'était pas le cas, car pas réel.
'*** l'ID pourrait très bien être le  d'immatriculation...
ReDim Preserve tboTaxis(1) 'sera l'Array de notre liste de Taxis
For I = 1 To UBound(tbopass, 1) ' on boucle sur les lignes
   For J = 1 To UBound(tboTaxis) ' on boucle sur le nombre de Taxis (progressif)
      If tbopass(I, 1) = tboTaxis(J) Then blnExiste = True 'si le Taxi est dans la liste, on met 
	  		'notre booleen à true
   Next J
   If blnExiste = False Then 'si le Taxi n'existe pas dans la liste
      If tboTaxis(1) <> "" Then ReDim Preserve tboTaxis(UBound(tboTaxis) + 1) 'on lui alloue une place
      tboTaxis(UBound(tboTaxis)) = tbopass(I, 1) 'et on l'ajoute
   End If
   blnExiste = False 'on remet à false :)
Next I

'For I = 1 To UBound(tboTaxis) 'Inutile, pour visualiser la liste si besoin...
'Cells(I, 10) = tboTaxis(I)
'Next I
'*** fin liste ***

'*** totaux consos et Km mensuels Taxis ***
ReDim rezultat(UBound(tboTaxis), 27) '25 = 1 col = nom Taxi et 12 * col conso et Km
For J = 1 To UBound(tboTaxis) 'pour chaque Taxis
   For K = 1 To 12 ' pour chaque mois
      For I = 1 To UBound(tbopass, 1) 'pour chaque cellule
         If tbopass(I, 1) = tboTaxis(J) Then ' si cell = Taxi
            If Month(tbopass(I, 4)) = K Then 'si cell = mois
                  rezultat(J, 1) = tboTaxis(J) 'Taxi
                  rezultat(J, K * 2) = rezultat(J, K * 2) + tbopass(I, 3) 'conso mois
                  rezultat(J, (K * 2) + 1) = rezultat(J, (K * 2) + 1) + tbopass(I, 2) 'Km mois
                  rezultat(J, 26) = rezultat(J, 26) + tbopass(I, 3) 'conso annuelle '****************
                  rezultat(J, 27) = rezultat(J, 27) + tbopass(I, 2) 'Km annuels '****************
            End If
         End If
      Next I
   Next K
Next J
Set objWshCible = Worksheets(2) ' pour la visualisation si besoin
With objWshCible
   With .Cells(1, 1)
      .Value = "ID du taxi Jaune"
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
      .WrapText = True
   End With
   For K = 1 To 12
      With .Cells(1, K * 2)
         .Value = "Conso " & MonthName(K)
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
         .WrapText = True
      End With
      With .Cells(1, (K * 2) + 1)
         .Value = "Km " & MonthName(K)
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
         .WrapText = True
      End With
      .Columns(K * 2).NumberFormat = "#,##0"" Km"""
      .Columns((K * 2) + 1).NumberFormat = "#,##0"" Litres"""
   Next K
   .Columns(26).NumberFormat = "#,##0"" Litres"""
   .Columns(27).NumberFormat = "#,##0"" Km"""
   .Cells(1, 26) = "Km Annuels"
   .Cells(1, 27) = "Conso Annuelle"
   .Cells(1, 25).Copy
   .Range("Z1:AA1").PasteSpecial Paste:=xlPasteFormats
   .Range("A2:AA" & UBound(tboTaxis) + 1) = rezultat()
End With
Application.CutCopyMode = False
Chrono2 = Timer
MsgBox Chrono2 - Chrono1 & "   secondes" ':)
End Sub

Il a été aisé à partir de notre variable tableau, d'ajouter au projet les données annuelles de chaque taxi en ajoutant les deux lignes se terminant par '****************.

On charge donc un tableau de 80 000 cellules pour créer un rapport avec calcul de plus de 8000 cellules en 10 secondes sur l'ordinateur de Benoît.

Les temps cités seraient bien sûr variables selon les machines. Ce qui importe ici est de considérer les gains de traitement sur une même machine.

XI. Analogies avec la représentation visuelle des feuilles Excel

Ce qui a précédemment été vu et la vision d'une feuille Excel nous fait bien comprendre que les variables tableau à deux dimensions seront les outils privilégiés pour manier des Transfert feuilles-variable tableau et variable tableau-feuilles efficaces et rapides.
On aura souvent intérêt à les employer, quand on en a la possibilité, pour le traitement de grandes plages de cellules sachant qu'ensuite, toutes les opérations se font directement en mémoire.
Une autre approche de conceptualisation, moins intéressante en programmation, mais pouvant être utile dans la phase conception d'un projet, non pour les variables tableau eux-mêmes, mais sur l'opportunité d'employer plusieurs feuilles (surtout avec les capacités 2007 - 2010) est de ne pas voir les feuilles "à plat" comme le permet le concept 2D de l'interface Excel, mais de les considérer comme la troisième dimension des références de vos données.

La phase de conception, primordiale, est d'ailleurs l'objet de ce sous-forum Excel sur DVP.

Image non disponible

On considère dans les vues suivantes que :
  • La dimension 1 est représentée par les lignes.
  • La dimension 2 est représentée par les colonnes.
  • La dimension 3 est représentée par les feuilles.

Image non disponible
Image non disponible
Image non disponible

Nous avons construit l'exemple avec 4 feuilles pour bien faire ressortir, à nouveau, que la limite supérieure de la troisième dimension n'a aucun rapport avec le chiffre 3, même si elle peut prendre cette valeur.

Dans les exemples ci-dessus nous avons, à dessein, complexifié les choses. Il faut bien considérer en effet que les feuilles sont indépendantes et nous n'avons donc pas adopté une interface largeur-hauteur des colonnes identique.

Il faut surtout éviter, dans le cas présent de faire une analogie avec les formules 3D d'Excel ce qui serait complètent inapproprié, car même s'il est vrai que l'ensemble des formules 3D appartiendrait à notre représentation, la réciproque n'est pas vraie. Les formules 3D ont, en effet, les dimensions un et deux forcément identiques pour l'ensemble de leurs éléments.

XII. À étudier

Nous indiquons ici des fonctions simples liées à notre sujet de façon étroite, mais dont l'étude n'est pas le but ici.

  • Split, fonction : renvoie un tableau de base zéro à une dimension contenant le nombre spécifié de sous-chaînes. Offre de très précieuses opportunités avec un peu d'astuce.
  • Join, fonction : renvoie une chaîne créée par la jonction de plusieurs sous-chaînes contenues dans un tableau.
  • Tout ce qui est alimentation de Listbox ou Combobox qui gagnent beaucoup en rapidité par l'utilisation des tableaux quand le contexte est approprié.

XIII. Liens utiles

XIV. Remerciements

Je remercie vivement :

Pierre Fauconnier, pour son aide et sa participation.

jpcheck, Philippe Jochmans, jacques_jean pour leurs relectures et conseils.

XV. Téléchargements

La Fiche Array de Benoît, fiche de rappel des conceptualisations visuelles des variables tableau à 1,2 et 3 dimensions à imprimer et garder sous le coude.

Fiche Array de Benoît

Téléchargement 1 fichier avec macro. Testé sous Excel 2003 et 2007.

Array - Taxis Jaunes.xls - avec macro

Toutes les feuilles d'un classeur qui se révèleront protégées pour éviter les accidents, le sont sans mot de passe !

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © Didier Gonard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.