Tableaux VBA

En VBA, un Tableau est une variable unique qui peut contenir plusieurs valeurs. Considérez un tableau comme une plage de cellules : chaque cellule peut stocker une valeur. Les tableaux peuvent être unidimensionnels (une seule colonne), bidimensionnels (plusieurs lignes et colonnes) ou multidimensionnels. Les valeurs d’un tableau sont accessibles par leur position (numéro d’index) dans le tableau.

Aide-Mémoire sur les Tableaux VBA

Tableaux

Exemples Rapides d’Utilisation de Tableaux en VBA

Voyons un exemple complet avant de nous plonger dans les détails :

Sub ExempleTableau()
    Dim strNoms(1 To 4) As String

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    strNoms(3) = "Simon"
    strNoms(4) = "Josée"

    MsgBox strNoms(3)
End Sub

Ici, nous avons créé un tableau unidimensionnel contenant des chaînes de texte nommé strNoms avec une taille de quatre (peut contenir quatre valeurs). Nous avons ensuite assigné les quatre valeurs. Enfin, nous affichons la troisième valeur dans une boîte de message.

Dans ce cas, l’avantage d’utiliser un tableau est minime : une seule déclaration de variable est nécessaire au lieu de quatre.

Cependant, examinons un exemple qui montrera la véritable puissance d’un tableau :

Sub ExempleTableauBoucle()
    Dim strNoms(1 To 6000) As String
    Dim i As Long
    
    For i = 1 To 6000
        strNoms(i) = Cells(i, 1).Value
    Next i
End Sub

Ici, nous avons créé un tableau pouvant contenir 6000 valeurs et nous avons rapidement rempli le tableau à partir de la colonne A d’une feuille de calcul.

L’Avantage des Tableaux? – La Rapidité!

Vous pensez peut-être que les tableaux sont similaires aux feuilles de calcul Excel :

• Chaque cellule (ou élément d’un tableau) peut contenir sa propre valeur
• Chaque cellule (ou élément d’un tableau) est accessible par sa position en ligne et en colonne.
  • Feuille de calcul Ex. cells(1,4).value = « Rangée 1, Colonne 4 »
  • Tableau Ex. arrVar(1,4) = « Rangée 1, Colonne 4 »

Alors pourquoi s’embêter avec les tableaux? Pourquoi ne pas simplement lire et écrire des valeurs directement dans les cellules d’Excel? Un seul mot : Vitesse!

Lire/écrire dans les cellules Excel est un processus lent. Travailler avec des tableaux est significativement plus rapide!

Créer / Déclarer un Tableau (Dim)

Remarque : les tableaux peuvent avoir plusieurs « dimensions ». Pour garder les choses simples, nous commencerons par travailler uniquement avec des tableaux à une dimension. Plus tard dans ce tutoriel, nous vous présenterons les tableaux à plusieurs dimensions.

Tableau Statique

Les tableaux statiques sont des tableaux qui ne peuvent pas changer de taille. À l’inverse, les tableaux dynamiques peuvent changer de taille. Ils sont déclarés de manière légèrement différente. Tout d’abord, examinons les tableaux statiques.

Remarque : si votre tableau n’a pas besoin de changer de taille, utilisez un tableau statique.

La déclaration d’une variable de tableau statique est très similaire à celle d’une variable ordinaire, sauf que vous devez définir la taille du tableau. Il existe plusieurs façons de définir la taille d’un tableau.

Vous pouvez déclarer explicitement les positions de début et de fin d’un tableau :

Sub TableauStatique()

    ' Crée un tableau avec les positions 1,2,3,4
    Dim arrExemple1(1 To 4) As String
    
    ' Crée un tableau avec les positions 4,5,6,7
    Dim arrExemple2(4 To 7) As Long
    
    ' Crée un tableau avec les positions 0,1,2,3
    Dim arrExemple3(0 To 3) As Long

End Sub

Ou vous pouvez saisir uniquement la taille du tableau :

