VBA - Transformer un Classeur Excel en Array (Table) 3D

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.

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

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 :

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

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.