Modifier les macros Excel VBA

Utilisation des macros Excel VBA

Les macros dans Excel sont stockées en tant que code VBA, et parfois vous voudrez modifier ce code directement. Ce tutoriel explique comment afficher et modifier les macros, décrit certaines techniques de débogage des macros et donne quelques exemples d’édition courants.

 

Afficher les Macros

Une liste de macros peut être affichée dans la boîte de dialogue Macros. Pour afficher cette boîte de dialogue, sélectionnez l’onglet Développeur dans le ruban et cliquez sur le bouton Macros.

Si plusieurs classeurs sont ouverts, les macros de tous les classeurs seront affichées dans la liste. Les macros du classeur actif n’apparaissent que par leur nom, tandis que les macros des autres classeurs sont précédées du nom du classeur et d’un point d’exclamation (par exemple, « Classeur2!AutreMacro »).

Ouvrir une Macro pour la Modifier

Vous pouvez utiliser la boîte de dialogue Macro pour ouvrir le code d’une macro en sélectionnant le nom de la macro et en cliquant sur le bouton Modifier. La macro s’ouvre alors dans l’éditeur VB.

Vous pouvez également ouvrir directement l’éditeur VB en cliquant sur le bouton Visual Basic dans l’onglet Développeur, ou en appuyant sur le raccourci clavier ALT+F11.

En utilisant cette méthode, vous devrez naviguer jusqu’à la macro (également appelée « procédure ») de votre choix. Nous allons passer en revue la présentation de l’éditeur VBA :

Aperçu de l’Éditeur VB

L’éditeur VB comporte plusieurs fenêtres ; dans ce tutoriel, nous aborderons la fenêtre de projet, la fenêtre des propriétés et la fenêtre du code.

Fenêtre de Projet

La fenêtre de projet présente chaque fichier Excel comme son propre projet, avec tous les objets de ce projet classés par type. Les macros enregistrées apparaissent dans la catégorie « Modules », généralement dans l’objet « Module1 ». (Si votre projet comporte plusieurs Modules et que vous n’êtes pas sûr de l’endroit où est stockée votre macro, ouvrez-la simplement à partir de la boîte de dialogue Macros mentionnée ci-dessus)

Fenêtre des Propriétés

La fenêtre des propriétés affiche les propriétés et les valeurs associées d’un objet. Par exemple, si vous cliquez sur un objet feuille de calcul dans la fenêtre de projet, la liste des propriétés de la feuille de calcul s’affiche. Les noms des propriétés se trouvent à gauche, et leurs valeurs à droite.

Si vous sélectionnez un module dans la fenêtre de projet, vous verrez qu’il n’a qu’une seule propriété, « (Name) » soit son nom. Vous pouvez modifier le nom d’un module en double-cliquant sur la valeur de la propriété, en tapant un nouveau nom et en appuyant sur Entrée. La modification du nom d’un module le renomme aussi dans la fenêtre de projet, ce qui est utile si vous avez beaucoup de modules.

Fenêtres de Code

Les fenêtres de code sont des éditeurs de texte spéciaux dans lesquels vous pouvez modifier le code VBA de votre macro. Si vous voulez voir le code d’une macro située dans le module1, vous devez double-cliquer sur le module 1 dans la fenêtre de projet.

Exécution de Macros dans l’Éditeur VB

Les macros peuvent être exécutées directement depuis l’éditeur VB, ce qui est utile pour les tests et le débogage.

Exécution d’une Macro

• Dans la fenêtre de projet, double-cliquez sur le module contenant la macro que vous voulez tester (pour ouvrir sa fenêtre de code)
• Dans la fenêtre Code, placez le curseur n’importe où dans le code de la macro entre « Sub » et « End Sub »
• Cliquez sur le bouton Exécuter de la barre d’outils ou appuyez sur le raccourci clavier F5

Exécuter une Macro « Pas-à-Pas »

Au lieu d’exécuter la macro d’un seul coup, vous pouvez l’exécuter ligne par ligne, en utilisant un raccourci clavier pour « parcourir » le code. La macro s’arrête sur chaque ligne, ce qui vous permet de vous assurer que chaque ligne de code fait bel et bien ce à quoi vous vous attendez dans Excel. Vous pouvez également arrêter une macro à tout moment en utilisant cette méthode.

Pour « parcourir » une macro :

