Les tableaux de VBA [#27 FORMATION EXCEL VBA COMPLETE]
Dans ce nouveau chapitre de la formation sur l’apprentissage du VBA nous allons découvrir un nouveau type de variable bien particulier. Il s’agit des tableaux de variables, lesquels permettent de manipuler des ensembles de variables d’un même type. Lisez bien ce chapitre jusqu’au bout, car nous découvrir à quel point ces variables sont puissantes et peuvent permettre de rendre la réalisation d’opérations complexes bien plus simples à mettre en place.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
Partie 1/2 : Utilisation des tableaux VBA
Partie 2/2 : Aller plus loin avec les fonctions
Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !
1. Présentation
Lorsque nous manipulons plusieurs variables dont le type est identique, il est possible de les rassembler dans un seule et même variable laquelle sera alors un tableau.
Tout comme les tableaux d’Excel, cela permet donc d’ordonner les informations en stockant un ensemble de valeurs dans une seule et même variable.
Par exemple, imaginons que nous souhaitions enregistrer les heures d’arrivées d’une personne sur son poste de travail sur cinq journées consécutives (du lundi au vendredi).
La solution classique consisterait à utiliser cinq variables, une pour chaque jour de la semaine :
Sub testTableaux()
Dim lundi As String
Dim mardi As String
Dim mercredi As String
Dim jeudi As String
Dim vendredi As String
lundi = "7:12"
mardi = "7:24"
mercredi = "6:57"
jeudi = "9:16"
vendredi = "6:42"
End Sub
En revanche, utiliser un tableau permettrai d’utiliser une seule variable, que nous gérerons alors en utilisant un numéro d’index :
Dim jour(4) As String
jour [0] = "7:12"
jour [1] = "7:24"
jour [2] = "6:57"
jour [3] = "9:16"
jour [4] = "6:42"
Les avantages d’utiliser un tableau plutôt qu’une multiplicité de variables sont nombreux :
- Dans l’exemple ci-dessus, nous n’avons utilisé que cinq variables, donc les déclarer une par une ne pose pas forcément de problème. Mais imaginez lorsque nous allons vouloir stocker les informations non pas sur une seule semaine, mais sur une année complète (365 jours), sur 10 ans, 100 ans ? Il est évidemment inconcevable dans ce cas-là de manipuler et surtout déclarer autant de variables. Avec un tableau, une seule déclaration suffit.
- Comme nous le verrons un peu plus tard, il va être très simple de passer en revue l’ensemble des éléments d’un tableau en utilisant son index (grâce à une boucle For/ For Each)
- Le code est lui aussi bien plus clair car toutes les variables identiques sont rassemblées dans un unique endroit
- …
2. Types de tableaux et utilisation
2.1. Le tableau unidimensionnel
Le cas le plus simple d’utilisation des tableaux, en VBA mais également dans tous les langages de développement, est le tableau avec une unique dimension.
Comme son nom l’indique, celui-ci permet de stocker autant de données que nous le souhaitons, sur une seule dimension
Pour déclarer un tableau unidimensionnel, il suffit de saisir le nombre d’éléments qui le compose entre parenthèse.
Attention toutefois, par défaut l’index de tableau commencer par l’index 0, le nombre d’éléments à déclarer est donc égal au nombre réel moins un élément.
Dans cet exemple, nous allons déclarer un tableau capable de traiter 10 éléments
Dim monTableau(9) as Integer
Le type que nous déclarons lors de l’initialisation du tableau correspond au type que nous allons attribuer aux variables qui le compose.
Ici nous le déclarons en tant que Integer, ce qui signifie que celui-ci ne pourra manipuler que des variables du type nombres entiers.
Ensuite, nous pouvons affecter une valeur à chacun des éléments en utilisant le numéro d’index entre parenthèse :
monTableau(0) = 5
monTableau(1) = 5
monTableau(2) = 5
monTableau(3) = 5
monTableau(4) = 5
monTableau(5) = 5
monTableau(6) = 5
monTableau(7) = 5
monTableau(8) = 5
monTableau(9) = 5
Ici, tous les éléments ont pour valeur le chiffre 5.
Evidemment, utiliser ainsi 10 fois la même ligne de code pour rentrer une valeur identique n’est pas la méthode optimale !
Comme vous pouvez vous en douter si vous avez bien suivi les chapitres précédents de la formation, nous pouvons bien sûr utiliser une boucle For.
Dim iAs Integer
For i= 0 To 9
monTableau(i) = 5
Next
Nous commençons par déclarer une variable iqui va nous permettre de passer en revue tous les élements du tableau.
Celle prend pour valeur initiale zéro, et pour valeur maximale le numéro du dernier index (soit 9).
Nous pouvons maintenant modifier très légèrement la procédure pour afficher un nombre aléatoire au lieu du chiffre 5 :
Dim iAs Integer
For i= 0 To 9
monTableau(i) = CInt(Rnd * 10)
Next
Ensuite, pour récupérer la valeur d’un élément, il suffit de très simplement l’appeler par son numéro d’index :
2.2. Le tableau multidimensionnel
Un tableau à plusieurs dimensions est un tableau qui possède deux ou plusieurs index pour appeler un élément.
Nous pouvons également parler de « tableau matriciel ».
Dans ce cas, pour le déclarer nous spécifions entre parenthèses les tailles de chacune des dimensions :
Sub testTableau2Dimensions()
Dim monTableau(9, 9) As Integer
End Sub
Ensuite pour manipuler les éléments de ce tableau, nous spécifions les index correspondants, que nous l’avions fait pour le tableau à une dimension :
monTableau(3, 3) = 5
Nous pouvons également remplir celui-ci de manière dynamique, en l’encapsulant directement dans deux boucles For.
Dans l’exemple suivant, nous allons stocker les tables de multiplications dans un tableau 10*10 (allant de 0 à 9) :
Dim iAs Integer, j As Integer
For i= 0 To 9
For j = 0 To 9
monTableau(i, j) = i* j
Next
Next
Puis pour récupérer le résultat correspondant, nous appelons l’élément ayant pour index les chiffres que nous multiplions :
Debug.Print "3x8=", monTableau(3, 8)
2.3. Le tableau dynamique
Dans les exemples que nous venons de voir, nous avons pu les déclarer en spécifiant la taille de chacun des tableaux, car connaissions celles-ci !
Parfois il arrive que nous ne puissions par la connaître et dans ce cas nous allons devoir déclarer un tableau dynamique, c’est-à-dire que la taille de celui-ci va s’adapter automatiquement en fonction des éléments à stocker.
Par exemple, imaginons que nous souhaitions enregistrer les valeurs de toutes les cellules contenues dans une feuille de calcul, dès lors que cette cellule n’est pas vide.
Evidemment, ce nombre de cellule va varier en fonction des opérations que nous allons constater sur la feuille !
Pour déclarer un tableau dynamique, nous ne spécifions tout simplement rien entre les parenthèses.
Dim monTableau() As Variant
Par contre, lorsque nous utilisons des tableaux dynamiques en suivant cette méthode, Excel n’attribue pas d’espace mémoire tant que nous ne lui avons demandé de le faire grâce à l’instruction Redim.
Voici comment stocker le contenu des cellules dans les variables :
Dim cAs Range, iAs Integer ' 1
i= 0 ' 2
For Each cIn Sheets("27-Tableaux").UsedRange ' 3
If c<> "" Then ' 4
monTableau(i) = c ' 5
i= i+ 1 ' 6
End If
Next
Voici le détail de ce code :
- 1 : Nous commençons par déclarer deux variables :
- La variable cva nous permettre de passer en revue toutes les cellules utilisées dans la feuille de calcul « 27-Tableaux »,
- Ensuite la variable iva nous permettre de compter le nombre de cellules affectées afin de pouvoir l’utiliser en tant qu’Index
- 2 : Ensuite, nous affectons la valeur de 1 à cette variable
- 3 : Puis nous passons en revue toutes les cellules contenues dans la plage des cellules utilisées de la feuille de calcul (« 27-Tableaux »). Pour cela, nous utilisons une instruction For Each que nous avons déjà abordé dans le détail dans un chapitre précédent
- 4 : Nous regardons si la cellule contient une valeur (c’est-à-dire que sa valeur n’est pas vide)
- 5 : lorsque c’est le cas, alors nous récupérons cette valeur dans le tableau monTableau, à l’index qui correspond à la valeur de i
- 6 : Et pour finir, avant de relancer la boucle, nous incrémentons la valeur de la variable id’une unité
A ce moment-là, si nous lançons la procédure pour effectuer un test, nous alors heurter à une erreur d’exécution :
En effet, étant donné que nous n’avons défini aucune taille pour le tableau, celui-ci n’est pas en mesure de recevoir la moindre information.
Il convient alors d’utiliser l’instruction ReDim qui permet de redéfinir la variable avec une taille donnée.
Cette taille va alors correspondre à la valeur de l’index en cours (c’est-à-dire la valeur de i) :
If c<> "" Then
ReDim monTableau(i)
monTableau(i) = c
i= i+ 1
End If
A partir de là, plus d’erreur d’exécution.
En revanche, nous ne sommes pas sortis d’affaires pour autant.
En effet, l’utilisation de ReDim a pour effet de réinitialiser les valeurs stockées dans le tableau !
Pour s’en rendre compte, nous pouvons passer en revue chacune des valeurs pour les afficher dans la fenêtre d’exécution :
Dim vAs Variant
For Each vIn monTableau
Debug.Print v
Next
Et voici le résultat :
Comme vous pouvez le constater la fenêtre d’exécution retourne une série de lignes vide, seule la dernière ligne affiche un résultat.
Il s’agit en effet de la valeur contenue dans la dernière cellule utilisée du classeur.
Pour stopper la réinitialisation des variables lors de chaque utilisation de ReDim, il faut combiner l’instruction avec le mot-clé Preserve :
ReDim Preserve monTableau(i)
Ce qui permet de bien récupérer l’ensemble des valeurs contenues dans les cellules de la feuille de calculs !
3. Fonction utiles
Maintenant que nous avons fait le tour des fonctionnalités offertes par les tableaux VBA dans la partie précédente, je vous propose que nous découvrions dans cette nouvelle partie les différentes fonctions qui vont permettre de manipuler les tableaux de manières encore plus poussée !
3.1. La fonction Array()
La première fonction que nous allons découvrir ici est la fonction ARRAY()
Celle-ci est très pratique car elle permet de remplir directement un tableau avec des valeurs que nous saisissons simplement en tant qu’argument.
Pour illustrer l’utilisation de cette fonction, revenons sur le tout premier exemple que nous avions découvert en ouverture de ce chapitre et dans lequel nous saisissons les heures d’arrivées d’une personne durant les cinq jours qui composent sa journée de travail.
Pour alimenter le tableau heureArrivee, nous avions alors utilisé cinq lignes d’affectation :
Dim jour(4) As String
jour [0] = "7:12"
jour [1] = "7:24"
jour [2] = "6:57"
jour [3] = "9:16"
jour [4] = "6:42"
Grâce à la fonction VBA Array(), nous pourrions réaliser cette opération sur une seule et même ligne :
Dim heureArrivee As Variant
heureArrivee = Array("7:17", "7:24", "6:54", "7:02", "6:32")
Attention, comme le montre l’exemple ci-dessus, nous devons utiliser la fonction Array() sur une variable de type Variant classique (c’est-à-dire non déclarée comme un tableau avec l’utilisation des parenthèses).
C’est en effet Excel qui au moment de l’utilisation de la fonction va insérer le tableau dans la variable heureArrivee.
3.2. Les fonctions LBound() et UBound()
Les fonctions LBound() et UBound() permettent de récupérer les limites inférieures et supérieures d’un tableau.
Ces limites correspondent respectivement au plus petit et au plus grand index qui composent le tableau.
Si nous reprenons l’exemple précédent, nous voyons que l’indice le plus petit est le zéro, lequel nous retourne l’horaire d’arrivé du lundi, soit « 7 :12 ».
À l’opposé, le plus grand des index, nous retourne l’horaire de vendredi, soit « 6:42 », c’est-à-dire l’index 4.
Le fait de connaître ces index permet notamment de pouvoir passer en revue les différents éléments qui composent un tableau.
En effet, pour récupérer dans une MsgBox les différents horaires que nous venons d’insérer avec l’instruction Array() juste avant, nous procéderions de la manière suivante :
Dim i As Integer
For i = LBound(heureArrivee) To UBound(heureArrivee)
MsgBox heureArrivee(i)
Next
Ici, nous commençons par déclarer une nouvelle variable que nous nommons i qui permet de passer en revue tous les éléments du tableau.
Nous donnons ensuite à cette variable la valeur de la limite inférieure du tableau (soit 0).
Puis nous incrémentons une boucle For jusqu’à ce que cette variable i prenne pour valeur la limite maximale du tableau (soit 4).
Dès lors, nous pouvons nous interroger de l’utilité de connaître la valeur de la limite minimale, étant donné que nous avons vu dans la partie précédente que celle-ci était égale à 0.
La raison est très simple, ce que nous avons vu plus tôt correspond en fait au comportement par défaut de VBA !
Il est en effet possible de modifier la valeur de la limite inférieure en appelant l’option VBA suivante (à insérer en tout début de code, juste en dessous de l’éventuel Option Explicit) :
Option Explicit
Option Base 1
À ce moment-là, le tableau va alors commencer directement avec l’index 1.
Et bien entendu l’index maximale sera également décalé :
3.3. Les fonctions Split() et Join()
Maintenant, découvrons les fonctions Split() et Join() qui permettent de transformer une chaîne de caractère en un tableau, ou inversement un tableau en une chaîne de caractères.
Pour commencer, voyons le fonctionnement de la fonction Join().
Celle-ci attend deux arguments :
- sourceArray : il s’agit du tableau que nous souhaitons joindre pour créer une chaîne de caractères,
- delimiter : il s’agit d’un élément sous la forme d’une chaîne de caractères que nous allons utiliser pour joindre les différents éléments du tableau
p>Pour l’exemple, nous allons une fois encore repartir du même exemple, que nous allons vouloir transformer en une chaîne de caractères, et dont les différents éléments seront assemblés à l’aide d’un point-virgule :
Dim chaine As String
chaine = Join(heureArrivee, ";")
MsgBox chaine
Ainsi, les différentes heures d’arrivées sont accessibles via un seul MsgBox :
Et nous retrouvons bien chaque élément accolé grâce au point-virgule.
Maintenant, la seconde fonction que nous souhaitons découvrir dans cette partie et la fonction Split(), qui permet à ‘inverse de la fonction Join() que nous venons d’étudier à l’instant de séparer une chaîne de caractères en plusieurs éléments constitutifs d’un tableau en utilisant un symbole de séparation.
Vous l’aurez évidemment compris, nous allons ici reconstituer le tableau d’origine en séparant la chaîne de caractères que nous venons de créer à l’aide des points-virgules.
Dim tableau As Variant
tableau = Split(chaine, ";")
MsgBox tableau(0)
MsgBox tableau(1)
Nous retrouvons alors chacun des éléments renseignés initialement avec la fonction Array :
Il est également intéressant de savoir que cette fonction peut être utilisée directement dans un boucle For Each, afin de passer en revue tous les éléments d’une chaîne de caractères :
Dim horaire As Variant
For Each horaire In Split(chaine, ";")
MsgBox horaire
Next
Cette technique permet de ne pas avoir à utiliser les fonction LBound() et UBound() que nous avons vu en détail juste avant, mais en revanche, nous perdons le numéro d’index qui était calculé automatiquement lors de chaque incrémentation de boucle dans la méthode précédente.
3.4. La fonction IsArray()
Ensuite, la fonction IsArray() permet d’identifier si une variable est en réalité un tableau ou non.
Le résultat retourné sera donc une valeur booléenne égale à True lorsque la variable étudiée est un tableau, et égale à False dans le cas contraire.
If IsArray(heureArrivee) Then
MsgBox "heureArrivee est un tableau"
Else
MsgBox "heureArrivee n'est pas un tableau"
End If
If IsArray(chaine) Then
MsgBox "chaine est un tableau"
Else
MsgBox "chaine n'est pas un tableau"
End If