Comment introduire des données dans un array et comment restituer ces données vers une feuille de calcul.

Vous pouvez télécharger le classeur fc-pap-arrays qui illustre les exemples présentés sur les pages pas à pas traitant des arrays

Attention, le code présenté dans ce tutoriel et dans le classeur exemple a été écrit et vérifié avec la version 2010 d'excel. Certaines propriétés peuvent ne pas fonctionner avec des versions antérieures, et avec les versions Mac d'excel antérieures à 2011.
Merci d'utiliser les commentaires pour poser des questions directement en rapport avec CE tutoriel EXCLUSIVEMENT et de poser les questions sur vos développements particuliers sur le forum microsoft answers excel.

Remplir un array

On peut remplir un array de différentes façons :

• Manuellement.
• En utilisant une boucle
• A partir des données d'une feuille excel
• A partir d'un autre array

Entrée manuelle

On assigne successivement à chaque index du tableau la valeur souhaitée :
Dim Tblo(4) as string
Tblo(1) = "janvier"
Tblo(2) = "février"
Tblo(3) = "mars"
Tblo(4) = "avril"

On peut également utiliser la fonction array :
Attention, l'utlisation de cette fonction fait que le tableau créé est OBLIGATOIREMENT de type variant. Le typer autrement renvoie une erreur (pas très explicite qui plus est !)

Dim Tblo as variant 'ou simplement Dim Tblo
Tblo = array("janvier", "février", "mars", avril")
'on pourrait également écrire
'Tblo = array(1, 2, 3, 4)
'Tblo = array (1, 3.14116, "Mardi")

On voit dans cet exemple qu'à l'intérieur de l'array, tous les éléments ne sont pas nécessairement de même type (integer, long, string).

Utilisation d'une boucle

Pour un tableau à une dimension :

for a = 1 to 10
   a = a + 1
   Tblo(i) = a
next a

Pour un tableau à 2 dimensions, on utilisera 2 boucles imbriquées. La taille de l'array est soit définie lors de la déclaration soit redimensionnée dans la boucle mais dans ce cas on ne peut modifier au fur et à mesure que la seconde dimension :
Dim Tblo(100,40)

For i = 1 To 100
        For j = 1 To 40       
            Tblo(i, j) = 12*i + 4*j
        Next j
   Next i

Comment vérifier que l'array contient bien ce que l'on souhaite ? Le plus simple est d'utiliser la fenêtre d'exécution rapide . Lorsque vous êtes dans l'éditeur VBA, ctrl+G vous permet d'afficher cette fenêtre. Vous pouvez la déplacer, par exemple pour la mettre verticalement, ce qui suivant le type de données facilite la visualisation de vos données.
Pour un array à deux dimensions, il faut boucler sur les deux dimensions pour afficher tous les éléments.
Vous pouvez également demander l'affichage des index inférieurs et supérieurs des dimensions du tableau pour vérifier que vous avez bien entré tous ce que vous souhaitiez.

'affiche les valeurs de l'array dans la fenêtre d'exécution
Debug.Print UBound(Tblo, 1) 'donne le nombre d'éléments dans la première dimension
Debug.Print UBound(Tblo, 2) 'donne le nombre d'éléments dans la seconde dimension
Debug.Print LBound(Tblo, 1) 'donne l'index du premier élément de la première dimension
Debug.Print LBound(Tblo, 2) 'donne l'index du premier élément de la seconde dimension

For a = 1 To UBound(Tblo, 1)
    ValLign = ""
    For b = 1 To UBound(Tblo, 2)
    ValLign = ValLign & Tblo(a, b) & Chr(9)
    Next b
    Debug.Print ValLign
Next a
End Sub

A partir des données de la feuille

Souvenez vous que même si les données de la feuille à copier dans l'array sont sur une seule ligne ou une seule colonne, l'array aura toujours obligatoirement 2 dimensions représentant dans l'ordre les lignes et les colonnes.
On peut bien sur faire une boucle pour recopier les données de la feuille vers un array. Mais il y a une méthode beaucoup beaucoup plus rapide et qui permet de faire cela en une fois :

Quand on connait la plage de départ par exemple la plage A1:A100

Dim Tblo(1 To 100, 1 To 1)
tblo() = Sheets("mafeuille").Range("A1:A100").Value

Quand la taille de la plage est variable :

Sub ex1feuilleversarray()
Dim Tblo()
Dim a As Integer, b As Integer

With ActiveSheet.Range("a1").CurrentRegion
    ReDim Tblo(.Rows.Count, .Columns.Count)
    Tblo = .Value
End With

Cette méthode permet de transférer en une seule opération un très grand nombre de valeurs dans l'array.

Transpose

On peut dans certains cas avoir besoin d'inverser les lignes et les colonnes.
L'instruction transpose le permet :

  remplacer

Tblo = range("A1:C3").Value

par

Tblo = Application.WorksheetFunction.Transpose(range("A1:C3")).Value

Attention au nombre de lignes et de colonnes quand on fait une transposition dans l'idée de transférer ensuite dans la feuille : il faut que le nombre de lignes et de colonnes soit compatible avec la sortie.

A partir d'un array

Copie d'un array dans un autre

Ceci impose que les deux arrays soient définis avec un type  identique et de même dimension. Dans l'exemple ci-dessous, définir le tableau d'arrivée as string renverra une erreur.

Sub copiearray1()
Dim TbloDep() As Variant
Dim TbloArr() As Variant

ReDim TbloDep(1 to 4)
TbloDep = array("bleu", "rouge", "vert", "jaune")

ReDim TbloArr(1 to 4)
TbloArr = TbloDep

Debug.Print TbloArr(1) & " - " & TbloArr(2) & " - " & TbloArr(3) & " - " & TbloArr(4)
End Sub

Cette méthode est extrêmement rapide. Elle permet par exemple de travailler sur une copie du tableau d'origine pour y faire des traitements.

Si la dimension du tableau de départ évolue au cours de la procédure, on peut remplacer l'instruction

ReDim TbloArr(1 to 4)

par

ReDim TbloArr(LBound(TbloDep) to UBound(TbloDep))

Si la taille de l'array d'arrivée est plus petite que celle de celui départ, l'array d'arrivée sera tronqué.

Boucle sur les valeurs d'un array pour le copier dans un autre

La méthode précédente ne fonctionne pas avec toutes les versions d'excel (notamment pas avec le VBA5 présent sur les plus anciennes versions d'excel PC et sur les versions MAc antérieures à 2011)
On peut dans ce cas copier les éléments d'un tableau dans un autre 1 à 1. Ceci permet plus de souplesse du point de vue des types de données. Il faut cependant que les types soient compatibles entre eux. Par exemple on ne pourra pas copier un élément de type string dans un array de type integer. Un integer pourra être mis dans un array de type long.
Voici un exemple permettant de copier un array à deux dimensions dans un autre, élément par élément :