Sub TableauStatique2()

    ' Crée un tableau avec les positions 0,1,2,3
    Dim arrExemple1(3) As String

End Sub

Important ! Notez que par défaut, les tableaux commencent à la position 0. Donc Dim arrExemple1(3) crée un tableau avec les positions 0,1,2,3.

Vous pouvez déclarer l’option Base 1 au début de votre module pour que les tableaux commence à la position 1 par défaut :

Option Base 1

Sub TableauStatique3()

    ' Crée un tableau avec les positions 1, 2 et 3
    Dim arrExemple1(3) As String

End Sub

Cependant, je trouve qu’il est beaucoup plus facile de déclarer explicitement les positions de début et de fin des tableaux. Cela réduit également les risques d’erreurs et rend le débogage plus facile.

Tableau Dynamique

Les Tableaux dynamiques sont des tableaux dont la taille peut être modifiée (ou dont la taille n’a pas besoin d’être définie).

Il existe deux façons de déclarer un tableau dynamique.

Tableaux de Variantes

La première façon de déclarer un tableau dynamique est de lui attribuer le type Variant.

Dim arrVar() As Variant

Avec un tableau de type Variant, vous n’avez pas besoin de définir la taille du tableau. La taille s’ajuste automatiquement. N’oubliez pas que le tableau commence à la position 0 (sauf si vous ajoutez l’option Base 1 en haut de votre module)

Sub TableauVariantes()
    Dim arrVar() As Variant
    
    ' Définir les valeurs (Taille = 0,1,2,3)
    arrVar = Array(1, 2, 3, 4)
    
    ' Modification des valeurs (taille = 0,1,2,3,4)
    arrVar = Array("1a", "2a", "3a", "4a", "5a")

    ' Sortie de la position 4 ("5a")
    MsgBox arrVar(4)

End Sub

Tableaux Dynamiques Non-Variants

Avec les tableaux qui contiennent un type de donnée autre que les variants, vous devez définir la taille du tableau avant d’y affecter des valeurs. Ainsi, le processus de création du tableau est légèrement différent :

Sub TableauDynamique()
    Dim arrExemple1() As String

    ' Redimensionnement du tableau avec les positions 1,2,3,4
    ReDim arrExemple1(1 To 4)
    
End Sub

Tout d’abord, vous déclarez le tableau, comme pour le tableau statique, sauf que vous omettez la taille du tableau :

Dim arrExemple1() As String

Maintenant, lorsque vous voulez définir la taille du tableau, vous utilisez la commande ReDim pour dimensionner le tableau :

' Redimensionne le tableau avec les positions 1,2,3,4
ReDim arrExempe1(1 To 4)

ReDim redimensionne le tableau. Vous trouverez ci-dessous la différence entre ReDim et ReDim Preserve.

ReDim vs. ReDim Preserve

Lorsque vous utilisez la commande ReDim, vous effacez toutes les valeurs existantes du tableau. Alternativement, vous pouvez utiliser la commande ReDim Preserve pour préserver les valeurs du tableau :

' Redimensionne le tableau avec les positions 1,2,3,4 (en préservant les valeurs existantes)
ReDim Preserve arrExemple1(1 To 4)

Déclarer des Tableaux de Manière Simplifiée

Vous vous sentez peut-être dépassé par la lecture de ce qui précède. Pour garder les choses simples, nous travaillerons principalement avec des tableaux statiques dans le reste de l’article.

Définition des Valeurs des Tableaux

Il est très facile de définir les valeurs d’un tableau. Avec un tableau statique, vous devez définir chaque position du tableau, une par une :

Sub ExempleTableau()
    Dim strNoms(1 To 4) As String

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    strNoms(3) = "Simon"
    strNoms(4) = "Josée"

End Sub

Avec un tableau de type Variant, vous pouvez définir l’ensemble du tableau en une seule ligne (pratique uniquement pour les petits tableaux) :

