Cours VBA : utilisations des tableaux

Les tableaux sont des "variables" qui permettent de stocker une multitude de valeurs. Nous avons effleuré le sujet à la leçon sur les variables, nous allons maintenant l'approfondir ...

Intérêt des tableaux

Imaginez que dans une procédure vous ayez besoin de stocker 500 valeurs. S'il fallait créer 500 variables pour stocker toutes ces valeurs, cela deviendrait très compliqué, tandis qu'avec un tableau, le stockage et l'utilisation de ces valeurs seront grandement simplifiés.
Le second intérêt des tableaux est leur "rapidité". Parcourir un tableau de données demande beaucoup moins de temps que parcourir un "tableau" (composé de cellules) sur une feuille Excel ...
Rien de tel qu'un exemple pour mieux comprendre ...
Sur la première feuille ("BD") : une base de données de 5000 lignes sur 3 colonnes :
Sur la seconde feuille : un "tableau" récapitulatif où seront comptabilisés les "OUI" en fonction des années et des clients :
Dans le cas présent, la procédure va parcourir la base de données en boucle et comptabilisera pour chaque année et chaque n° de client le nombre de "OUI" avant de l'entrer dans la cellule correspondante.
Sans utiliser de tableau, il faudra 131.44 secondes à Excel pour exécuter la procédure :
En enregistrant d'abord la base de données (de la feuille "BD") dans un tableau et en effectuant ensuite les mêmes calculs (en utilisant le tableau à la place de la base de données de la feuille "BD"), il ne faudra que 1.74 secondes pour exécuter la procédure :
Et si l'on décide d'optimiser la procédure en n'enregistrant que les données avec les "OUI" dans le tableau (ce qui représente environ le 3/4 des données), 1.02 secondes suffisent :

Dans cet exemple, l'utilisation d'un tableau a permis d'exécuter la procédure environ 128x plus rapidement et cette différence peut encore augmenter sensiblement lorsque l'on travaille avec plusieurs bases de données en même temps (voir un second exemple).

Vous l'avez compris, l'utilisation de tableaux peut vraiment faire la différence.

Cet exemple sera détaillé en fin de leçon ...

Déclaration d'un tableau

Voici quelques exemples de déclarations (si les 2 premiers ne sont pas clairs pour vous, relisez ceci) :

'Exemple de déclaration d'un tableau à 1 dimension
Dim tab1(4)

'Exemple de déclaration d'un tableau à 2 dimensions
Dim tab2(6, 1)

'Exemple de déclaration d'un tableau dynamique
Dim tab()

Si vous ne pouvez pas entrer de valeurs fixes (parce que cela dépend de la taille de la base de données par exemple), laissez les parenthèses vides.
Vous n'avez pas besoin de déclarer un type (string, long, etc.), dans bien des cas cela ralentirait votre procédure ...

Enregistrer des données dans un tableau

Commençons par enregistrer ces quelques données dans un tableau :
Nous voulons enregistrer ici 11 x 1 valeurs, il faudra donc créer un tableau à une dimension :

'Déclaration
Dim tab_exemple(10)

Rappelez-vous que la numérotation d'un tableau commence à 0 (c'est une norme en programmation, autant prendre de bonnes habitudes dès le début même s'il est possible de modifier cela en VBA).
Chaque élément du tableau reçoit ensuite sa valeur :

'Enregistrement des valeurs dans le tableau
tab_exemple(0) = Range("A2")
tab_exemple(1) = Range("A3")
tab_exemple(2) = Range("A4")
tab_exemple(3) = Range("A5")
tab_exemple(4) = Range("A6")
tab_exemple(5) = Range("A7")
tab_exemple(6) = Range("A8")
tab_exemple(7) = Range("A9")
tab_exemple(8) = Range("A10")
tab_exemple(9) = Range("A11")
tab_exemple(10) = Range("A12")

Vous pouvez utiliser, modifier chaque élément du tableau comme une variable.
En voici un exemple avec tab_exemple(8) :

