Cours VBA : utilisations des tableaux

Les tableaux

Les tableaux permettent de stocker un grand nombre de valeurs contrairement aux variables qui ne peuvent stocker qu'une seule valeur à la fois.

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 vite 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 est la vitesse d'exécution (parcourir un tableau de données est infiniment plus rapide que de parcourir une plage de cellules équivalente).

Rien de tel qu'un exemple pour mieux comprendre ...

La première feuille contient ici une base de données de 5000 lignes sur 3 colonnes :

La seconde feuille contient une grille où seront comptabilisés les OUI en fonction des années et des clients :

Dans cet exemple, la procédure va parcourir la base de données en boucle et comptabilisera pour chaque année et chaque numéro de client le nombre de OUI avant de l'entrer dans la cellule correspondante.

Sans utiliser de tableau, il faudra 171.11 secondes à Excel pour exécuter la procédure :

En enregistrant tout d'abord la base de données dans un tableau et en effectuant ensuite les mêmes calculs (en parcourant le tableau au lieu des cellules), il ne faudra que 1.67 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) et uniquement les années des dates, 0.58 secondes suffisent :

Dans cet exemple, l'utilisation d'un tableau a permis d'exécuter la procédure environ 295x plus rapidement.

Déclaration d'un tableau

Voici quelques exemples de déclarations (si les 2 premières déclarations ne sont pas claires 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 tab3()

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, Integer, etc.) car 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 déclarer un tableau à une dimension :

Dim tableau(10)

Le tableau tableau(10) peut contenir 11 valeurs, car rappelez-vous que la numérotation d'un tableau commence à 0.

Chaque élément du tableau reçoit ensuite sa valeur :

Sub exemple()

    Dim tableau(10)

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

End Sub

Mais pour éviter toutes ces répétitions, l'utilisation d'une boucle For est plus que recommandée :

Sub exemple()
    
    Dim tableau(10), i As Integer
    
    'Enregistrement des valeurs dans le tableau
    For i = 0 To 10
        tableau(i) = Range("A" & i + 2)
    Next

End Sub

Notez que vous pouvez utiliser, modifier chaque élément du tableau individuellement comme une variable.

En voici un exemple avec tableau(3), la 4^e valeur du tableau :

Sub exemple()
    
    Dim tableau(10), i As Integer
    
    'Enregistrement des valeurs dans le tableau
    For i = 0 To 10
        tableau(i) = Range("A" & i + 2)
    Next
    
    'Affichage 1
    MsgBox tableau(3) 'Renvoie : 08.03.2023
    
    'Modification de l'une des valeurs 
    tableau(3) = Year(tableau(3))
    
    'Affichage 2
    MsgBox tableau(3) 'Renvoie : 2023
	
End Sub

Dans cet exemple, la fonction Year (qui renvoie l'année d'une date) a été utilisée pour modifier tableau(3).