Sub ExempleTableau_1Ligne()
    Dim strNoms() As Variant

    strNoms = Array("Julie", "Martin", "Simon", "Josée")

End Sub

Si vous tentez de définir une valeur pour un emplacement de tableau qui n’existe pas, vous recevrez une erreur d’exécution « L’indice n’appartient pas à la sélection » :

strNoms(5) = "Françis"

Dans la section « Assignation d’une plage à un tableau », nous allons vous montrer comment utiliser une boucle pour assigner rapidement un grand nombre de valeurs à des tableaux.

Récupérer les Valeurs d’un Tableau

Vous pouvez récupérer les valeurs d’un tableau de la même manière. Dans l’exemple ci-dessous, nous allons écrire les valeurs du tableau dans des cellules :

    Range("A1").Value = strNoms(1)
    Range("A2").Value = strNoms(2)
    Range("A3").Value = strNoms(3)
    Range("A4").Value = strNoms(4)

Affectation d’une Plage à un Tableau

Pour affecter une plage à un tableau, vous pouvez utiliser une boucle:

Sub AffecterPlageÀUnTableau()
    Dim strNoms(1 To 60000) As String
    Dim i As Long

    For i = 1 To 60000
        strNoms(i) = Cells(i, 1).Value
    Next i
End Sub

Ceci va boucler à travers les cellules A1:A60000, en assignant les valeurs des cellules au tableau.

Sortie des Valeurs du Tableau Vers une Plage

Vous pouvez également utiliser une boucle pour affecter un tableau à une plage :

    For i = 1 To 60000
        Cells(i, 1).Value = strNoms(i)
    Next i

Ce code effectue l’inverse de l’exemple précédent. Il affecte les valeurs du tableau aux cellules A1:A60000

Tableaux 2D / Multidimensionnels

Jusqu’à présent, nous avons travaillé exclusivement avec des tableaux unidimensionnels (1D). Cependant, les tableaux peuvent avoir jusqu’à 32 dimensions.

Considérez un tableau 1D comme une seule ligne ou colonne de cellules Excel, un tableau 2D comme une feuille de calcul Excel entière avec plusieurs lignes et colonnes, et un tableau 3D comme un classeur entier, contenant plusieurs feuilles, chacune contenant plusieurs lignes et colonnes (vous pouvez également considérer un tableau 3D comme un Cube Rubik).

Exemples de Tableaux Multidimensionnels

Voyons maintenant des exemples d’utilisation de tableaux de différentes dimensions.

Exemple de tableau 1D

Cette procédure combine les exemples de tableaux précédents en une seule procédure, afin de montrer comment vous pouvez utiliser les tableaux en pratique.

Sub ExempleTableau_1D()
    Dim strNoms(1 To 60000) As String
    Dim i As Long
 
    ' Affectation des valeurs au tableau
    For i = 1 To 60000
        strNoms(i) = Cells(i, 1).Value
    Next i
    
    ' Sortie des valeurs du tableau dans la plage
    For i = 1 To 60000
        Sheets("Sortie").Cells(i, 1).Value = strNoms(i)
    Next i
End Sub

Exemple de Tableau 2D

Cette procédure contient un exemple de tableau 2D :

Sub ExempleTableau_2D()
    Dim strNoms(1 To 60000, 1 To 10) As String
    Dim i As Long, j As Long
 
    ' Affectation des valeurs au tableau
    For i = 1 To 60000
        For j = 1 To 10
            strNoms(i, j) = Cells(i, j).Value
        Next j
    Next i
    
    ' Sortie des valeurs du tableau dans la plage
    For i = 1 To 60000
        For j = 1 To 10
            Sheets("Sortie").Cells(i, j).Value = strNoms(i, j)
        Next j
    Next i
End Sub

Exemple de Tableau 3D

Cette procédure contient un exemple de tableau 3D permettant de travailler avec plusieurs feuilles :

