Les tableaux de VBA [#27 FORMATION EXCEL VBA COMPLETE]

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

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

Excel formation - VBA27 - Les tableaux de VBA - 01

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 :

Excel formation - VBA27 - Les tableaux de VBA - 02

 

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

Excel formation - VBA27 - Les tableaux de VBA - 03

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)

 

Excel formation - VBA27 - Les tableaux de VBA - 04

   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 :

Excel formation - VBA27 - Les tableaux de VBA - 05

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 :

Excel formation - VBA27 - Les tableaux de VBA - 06

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.

Excel formation - VBA27 - Les tableaux de VBA - 07

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 !

Excel formation - VBA27 - Les tableaux de VBA - 08

 

 

 

 

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.

Excel formation - VBA27 - Les tableaux de VBA - 09

Et bien entendu l’index maximale sera également décalé :

Excel formation - VBA27 - Les tableaux de VBA - 10

 

 

   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 :

Excel formation - VBA27 - Les tableaux de VBA - 11

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 :

Excel formation - VBA27 - Les tableaux de VBA - 12

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

 

Excel formation - VBA27 - Les tableaux de VBA - 13

Excel formation - VBA27 - Les tableaux de VBA - 14

 



Articles qui pourraient vous intéresser

Comment détecter, supprimer ou bloquer les doublons sur Excel ?
La fonctionnalité méconnue pour des prévisions en un clin d’œil sur Excel
Comment changer la couleur d'un graphique en fonction de la valeur dans Excel ?
Comment imprimer les titres de colonnes sur toutes les pages dans Excel ?
TOP3 des (vraies) méthodes pour retirer les chiffres derrière la virgule sur Excel
Comment savoir si une cellule est utilisée dans une autre formule sur Excel ?
Comment faire pour attribuer des couleurs différentes à chaque point dans un graphique Excel ?
Comment ajouter une moyenne mobile sur un graphique Excel ?
Comment rendre vos graphiques Excel plus esthétiques avec des dégradés multicolores ?
Comment créer des listes personnalisées sur Excel en un rien de temps ?
Découvrez comment utiliser les émojis pour créer des graphiques Excel mémorables !
Découvrir les plages nommées d’Excel : utilisation et intérêt !

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.