ReDim TbloArr(UBound(TbloDep, 1), UBound(TbloDep, 2))
For i = LBound(TbloDep, 1) To UBound(TbloDep, 1)
    For j = LBound(TbloDep, 2) To UBound(TbloDep, 2)
    'ici on peut ajouter des calculs ou des tests conditionnels
        TbloArr(i, j) = TbloDep(i, j)
    Next j
Next i

Transférer les données d'un array dans la feuille de calcul.

Reprenons l'array contenant les 12 mois de l'année. Si nous voulons le copier dans une feuille, l'instruction

Sheets("intro").Range(Cells(1, 1), Cells(1, UBound(Tblo))).Value = Tblo

copie les valeurs dans les cellules A1 à L1, c'est à dire sur une ligne.
Si vous voulez que les mois soient dans la colonne A, il faut transposer l'array :

'Sheets("intro").Range(Cells(1, 1), Cells(UBound(Tblo), 1)).Value = Application.WorksheetFunction.Transpose(Tblo)

Vous pourriez évidemment aussi boucler sur les éléments du tableau avant de les transférer dans la feuille mais c'est beaucoup plus long.

Pour un tableau à deux dimensions, il suffit de sélectionner une plage de cellules à plusieurs lignes/colonnes :

Sheets("ex1").Range(Cells(1, 9), Cells(f, 13)).Value = Application.WorksheetFunction.Transpose(Tblo1)