Sub ExempleTableau_3D()
    Dim strNoms(1 To 60000, 1 To 10, 1 To 3) As String
    Dim i As Long, j As Long, k As Long
 
    ' Affectation des valeurs au tableau
    For k = 1 To 3
        For i = 1 To 60000
            For j = 1 To 10
                strNoms(i, j, k) = Sheets("Feuil" & k).Cells(i, j).Value
            Next j
        Next i
    Next k
    
    ' Sortie des valeurs du tableau dans la plage
    For k = 1 To 3
        For i = 1 To 60000
            For j = 1 To 10
                Sheets("Sortie" & k).Cells(i, j).Value = strNoms(i, j, k)
            Next j
        Next i
    Next k
End Sub

Longueur / Taille des Tableaux

Jusqu’à présent, nous vous avons présenté les différents types de tableaux et nous vous avons appris à déclarer les tableaux et à obtenir/établir les valeurs des tableaux. Nous allons maintenant nous concentrer sur d’autres sujets nécessaires pour travailler efficacement avec des tableaux.

Fonctions UBound et LBound

La première étape pour obtenir la longueur/taille d’un tableau consiste à utiliser les fonctions UBound et LBound pour obtenir les limites supérieure et inférieure du tableau :

Sub UBoundLBound()
    Dim strNoms(1 To 4) As String
    
    MsgBox UBound(strNoms)
    MsgBox LBound(strNoms)
End Sub

En soustrayant les deux valeurs (et en ajoutant 1), vous obtiendrez la longueur du tableau :

LongueurTableau = UBound(strNoms) - LBound(strNoms) + 1

Fonction pour Obtenir la Longueur des Tableaux

Voici une fonction personnalisée permettant d’obtenir la longueur d’un tableau unidimensionnel :

Public Function ObtenirDimTableau(a As Variant) As Long
   if IsEmpty(a) Then
      ObtenirDimTableau = 0
   Else
      ObtenirDimTableau = UBound(a) - LBound(a) + 1
   End If
End Function

Vous avez besoin de calculer la taille d’un tableau 2D ? Consultez notre tutoriel : Calculer la taille d’un tableau.

Boucle dans un Tableau

Il existe deux façons de parcourir un tableau en boucle. La première consiste à boucler sur les entiers correspondant aux positions numériques du tableau. Si vous connaissez la taille du tableau, vous pouvez la spécifier directement :

Sub ExempleTableau_Boucle1()
    Dim strNoms(1 To 4) As String
    Dim i As Long

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    strNoms(3) = "Simon"
    strNoms(4) = "Josée"
    
    For i = 1 To 4
        MsgBox strNoms(i)
    Next i
End Sub

Cependant, si vous ne connaissez pas la taille du tableau (par exemple, si le tableau est dynamique), vous pouvez utiliser les fonctions LBound et UBound présentée à la section précédente :

Sub ExempleTableau_Boucle2()
    Dim strNoms(1 To 4) As String
    Dim i As Long

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    strNoms(3) = "Simon"
    strNoms(4) = "Josée"
    
    For i = LBound(strNoms) To UBound(strNoms)
        MsgBox strNoms(i)
    Next i
End Sub

Boucle « For Each » dans un Tableau

La deuxième méthode consiste à utiliser une boucle For Each. Elle passe en revue chaque élément du tableau :

Sub ExempleTableau_Boucle3()
    Dim strNoms(1 To 4) As String
    Dim Élément

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    strNoms(3) = "Simon"
    strNoms(4) = "Josée"
    
    For Each Élément In strNoms
        MsgBox Élément
    Next Élément
End Sub

La boucle For Each fonctionne avec des tableaux multidimensionnels en plus des tableaux unidimensionnels.

Boucle dans un Tableau 2D

Vous pouvez également utiliser les fonctions UBound et LBound pour parcourir en boucle un tableau multidimensionnel. Dans cet exemple, nous allons parcourir un tableau à 2 dimensions. Notez que les fonctions UBound et LBound vous permettent de spécifier la dimension du tableau pour trouver les limites supérieure et inférieure (1 pour la première dimension, 2 pour la seconde).

