Tableaux VBA

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

Fiche rapide de la baie VBA

Tableaux

La descriptionCode VBACréerDim arr (1 à 3) comme variante
arr(1) = "un"
arr(2) = "deux"
arr(3) = "trois"Créer à partir d'ExcelDim arr (1 à 3) comme variante
Cellule obscurcie en tant que plage, i en tant qu'entier
i = LBound(arr)
Pour chaque cellule dans la plage ("A1:A3")
je = je + 1
arr(i) = cellule.valeur
Cellule suivanteLire tous les articlesDim i aussi long
Pour i = LBound(arr) À UBound(arr)
MsgBox arr(i)
Ensuite jeEffacerEffacer l'arrTableau en chaîneDim sName en tant que chaîne
sNom = Join(arr, ":")Augmenter la tailleReDim Conserver arr (0 à 100)Définir la valeurarr(1) = 22

Exemples rapides de baies VBA

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

12345678910

Exemple de sous-tableau()Dim strNames (1 à 4) en tant que chaînestrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"msgbox strNames(3)Fin du sous-marin

Ici, nous avons créé le tableau de chaînes unidimensionnel : strNames de taille quatre (peut contenir quatre valeurs) et attribué les quatre valeurs. Enfin, nous affichons la 3ème valeur dans une boîte de message.

Dans ce cas, l'avantage d'utiliser un Array est faible : une seule déclaration de variable est requise au lieu de quatre.

Cependant, regardons un exemple qui montrera la vraie puissance d'un tableau :

12345678

Sous-tableauExample2()Dim strNames (1 à 60000) en tant que chaîneDim j'aussi longtempsPour i = 1 à 60000strNames(i) = Cells(i, 1).ValeurEnsuite jeFin du sous-marin

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

Avantages de la gamme ? - La vitesse!

Vous pourriez penser à des tableaux 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 de ligne et de colonne.
  • Feuille de travail Ex. cellules (1,4).value = "Ligne 1, colonne 4"
  • Tableau Ex. arrVar(1,4) = "Ligne 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 mot: La vitesse!

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

Créer/déclarer un tableau (Dim)

Remarque : Les tableaux peuvent avoir plusieurs « dimensions ». Pour simplifier les choses, nous commencerons par travailler uniquement avec des tableaux à une dimension. Plus tard dans le didacticiel, nous vous présenterons les tableaux à plusieurs dimensions.

Tableau statique

Tableaux statiques sont des tableaux qui ne peuvent pas changer de taille. Inversement, Tableaux dynamiques peut changer de taille. Ils sont déclarés légèrement différemment. Tout d'abord, regardons les tableaux statiques.

Remarque : si votre baie ne change pas de taille, utilisez une baie statique.

La déclaration d'une variable de tableau statique est très similaire à la déclaration d'une variable normale, 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 :

123456789101112

Sous TableauStatique1()'Crée un tableau avec les positions 1,2,3,4Dim arrDemo1 (1 à 4) en tant que chaîne'Crée un tableau avec les positions 4,5,6,7Dim arrDemo2 (4 à 7) aussi longtemps'Crée un tableau avec les positions 0,1,2,3Dim arrDemo3 (0 à 3) aussi longtempsFin du sous-marin

Ou vous pouvez saisir uniquement la taille du tableau :

123456

Sous TableauStatique2()'Crée un tableau avec les positions 0,1,2,3Dim arrDemo1(3) en tant que chaîneFin du sous-marin

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

Vous pouvez déclarer Base d'options 1 en haut de votre module pour que le tableau commence à la place à la place :

12345678

Base d'options 1Sous TableauStatique3()'Crée un tableau avec les positions 1,2,3Dim arrDemo1(3) en tant que chaîneFin du sous-marin

Cependant, je trouve qu'il est beaucoup plus facile (et moins déroutant) de simplement déclarer explicitement les positions de début et de fin des tableaux.

Fatigué de rechercher des exemples de code VBA ? Essayez AutoMacro !

