Les Tableaux VBA – aussi connus comme Array – sont un moyen très efficace de travailler avec des données dans Excel. Le Tableau est une "table en mémoire de l'ordinateur" qui vous permet de travailler extrêmement rapidement dans un ensemble de données multidimensionnel sans avoir besoin d'avoir la source des données ouverte. Dans cet article, vous pouvez trouver une fonction VBA "prête à l'emploi" qui charge le contenu d'un Classeur en Array (Table).
Le code est facile à implémenter dans votre Projet VBA et il est 100% générique – vous pouvez l'utiliser immédiatement tel quel sans modifications.
Classeur Excel vers Array(): comment ça marche…
La fonction ClasseurVersTableArray() que je vous propose dans cet article, a un seul argument (de type Workbook) – c'est le classeur Excel que vous souhaitez charger dans le Tableau.
Le résultat de la fonction est, logiquement, un Tableau/Array tridimensionnel qui contient toutes les données du Classeur.
Les trois dimensions, dont vous avez besoin pour retrouver n'importe quel point de données du Classeur, sont les suivantes:
- la Feuille du Classeur
- la Ligne de la Feuille de calcul
- la Colonne de la Feuille de calcul
Une fois les données chargées dans le Tableau/Array (= une variable dans la mémoire), vous pouvez fermer le Classeur importé et continuer à utiliser son contenu à partir du Tableau.
Cela facilite l'utilisation des boucles dans l'ensemble de données (et l'utilisation, par exemple, des conditions), mais cela vous permet également d'accéder à n'importe quel point de données du contenu du Classeur: Sélectionnez simplement l'endroit exact dont vous voulez obtenir la valeur. Et tout cela bien plus vite que si vous travailliez avec les données directement dans le Classeur (ex. ActiveWorkbook.Sheets(1).Range("K128").Value )
Pour avoir n'importe quelle valeur souhaitée, il vous suffit d'utiliser les 3 coordonnées listées ci-dessus : le numéro de la Feuille, le numéro de la Ligne et le numéro de la Colonne (ci-dessous, vous pouvez voir quelques exemples d'utilisation).
Remarque importante : n'oubliez pas que, par défaut, le décompte des valeurs d'un Array commence par 0. Donc si vous voulez voir le premier élément d'un Array, il faut "demander l'élément n°0"!
Code VBA "prêt à l'emploi"- Classeur en Array()
Vous pouvez simplement copier/coller le code suivant dans votre projet VBA. Vous n'avez besoin de rien d'autre pour l'utiliser.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
Public Function ClasseurVersTableArray(Classeur As Workbook) As Variant 'par Excel-Malin.com ( https://excel-malin.com ) Dim LigneMax As Single Dim ColonneMax As Single Dim WS As Worksheet Dim FeuillesNombre As Single Dim FeuillesCompteur As Single Dim LignesCompteur As Single Dim ColonnesCompteur As Single Dim TableTemporaire As Variant On Error GoTo ClasseurVersTableArrayErreur 'trouver la taille des 3 dimensions de la Table (Array): FeuillesNombre, LigneMax, ColonneMax LigneMax = 0 ColonneMax = 0 FeuillesNombre = Classeur.Worksheets.Count For Each WS In Classeur.Worksheets If WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row > LigneMax Then _ LigneMax = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row If WS.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column > ColonneMax Then _ ColonneMax = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column Next WS ReDim TableTemporaire(FeuillesNombre - 1, LigneMax - 1, ColonneMax - 1) 'Remplir la Table temporaire avec les valeurs du Classeur FeuillesCompteur = 0 For Each WS In Classeur.Worksheets For LignesCompteur = 0 To LigneMax - 1 For ColonnesCompteur = 0 To ColonneMax - 1 TableTemporaire(FeuillesCompteur, LignesCompteur, ColonnesCompteur) = WS.Cells(LignesCompteur + 1, ColonnesCompteur + 1).Value Next ColonnesCompteur Next LignesCompteur FeuillesCompteur = FeuillesCompteur + 1 Next WS 'assigner la Table/Array temporaire (TableTemporaire) en tant que résultat de la fonction ClasseurVersTableArray = TableTemporaire Exit Function ClasseurVersTableArrayErreur: ClasseurVersTableArray = CVErr(xlErrValue) End Function |
Comme vous pouvez le constater, la fonction utilise le code VBA pour trouver la dernière cellule non-vide de chaque Feuille et c'est la cellule la plus "éloignée" qui sert de repère pour déterminer les dimensions de la Table.
Exemple pratique d'utilisation de la fonction VBA ClasseurVersTableArray()
Pour illustrer comment ce code peut être utilisé et comment vous pouvez extraire les valeurs du Tableau, j'ai écrit la petite procédure VBA qui suit.
Elle charge le contenu du Classeur actif dans une Table/Array appelé "MonArray" puis il affiche plusieurs valeurs dans le MessageBox :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub TestClasseurVersTable_Array() 'par Excel-Malin.com ( https://excel-malin.com/ ) On Error GoTo TestErreur Dim MonClasseur As Workbook Set MonClasseur = ActiveWorkbook Dim MonArray As Variant MonArray = ClasseurVersTableArray(MonClasseur) Dim DonneeRecherchee As String DonneeRecherchee = "Valeur de: 1ère feuille, 2ème ligne, 4ème colonne = " MsgBox DonneeRecherchee & """" & MonArray(0, 1, 3) & """" DonneeRecherchee = "Valeur de: 2ème feuille, 10ème ligne, 1ère colonne = " MsgBox DonneeRecherchee & """" & MonArray(1, 9, 0) & """" DonneeRecherchee = "Valeur de: 9ème feuille, 4ème ligne, 15ème colonne = " MsgBox DonneeRecherchee & """" & MonArray(8, 3, 14) & """" Exit Sub TestErreur: MsgBox "Le point de données demandé (" & DonneeRecherchee & ") n'est pas disponible dans le Classeur importé." End Sub |
Conclusion
Ceci est le premier des codes VBA concernant les Tables (Arrays) que je vous propose. D'autres suivront. Les Tables / Arrays sont un concept certes un peu abstrait mais la rapidité et la facilité de travail avec des données qui en résulte vaut vraiment la peine de s'y intéresser de plus près!
Si vous le souhaitez, vous pouvez consulter la page sur le site de Microsoft dédiée aux Arrays.
Pour aller plus loin en Excel et VBA
Et pour finir, voici quelques articles qui pourraient rendre votre travail avec Excel et VBA plus efficace…
- LA liste de toutes les fonctions VBA
- Liste de toutes les fonctions Excel (plus de 500 fonctions!)
- Comment utiliser RECHERCHEV en VBA?
- Tout sur les Tableaux Croisées Dynamiques Excel (grand tutoriel)
- Excel Online – utiliser Excel en ligne gratuitement (oui, c'est possible!)
- Les outils pratiques en Excel à télécharger gratuitement
- Débloquer VBA dans les fichiers en provenance d'Internet
