Il y a de nombreuses façons d’optimiser le code VBA pour Excel afin d’améliorer la performance et, conséquemment, le temps d’exécution. On peut regrouper ces trucs pour rédiger des macros plus rapides, en 2 grandes catégories:
- Les astuces de programmation qui ne dépendent pas réellement de la façon dont le code est bâti, mais plutôt de la compréhension du fonctionnement du VBA pour Excel;
- Les astuces liés à la structure du programme lui-même. Ici, on pense à du code qui est structuré de telle manière que son exécution sera plus ou moins longue.
Cet article vise donc à vous expliquer comment écrire des macros plus rapides.
Astuces de programmation pour des macros plus rapides
Mettre à Off tout ce qui peut être mis à Off!
Le premier truc, que bien des gens connaissent, est d’utiliser VBA afin d’éviter d’utiliser des fonctionnalités Excel qui prennent du temps d’exécution, l’exemple classique étant la mise à Off du rafraîchissement de l’écran en cours d’exécution. En effet, qui veut voir les écrans se promener en fou pendant que ça roule! Et ceux qui maîtrisent ce simple truc remarquent que cela accélère de beaucoup l’exécution du code VBA.
Cela étant dit, il y a bien d’autres fonctionnalités qui peuvent être modifiées afin d’accélérer l’exécution du code VBA. Voici donc les principales:
- Tourner à Off le rafraîchissement de l’écran: Comme expliqué plus haut, l’interaction avec l’écran gaspille beaucoup de temps et devrait être mis à OFF.
- Tourner à Off la mise à jour de la barre de statut: À chaque instant, Excel met à jour la barre de statut (la barre qui est affichée au bas de l’écran). Même si cette interaction avec l’écran est minimale, mettre ce rafraîchissement à OFF va sauver un peu de temps d’exécution.
- Gérer les options de calcul: Il existe 3 modes de calcul en Excel: Automatique, où tout est recalculé à chaque fois qu’une cellule est modifiée, Automatique sauf les tables de données, où tout est recalculé à l’exception des tables de données, ce qui peut déjà sauver du temps! et le mode Manuel, où aucun calcul n’est mis à jour tant que l’usager n’en fait pas la demande en appuyant sur F9. Si le code VBA interagit souvent en modifiant des cellules dans le fichier Excel (ce qui est habituellement le but!), un mode de calcul automatique peut consommer beaucoup de ressources pendant l’exécution car Excel recalcule souvent l’ensemble du chiffrier. En général, il est donc souhaitable de mettre l’option de calcul à Manuel et de forcer le recalcul, dans le code, aux instants appropriés en cours d’exécution de la macro VBA. Également, lorsque le code s’exécute, évitez d’avoir de nombreux fichiers Excel ouverts qui ne sont pas liés à votre code VBA, car un recalcul va se faire sur l’ensemble des fichiers qui sont ouverts.
- Gérer les événements: Excel interagit avec l’environnement Office et gère la relation avec des compléments (des add-in). Ces fonctionnalités surveillent donc en permanence la venue d’événements qui les impliquent et des messages peuvent même s’afficher qui demandent la participation de l’usager. Par exemple, si votre code VBA ouvre un fichier Excel pour en extraire des données et que, en cours d’exécution, Excel détecte que le fichier a été modifié, si vous fermez ce fichier dans le code VBA, Excel va vous demander si vous voulez sauvegarder les modifications. En mettant la gestion des événements à Off, cette question ne vous sera pas posée et Excel ne surveillera pas la venue de tels événements, économisant ainsi du temps d’exécution;
- Tourner à Off le recalcul des sauts de page: Finalement, quand une feuille est modifiée, Excel recalcule les sauts de page. Afin d’éviter que du temps d’exécution soit consacré à cette tâche, il faut donc mettre à Off le traitement des sauts de page. Il est à noter que cette configuration devrait être traitée dans chaque feuille concernée! Si de nombreuses feuilles ne sont que légèrement modifiées, la gestion du recalcul des sauts de page peut donc devenir laborieuse. Pour cette raison, je ne l’utilise pas moi-même. Toutefois, si une seule feuille est grandement modifiée par l’exécution du code VBA, il devient intéressant de l’utiliser.
Comment faire pour rédiger des macros plus rapides?
Dans le code VBA, il faut donc, au début d’une procédure, conserver le statut de ces différentes configurations pour ensuite les forcer à OFF. Et, à la fin de la procédure, on les remet dans leur état initial.
Donc, votre code VBA devrait ressembler à:
Vous souhaitez créer et personnaliser des outils de gestion automatisés dans Excel pour mieux répondre aux besoins spécifiques de votre entreprise ? Apprenez à les développer et les présenter avec des interfaces conviviales pour les usagers en suivant nos formations en programmation de macros VBA. |
---|
La déclaration explicite des variables
VBA est un des rares langages de programmation qui ne requiert pas la déclaration des variables. Donc, vous pouvez programmer à la qui-mieux-mieux sans vous soucier de la définition des variables utilisées. Cette façon de programmer en VBA crée un risque très important, dont voici un excellent exemple. Supposons que vous utilisez la variable dSomme afin de calculer un résultat intermédiaire dans votre procédure. Et cette variable dSomme apparaît sur plusieurs lignes de programmation. Supposons maintenant que, à un endroit, vous vous trompez et que vous passez dSommes (avec un s à la fin) à la place dans un calcul. Comme VBA voit la variable dSommes pour la première fois, il lui assigne la valeur 0, donc votre calcul ne sera pas bon.
Afin d’éviter ces ennuis, il est donc grandement préférable de travailler en mode de déclaration explicite des variables. Quand une variable est déclarée en VBA, on peut ne déclarer que son nom ou son nom et son type. Cette deuxième approche est préférable.
En effet, la déclaration du type évite à la procédure d’assigner un type à chaque fois que la variable est utilisée. Si vous avez une procédure itérative qui utilise 5 variables et qui génère 50 000 itérations, la procédure doit se questionner 250 000 fois sur le type de variable à utiliser!
Afin de vous assurer de bien suivre cette règle, dans un module, avant toute fonction ou procédure, ajouter l’instruction:
Option Explicit
qui va forcer la déclaration explicite des variables.
Astuces de structure du code pour des macros plus rapides
Écrire et lire dans les feuilles en bloc
Il arrive que le travail de la procédure en VBA soit de lire et de modifier le contenu de cellules. À chaque fois que le code VBA fait une requête de lecture ou d’écriture dans la feuille, cela demande un certain temps d’exécution. En conséquence, il est judicieux de lire une plage de données complète en une seule opération en la chargeant dans une variable de type tableau. Puis, lorsque les opérations sont effectuées, de reporter les résultats en transcrivant tout le tableau d’un seul coup.
À titre d’exemple, regardons le cas suivant qui lit des données (et non des formules) dans la plage A1:C10000. Dans un premier temps, la version non-optimisée est présentée. Cette version lit les cellules une à une:
Ce code, même s’il va bien fonctionner, n’est pas la façon la plus optimale de programmer. Une façon beaucoup plus rapide consiste à lire et copier toute la plage de cellule en une seule étape vers une variable de type tableau. Ensuite, de modifier les valeurs, le cas échéant, des variables contenues dans le tableau et, finalement, de transcrire le tableau dans la feuille Excel. Donc, en tout: une opération de lecture et une d’écriture, alors que la méthode précédente avait 30 000 opérations de lectures et un grand nombre d’opérations d’écriture selon les valeurs des variables.
Le code serait plutôt alors:
Le fait de minimiser l’interaction entre le feuille et le code VBA améliore beaucoup la performance. Faites le test et vous verrez que la différence est hallucinante!
Éviter de sélectionner ou d’activer des objets
Le fait de sélectionner ou d’activer des objets requiert du temps d’exécution. Si le nombre d’objets auxquels on doit se référer est grand, il est préférable d’y accéder par référence au lieu de les sélectionner.
Par exemple, si on voulait changer le texte contenu dans toutes les Formes d’une feuille, on peut le faire en les sélectionnant à tour de rôle, puis en en changeant ensuite le texte. Ou on réfère à l’objet afin de changer directement le texte. Dans le premier cas, qui génère un temps d’exécution plus élevé, la portion du code VBA serait la suivante:
Le code suivant fait le même travail, mais en se référant aux objets, sans les sélectionner, ce qui sauve du temps:
Personnellement, si le nombre d’objets est petit, une sélection explicite ne me dérange pas et je trouve que le code est plus clair, surtout quand il s’agit d’objets tels des feuilles, des range ou des fichiers. Tel que mentionné, lors d’opérations répétitives, la référence, au lieu de la sélection, représente toutefois un gain à ne pas négliger.
Utiliser des fonctions créées en VBA
Si vos procédures font des calculs itératifs, il peut être avantageux de créer des fonctions en VBA afin de faire des bouts de calculs, même si ces fonctions sont déjà définies dans Excel et peuvent être appelées dans la procédure. En effet, sortir du code VBA afin d’appeler une fonction Excel prend du temps d’exécution. Toutefois, si la fonction est complexe à programmer, peut-être vaut-il mieux prendre celle d’Excel, qui fonctionne bien! Le jugement du programmeur est important à ce niveau!
Conclusion sur l’art de rédiger des macros plus rapides
En conclusion, la première décision est de faire ou non du code VBA. En effet, est-ce bien requis? On voit bien des cas où des procédures VBA ont été mises en place, alors que de simples fonctions et fonctionnalités d’Excel auraient été suffisantes pour les besoins. Car en effet, des procédures VBA vont éventuellement avoir besoin de maintenance et d’évolution et requièrent donc que l’expertise soit disponible à cette fin. Donc, si on peut éviter du code VBA, on l’évite.
Cela étant dit, tant qu’à faire du code VBA, il faut s’assurer que cela soit fait d’une manière efficace. Conséquemment, les trucs et astuces montrés ici s’avéreront utiles.
Formation complémentaire
Pour une introduction à la programmation avec le langage VBA, qui permet de sauver du temps et de réduire les risques d’erreurs en automatisant certaines tâches et certains processus, afin de développer des solutions robustes dans Excel, suivez la formation Excel – VBA (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
Merci pour ces astuces notamment le chapitre “Écrire et lire dans les feuilles en bloc”.
Je viens d’essayer et je confirme ça va beaucoup plus vite de mettre d’abord les valeurs dans un tableau puis de lire le tableau !
Vraiment efficace.
Merci pour cet article. J’y ai trouvé une info qui bouleverse le temps d’exécution d’une macro sur un gros fichier : figer le recalcul automatique !!! Je suis passé de près de 30 secondes à 3.
Merci notamment pour la partie “Écrire et lire dans les feuilles en bloc”, je fais actuellement des milliers de lectures dans un gros fichier xls avec plusieurs feuilles, et faut pas être pressé … donc astuce à tester rapidement.
Bonjour,
Merci à vous d’avoir pris le temps de partager vos connaissances. Cependant, lorsque j’essaie votre méthode (création d’une variable de type tableau), j’ai une erreur de type “Objet requis”.
Respectueusement, Emmanuel Vankerkore.
Bonjour,
Il m’est difficile de vous aider sans voir le code… Pouvez-vous ouvrir un billet sur le forum et y joindre votre fichier?
Merci,
Francis
Bonjour,
Le code ci-dessous me permet de transposer des lignes en colonnes (seulement si il y a une valeur).
Est-il possible de le faire en bloc pour que ce soit plus rapide?
Mon code est ci-dessous:
‘variables
Dim screen As Boolean
Dim events As Boolean
Dim display As Boolean
Dim calcul As Integer
Dim l As Long
Dim a As Long
Dim r As Long
Dim q As Long
Dim i As Long
Dim c As Long
Dim k As Long
Dim j As Long
‘hide macro
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.Calculation = xlCalculationManual
‘row counter for the data base
l = 0
Do Until IsEmpty(Sheets(“Tabelle1”).Cells(l + 2, 15)) = True
l = l + 1
Loop
‘Tabelle2 clear
With Sheets(“Tabelle2”)
.Range(.Cells(1, 1), .Cells(l, 17)).Clear
End With
‘row start Tabelle2: K
k = 2
‘ask user to enter variables
a = InputBox(“Bitte geben Sie die Nummer der ersten Spalte ein, die transponiert werden soll (z.B A = 1, B = 2, etc..) : “, “Erste Spalte zum transponieren”, “16”)
r = InputBox(“Bitte geben Sie die Nummer der letzten Spalte ein, die transponiert werden soll(z.B A = 1, B = 2, etc..): “, “Letze Spalte zum transponieren”, “27”)
q = a – 1
‘copy database header
Sheets(“Tabelle1”).Range(“a1:o1”).Copy
Sheets(“Tabelle2”).Cells(1, 1).PasteSpecial (xlPasteAll)
‘Transposition process
For i = 2 To l + 1
‘number of values c
‘Tabelle2 start row k
c = 0
For j = a To r
If IsEmpty(Sheets(“Tabelle1”).Cells(i, j)) = False Then
With Sheets(“Tabelle1”)
.Range(.Cells(i, 1), .Cells(i, q)).Copy
End With
With Sheets(“Tabelle2”)
.Cells(k + c, 1).PasteSpecial (xlPasteAll)
Sheets(“Tabelle1”).Cells(1, j).Copy
.Cells(k + c, q + 1).PasteSpecial (xlPasteAll)
.Cells(k + c, q + 2).Value = Sheets(“Tabelle1”).Cells(i, j).Value
End With
c = c + 1
End If
Next j
k = k + c
Next I
Merci beaucoup pour votre aide!
Cordialement
Luc
Luc,
Vous semblez en effet passer par le chemin le plus long (qui est peut-être également le plus fréquenté, je ne sais pas, je ne suis pas sur ce chemin), considérez plutôt le code suivant:
Dim vMatSource() as Variant
Dim vMatDest() as Variant
vMatSource=range(…).Value ‘ Ici, passez votre plage de cellules de tabelle1 en fonction de vos paramètres
vMatDest = Application.Transpose(vMatSource)
Sheets(”Tabelle2”).range.value = vTabDest ‘ Ici, il vous faut bien définir la plage de cellules de Tabelle2 afin d’éviter les erreurs
C’est à peu près cela…
Je vous invite donc à me rejoindre sur ce chemin moins fréquenté…
Francis
Bonjour Francis,
Merci beaucoup pour votre aide!
Bien cordialement.
Bonjour, pouvez-vous m’aider?
J’essaie d’appliquer la méthode Écrire et lire dans les feuilles en bloc pour copier coller une colonne vers une autre dans la même feuille avec ajout de texte sur les cellules de la colonne copiée.
Comment intégrer à ce code
copier de colonne A copie vers colonne W
ajout texte devant chaque cellule de la colonne W
exemple avec ajout du mot bonjour :
A1 Dupont en W1 bonjour Dupont
A2 Durand en W2 bonjour Durand
etc…
Bonjour,
Voici le code de l’article modifié afin de répondre à votre besoin,
Sub LireTraiterCellule()
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As String
‘Lire les données d’un seul coup
DataRange = Range(“A1:A10000”).Value
For Irow = 1 To 10000
Icol = 1
MyVar = DataRange(Irow, Icol)
If MyVar “” Then
‘Changer la valeur
MyVar = “Bonjour ” & MyVar
‘Si changement le faire dans le tableau
DataRange(Irow, Icol) = MyVar
End If
Next Irow
‘Transcrire le tout dans la feuille Excel
Range(“W1:W10000”).Value = DataRange
End Sub
Cordialement,
Bonjour,
Dans l’application que je construis, une macro est lancée depuis une boite de dialogue VBA (bouton dans la boite de dialogue).
Cette boite de dialogue est elle même ouverte, soit par un bouton de formulaire, soit sur activation de la feuille (évènement activate de la feuille). Dans les deux cas, le résultat final est ok, mais le temps nécessaire est 10x supérieur lorsque la fenêtre a été initialement ouverte depuis l’évènement.
Avez-vous une explication ? quelle propriété de d’application, de classeur ou de la feuille faut-il désactiver pour optimiser ces temps, que ces deux temps soient identiques ?
nota: Le temps le plus long est également celui obtenu en ouvrant la fenêtre depuis un bouton de contrôle active X ou depuis une image…..
Je vous serais extrêmement reconnaissant de me faire partager votre expérience.
Votre article est le plus sérieux que j’ai pu trouver sur le sujet et j’ai déjà appliqué ce que j’ai pû….
Merci
Christophe
Christophe,
Il est possible que ce délai soit dû à la gestion des événements. En effet, si une macro s’active lors d’un événement (par exemple, l’activation d’une feuille) et que cette même macro soit impliqué, plus ou moins, dans un événement lui-même induisant une macro, vous vous retrouvez dans une sorte de boucle récursive ou l’action de la macro lance une gestion d’événement qui appelle une macro qui active la gestion d’événements et ainsi de suite…
En VBA, avant qu’une macro n’entre dans une série d’instructions qui pourrait mener vers une telle gestion d’événements, il est plus que souhaitable d’insérer la commande
Application. EnableEvents=False
De cette façon, Excel arrête de monitorer la gestion d’événements, ce qui devrait éviter ces délais. À la sortie de la macro, ne pas oublier de remettre la gestion d’événements en place avec la commande
Application. EnableEvents=True
Voilà,
Francis
Bonjour Francis,
Je souhaite savoir si il est possible de bloquer la souris ou les clics souris pendant l’exécution d’une macro elle même lancée par un clic bouton sur un userform…
ou un autre moyen afin d’empêcher les utilisateurs de lancer deux fois la macro en recliquant sur le bouton pendant que la macro est déjà lancée.(sauf de cacher l’userform) Par avance merci beaucoup de votre retour.
Bien à vous.
Bonjour Collignon,
La meilleure solution, selon moi, est de créer une variable globale au sein du module du UserFOrm (disons bEnCours as boolean). Au début de l’exécution de votre macro, vous la mettez à vrai et à la fin à faux.
Si quelqu’un pèse sur le bouton (ou n’importe quel bouton) pendant l’exécution, la procédure de traitement de ce clic devrait commencer par
if bEnCours then exit sub
Alors, il n’y aura aucun de traitement si l’usager clique 2 fois ou peu importe. Vous devez mettre cette instruction dans toutes les procédures concernées.
Voilà,
Francis
Bonjour très utile ces infos
Please j’aimerais prendre contact avec vous par mail comment faire ?
Bonjour Francis,
Merci beaucoup pour votre aide!
S’il vous plait, j’aimerais prendre contact avec vous par mail comment faire ?
Bien cordialement.
Bonjour,
je suis resté estomaqué quand j’ai constaté que les exemples de code ne pouvaient pas être copiés simplement.
Francis, vous m’avez déçu.