Tableau dynamique

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 manières de déclarer un tableau dynamique.

Tableaux de variantes

La première façon de déclarer un tableau dynamique consiste à définir le tableau sur le type Une variante.

1	Dim arrVar() comme variante

Avec un Variante Array, vous n'avez pas besoin de définir la taille du tableau. La taille s'ajustera automatiquement. N'oubliez pas que le tableau commence par la position 0 (à moins que vous n'ajoutiez Option Base 1 en haut de votre module)

12345678910111213

Sous-VariantArray()Dim arrVar() comme variante'Définir les valeurs (Taille = 0,1,2,3)VarVar = Tableau(1, 2, 3, 4)'Modifier les valeurs (Taille = 0,1,2,3,4)tableauVar = Tableau("1a", "2a", "3a", "4a", "5a")'Position de sortie 4 ("5a")MsgBox arrVar(4)Fin du sous-marin

Tableaux dynamiques sans variante

Avec les tableaux sans variante, vous devez définir la taille du tableau avant d'affecter des valeurs au tableau. Cependant, le processus de création du tableau est légèrement différent :

1234567

Sous TableauDynamique1()Dim arrDemo1() en tant que chaîne'Redimensionne le tableau avec les positions 1,2,3,4ReDim arrDemo1(1 à 4)Fin du sous-marin

Vous déclarez d'abord le tableau, similaire au tableau statique, sauf que vous omettez la taille du tableau :

1	Dim arrDemo1() en tant que chaîne

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

12	'Redimensionne le tableau avec les positions 1,2,3,4ReDim arrDemo1(1 à 4)

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

ReDim vs ReDim Preserve

Lorsque vous utilisez le ReDim commande vous effacez toutes les valeurs existantes du tableau. Au lieu de cela, vous pouvez utiliser ReDim préserver pour conserver les valeurs du tableau :

12	'Redimensionne le tableau avec les positions 1,2,3,4 (en préservant les valeurs existantes)ReDim Conserver arrDemo1(1 à 4)

Déclaration de tableaux simplifiée

Vous pourriez vous sentir dépassé après avoir lu tout ce qui précède. Pour garder les choses simples, nous travaillerons principalement avec des tableaux statiques pour le reste de l'article.

Définir les valeurs du tableau

La définition des valeurs du tableau est très simple.

Avec un tableau statique, vous devez définir chaque position du tableau, une à la fois :

12345678

Exemple de sous-tableau()Dim strNames (1 à 4) en tant que chaînestrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Fin du sous-marin

Avec un tableau de variantes, vous pouvez définir l'ensemble du tableau avec une seule ligne (pratique uniquement pour les petits tableaux) :

123456

Sous-tableauExample_1Line()Dim strNames() en tant que variantestrNames = Array("Shelly", "Steve", "Neema", "Jose")Fin du sous-marin

Si vous essayez de définir une valeur pour un emplacement de tableau qui n'existe pas, vous recevrez une erreur Indice hors plage :

1	strNames(5) = "Shannon"

Dans la section « Attribuer une plage à un tableau » ci-dessous, nous vous montrerons comment utiliser une boucle pour attribuer rapidement un grand nombre de valeurs aux tableaux.

Obtenir la valeur du tableau

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

1234	Plage("A1").Valeur = strNames(1)Plage("A2").Valeur = strNames(2)Plage("A3").Valeur = strNames(3)Plage ("A4").Valeur = strNames(4)

Programmation VBA | Le générateur de code fonctionne pour vous !

Attribuer une plage à un tableau

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

12345678

Sous-plage vers tableau()Dim strNames (1 à 60000) en tant que chaîneDim j'aussi longtempsPour i = 1 à 60000strNames(i) = Cells(i, 1).ValeurEnsuite jeFin du sous-marin

Cela bouclera à travers les cellules A1:A60000, en attribuant les valeurs des cellules au tableau.

Tableau de sortie vers plage