• Dans la fenêtre du projet, double-cliquez sur le module contenant la macro que vous souhaitez tester (pour ouvrir sa fenêtre de code)
• Dans la fenêtre Code, placez le curseur n’importe où dans le code de la macro
• Appuyez sur le raccourci clavier F8 pour lancer le processus d’exécution « Pas-à-Pas »
• Appuyez plusieurs fois sur F8 pour faire avancer l’exécution du code qui est indiquée par la surbrillance jaune dans la fenêtre de code
• Pour empêcher une macro de continuer, appuyez sur le bouton Réinitialiser

Pourquoi Éditer des Macros VBA ?

L’enregistreur de macros, bien qu’efficace, est également très limité. Dans certains cas, il produit des macros lentes, enregistre des actions que vous n’aviez pas l’intention de répéter, ou enregistre des choses que vous ne pensiez pas faire. En apprenant à modifier vos macros, vous les rendrez plus rapides, plus efficaces et plus prévisibles.

En plus de résoudre ces problèmes, vous gagnerez énormément en productivité en exploitant toute la puissance des macros. Les macros ne doivent pas seulement être des enregistrements de tâches – les macros peuvent inclure une logique afin de n’exécuter des tâches que dans certaines conditions. En quelques minutes seulement, vous pouvez coder des boucles qui répètent une tâche des centaines ou des milliers de fois en une seule exécution!

Vous trouverez ci-dessous quelques conseils pratiques pour vous aider à optimiser le code de vos macros, ainsi que des outils qui vous permettront de les faire travailler plus efficacement et plus intelligemment.

Exemples Courants d’Édition de Macros

Accélération des Macros

Si une macro prend beaucoup de temps à s’exécuter, il peut y avoir plusieurs raisons.

Premièrement, lorsqu’une macro s’exécute, Excel affiche tout ce qui se passe en temps réel. Bien que cela puisse vous sembler rapide, l’affichage du travail représente une perte de performance significative. Une façon d’accélérer considérablement l’exécution d’Excel est de lui demander d’arrêter de mettre à jour l’écran:

' Désactiver la mise à jour de l'écran
Application.ScreenUpdating = False

' Activer la mise à jour de l'écran
Application.ScreenUpdating = True

La ligne « Application.ScreenUpdating = False » signifie que vous ne verrez pas la macro fonctionner, mais qu’elle s’exécutera beaucoup plus rapidement. Notez que vous devez toujours définir ScreenUpdating à True à la fin de votre macro, sinon Excel pourrait ne pas agir comme vous le souhaitez par la suite!

Autre moyen d’accélérer les macros : désactivez le calcul automatique dans la macro. Si vous avez déjà travaillé avec des feuilles de calcul complexes, vous savez que de petits changements peuvent déclencher des milliers de calculs qui prennent du temps, c’est pourquoi de nombreuses personnes désactivent le calcul automatique dans les options d’Excel. Vous pouvez également désactiver cette fonction avec le code VBA, de sorte que votre macro continue à fonctionner rapidement sur d’autres ordinateurs. Cela s’avère utile lorsque vous copiez-coller un grand nombre de cellules de formules ou que vous déclenchez de nombreux calculs lorsque vous collez des données dans une plage :

' Désactiver le calcul automatique
Application.Calculation = xlCalculationManual

' Activer le calcul automatique
Application.Calcul = xlCalculationAutomatic

Ajouter des Boucles et de la Logique (Instructions If)

L’enregistreur de macros enregistre toutes vos actions sous forme de code dans un langage appelé VBA. VBA est plus qu’un simple moyen d’enregistrer des actions dans Excel – c’est un langage de programmation, ce qui signifie qu’il peut contenir du code pour prendre des décisions sur les actions à effectuer, ou répéter des actions jusqu’à ce qu’une condition soit remplie.

Bouclage

Imaginons que vous souhaitiez créer une macro pour préparer un rapport et que, dans le cadre de cette macro, vous deviez ajouter dix-neuf feuilles au classeur, pour un total de vingt. Vous pouvez vous enregistrer en train de cliquer sur le bouton (+) encore et encore, ou écrire une boucle qui répète l’action à votre place, comme ceci :

Sub PréparationRapport()
    Dim i As Long
    
    For i = 1 To 19
        Sheets.Add
    Next i
End Sub

Dans cet exemple, nous utilisons une Boucle For, qui est une sorte de boucle qui parcourt une plage d’éléments. Ici, notre plage est constituée des nombres 1 à 19, et nous utilisons une variable nommée « i » pour que la boucle puisse en garder la trace. Dans notre boucle, une seule action est répétée entre la ligne for et la ligne next (l’ajout d’une feuille), mais vous pouvez ajouter autant de code que vous le souhaitez à l’intérieur de la boucle pour faire des choses comme formater la feuille, ou copier et coller des données sur chaque feuille – tout ce que vous voulez répéter.

Instructions If

