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 :

bd - tableaux vba

Sur la seconde feuille : un "tableau" récapitulatif où seront comptabilisés les "OUI" en fonction des années et des clients :

res - tableaux vba

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 :

sans - tableaux vba

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 :

avec - tableaux vba

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 :

avec opti - tableaux vba

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

Voici un autre exemple avec une procédure utilisant 2 bases de données, l'équivalent ici d'une "grosse boucle" exécutée 78'240'000 fois.

Sans utiliser de tableaux, il faudra plus de 49 minutes à Excel pour exécuter cette lourde procédure :

sans2 - tableaux vba

En utilisant simplement un tableau par base de données (et sans autres modifications), à peine plus de 10 secondes suffisent pour exécuter la même procédure :

avec2 - tableaux vba

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 :

enreg-1-dim - tableaux vba

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
Télécharger ce cours au format PDF