Ou vous pouvez utiliser une boucle pour affecter un tableau à une plage :

123	Pour i = 1 à 60000Cells(i, 1).Valeur = strNames(i)Ensuite je

Cela fera l'inverse : attribuez des valeurs de tableau aux cellules A1: A60000

Réseaux 2D / Multidimensionnels

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

Pensez à 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 est comme un classeur entier, contenant plusieurs feuilles contenant chacune plusieurs lignes et colonnes (vous pourrait également considérer un tableau 3D comme un Rubik's Cube).

Exemples de tableaux multidimensionnels

Maintenant, montrons des exemples de travail avec des tableaux de différentes dimensions.

Programmation VBA | Le générateur de code fonctionne pour vous !

Exemple de tableau 1D

Cette procédure combine les exemples de tableaux précédents en une seule procédure, montrant comment vous pouvez utiliser des tableaux dans la pratique.

1234567891011121314

Sous-tableauEx_1d()Dim strNames (1 à 60000) en tant que chaîneDim j'aussi longtemps'Attribuer des valeurs au tableauPour i = 1 à 60000strNames(i) = Cells(i, 1).ValeurEnsuite je'Valeurs du tableau de sortie à la plagePour i = 1 à 60000Sheets("Sortie").Cells(i, 1).Value = strNames(i)Ensuite jeFin du sous-marin

Exemple de tableau 2D

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

123456789101112131415161718

Sous-tableauEx_2d()Dim strNames (1 à 60000, 1 à 10) en tant que chaîneDim i aussi long, j aussi long'Attribuer des valeurs au tableauPour i = 1 à 60000Pour j = 1 à 10strNames(i, j) = Cells(i, j).Valeurj suivantEnsuite je'Valeurs du tableau de sortie à la plagePour i = 1 à 60000Pour j = 1 à 10Sheets("Sortie").Cells(i, j).Value = strNames(i, j)j suivantEnsuite jeFin du sous-marin

Exemple de tableau 3D

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

12345678910111213141516171819202122

Sous-tableauEx_3d()Dim strNames (1 à 60000, 1 à 10, 1 à 3) en tant que chaîneDim i aussi long, j aussi long, k aussi long'Attribuer des valeurs au tableauPour k = 1 à 3Pour i = 1 à 60000Pour j = 1 à 10strNames(i, j, k) = Sheets("Sheet" & k).Cells(i, j).Valuej suivantEnsuite jeSuivant k'Valeurs du tableau de sortie à la plagePour k = 1 à 3Pour i = 1 à 60000Pour j = 1 à 10Sheets("Sortie" & k).Cellules(i, j).Valeur = strNames(i, j, k)j suivantEnsuite jeSuivant kFin du sous-marin

Longueur/taille du tableau

Jusqu'à présent, nous vous avons présenté les différents types de tableaux et vous avons appris à déclarer les tableaux et à obtenir/définir les valeurs des tableaux. Ensuite, nous nous concentrerons sur d'autres sujets nécessaires pour travailler avec des tableaux.

Programmation VBA | Le générateur de code fonctionne pour vous !

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 :

123456

Sous-UBoundLBound()Dim strNames (1 à 4) en tant que chaîneMsgBox UBound(strNames)MsgBox LBound(strNames)Fin du sous-marin

En soustrayant les deux (et en ajoutant 1) vous obtiendrez la longueur :

1	GetArrLength = UBound(strNames) - LBound(strNames) + 1

Fonction de longueur de tableau

Voici une fonction pour obtenir la longueur d'un tableau à une dimension :

1234567

Fonction publique GetArrLength(a As Variant) As LongSi est vide(a) alorsGetArrLength = 0AutreGetArrLength = UBound(a) - LBound(a) + 1Fin siFonction de fin

Besoin de calculer la taille d'un tableau 2D ? Consultez notre tutoriel : Calculer la taille d'un tableau.

Boucle à travers le tableau

Il existe deux façons de parcourir un tableau. Le premier parcourt les entiers correspondant aux positions numériques du tableau. Si vous connaissez la taille du tableau, vous pouvez la spécifier directement :

12345678910111213

Sous-tableauExample_Loop1()Dim strNames (1 à 4) en tant que chaîneDim j'aussi longtempsstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Pour i = 1 à 4MsgBox strNames(i)Ensuite jeFin du sous-marin

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

12345678910111213

Sous-tableauExample_Loop2()Dim strNames (1 à 4) en tant que chaîneDim j'aussi longtempsstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Pour i = LBound(strNames) À UBound(strNames)MsgBox strNames(i)Ensuite jeFin du sous-marin

Pour chaque boucle de tableau

La deuxième méthode consiste à utiliser une boucle For Each. Cela parcourt chaque élément du tableau :

12345678910111213

Sous-tableauExample_Loop3()Dim strNames (1 à 4) en tant que chaîneÉlément faiblestrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Pour chaque élément dans strNamesÉlément MsgBoxProchain pointFin du sous-marin

La boucle For Each Array Loop fonctionnera avec des tableaux multidimensionnels en plus des tableaux unidimensionnels.

Programmation VBA | Le générateur de code fonctionne pour vous !

Boucle à travers le tableau 2D

Vous pouvez également utiliser les fonctions UBound et LBound pour parcourir un tableau multidimensionnel. Dans cet exemple, nous allons parcourir un tableau 2D. 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 deuxième dimension).

