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

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

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…

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

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.

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 :

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.

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

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.

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

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.

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

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.

        

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    voir la Vidéo

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▲

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▲

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

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

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

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

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

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

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

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

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

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

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-D. Représentation rapide lors de la saisie du code VBA▲

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.

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

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

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

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

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

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.

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

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

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

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▲

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-A. Oui▲

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

X-B. Non▲

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 N° 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

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.

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

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.

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

https://silkyroad.developpez.com/vba/tableaux/ par Silkyroad.

Je remercie vivement :

Pierre Fauconnier, pour son aide et sa participation.

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

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 !