VBA-Leçon 13.1. Utiliser des tableaux (Arrays)

Les tableaux (Arrays) sont très souvent utilisés en programmation, y compris dans Excel VBA.

Un tableau est essentiellement une variable unique avec de nombreuses cellules pour stocker des valeurs, alors qu'une variable typique n'a qu'une seule cellule de stockage dans laquelle elle ne peut stocker qu'une seule valeur.

Un tableau est accessible dans son ensemble si vous souhaitez faire référence à toutes les valeurs qu'il contient, ou vous pouvez faire référence à ses éléments individuels.

Vous pouvez déclarer un tableau pour qu'il fonctionne avec un ensemble de valeurs du même type de données. Nous avons déjà abordé cela dans VBA-Lesson 6.2. Datatypes Mais approfondissons encore davantage maintenant...

Pourquoi les tableaux sont-ils utilisés ?

Imaginez que vous essayez d'écrire une procédure dans laquelle vous devez stocker jusqu'à 500 valeurs. Si vous devez créer 500 variables distinctes, ce sera très difficile. Au milieu du tableau, stocker et travailler avec ces valeurs sera beaucoup plus facile.

La deuxième raison d’utiliser les tableaux est leur vitesse. La lecture des données à partir de tableaux prend beaucoup moins de temps qu'à partir de tableaux (constitués de cellules) dans la feuille de calcul Excel.

Un exemple d'utilisation de tableaux

Voici donc un exemple qui montrera l'avantage évident d'utiliser des tableaux VBA dans Excel.

La première feuille de calcul ("DS") contient un ensemble de données : 5 000 lignes sur 3 colonnes :

Sur la deuxième feuille, vous trouverez un tableau récapitulatif qui prend en compte toutes les réponses "Oui" ("YES") par année et client :

Dans ce cas, la procédure utilisera une boucle pour traiter l'ensemble de données et enregistrera le nombre de réponses « Oui » pour chaque année et chaque numéro de client, puis saisira ces données dans les cellules appropriées.

Sans utiliser de tableaux, il faudrait Excel 131,44 secondes pour exécuter cette procédure :

Mais pour enregistrer d'abord les données dans un tableau (à partir de la feuille de calcul "DS"), puis effectuer les mêmes calculs (en utilisant les tableaux au lieu de l'ensemble de données de la feuille de calcul "DS"), cela ne prendra que 1,74 seconde :

Si nous décidons d'optimiser notre procédure en stockant uniquement les données contenant les réponses "YES" dans le tableau (ce qui représente environ les 3/4 des données), cela ne prendrait que 1,02 seconde :

C'est un bon exemple de la façon dont l'utilisation de tableaux vous permet d'effectuer une procédure 128 fois plus rapidement. Le résultat de notre optimisation serait encore meilleur si nous travaillions avec plusieurs ensembles de données en même temps.

Nous reviendrons sur les détails de notre exemple à la fin de la leçon.

Déclaration de tableaux

Ci-dessous quelques exemples de déclaration de tableaux dans VBA (si les deux premiers exemples ne vous sont pas clairs, lisez-le) :

 'Un exemple de déclaration d'un tableau unidimensionnel
 Dim array1(4)

 'Un exemple de déclaration d'un tableau à deux dimensions
 Dim array2(6, 1)

 'Un exemple de déclaration d'un tableau dynamique
 Dim array3()

Si vous ne pouvez pas saisir de valeurs fixes lors de la déclaration des tableaux (car elles dépendent, par exemple, de la taille de l'ensemble de données), laissez les parenthèses vides.

Vous n'êtes pas obligé de déclarer un type de données (string, long, etc.), même si dans de nombreux cas cela ralentira l'exécution de votre procédure.

Stockage des données dans un tableau

Essayons de stocker quelques données dans un tableau :

Nous voulons stocker des valeurs 11x1 dans ce cas, nous devons donc créer un tableau unidimensionnel :

 'Déclaration
 Dim array_example(10)

N'oubliez pas que la numérotation des éléments du tableau commence par 0 (c'est la norme en programmation, cela vaut donc la peine de prendre cette habitude tout de suite, même si, en fait, vous pouvez changer cette approche dans VBA).

Chaque élément du tableau recevra désormais sa valeur :

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

Vous pouvez opérer ou modifier chaque élément du tableau comme s'il s'agissait d'une variable normale.

Voici ensuite un exemple où nous utilisons array_example(8) :

Sub example()
     'Déclaration
     Dim array_example(10)
     
     'Sauvegarder les valeurs dans un tableau
     array_example(0)  = Range("A2")
     array_example(1)  = Range("A3")
     array_example(2)  = Range("A4")
     array_example(3)  = Range("A5")
     array_example(4)  = Range("A6")
     array_example(5)  = Range("A7")
     array_example(6)  = Range("A8")
     array_example(7)  = Range("A9")
     array_example(8)  = Range("A10")
     array_example(9)  = Range("A11")
     array_example(10) = Range("A12")
     
     'Essai 1
     MsgBox array_example(8) '=> retours : 04/02/2016
    
     'Changer une des valeurs
     array_example(8) = Year(array_example(8))
     
     'Essai 2
     MsgBox array_example(8) '=> retours : 2016
End Sub

Une boucle For serait une meilleure option pour sauvegarder le tableau plus rapidement :

 'Déclaration
 Dim array_example(10)

 'Sauvegarder les valeurs dans un tableau
 For i = 0 To 10
    array_example(i) = Range("A" & i + 2)
 Next