1234567891011121314151617181920

Sous-tableauExample_Loop4()Dim strNames (1 à 4, 1 à 2) en tant que chaîneDim i aussi long, j aussi longstrNames(1, 1) = "Shelly"strNames(2, 1) = "Steve"strNames(3, 1) = "Neema"strNames(4, 1) = "José"strNames(1, 2) = "Shelby"strNames(2, 2) = "Steven"strNames(3, 2) = "Némo"strNames(4, 2) = "Jesse"Pour j = LBound(strNames, 2) À UBound(strNames, 2)Pour i = LBound(strNames, 1) À UBound(strNames, 1)MsgBox strNames(i, j)Ensuite jej suivantFin du sous-marin

Autres tâches de la baie

Effacer le tableau

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

1	Effacer strNames

Exemple d'utilisation :

12345678910

Exemple de sous-tableau()Dim strNames (1 à 4) en tant que chaînestrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Effacer strNamesFin du sous-marin

Alternativement, vous pouvez également ReDim le tableau pour le redimensionner, en effaçant une partie du tableau :

1	ReDim strNames (1 à 2)

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

Tableau de comptage

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

Vous pouvez également compter le nombre d'éléments entrés (ou d'éléments qui répondent à certains critères) en parcourant le tableau.

Cet exemple va parcourir un tableau d'objets et compter le nombre de chaînes non vides trouvées dans le tableau :

123456789101112131415

Sous-tableauLoopandCount()Dim strNames (1 à 4) en tant que chaîneDim i aussi long, n aussi longstrNames(1) = "Shelly"strNames(2) = "Steve"Pour i = LBound(strNames) À UBound(strNames)Si strNames(i) "" Alorsn = n + 1Fin siEnsuite jeMsgBox n & " valeurs non vides trouvées."Fin du sous-marin

Programmation VBA | Le générateur de code fonctionne pour vous !

Supprimer les doublons

À un moment donné, vous souhaiterez peut-être supprimer les doublons d'un tableau. Malheureusement, VBA n'a pas de fonctionnalité intégrée pour le faire. Cependant, nous avons écrit une fonction pour supprimer les doublons d'un tableau (c'est trop long à inclure dans ce didacticiel, mais visitez le lien pour en savoir plus).

Filtre

La fonction de filtre VBA vous permet de filtrer un tableau. Pour ce faire, il crée un nouveau tableau avec uniquement les valeurs filtrées. Vous trouverez ci-dessous un exemple rapide, mais assurez-vous de lire l'article pour plus d'exemples pour différents besoins.