Sub enreg_tab()
    'Déclaration
    Dim tab_exemple(10)
   
    'Enregistrement des valeurs dans le tableau
    tab_exemple(0) = Range("A2")
    tab_exemple(1) = Range("A3")
    tab_exemple(2) = Range("A4")
    tab_exemple(3) = Range("A5")
    tab_exemple(4) = Range("A6")
    tab_exemple(5) = Range("A7")
    tab_exemple(6) = Range("A8")
    tab_exemple(7) = Range("A9")
    tab_exemple(8) = Range("A10")
    tab_exemple(9) = Range("A11")
    tab_exemple(10) = Range("A12")
   
    'Test 1
    MsgBox tab_exemple(8) '=> renvoie : 04.02.2016
 
    'Modification de l'une des valeurs
    tab_exemple(8) = Year(tab_exemple(8))
   
    'Test 2
    MsgBox tab_exemple(8) '=> renvoie : 2016
End Sub

Pour enregistrer ce même tableau plus rapidement, une boucle For est toute indiquée :

'Déclaration
Dim tab_exemple(10)

'Enregistrement des valeurs dans le tableau
For i = 0 To 10
    tab_exemple(i) = Range("A" & i + 2)
Next

 

 

Le tableau à 2 dimensions

Pour enregistrer plusieurs colonnes de données, une dimension supplémentaire est nécessaire. En voici un exemple :
Enregistrement des données dans un tableau à 2 dimensions :

'Déclaration
Dim tab_exemple(10, 2) 'Tableau de 11 x 3 "cases"

'Enregistrement des valeurs dans le tableau
For i = 0 To 10
    tab_exemple(i, 0) = Range("A" & i + 2)
    tab_exemple(i, 1) = Range("B" & i + 2)
    tab_exemple(i, 2) = Range("C" & i + 2)
Next

Et quelques exemples de valeurs :

MsgBox tab_exemple(0, 0) '=> renvoie : 11.03.2026
MsgBox tab_exemple(0, 1) '=> renvoie : 24
MsgBox tab_exemple(9, 2) '=> renvoie : NON
MsgBox tab_exemple(10, 2) '=> renvoie : OUI


Le tableau dynamique

Imaginons que cette même base de données soit régulièrement mise à jour et que l'on ne puisse donc pas entrer de valeurs fixes à la déclaration ...
Pour connaître le n° de ligne de la dernière cellule d'un bloc de cellules non vides, autrement dit, la dernière ligne de notre base de données, utilisez cette formule :

derniere_ligne = Range("A1").End(xlDown).Row

Si vous entrez une variable lors de la déclaration, Excel ne l'acceptera pas.
Déclarez un tableau dynamique (parenthèses vides), puis définissez ses dimensions avec Redim :

Dim tab_exemple()
ReDim tab_exemple(derniere_ligne - 2, 2)

De cette manière vous enregistrerez automatiquement toutes les lignes de la base de données dans le tableau :

Sub enreg_tab()
    derniere_ligne = Range("A1").End(xlDown).Row 'Dernière ligne de la base de données

    Dim tab_exemple()
    ReDim tab_exemple(derniere_ligne - 2, 2)
   
    'Enregistrement des valeurs dans le tableau
    For i = 0 To derniere_ligne - 2
        tab_exemple(i, 0) = Range("A" & i + 2)
        tab_exemple(i, 1) = Range("B" & i + 2)
        tab_exemple(i, 2) = Range("C" & i + 2)
    Next
End Sub

Ubound

Dans l'exemple ci-dessus, le dernier n° de notre tableau était derniere_ligne - 2 :

For i = 0 To derniere_ligne - 2

Une autre solution pour connaître ce n° consiste à utiliser la fonction Ubound :

For i = 0 To UBound(tab_exemple)

Cette fonction renvoie le plus grand n° pour une dimension choisie (par défaut la première).
Quelques exemples pour mieux comprendre :

Sub enreg_tab()
    Dim tab_exemple(10, 2)
   
    MsgBox UBound(tab_exemple) '=> renvoie : 10
    MsgBox UBound(tab_exemple, 1) '=> renvoie : 10
    MsgBox UBound(tab_exemple, 2) '=> renvoie : 2
End Sub

Enregistrer une plage de cellules

Il est possible d'enregistrer une plage de cellules dans un tableau sans passer par une boucle.

'Déclaration
Dim tab_exemple(10, 2) 'Tableau de 11 x 3 "cases"

'Enregistrement des valeurs dans le tableau
For i = 0 To 10
    tab_exemple(i, 0) = Range("A" & i + 2)
    tab_exemple(i, 1) = Range("B" & i + 2)
    tab_exemple(i, 2) = Range("C" & i + 2)