Sub ExempleTableau_Boucle4()
    Dim strNoms(1 To 4, 1 To 2) As String
    Dim i As Long, j As Long

    strNoms(1, 1) = "Julie"
    strNoms(2, 1) = "Martin"
    strNoms(3, 1) = "Simon"
    strNoms(4, 1) = "Josée"
    
    strNoms(1, 2) = "Juju"
    strNoms(2, 2) = "Tintin"
    strNoms(3, 2) = "Sim"
    strNoms(4, 2) = "Jo"
    
    For j = LBound(strNoms, 2) To UBound(strNoms, 2)
        For i = LBound(strNoms, 1) To UBound(strNoms, 1)
            MsgBox strNoms(i, j)
        Next i
    Next j
End Sub

Autres Tâches Liées aux Tableaux

Effacer un Tableau

Pour effacer un tableau entier, utilisez l’instruction Erase:

Erase strNoms

Exemple d’utilisation :

Sub ExempleTableau()
    Dim strNoms(1 To 4) As String

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    strNoms(3) = "Simon"
    strNoms(4) = "Josée"
    
    Erase strNoms
End Sub

Alternativement, vous pouvez aussi utiliser ReDim sur le tableau pour le redimensionner, en effaçant une partie du tableau :

ReDim strNoms(1 To 2)

Cela redimensionne le tableau à la taille 2, en supprimant les positions 3 et 4.

Compter dans un Tableau

Vous pouvez compter le nombre de positions dans chaque dimension d’un tableau à l’aide des fonctions UBound et LBound (voir ci-dessus).

Vous pouvez également compter le nombre d’éléments saisis (ou d’éléments répondant à certains critères) en parcourant le tableau en boucle.

L’exemple suivant parcourt en boucle un tableau d’objets et compte le nombre de chaînes de caractères non vides trouvées dans le tableau :

Sub CompterDansUnTableau()
    Dim strNoms(1 To 4) As String
    Dim i As Long, n As Long

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    
    For i = LBound(strNoms) To UBound(strNoms)
        If strNoms(i) <> "" Then
            n = n + 1
        End If
    Next i
    
    MsgBox n & " valeurs non vides trouvées"
End Sub

Suppression des Doublons

À un certain moment, vous voudrez probablement supprimer les doublons dans un tableau. Malheureusement, VBA ne dispose pas d’une fonction intégrée pour le faire. Cependant, nous avons écrit une fonction pour supprimer les doublons d’un tableau (elle est trop longue pour être incluse dans ce tutoriel, mais consultez le lien pour en savoir plus).

Filtre

La fonction VBA Filter vous permet de filtrer un tableau. Pour ce faire, elle crée un nouveau tableau contenant uniquement les valeurs filtrées. Vous trouverez ci-dessous un exemple rapide, mais n’oubliez pas de lire l’article pour obtenir d’autres exemples répondant à différents besoins.

Sub Filtrer_Tableau()
 
    ' Définition du tableau
    Dim strNoms As Variant
    strNoms = Array("Julie Dupont", "Martin Dupont", "Simon Leblanc")
 
    ' Filtrer le tableau
    Dim strSubNames As Variant
    strSubNames = Filter(strNoms, "Dupont")
    
    ' Compter les éléments filtrés
    MsgBox UBound(strSubNames) - LBound(strSubNames) + 1 & " noms trouvés."
 
End Sub

Fonction IsArray

Vous pouvez tester si une variable est un tableau à l’aide de la fonction IsArray:

Sub ExempleIsArray()

    ' Crée un tableau avec les positions 1,2,3
    Dim arrExemple1(3) As String
    
    ' Crée une variable de type chaîne régulière
    Dim str As String
    
    MsgBox IsArray(arrExemple1)
    MsgBox IsArray(str)