1234567891011121314

Sub Filter_Match()'Définir le tableauDim strNames en tant que variantestrNames = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")'Filtrer la matriceDim strSubNames comme variantestrSubNames = Filter(strNames, "Smith")'Compter le tableau filtréMsgBox "Trouvé " & UBound(strSubNames) - LBound(strSubNames) + 1 & " noms."Fin du sous-marin

Fonction IsArray

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

123456789101112

Sous IsArrayEx()'Crée un tableau avec les positions 1,2,3Dim arrDemo1(3) en tant que chaîne'Crée une variable de chaîne régulièreDim str en tant que chaîneMsgBox IsArray(arrDemo1)MsgBox IsArray(str)Fin du sous-marin

Rejoindre le tableau

Vous pouvez rapidement « rejoindre » un tableau entier avec la fonction Join :

123456789101112

Sous Array_Join()Dim strNames (1 à 4) en tant que chaîneDim joinNames As StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"joinNames = Join(strNames, ", ")MsgBox joinNamesFin du sous-marin

Programmation VBA | Le générateur de code fonctionne pour vous !

Diviser la chaîne en tableau

La fonction de fractionnement VBA divisera une chaîne de texte en un tableau contenant les valeurs de la chaîne d'origine. Regardons un exemple :

123456789

Sous-tableau_Split()Dim Names () en tant que chaîneDim joinNames As StringjoinNames = "Shelly, Steve, Nema, José"Noms = Diviser (noms joints, ",")Noms des boîtes de messages(1)Fin du sous-marin

Ici, nous divisons cette chaîne de texte "Shelly,Steve,Nema,Jose" en un tableau (taille 4) en utilisant le délimiteur a virgule (,").

Tableau de constantes

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

123456789

' Définir un tableauConstantFonction TableauConst()TableauConst = Tableau(4, 12, 21, 100, 5)Fonction de fin' Récupérer la valeur ConstantArraySous-RécupérerValeurs()MsgBox ConstantArray(3)Fin du sous-marin

Copier le tableau

Il n'existe aucun moyen intégré de copier un tableau à l'aide de VBA. Au lieu de cela, vous devrez utiliser une boucle pour affecter les valeurs d'un tableau à un autre.

12345678910111213141516171819

Sous CopyArray()Dim Arr1 (1 à 100) aussi longDim Arr2 (1 à 100) aussi longDim j'aussi longtemps'Créer un tableau1Pour i = 1 à 100Arr1(i) = jeEnsuite je'CopyArray1 to Array2Pour i = 1 à 100Arr2(i) = Arr1(i)Ensuite jeMsgBox Arr2(74)Fin du sous-marin

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.

Programmation VBA | Le générateur de code fonctionne pour vous !

Tableau de retour de fonction

Une question courante 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 en utilisant des tableaux de variantes. Nous avons écrit un article sur le sujet : VBA Function 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 que 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 l'objet RecordSet plutôt que l'objet Range.

1234567891011121314151617181920212223

Sub RangeToArrayAccess()En cas d'erreur Reprendre ensuiteDim strNames() en tant que chaîneDim j'aussi longtempsDim iCount As LongDim dbs en tant que base de donnéesDim d'abord en tant que jeu d'enregistrementsDéfinir dbs = CurrentDbDéfinir d'abord = dbs.OpenRecordset("tblClients", dbOpenDynaset)Avec d'abord.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 à iCount)Pour i = 1 Pour iCompterstrNames(i) = rst.Fields("ClientName").MoveNextEnsuite jeTerminer parpremier.FermerDéfinir en premier = RienDéfinir dbs = RienFin du sous-marin

Tutoriels sur les baies
Méga-guide sur les tableaux	Oui
Obtenir la taille du tableau
Effacer le tableau
Filtrer la matrice
Transposer le tableau
Tableau de retour de fonction
Supprimer les doublons