Astuce 1 : Copier des cellules avec VBA
Il s’agit du premier billet de blog d’une série de sept pour approfondir plusieurs techniques qui permettent d’accélérer le traitement des données dans Excel VBA. Ainsi, vous apprendrez comment gagner du temps en rendant des cellules identiques au lieu de copier des cellules/plages séparément.
- Copier des cellules avec VBA
- Ajouter des données à un tableau
- Ouvrir un fichier et transférer les données vers le fichier cible
- Ouvrir une série de fichiers et transférer les données vers le fichier cible
- Sauvegarder des données dans un jeu d’enregistrements
- Lire les données de plusieurs fichiers avec un jeu d’enregistrements sans ouvrir les fichiers
- Réaliser un jeu d’enregistrement, sur la base des données de 2 feuilles de calcul différentes
Le système d’exploitation (Windows) gère le copier-coller de données, ce qui signifie qu’Excel doit, pour chaque action, communiquer avec une application externe. Lorsque vous n’effectuez cette opération qu’une seule fois, vous le remarquerez à peine. Mais si vous devez répéter le copier-coller dans une boucle plusieurs fois, le temps d’exécution augmente systématiquement. Si vous créez deux cellules identiques, Excel échangera simplement les données entre 2 de ses propres blocs. Une action super rapide qui reste efficace même si les répétitions sont très nombreuses.
Dans ce blog, nous partons d’une matrice de données composée de 10 000 lignes et de 5 colonnes. L’objectif consiste à copier les valeurs de chaque ligne dans une nouvelle zone de données verticale. La ligne suivante doit se positionner sous le premier résultat. On obtient donc une liste verticale de 50 000 cellules dans 1 colonne comme le montre l’exemple ci-dessous.
Dans Excel, vous pouvez le faire manuellement en copiant la première ligne (verte) puis en choisissant « transposer » via « collage spécial ».
Si vous enregistrez cette action en VBA, vous obtenez le résultat suivant :
Range(« A1:E1 »).Select
Selection.Copy
Range(« G1 »).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Une version corrigée de ce code est :
Range(« A1:E1 »).Copy
Range(« G1 »).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Avec VBA, vous pourrez répéter cette action 10 000 fois pour chaque ligne de la plage. Dans les étapes suivantes, je vous explique comment effectuer cette action avec VBA. Un temps d’exécution indicatif est affiché à chaque fois afin de pouvoir arriver, avec une adaptation du code VBA, à un résultat toujours plus rapide.
Le système d’exploitation (Windows) gère le copier-coller des données. Dans cet exemple, vous devrez donc communiquer 10 000 fois avec une application externe. Copier-coller une cellule/zone ne dure qu’une fraction de seconde. Copier 5 cellules prend à peu près le même temps que copier 10 000 cellules, mais si vous demandez 10.000 fois de copier 5 cellules, vous devez multiplier le temps d’exécution par 10 000.
Dans l’exemple ci-dessous, la macro enregistrée est exécutée 10 000 fois. Quelques variables sont prévues pour faciliter la tâche des compteurs de lignes/colonnes. Le temps d’exécution mentionné est déterminé par mon ordinateur portable et sert uniquement à montrer le rapport entre les différents exemples. Le temps d’exécution sur votre propre ordinateur peut varier, mais le rapport doit être identique.
Sub Ex1_CopyTranspose()
Dim xRow As Long ‘ RowCounter for row 1 till 10000
Dim dRow As Long ‘ Destination Row
Debug.Print Now
dRow = 1
For xRow = 1 To 10000 Step 1
Range(Cells(xRow, 1), Cells(xRow, 5)).Copy
Cells(dRow, 7).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
dRow = dRow + 5
Next xRow
Debug.Print Now
‘Code Execution : 06:05 min
End Sub
Remarque: Le code « Debug.printNow » indique le temps du début à la fin de l’exécution. Pour voir le résultat, affichez la « Fenêtre Direct » (Debug Window) dans Visual Basic. Vous pouvez le faire à l’aide de la combinaison de touches « CTRL + G ».
Dans ce premier exemple, Excel a besoin de plus de 6 minutes pour coller toutes les lignes dans une colonne séparée, les unes sous les autres.
Dans l’exemple suivant, nous ajoutons 2 lignes de code connues qui obligent Excel, lors de l’exécution du code, à ne pas rafraîchir l’écran, mais à attendre que tout soit terminé. Vous gagnez ainsi du temps pour l’exécution de votre code VBA. La raison pour laquelle je ne l’ajoute que maintenant est plus claire dans les exemples suivants.
Vous le faites avec le code : Application.ScreenUpdating = False au début d’un (morceau) de code et Application.ScreenUpdating = True à la fin d’un (morceau) de code.
Sub Ex2_CopyTranspose()
Dim xRow As Long ‘
Dim dRow As Long ‘ Destination Row
Debug.Print Now
Application.ScreenUpdating = False
dRow = 1
For xRow = 1 To 10000 Step 1
Range(Cells(xRow, 1), Cells(xRow, 5)).Copy
Cells(dRow, 7).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
dRow = dRow + 5
Next xRow
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution : 01:11 min
End Sub
Attention: Ce code fonctionne au niveau « application » et si vous oubliez d’activer (true) « refresh » dans le code, Excel n’effectuera plus aucun changement à l’écran. Vous pouvez également exécuter le texte « Application.ScreenUpdating = True » depuis la « fenêtre Direct » (CTRL+G en VBA).
En ajoutant « Screenupdating », nous obtenons un gain de temps appréciable. Le temps d’exécution passe d’environ 6 minutes 5 secondes à 1 minute 11 secondes. Notez que dans ce cas, nous communiquons encore 10 000 fois avec le « presse-papier ».
Que se passe-t-il maintenant si nous demandons à Excel de faire correspondre les cellules 1 à 1. La cellule G1 correspond à la cellule A1 et ensuite la cellule G2 correspond à la cellule A2, et ainsi de suite jusqu’à ce que les 50 000 cellules soient à l’emplacement correct. Dans cet exemple, vous ne devez pas communiquer avec le « presse-papier » externe et vous travaillez directement avec les blocs d’Excel. Le gain de temps ainsi obtenu est considérable.
Remarque: Cette méthode comporte un avantage supplémentaire : seul le contenu de la cellule est transféré dans l’autre cellule. Le format n’est pas repris.
Cette fois, nous ajoutons des variables supplémentaires pour faire une boucle par rangée, de gauche à droite (colonne 1 à 5).
Sub Ex3_CopyTranspose()
Dim xRow As Long ‘ 1 to 10000 (Top Bottom)
Dim xCol As Long ‘ 1 to 5 (Left Right)
Dim dRow As Long ‘ Destination Row
Debug.Print Now
dRow = 1
For xRow = 1 To 10000 Step 1
For xCol = 1 To 5
Cells(dRow, 7) = Cells(xRow, xCol)
dRow = dRow + 1
Next xCol
Next xRow
Debug.Print Now
‘Code Execution : 00:02 to 00:03 sec
End Sub
Le temps d’exécution de ce code prend de 2 à 3 secondes. Dans ce cas, vous n’obtenez pas un gain de temps supplémentaire en ajoutant le « screenupdating » (rafraîchissement d’écran). J’ai testé l’exemple avec 20 000 lignes et le temps d’exécution était toujours identique. Personnellement, j’inclurai quand même toujours ces lignes dans le code, car sur des ordinateurs plus lents, cela peut quand même faire une différence.
Sub Ex4_CopyTranspose()
Dim xRow As Long ‘ 1 to 10000 (Top Bottom)
Dim xCol As Long ‘ 1 to 5 (Left Right)
Dim dRow As Long ‘ Destination Row
Debug.Print Now
Application.ScreenUpdating = False
dRow = 1
For xRow = 1 To 10000 Step 1
For xCol = 1 To 5
Cells(dRow, 7) = Cells(xRow, xCol)
dRow = dRow + 1
Next xCol
Next xRow
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution : 00:02 to 00:03 sec
End Sub
Prochain blog : Ajouter des données à un tableau (Blog 2/7)
Si vous avez contracté le virus « Excel » voire « Power BI » après avoir lu ce billet de blog, n’hésitez pas à consulter notre offre de formations ouvertes Excel. Qui sait, peut-être pourrions-nous bientôt nous rencontrer à Anvers ou à Bruxelles ?