End Sub

Joindre un Tableau

Vous pouvez rapidement « joindre » un tableau entier à l’aide de la fonction Join:

Sub Joindre_Tableau()
    Dim strNoms(1 To 4) As String
    Dim JoindreNoms As String

    strNoms(1) = "Julie"
    strNoms(2) = "Martin"
    strNoms(3) = "Simon"
    strNoms(4) = "Josée"
    
    JoindreNoms = Join(strNoms, ", ")
    MsgBox JoindreNoms
End Sub

Division d’une Chaîne en Tableau

La fonction Split de VBA divise une chaîne de texte en tableau contenant les valeurs de la chaîne d’origine. Voyons un exemple :

Sub DiviserChaine_Tableau()
    Dim Noms() As String
    Dim NomsChaine As String
    
    NomsChaine = "Julie,Martin,Simon,Josée"
    Noms = Split(NomsChaine, ",")

    MsgBox Noms(1)
End Sub

Ici, nous divisons cette chaîne de texte « Julie,Martin,Simon,Josée » en un tableau de taille 4 en utilisant la virgule comme délimiteur de valeurs.

Constante Tableau

Un tableau ne peut pas ne peut être déclaré comme une constante en VBA. Cependant, vous pouvez contourner ce problème en créant une fonction à utiliser comme un tableau constant:

' Définition de ConstanteTableau
Function ConstanteTableau()
    ConstanteTableau = Array(4, 12, 21, 100, 5)
End Function

' Récupérer la valeur de ConstantArray
Sub RécupérationDesValeurs()
    MsgBox ConstanteTableau(3)
End Sub

Copie d’un Tableau

Il n’existe pas de moyen intégré pour copier un tableau en utilisant VBA. Vous devrez plutôt utiliser une boucle pour assigner les valeurs d’un tableau à un autre.

Sub CopierTableau()

    Dim Tableau1(1 To 100) As Long
    Dim Tableau2(1 To 100) As Long
    Dim i As Long
    
    ' Création du tableau 1
    For i = 1 To 100
        Tableau1(i) = i
    Next i
    
    ' Copie de Tableau1 dans Tableau2
    For i = 1 To 100
        Tableau2(i) = Tableau1(i)
    Next i
    
    MsgBox Tableau2(74)

End Sub

Transposer

Il n’y a pas de fonction VBA intégrée pour vous permettre de transposer un tableau. Cependant, nous avons écrit une fonction pour transposer un tableau 2D. Lisez l’article pour en savoir plus.

Retourner une Tableau à l’Aide d’une Fonction

Une question fréquente des développeurs VBA est de savoir comment créer une fonction qui renvoie un tableau. Je pense que la plupart des difficultés sont résolues par l’utilisation d’un tableau de type Variant. Nous avons rédigé un article sur le sujet : Fonction VBA Return Array.

Utilisation de Tableaux dans Access VBA

La plupart des exemples de tableaux ci-dessus fonctionnent exactement de la même manière dans Access VBA et dans Excel VBA. La seule différence majeure est que lorsque vous souhaitez remplir un tableau à l’aide de données Access, vous devez parcourir en boucle l’objet RecordSet plutôt que l’objet Range.

Sub PlagerVersTableauAccess()
   On Error Resume Next
   Dim strNoms() As String
   Dim i As Long
   Dim iCompte As Long
   Dim dbs As Database
   Dim rst As Recordset
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)
   With rst
      .MoveLast
      .MoveFirst
      iCompte = .RecordCount
      ReDim strNoms(1 To iCompte)
      For i = 1 To iCompte
         strNoms(i) = rst.Fields("NomClient")
         .MoveNext
      Next i
   End With
   rst.Close
   Set rst = Nothing
   Set dbs = Nothing
End Sub

Array Tutorials
Array Mega-Guide	yes
Get Array Size
Clear Array
Filter Array
Transpose Array
Function Return Array
Remove Duplicates