Ouvrez Excel et le VBE (Visual Basic Editor). Sauf si elle a été modifiée, la fenêtre VBE contient la fenêtre ProjectExplorer et la fenêtre Properties (celles-ci sont accessibles à partir du menu Affichage).

Explorateur de projet: fonctionne comme un gestionnaire de fichiers. Vous aide à naviguer dans le code de votre classeur.

Fenêtre Propriétés: affiche les propriétés de l’objet actuellement actif (par exemple Sheet1) du classeur actuel (par exemple_Book1_).

Dans cet article, nous allons apprendre à quel point l’enregistrement de macros est facile dans Excel.

Exercice 1: Enregistrer une macro.

Cet exercice montre ce qui se passe lorsqu’une macro est enregistrée et démontre la différence entre l’enregistrement de références absolues et relatives.

  1. Sur une feuille de calcul vide dans un nouveau classeur, sélectionnez la cellule C10 2. Démarrez l’enregistreur de macros avec l’option pour enregistrer la macro dans Ce classeur. À ce stade, le VBE crée un nouveau dossier Modules. Il est tout à fait sûr d’aller le regarder – vos actions ne seront pas enregistrées. Cliquez sur le [+] à côté du dossier et voyez que le VBE a placé un module dans le dossier et l’a nommé Module1. Double-cliquez sur l’icône du module pour ouvrir sa fenêtre de code. Revenez à Excel.

  2. Assurez-vous que le bouton Relative Reference de la barre d’outils Stop Recording n’est PAS enfoncé.

  3. Sélectionnez la cellule B5 et arrêtez l’enregistreur.

  4. Basculez vers le VBE et regardez le code:

Plage (« B5 »). Sélectionnez 6. Maintenant, enregistrez une autre macro, exactement de la même manière, mais cette fois avec le bouton Relative Reference enfoncé.

  1. Passez au VBE et regardez le code:

ActiveCell.Offset (-5, -1) .Range (« A1 »). Sélectionnez 8. Maintenant, enregistrez une autre macro, mais au lieu de sélectionner la cellule B5, sélectionnez un bloc de cellules 3 x 3 commençant à B5 (sélectionnez les cellules B5: F7) Retour au début

  1. Passez au VBE et regardez le code:

ActiveCell.Offset (-5, -1) .Range (« A1: B3 »). Sélectionnez 10. Lisez les macros, après avoir sélectionné au préalable une cellule différente de C10 (pour Macro2 et Macro3, la cellule de départ doit être à la ligne 6 ou ci-dessous – voir l’étape 11 ci-dessous)

Macro1 – déplace toujours la sélection vers B5 Macro2 – déplace la sélection vers une cellule de 5 lignes vers le haut et 1 colonne à gauche de la cellule sélectionnée.

Macro3 – sélectionne toujours un bloc de six cellules commençant 5 lignes vers le haut et 1 colonne à gauche de la cellule sélectionnée.

  1. Exécutez Macro2 mais forcez une erreur en sélectionnant une cellule de la ligne 5 ou supérieure. La macro essaie de sélectionner une cellule inexistante car son code lui dit de sélectionner une cellule 5 lignes au-dessus du point de départ, et c’est en haut de la feuille. Appuyez sur Déboguer pour accéder à la partie de la macro qui a causé le problème.

