Utilisation des macros Excel VBA
Les macros dans Excel sont stockées sous forme de code VBA et vous souhaiterez parfois modifier ce code directement. Ce didacticiel expliquera comment afficher et éditer des macros, décrire certaines techniques de débogage de macros et donner 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 sur 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 apparaîtront uniquement par leur nom, tandis que les macros des autres classeurs seront précédées du nom du classeur et d'un point d'exclamation (c'est-à-dire « Livre2 ! AutreMacro »).
Ouvrir une macro pour l'édition
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. Cela ouvrira la macro dans l'éditeur VB.
Vous pouvez également ouvrir l'éditeur VB directement en cliquant sur le bouton Visual Basic sous l'onglet Développeur ou en appuyant sur le raccourci clavier ALT+F11.
En utilisant cette méthode, vous devrez accéder à la macro souhaitée (également appelée « procédure »). Nous allons passer en revue la disposition de l'éditeur VBA :
Présentation de l'éditeur VB
L'éditeur VB a plusieurs fenêtres; dans ce didacticiel, nous couvrirons la fenêtre de projet, la fenêtre de propriétés et la fenêtre de code.
Fenêtre de projet
La fenêtre de projet affiche chaque fichier Excel comme son propre projet, avec tous les objets de ce projet classés par type. Les macros enregistrées apparaîtront dans la catégorie "Modules", d'habitude dans l'objet "Module1". (Si votre projet comporte plusieurs modules et que vous ne savez pas où est stockée votre macro, ouvrez-la simplement à partir de la boîte de dialogue Macros susmentionnée.)
Fenêtre Propriétés
La fenêtre Propriétés affiche les propriétés et les valeurs associées d'un objet - par exemple, cliquer sur un objet de feuille de calcul dans la fenêtre Projet affichera une liste de propriétés pour la feuille de calcul. Les noms de propriété sont à gauche et les valeurs de propriété sont à droite.
La sélection d'un module dans la fenêtre Projet montrera qu'il n'a qu'une seule propriété, "(Nom)". Vous pouvez modifier le nom d'un module en double-cliquant sur la valeur de la propriété, en saisissant un nouveau nom et en appuyant sur Entrée. Changer le nom d'un module le renommera 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 vouliez voir le code d'une macro située dans Module1, vous double-cliquez sur « Module1 » dans la fenêtre du projet.
Exécution de macros dans l'éditeur VB
Les macros peuvent être exécutées directement à partir de l'éditeur VB, ce qui est utile pour les tests et le débogage.
Exécuter une macro
- Dans la fenêtre de 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ù sur le code de la macro entre "Sub" et "End Sub"
- Clique le Courir dans la barre d'outils ou appuyez sur le raccourci clavier F5
« Pas à pas » une macro
Au lieu d'exécuter la macro en une seule fois, vous pouvez exécuter la macro une ligne à la fois, en utilisant un raccourci clavier pour « parcourir » le code. La macro s'arrêtera sur chaque ligne, vous permettant de vous assurer que chaque ligne de code fait ce que vous attendez dans Excel. Vous pouvez également arrêter une macro de continuer à tout moment en utilisant cette méthode.
Pour « parcourir » une macro :
- Dans la fenêtre de 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ù sur le code de la macro
- Appuyez sur le raccourci clavier F8 pour commencer le processus « pas à pas »
- Appuyez plusieurs fois sur F8 pour faire avancer l'exécution du code, indiqué par la surbrillance jaune dans la fenêtre Code
- Pour arrêter une macro de continuer, appuyez sur le Réinitialiser bouton
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. Apprendre à modifier vos macros les aidera à s'exécuter plus rapidement, plus efficacement et de manière plus prévisible.
En plus de résoudre ces problèmes, vous obtiendrez également une augmentation massive de la productivité lorsque vous exploitez toute la puissance des macros. Les macros ne doivent pas seulement être des enregistrements de tâches - les macros peuvent inclure une logique afin qu'elles n'exécutent des tâches que sous 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 fois !
Ci-dessous, vous trouverez quelques conseils pratiques pour vous aider à optimiser votre code de macro, ainsi que des outils pour rendre vos macros plus efficaces et plus efficaces.
Exemples courants d'édition de macros
Accélérer les macros
Si vous avez une macro qui prend beaucoup de temps à s'exécuter, il peut y avoir plusieurs raisons pour lesquelles elle s'exécute lentement.
Premièrement : lorsqu'une macro s'exécute, Excel affiche tout ce qui se passe en temps réel - alors que cela peut voir vite pour toi, en faitmontrant le travail est un coup de performance significatif. Une façon de faire fonctionner Excel beaucoup plus rapidement est de lui dire de arrêter de mettre à jour l'écran:
' Désactiver l'application de mise à jour de l'écran.ScreenUpdating = False ' Activer l'application de mise à jour de l'écran.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 sur True à la fin de votre macro, sinon Excel pourrait ne pas agir comme prévu plus tard !
Une autre façon d'accélérer les macros :désactiver le calcul automatique dans la macro. Si vous avez travaillé avec des feuilles de calcul complexes, vous saurez 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 basculer cela avec le code VBA, de sorte que votre macro fonctionnera toujours rapidement sur d'autres ordinateurs. Cela aide dans les cas où vous copiez-collez de nombreuses cellules de formule ou provoquez le déclenchement de nombreux calculs lorsque vous collez des données dans une plage :
' Désactiver l'application de calcul automatique.Calculation = xlCalculationManual ' Activer l'application de calcul automatique.Calculation = xlCalculationAutomatic
Ajouter des boucles et une 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
Supposons que vous vouliez 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 cliquant sur le bouton (+) encore et encore, ou vous pouvez écrire une boucle qui répète l'action pour vous, comme ceci :
Sub ReportPrep() Dim i As Long For i = 1 To 19 Sheets.Add Next i End Sub
Dans cet exemple, nous utilisons un Pour boucle, qui est une sorte de boucle qui parcourt une plage d'éléments. Ici, notre plage est les nombres 1 à 19, en utilisant une variable nommée "i" afin que la boucle puisse suivre. Dans notre boucle, il n'y a qu'une seule action répétée entre le pour etSuivant lignes (l'ajout de la feuille), mais vous pouvez ajouter autant de code à l'intérieur de la boucle que vous le souhaitez 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.
Si les déclarations
Un Si déclaration est utilisé pour décider si un code s'exécute ou non, en utilisant un test logique pour prendre la décision. Voici un exemple simple :
Sub ClearIfSmall() If 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 vraie ou fausse (la valeur de la cellule sélectionnée est-elle inférieure à 100 ?), et si le test renvoie True, le code à l'intérieur s'exécute.
Un inconvénient de ce code est qu'il ne teste qu'une cellule à la fois (et échouerait si vous sélectionnez plusieurs cellules). Ce serait plus utile si vous pouviez… parcourir chaque cellule sélectionnée et tester chacune…
Sub ClearIfSmall() Dim c As Range pour chaque c In Selection.Cells If c.Value < 100 Then c.Clear End If Next c End Sub
Dans cet exemple, il existe une boucle For légèrement différente - celle-ci ne parcourt pas une plage de nombres, mais parcourt toutes les cellules de la sélection, en utilisant une variable nommée "c" pour suivre. À l'intérieur de 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 dans des boucles, ou un If dans un autre, ou utiliser un If pour décider si une boucle doit s'exécuter.
<<>>
Supprimer les effets de défilement
Une raison courante pour modifier le code de macro est de supprimer le défilement de l'écran. Lors de l'enregistrement d'une macro, vous devrez peut-être atteindre d'autres zones d'une feuille de calcul en faisant défiler, mais les macros n'ont pas besoin de faire 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 sur la barre de défilement :
Ce type de code est totalement inutile et peut être supprimé sans affecter aucune autre fonctionnalité. Même si vous vouliez conserver le défilement, ce code pourrait toujours être condensé en une boucle.
Supprimer 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. Prenons par exemple le code enregistré suivant, qui enregistre la modification d'un nom de 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 de texte, etc. la macro enregistrée ne fonctionnerait pas !
Il est possible de changer cette macro pour qu'elle ne change que le nom de la police :
Non seulement cette macro fonctionnera comme prévu maintenant, mais elle est également beaucoup plus facile à lire.
Supprimer les mouvements du curseur
Une autre chose qui est enregistrée dans les macros sont les sélections de feuilles de calcul et de cellules. C'est un problème car un utilisateur peut facilement perdre la trace de ce sur quoi il travaillait si le curseur se déplace vers une position différente après l'exécution d'une macro.
Comme pour le défilement, tu peut avoir besoin 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, puis la colle dans trois autres feuilles :
Il y a quelques problèmes avec ce code :
- L'utilisateur perdra sa place précédente dans le classeur
- La macro ne spécifie pas quelle feuille nous copionsde - cela pourrait être un problème si la macro a été exécutée sur la mauvaise feuille
De plus, le code est difficile à lire et inutile. Ces problèmes peuvent être résolus assez facilement :
Dans ce code, il est clair que nous copions à partir de Sheet1, et ni la feuille de calcul active ni la plage sélectionnée n'ont besoin de changer pour coller les données. (Un changement important est l'utilisation de "PasteSpecial" au lieu de "Paste" - Les objets Range, comme "Range("C4″)", n'ont accès qu'à la commande PasteSpecial.)
Chaque fois que le code devient plein de références à ".Select" et "Selection", c'est un indice qu'il y a de la place pour optimiser ce code et le rendre plus efficace.
