[VBA] Boostez la vitesse d’exécution de vos macros en utilisant les « variables Tableau » 3/3

Retranscrire les données d’une variable sur la feuille Excel

Le dernier article de cette série s’attachera à retranscrire les données contenues dans une variable sur la feuille Excel.
Ce sujet a déjà été partiellement abordé dans les articles précédents à des fins d’exemples; nous allons maintenant étudier en détail les mécanismes de cette fonction.

Reprenons l’exemple de l’article précédent

Plage VT

Supposons que nous devions recopier ce tableau à partir de la cellule E1.
Une méthode consiste à enregistrer le tableau dans une variable nommée aa puis à recopier chaque valeur contenue dans la variable par l’intermédiaire de boucles entrelacées.

Option Base 1
Sub Boucle()
Dim aa As Variant
aa = Range("A1").CurrentRegion

For a = LBound(aa) To UBound(aa)
    For b = LBound(aa, 2) To UBound(aa, 2)
        Cells(a, b + 4) = aa(a, b)
    Next b
Next a
End Sub

La première boucle For a = LBound(aa) To UBound(aa) traite chaque ligne du tableau enregistré dans notre variable et le seconde, For b = LBound(aa, 2) To UBound(aa, 2), chaque colonne de chaque ligne.
Cette macro recopie le tableau, cellule par cellule; la méthode reste chronophage.

La seconde méthode enregistre le tableau dans la variable aa et retranscrit la variable immédiatement dans la feuille Excel.

Option Base 1
Sub Boucle2()
Dim aa As Variant, Tablo() As Variant
Dim c As Byte
aa = Range("A1").CurrentRegion

Range("E1").Resize(UBound(aa, 1), UBound(aa, 2)) = aa
End Sub

Il est nécessaire de définir avec précision les dimensions de la plage qui va accueillir les valeurs du tableau.
Nous savons que la commande Ubound(aa,1) nous permet de définir le nombre total de lignes de la variable et que le nombre total de colonnes est obtenu grâce à la commande Ubound(aa,2).
Il suffit donc de redimensionner la cellule E1 par le nombre de lignes et par le nombre de colonnes pour définir la plage d’accueil Range("E1").Resize(UBound(aa, 1), UBound(aa, 2)) et de coller l’information avec =aa.

Il est parfois nécessaire d’inverser le sens lignes/colonnes :

Range("E1").Resize(UBound(aa, 2), UBound(aa, 1)) = Application.Transpose(aa)

On inverse donc l’ordre des commandes de redimensionnement et l’on fait précéder la variable aa de la commande Application.Transpose qui indique à Excel d’inverser le sens lignes/colonnes.

On peut choisir de ne copier q’une ligne :

Range("E1").Resize(1, UBound(aa, 2)) = Application.Index(aa, 5)

C’est la fonction Index qui définit le classement d’un objet parmi une collection d’objets similaires, suivi du nom de la variable et du N° de ligne (ici, la ligne N° 5) qui nous permet de recopier une ligne bien précise.

Le principe est exactement le même pour recopier une colonne :

Range("E1").Resize(UBound(aa, 1), 1) = Application.Index(aa, , 2)

La colonne 2 est, ici, recopiée : la double virgule (aa, , 2) indique au programme qu’il sagit d’une colonne.

On peut aussi décider de copier une ligne en sens vertical :

Range("E1").Resize(UBound(aa, 2), 1) = Application.Transpose(Application.Index(aa, 5))

ou, à l’inverse, copier une colonne dans le sens horizontal :

Range("E1").Resize(1, UBound(aa, 1)) = Application.Transpose(Application.Index(aa, , 2))

Il est aussi possible de copier plusieurs lignes de la variable aa dans une variable tablo et retranscrire les lignes sélectionnées sur la feuille Excel; ici nous enregistrons les lignes paires dans le tablo :

For a = LBound(aa) To UBound(aa)'dela première à la dernière ligne du tableau dans la variable aa
    If a Mod 2 = 0 Then'si le N° de ligne est pair
        c = c + 1'incrémente la variable c de 1
        ReDim Preserve Tablo(c)'redimensionne le tablo
        Tablo(c) = Application.Index(aa, a)'enregistrer toute la ligne dans le tablo
    End If
Next a
Range("E1").Resize(UBound(Tablo, 1), UBound(aa, 2)) = Application.Transpose(Application.Transpose(Tablo))'transposition

Nous remarquons que la commande Application.Transpose est doublée : la ligne de code Range("E1").Resize(UBound(Tablo, 1), UBound(aa, 2)) = Tablo ne fonctionne pas.

Voici comment enregistrer certaines lignes (toujours les lignes paires) de la variable aa et transposer (inverser le sens lignes/colonnes) leur écriture :

c = 0
For a = LBound(aa) To UBound(aa)
    If a Mod 2 = 0 Then
        c = c + 1
        ReDim Preserve Tablo(c)
        Tablo(c) = Application.Index(aa, a)
    End If
Next a
Range("E1").Resize(UBound(aa, 2), UBound(Tablo, 1)) = Application.Transpose(Tablo)

Encore une fois, il nous suffit d’inverser les commandes de redimensionnement et d’appliquer la transposition.

Juste pour information, voici une alternative simple qui procède à l’inscription des colonnes voulues sur la feuille Excel sans les enregistrer dans la variable tablo au préalable.

c = 0
For a = LBound(aa, 2) To UBound(aa, 2)
    If a Mod 2 = 1 Then
        c = c + 1
        Cells(1, 4 + c).Resize(UBound(aa), 1) = Application.Index(aa, , a)
    End If
Next a

Voici le même résultat, en employant le tablo :

c = 0
For a = LBound(aa, 2) To UBound(aa, 2)
    If a Mod 2 = 1 Then
        c = c + 1
        ReDim Preserve Tablo(c)
        Tablo(c) = Application.Transpose(Application.Index(aa, , a))
    End If
Next a
Range("E1").Resize(UBound(aa, 1), UBound(Tablo, 1)) = Application.Transpose(Tablo)

Ici, nous usons d’un artifice pour pouvoir obtenir le résultat voulu : lorsque nous enregistrons nos colonnes dans le tablo, nous les enregistrons en tant que lignes, c’est pour cela que nous utilisons la commande de transposition une première fois dans la ligne de code Tablo(c) = Application.Transpose(Application.Index(aa, , a)) puis nous retransposons lors de l’inscription dans la feuille Excel les informations du tablo.

Enfin, pour faire une vraie transposition de certaines des colonnes retranscrites en lignes, il nous faut encore jongler avec la commande de transposition :

c = 0
For a = LBound(aa, 2) To UBound(aa, 2)
    If a Mod 2 = 1 Then
        c = c + 1
        ReDim Preserve Tablo(c)
        Tablo(c) = Application.Transpose(Application.Index(aa, , a))
    End If
Next a
Range("E1").Resize(UBound(Tablo, 1), UBound(aa, 1)) = Application.Transpose(Application.Transpose(Tablo))

Il nous faut faire une première transposition lors de l’enregistrement de la colonne dans le tablo, puis une double transposition lors de l’inscription sur la feuille Excel.

Nous venons de faire un tour assez complet des possiblités de retranscription des données d’une variable sur la feuille Excel.

Cette série d’articles est maintenant complète… certains concepts peuvent paraître un peu abstraits, au premier abord, mais avec un peu de pratique, les outils décrits dans ces 3 billets vous permettront de gérer rapidement et efficacement de grandes quantités de données à traiter.

Ci-dessous le fichier au format .xls contenant les exemples de cet article.
variables-tablo-3

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s