Next

Le code ci-dessus peut être remplacé par :

'Déclaration
Dim tab_exemple()

'Enregistrement des valeurs dans le tableau
tab_exemple = Range("A2:C12").Value

Même si au premier abord cette seconde méthode semble séduisante, elle peut dans bien des cas vous faire perdre plus de temps que la première méthode ...
En enregistrant vos données dans le tableau de cette manière, le premier n° n'est pas 0 mais 1, cela peut être source de confusion ... De plus, si au cours du développement vous choisissez de n'enregistrer dans le tableau que les données répondant à certains critères (ou effectuer toute autre opération), vous aurez besoin de tout réécrire avec une boucle ...
Cette seconde méthode reste tout de même intéressante lorsque vous avez besoin d'enregistrer l'ensemble du contenu d'une grande base de données, car plus rapide qu'avec une boucle (gain d'environ 0.2 secondes pour 15'000 entrées).

Array

Vous aurez peut-être parfois besoin de créer un tableau contenant des données "fixes".
Une solution consiste à l'écrire ligne par ligne :

Dim fr(5)

fr(0) = "SI"
fr(1) = "RECHERCHEV"
fr(2) = "SOMME"
fr(3) = "NB"
fr(4) = "ESTNUM"
fr(5) = "STXT"

Heureusement, vous pouvez vous simplifier la tâche en utilisant Array :

fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")

Voici un exemple d'utilisation de la fonction Replace (utile pour mieux comprendre l'exemple suivant) :

Sub remplacement()
    Dim chaine_a_traiter As String

    'Une chaîne pour cet exemple
    chaine_a_traiter = "Hello World !"
   
    'Remplacement de "World" par "toi" dans la chaîne de caractères
    chaine_a_traiter = Replace(chaine_a_traiter, "World", "toi")

    'La chaîne après remplacement
    MsgBox chaine_a_traiter '=> renvoie "Hello toi !"
End Sub

Maintenant si l'on veut remplacer une série de valeurs par une autre série, l'utilisation de tableaux (Array) est toute indiquée :

Sub traduction() 'Exemple simplifié de traduction FR-EN de formules
    Dim chaine_a_traiter As String

    'Une chaîne pour cet exemple
    chaine_a_traiter = "Formule à traduire : SOMME(SI(ESTNUM(A1:E1);A1:E1;0))"
   
    'Les 2 séries de valeurs
    fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
    en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
   
    'Remplacement de "SI" par "IF", de "RECHERCHEV" par "VLOOKUP", etc.
    For i = 0 To UBound(fr)
        chaine_a_traiter = Replace(chaine_a_traiter, fr(i), en(i))
    Next

    'La chaîne après les remplacements
    MsgBox chaine_a_traiter '=> renvoie "Formule à traduire : SUM(IF(ISNUMBER(A1:E1);A1:E1;0))"
End Sub

Split

La fonction Split permet de convertir une chaîne de caractères en un tableau.
Pour convertir cette chaîne de caractères en tableau :

chaine = "SI/RECHERCHEV/SOMME/NB/ESTNUM/STXT"

Utilisez la fonction Split et définissez le séparateur :

fr = Split(chaine, "/")

Le tableau fr renverra les valeurs suivantes :

MsgBox fr(0) '=> renvoie : SI
MsgBox fr(1) '=> renvoie : RECHERCHEV
MsgBox fr(2) '=> renvoie : SOMME
MsgBox fr(3) '=> renvoie : NB
MsgBox fr(4) '=> renvoie : ESTNUM
MsgBox fr(5) '=> renvoie : STXT


Les 3 tableaux suivants renvoient également les mêmes valeurs :

fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
fr = Split("SI,RECHERCHEV,SOMME,NB,ESTNUM,STXT", ",")
fr = Split("SI RECHERCHEV SOMME NB ESTNUM STXT", " ")

L'exemple suivant renvoie la 3^e valeur de la chaîne de caractères :

MsgBox Split("SI,RECHERCHEV,SOMME,NB,ESTNUM,STXT", ",")(2) '=> renvoie : SOMME


La fonction à l'opposé de Split est Join.
Cette fonction permet d'assembler les valeurs d'un tableau en une chaîne de caractères.

MsgBox Join(Array(1, 2, 3, 4, 5), "") '=> renvoie : 12345