Une instruction If est utilisée pour décider si un code doit être exécuté ou non, en utilisant un test logique pour prendre la décision. Voici un exemple simple :

Sub EffacerSiFaible()
    Si Selection.Value < 100 Then
        Selection.Clear
    End If
End Sub

Cet exemple simple montre comment fonctionne l’instruction If – vous testez une condition qui est soit vraie, soit fausse (la valeur de la cellule sélectionnée est-elle inférieure à 100?), et si le test renvoie Vrai, le code qu’il contient s’exécute. Le défaut de ce code est qu’il ne teste qu’une seule cellule à la fois (et qu’il échouerait si plusieurs cellules étaient selectionnées). Ce serait plus utile si vous pouviez… faire une boucle pour traiter chaque cellule sélectionnée et tester chacune d’entre elles…

Sub EffacerSiFaible()
    Dim c As Range
    
    For Each c In Selection.Cells
        If c.Value < 100 Then
            c.Clear
        End If
    Next c
End Sub

Dans cet exemple, la boucle For est légèrement différente. Elle ne parcourt pas une plage de nombres, mais toutes les cellules de la sélection, en utilisant une variable appelée ‘c’ pour en garder la trace. Dans la boucle, la valeur de « c » est utilisée pour déterminer si la cellule doit être effacée ou non.

Les boucles et les instructions If peuvent être combinées comme vous le souhaitez : vous pouvez placer des boucles à l’intérieur de boucles, ou un If à l’intérieur d’un autre, ou encore utiliser un If pour décider si une boucle doit être exécutée.

<<<Steve: add AutoMacro calls to action here!>>>

Supprimer les Effets de Défilement

Une raison courante de modifier le code d’une macro est de supprimer le défilement de l’écran. Lorsque vous enregistrez une macro, vous pouvez être amené à atteindre d’autres zones d’une feuille de calcul en faisant défiler l’écran, mais les macros n’ont pas besoin de défiler pour accéder aux données.

Le défilement peut encombrer votre code de centaines, voire de milliers de lignes de code inutiles. Voici un exemple du code qui est enregistré lorsque vous cliquez et faites glisser la barre de défilement :

Ce type de code est totalement inutile et pourrait être supprimé sans affecter aucune autre fonctionnalité. Même si vous vouliez conserver le défilement, ce code pourrait être condensé dans une boucle.

Supprimez le code redondant

Les macros enregistrées ont tendance à ajouter beaucoup de code redondant qui ne reflète pas nécessairement ce que vous voulez que la macro fasse. Prenez par exemple le code enregistré suivant, qui enregistre le changement du nom d’une police sur une cellule :

Même si seul le nom de la police a été modifié, onze (11) changements de police ont été enregistrés, comme la taille de la police, les effets du texte, etc. Si l’intention de la macro était de ne changer que le nom de la police (en laissant toutes les autres propriétés intactes), cette macro enregistrée ne fonctionnerait pas!

Il est possible de modifier cette macro pour qu’elle ne change que le nom de la police :

Non seulement cette macro fonctionne comme prévu, mais elle est aussi beaucoup plus facile à lire.

Suppression des mouvements du curseur

Les sélections de feuilles de calcul et de cellules sont une autre chose qui est enregistrée dans les macros. C’est un problème car un utilisateur peut facilement perdre la trace de ce sur quoi il était en train de travailler si le curseur se déplace après l’exécution d’une macro.

Comme pour le défilement, il vous est peut être nécessaire de déplacer le curseur et de sélectionner différentes cellules pour effectuer une tâche, mais les macros n’ont pas besoin d’utiliser le curseur pour accéder aux données. Considérez le code suivant, qui copie une plage et la colle ensuite dans trois autres feuilles :

Il y a quelques problèmes avec ce code :

• L’utilisateur perd la place qu’il occupait dans le classeur
• La macro ne précise pas la feuille à partir de laquelle elle est copiée – ce qui peut poser problème si la macro a été exécutée sur la mauvaise feuille

En outre, le code est difficile à lire et contient plusieurs lignes inutiles. Ces problèmes peuvent être résolus assez facilement :

Dans ce code, il est clair que nous copions à partir de Feuil1, et ni la feuille de calcul active ni la plage sélectionnée ne doivent être modifiées pour coller les données. (Une modification importante est l’utilisation de « PasteSpecial » au lieu de « Paste » – les objets Range, comme « Range(« C4″) », n’ont accès qu’à la commande PasteSpecial)

Lorsque le code contient de nombreuses de références à « .Select » et « Selection », c’est un indice qu’il est possible de l’optimiser et de le rendre plus efficace.