VBA - Collections et tableaux

Auteur: Michel Berthiaume
Reproduction interdite sans autorisation

Une collection est un groupe d'instances d'une même classe (Wikipedia).

Exemples:

un classeur Excel contient une collection de feuilles Excel
une feuille Excel contient une collection de plages Excel
une plage Excel contient une collection ... de plages Excel!

La propriété Count contient le nombre d'objets de la collection.

On peut manipuler les objets d'une collection de diverses façons:

par son numéro:
Sheets(1) est la 1ère feuille du classeur actif
par son nom:
Sheets("Provinces") est la feuille Provinces du classeur actif.
en utilisant l'instruction For Each:
Dim sFeuille As Worksheet
For Each sFeuille In Worksheets
Next

qui est l'équivalent de:

Dim sFeuille As Worksheet, i As Long
For i = 1 To Worksheets.Count
Set sFeuille = Worksheets(i)
Next

Tableaux

On peut concevoir un tableau VBA comme une collection de variables VBA, mais comme les tableaux existaient en Basic
bien avant l'introduction de la programmation objet, le traitement des tableaux diffère du traitement des collections.

Un tableau VBA doit être déclaré.

Le nombre d'éléments d'un tableau peut être fixé lors de la déclaration (nombre fixe d'éléments) ou lors de l'exécution (tableau dynamique).

Exemples:

Dim tTableau(1 to 15) As Currency '15 éléments de type Currency
Dim tTableau(15) As Currency '16 éléments de type Currency
Dim tTableau() As Currency
...
Redim tTableau(1 to 15) '15 éléments de type Currency
Dim tTableau(1 To 5, 1, To 5) As Currency '25 éléments de type Currency

La seule façon d'utiliser un élément d'un tableau est d'utiliser son adresse:

tTableau(1) 'Le premier élément du tableau
tTableau(10) 'Le 10e élément du tableau
tTableau(1, 5) 'Le 1er élément de la 5e colonne

Lorsqu'un tableau a une dimension, comme tTableau(1 to 15), c'est un vecteur, et on le représente comme une liste.

Lorsqu'un tableau a deux dimensions, comme tTableau(1 to 5, 1 To 5), c'est une matrice, et on le représente comme un tableau,
le premier indice étant le numéro de ligne, le second le numéro de colonne.

Lorsqu'un tableau a trois dimensions, comme tTableau(1 to 5, 1 To 5, 1 To 5), c'est un cube, et on ne le représente pas.
Le premier indice est le numéro de ligne, le second le numéro de colonne et le troisième le numéro de rangée (profondeur).

Un tableau peut avoir plus de 3 dimensions, mais les applications sont rares.

Instructions de tableaux

Certaines instructions VBA sont spécifiques aux tableaux:

ReDim [Preserve] nom(indices) [As type] [, nom(indices) [As type]] . . .

Où:

Preserve Indique que les données existante avant de redimensionnement doivent être conservées. Rarement utilisé.
nom Nom de la variable.
indices Dimensions du tableau Utilisez la forme:
[Min To] max[,[min To] max] ...
Où min est la valeur plancher de l'indice et max la valeur plafond
Valeur par défaut de min: 0 et de max: 10
type Type de données de la variable.

Utilisez ReDim pour les tableaux dont les dimensions ne sont pas précisées dans l'instruction Dim.

LBound(NomTableau[, dimension])

Où

NomTableau Est le nom d'un tableau VBA.
dimension Est le numéro de la dimension dont LBound retournera la valeur minimale. Défaut 1.

LBound est utilisé pour obtenir la valeur minimale de la dimension d'un tableau VBA.

UBound(NomTableau[, dimension])

Où

NomTableau Est le nom d'un tableau VBA.
dimension Est le numéro de la dimension dont UBound retournera la valeur maximale. Défaut 1.

UBound est utilisé pour obtenir la valeur maximale de la dimension d'un tableau VBA.

Ces trois instructions sont utiles lorsqu'on utilise un tableau dynamique, c'est à dire dont la dimension n'est connue
qu'au moment de l'exécution (comme une liste d'étudiants ou d'items d'une facture),
par opposition à un tableau statique, dont le nombre d'éléments est connu au moment
de l'écriture du programme (une liste de noms de mois, par exemple).

Plage ou tableau?

Si on a une liste de valeurs à manipuler ou produire en VBA Excel, doit-on le faire en utilisant un plage Excel ou un tableau VBA?

Avantages de la plage Excel:

plus facile à déboguer car les valeurs sont visibles dans Excel
on peut utiliser les fonctions Excel

Avantages du tableau VBA:

beaucoup plus rapide si on a un nombre considérable d'éléments
le code est plus facilement réutilisable en Access, Word, VB6 ou même VB.net.

Copier des données entre Excel et VBA

Vous pouvez copier directement une plage Excel dans un tableau VBA dynamique de type Variant,
sans même utiliser l'instruction ReDim:

Dim vTableau() As Variant
vTableau = Range("Plage")

a l'effet suivant:

vTableau est redimensionné au nombre de lignes et de colonnes de la plage Plage
le contenu de plage est copié dans vTableau

Bien noter que vTableau doit être un tableau dynamique de type Variant.

Vous pouvez copier directement un tableau VBA dans une plage Excel:

Range(Cells(1, 1), Cells(UBound(vTableau, 1), UBound(vTableau, 2))) = vTableau

copie tout le tableau vTableau dans la plage de même dimension commençant en A1 de la feuille active.
vTableau peut être de n'importe quel type.

Exemples

Sub ExemplesTableaux()
'Auteur: Michel Berthiaume
'Exemples de manipulation de collection et de tableau
Dim cNombres() As Currency
Dim vTableau() As Variant
Dim lLigne As Long, lColonne As Long
Dim tDépart As Date

With Worksheets("Tableaux")

    Cells.Clear

    'Créer une matrice 10000x100 de nombres dans Excel
    tDépart = Now 'Date et heure du départ
    For lLigne = 1 To 10000
        For lColonne = 1 To 100
            .Cells(lLigne, lColonne) = lLigne + lColonne
        Next
    Next
    MsgBox "Création de matrice Excel en " & Round((Now - tDépart) * 24 * 60 * 60, 2) & " secondes"

    'Créer une matrice 10000x100 de nombres dans VBA
    ReDim cNombres(1 To 10000, 1 To 100)
    tDépart = Now 'Date et heure du départ
    For lLigne = 1 To 10000
        For lColonne = 1 To 100
            cNombres(lLigne, lColonne) = lLigne + lColonne
        Next
    Next
    MsgBox "Création de matrice VBA en " & Round((Now - tDépart) * 24 * 60 * 60, 10) & " secondes"

    'Copie d'une matrice Excel dans un tableau VBA
    'ATTENTION: le tableau DOIT ÊTRE DE TYPE Variant
    'Le tableau est automatiquement redimensionné aux dimensions de la plage
    vTableau = Range(.Cells(1, 1), .Cells(1000, 100))

    'Copie d'une matrice VBA dans un tableau Excel
    .Cells.Clear
    .Range(.Cells(1, 1), .Cells(UBound(vTableau, 1), UBound(vTableau, 2))) = vTableau

End With

End Sub

Suite: Dialogues et formulaires