VBA: Rédiger des macros plus rapides

Publié le 14 mai 2015
par Francis Paquet M.Sc., EEE
VBA et configuration de paramètres

VBA: Rédiger des macros plus rapides

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 en 2 grandes catégories:

  1. 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;
  2. 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.

Astuces de programmation

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?

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 à:

Paramètres d'affichage
Paramètres d’affichage

 

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

É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:

Lecture cellule par cellule
Lecture cellule par cellule

 

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:

Lire les cellules d'un coup
Lire les cellules d’un coup

 

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:

Objets traités par sélection
Objets traités par sélection

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:

Objets traités par référence
Objets traités par référence

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!

En conclusion

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.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

15 réflexions sur “VBA: Rédiger des macros plus rapides”

  1. 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.

  2. 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.

  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.

  4. 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.

  5. 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

  6. 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

  7. 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

  8. 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…

  9. 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,

  10. Christophe FAURE

    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

  11. 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

  12. 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.

  13. 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

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top