La plage de cellules sur la feuille peut bien entendu être définie à l'aide de variables. Nous en verrons des exemples dans les pages suivantes.
Il faut que la plage d'arrivée ait la même "taille" que l'array. et toutes les coordonnées de la plage doivent être définies. On ne peut pas juste indiquer la cellule en haut à gauche de la plage comme on le fait pour un copier coller par exemple.
Une façon simple de définir la plage c'est d'indiquer la cellule en haut à gauche puis d'utiliser l'instruction resize.

Range("G2").Resize(UBound(Tblo, 1), UBound(Tblo, 2)).Value = Tblo

Exemple commenté

Dans cet exemple, on part d'une plage de données qui contient 3 colonnes (A, B, C). La troisième colonne contient parfois des cellules vides et il y a des lignes entières qui sont vides.
Pour chaque ligne qui contient une donnée dans la colonne C, on veut concaténer les colonnes A et C.
On pourrait faire cela avec une boucle sur les cellules de la feuille en ne remplissant le tableau qu'avec les données des lignes dont la clonne C n'est pas vide.  Il est beaucoup plus rapide de récupérer toute la plage avec ses cellules vides dans un array puis de le traiter.
Voici deux façons de faire ensuite :

• soit on traite les données au moment de les écrire avec une boucle dans la feuille,
• soit on les traite en les copiant dans un nouvel array qu'onréinjecte en une seule fois dans la feuille.

Première méthode

Sub tableau()
'MichD, excel-downloads.com, novembre 2011
Dim T As Variant, A As Long, B As Long

With Worksheets("Feuil1")
   'Remplir le tableau des données de la plage
    T = .Range("a1:d" & Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

'Boucle sur le tableau

For A = LBound(T, 1) To UBound(T, 1)
   'Vérifier si les colonnes 1 et 3 du tableau ne sont pas vides
   'Colonnes A       'colonne C
   If T(A, 1) <> "" And T(A, 3) <> "" Then
     's'ils les 2 colonnes ne sont pas vide, concaténation
     'des colonnes A & C et résultat dans colonne D
     T(A, 4) = T(A, 1) & ", " & T(A, 3)
   End If
Next A

'Recopie du tableau dans la feuil2

With Worksheets("Feuil2")
   .Range("A1").Resize(UBound(T, 1), UBound(T, 2)) = T
End With

End Sub

Deuxième méthode

Une autre façon de procéder est de mettre le résultat de la concaténation dans un autre tableau.

Sub tableau1()
'MichD sur Excel-downloads, novembre 2011

Dim T As Variant, T2 As Variant, A As Long, B As Long

With Worksheets("Feuil1")
   'Remplir le tableau des données de la plage
   With .Range("a1:d" & Cells(Rows.Count, 1).End(xlUp).Row)
     T = .Value
     'Dimensionner le deuxième tableau selon le nombre de lignes
     'maximale de la plage de cellules.
     ReDim T2(1 To .Rows.Count)
   End With
End With

'Boucle sur le tableau

For A = LBound(T, 1) To UBound(T, 1)
   'Vérifier si les colonnes 1 et 3 du tableau ne sont pas vides
   'Colonnes A       'colonne C
   If T(A, 1) <> "" And T(A, 3) <> "" Then
     's'ils les 2 colonnes ne sont pas vide, concaténation
     'des colonnes A & C et résultat dans colonne D
   T2(A) = T(A, 1) & ", " & T(A, 3)
   End If
Next A

'Recopie du tableau T2 qui contient seulement les données
'de la colonne 4 dans le tableau des données de la feuille Feuil1

With Worksheets("Feuil2")
   .Range("D1").Resize(UBound(T2, 1)) = Application.Transpose(T2)
End With

End Sub

NB dans ces deux procédures, si il y a des lignes vides dans le tableau de départ, le tableau d'arrivée contiendra les mêmes lignes vides.
Si vous voulez supprimer ces lignes, il vous suffit de modifier les boucles en ajoutant une variable pour l'incrémentation :

For A = LBound(T, 1) To UBound(T, 1)
N=N+1
   If T(A, 1) <> "" And T(A, 3) <> "" Then
     's'ils les 2 colonnes ne sont pas vide, concaténation
     'des colonnes A & C et résultat dans colonne D
   T2(N) = T(A, 1) & ", " & T(A, 3)
   End If
Next A