Voici déjà le deuxième billet de blog d’une série de 7 pour approfondir un certain nombre de techniques qui permettent d’accélérer le traitement des données dans Excel VBA.
- 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
Ce blog montre le temps qui peut être gagné lorsque vous devez ajouter un grand nombre de cellules à un tableau. Si vous ajoutez un grand volume de données à un tableau via un code VBA, vous remarquerez assez vite un retard d’exécution. En effet, pour chaque cellule que vous ajoutez, Excel doit redéfinir la structure du tableau, mettre à jour les formules et élaborer la structure graphique. Et ce dernier point n’est pas un point fort d’Excel.
Dans ce blog, nous partons d’un tableau avec un nombre limité de lignes. Nous allons ajouter une nouvelle ligne à ce tableau. Nous répéterons cette action plusieurs fois au moyen d’une boucle pour voir quel effet cela a sur le temps d’exécution du code VBA. Pour terminer, nous verrons une méthode intéressante qui peut accélérer l’exécution.
L’exemple ci-dessus montre le tableau (plage B4 à O9) et les données de la ligne 2 que nous allons à chaque fois ajouter au tableau.
Dans le blog précédent, nous avons déjà montré que des « copier-coller » à répétition ralentissent le temps d’exécution du code. C’est pourquoi nous utilisons dans le premier exemple une méthode simple pour ajouter la ligne en dehors de la copie de tableau à tableau. Dans l’exemple utilisé, des valeurs fixes ont été utilisées au lieu de variables.
Sub Macro4()
Range(Cells(10, 2), Cells(10, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
End Sub
Lorsque vous ajoutez des données à un tableau, Excel doit également vérifier la mise en forme de la ligne et les éventuelles formules présentes. Le tableau doit également adapter l’adresse sous-jacente du tableau à la nouvelle zone. Cela se produit chaque fois que vous ajoutez des données au tableau Dans l’exemple suivant, ceci est illustré par l’ajout de la même ligne 10 000 fois.
Sub Add10KRows_1()
Dim xRow As Long
Debug.Print Now
For xRow = 10 To 10000
Range(Cells(xRow, 2), Cells(xRow, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
Next xRow
Debug.Print Now
‘Code Execution 01:16
End Sub
Le temps d’exécution de ce code est de 1 min 16 sec.
Avec l’ajout de « ScreenUpdating » (Voir blog 1 de cette série), ce temps passe sous la minute (57 secondes).
Sub Add10KRows_2()
Dim xRow As Long
Debug.Print Now
Application.ScreenUpdating = False
For xRow = 10 To 10000
Range(Cells(xRow, 2), Cells(xRow, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
Next xRow
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:57
End Sub
Dans ce cas, le gain de rapidité n’est pas aussi spectaculaire que dans le premier billet de blog de cette série. C’est principalement imputable au tableau lui-même.
Remarque: N’essayez pas, comme dans le blog précédent, de copier toutes les cellules une par une dans le tableau. Cela demanderait 140 000 traitements dans le tableau. Sur mon ordinateur portable, cela a duré très longtemps : de 10 à 15 minutes. Ensuite, Excel continue à lutter avec les commandes les plus simples et le redémarrage de l’application est la seule solution.
C’est pourquoi nous allons utiliser une autre méthode : nous allons placer le même nombre de cellules (10 000 lignes x 14 colonnes) en dessous du tableau, puis nous déplacerons le tout dans le tableau. Ainsi, nous n’ajouterons quelque chose au tableau qu’une seule fois au lieu de 10 000 ou de 140 000 fois.
Cette fois, les données seront placées dans la ligne 15. C’est 5 lignes plus bas que la dernière ligne du tableau. C’est pourquoi Excel ne doit rien ajouter ou modifier au tableau. En outre, les cellules sont transférées une par une. Comme il est apparu dans le premier blog, il semble que ce soit la méthode la plus rapide.
Sub Add10KRows_3 ()
Dim x As Long, y As Long
Debug.Print Now
Application.ScreenUpdating = False
For y = 15 To 10015 Step 1
For x = 2 To 15 Step 1
Cells(y, x) = Cells(2, x)
Next x
Next y
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:07
End Sub
Cette méthode ne dure que 7 secondes, ce qui est considérablement plus rapide que la minute de l’exemple précédent. Nous n’avons qu’à compléter le code en ajoutant la nouvelle zone au tableau.
Sub CopyLoop()
Dim x As Long, y As Long
Debug.Print Now
Application.ScreenUpdating = False
For y = 15 To 10015 Step 1
For x = 2 To 15 Step 1
Cells(y, x) = Cells(2, x)
Next x
Next y
Range(« B15 »).CurrentRegion.Cut Destination:=Range(« B10 »)
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:07
End Sub
Maintenant, ce code prend à peu près le même temps parce que l’action copier/coller ne doit être effectuée qu’une seule fois et Excel ne doit mettre à jour le tableau qu’une seule fois.
Remarque: Si vous effectuez des traitements avec beaucoup de données dans VBA, il est préférable de fermer régulièrement Excel lors de l’écriture et du test du code, avant d’essayer une nouvelle exécution. Autrement, le temps d’exécution pourrait vous donner une fausse impression qui vous inciterait à faire le mauvais choix.
Prochain blog : Ouvrir un fichier et transférer les données vers le fichier cible. (Blog 3/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 deformations ouvertes Excel. Qui sait, peut-être pourrions-nous bientôt nous rencontrer à Anvers ou à Bruxelles ?