REMARQUE: lorsque le VBE est en mode débogage, la ligne de code qui a provoqué le problème est mise en surbrillance en jaune. Vous devez « réinitialiser » la macro avant de pouvoir continuer. Cliquez sur le bouton Réinitialiser dans la barre d’outils VBE ou accédez à Exécuter> Réinitialiser. La surbrillance jaune disparaît et le VBE sort du mode Debug.

  1. Il est important d’essayer d’anticiper les erreurs de l’utilisateur comme celle-ci. Le moyen le plus simple est de modifier le code pour simplement ignorer les erreurs et passer à la tâche suivante. Pour ce faire, ajoutez la ligne… On Error Resume Next… immédiatement au-dessus de la première ligne de la macro (sous la ligne Sub Macro1 ()

  2. Exécutez Macro2 comme avant, en commençant trop haut sur la feuille. la ligne que vous avez tapée indique à Excel d’ignorer la ligne de code qu’il ne peut pas exécuter. Il n’y a pas de message d’erreur et la macro se termine après avoir fait tout ce qu’elle peut. Utilisez cette méthode pour gérer les erreurs avec prudence. Il s’agit d’une macro très simple. R une macro plus complexe ne fonctionnerait probablement pas comme prévu si les erreurs étaient simplement ignorées. De plus, l’utilisateur n’a aucune idée que quelque chose s’est mal passé.

  3. Modifiez le code de Macro2 pour inclure un gestionnaire d’erreurs plus sophistiqué ainsi:

Sub Macro2 ()

En cas d’erreur GoTo ErrorHandler ActiveCell.Offset (-5, -1) .Range (« A1 »). Sélectionnez Exit Sub ErrorHandler:

MsgBox « Vous devez démarrer sous la ligne 5 « 

End Sub 15. Cette fois, une boîte de dialogue s’affiche lorsque quelque chose ne va pas. S’il n’y a pas d’erreur la ligne ExitSub provoque la fin de la macro une fois qu’elle a fait son travail – sinon l’utilisateur verrait le message même s’il n’y avait pas d’erreur.

Amélioration des macros enregistrées La bonne façon d’apprendre les bases de VBA est d’enregistrer une macro et de voir comment Excel écrit son propre code. Cependant, les macros enregistrées contiennent souvent beaucoup plus de code que nécessaire. Les exercices suivants montrent comment vous pouvez améliorer et rationaliser le code qui a été produit par une macro enregistrée.

Exercice 2: Amélioration des macros enregistrées Cet exercice montre que lorsque des macros sont enregistrées, souvent plus de code est généré que nécessaire. Il démontre l’utilisation de l’instruction With pour préciser le code.

  1. Sélectionnez une cellule ou un bloc de cellules.

  2. Démarrez l’enregistreur de macros et appelez la macro FormatCells. Le paramètre Références relatives ne sera pas pertinent.

  3. Allez dans Format> Cellules> Police et choisissez Times New Roman et Red.

Allez dans Patterns et choisissez Yellow.

Allez dans Alignement et choisissez Horizontal, Center Allez dans Number et choisissez Currency.

  1. Cliquez sur OK et arrêtez l’enregistreur.

  2. Cliquez sur le bouton Annuler (ou Ctrl + Z) pour annuler vos modifications dans la feuille de calcul.

  3. Sélectionnez un bloc de cellules et exécutez la macro FormatCells. Notez qu’il ne peut pas être annulé! Tapez les cellules pour vérifier le résultat de la mise en forme.

  4. Regardez le code:

Sous-FormatSelection ()

Selection.NumberFormat = « $ , # 0,00″

Avec Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With With Selection.Font .Name = « Times New Roman »

Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 3 Fin avec Selection.Interior .ColorIndex = 6 .Pattern = xlSolidIndexlutomatic. End With End Sub 8. Notez toutes les instructions supplémentaires qui ont été enregistrées. Supprimer les lignes de code de manière à ne conserver que les éléments suivants:

Sous-FormatSelection ()

Selection.NumberFormat = « $ , # 0,00″

Avec Selection .HorizontalAlignment = xlCenter Fin Avec Avec Selection.Font .Name = « Times New Roman »

ColorIndex = 3 End With With Selection.Interior .ColorIndex = 6 End With End Sub 9. Exécutez la macro pour tester le code modifié. Cela fonctionne toujours comme avant.

  1. Modifiez maintenant encore le code:

Sous-FormatSelection ()

Avec Selection .NumberFormat = « $ , # 0.00″

Font.ColorIndex = 3 .Interior.ColorIndex = 6 End With End Sub 11. Testez la macro. Tout fonctionne toujours et le code fonctionnera beaucoup plus rapidement.

  1. Essayez d’enregistrer la même macro en utilisant les boutons de la barre d’outils au lieu d’aller dans la boîte de dialogue:

Changez la police en Times New Roman Changez la couleur de police en Red Changez la couleur de remplissage en Yellow Cliquez sur le bouton Center Cliquez sur le bouton Currency 13. Regardez le code. Vous obtenez toujours beaucoup de choses dont vous ne voulez pas nécessairement. Excel enregistre tous les paramètres par défaut. La plupart d’entre eux peuvent être supprimés en toute sécurité.

  1. Expérimentez avec l’édition directement dans le code pour changer les couleurs, la police, le format des nombres, etc.

Exercice 3: Regarder une macro en cours d’enregistrement Cet exercice montre que vous pouvez apprendre en regardant la macro construire pendant son enregistrement. C’est aussi un exemple de cas où parfois l’instruction With n’est pas appropriée.

  1. Ouvrez le fichier VBA01.xls.

Bien que cette feuille de travail soit visuellement correcte et puisse être comprise par l’utilisateur, la présence de cellules vides peut poser des problèmes. Essayez de filtrer les données et voyez ce qui se passe. Accédez à Données> Filtre> Filtre automatique et filtrez par région ou par mois. Il est clair qu’Excel ne fait pas les mêmes hypothèses que l’utilisateur. Les cellules vides doivent être remplies.

  1. Mettez en mosaïque les fenêtres Excel et VBE (verticalement) afin qu’elles soient côte à côte.

  2. Sélectionnez une cellule dans les données. S’il s’agit d’une cellule vide, elle doit être adjacente à une cellule contenant des données.

  3. Démarrez l’enregistreur de macros et appelez la macro FillEmptyCells.

Défini pour enregistrer les références relatives.

  1. Dans la fenêtre VBE, recherchez et double-cliquez sur le module (Module1)

pour que le classeur actuel ouvre le volet d’édition, puis désactivez la fenêtre Explorateur de projet et la fenêtre Propriétés (uniquement pour libérer de l’espace).

  1. Enregistrez la nouvelle macro comme suit:

Étape 1. Ctrl + * (pour sélectionner la région actuelle)

Étape 2. Édition> Aller à> Spécial> Blancs> OK (pour sélectionner toutes les cellules vides de la région actuelle)

Étape 3. Tapez = [UpArrow] puis appuyez sur Ctrl + Entrée (pour placer votre saisie dans toutes les cellules sélectionnées)

Étape 4. Ctrl + * (pour sélectionner à nouveau la région actuelle)

Étape 5. Ctrl + C (pour copier la sélection – n’importe quelle méthode fera l’affaire)

Étape 6. Édition> Collage spécial> Valeurs> OK (pour coller les données au même endroit mais en supprimant les formules)

Étape 7. Esc (pour sortir du mode copie)

Étape 8. Arrêtez l’enregistrement.

  1. Regardez le code:

Sous FillEmptyCells ()

Selection.CurrentRegion.Select Selection.SpecialCells (xlCellTypeBlanks) .Select Selection.FormulaR1C1 = « = R [-1] C »

Selection.CurrentRegion.Select Selection.Copy Selection.PasteSpecial Paste: = xlValues, Operation: = xlNone, SkipBlanks: = _ False, Transpose: = False Application.CutCopyMode = False End Sub 8. Notez l’utilisation de l’espace et du trait de soulignement «_» Pour désigner le fractionnement d’une seule ligne de code sur une nouvelle ligne. Sans cela, Excel traiterait le code comme deux instructions distinctes.

  1. Comme cette macro a été enregistrée avec des commandes bien pensées, il y a peu de code inutile. Dans le Paste Special, tout ce qui suit le mot «xlValues» peut être supprimé.

  2. Essayez la macro. Utilisez ensuite l’outil Filtre automatique et notez la différence.