Macros-commandes VBA/Créer des macros complémentaires
Introduction
[modifier | modifier le wikicode]
Introduction à la leçon : Macros Complémentaires
L'une des plus astucieuses fonctionnalités d'Excel - à mon avis, du moins - est la possibilité de créer des macros complémentaires, appelées aussi "compléments". Vous comprendrez leur intérêt dans ce chapitre, et vous apprendrez à les créer en utilisant les outils intégrés à Excel.
Qu'est-ce qu'une macro complémentaire ?
[modifier | modifier le wikicode]
Une macro complémentaire est une sorte d'extension qui augmente les fonctionnalités d'Excel. Certaines fournissent de nouvelles fonctions de feuille de calcul utilisables dans les formules, d'autres de nouvelles commandes ou des utilitaires. Si la macro complémentaire a été conçue dans les règles de l'art, la nouvelle fonctionnalité s'intègre parfaitement dans l'interface d'Excel, comme si elle en avait toujours fait partie.
Excel est livré avec plusieurs macros complémentaires. Les plus connues sont l'Utilitaire d'analyse, l'Assistant Somme conditionnelle et le Complément Solveur. Vous pouvez en obtenir d'autres auprès de programmeurs et d'éditeurs tiers. Mon Power Utility Pak en est un (très bon...) exemple.
Tout utilisateur un tant soit peu averti peut créer des macros complémentaires, à condition bien sûr de savoir programmer en VBA. Une macro complémentaire d'Excel est fondamentalement une forme différente de fichier de classeur XLSM. Plus spécifiquement, une macro complémentaire est un classeur XLSM normal, à quelques différences près tout de même :
- la propriété IsAddin de l’objet Workbook est True.
- la fenêtre du classeur est masquée et ne peut pas être affichée avec les commandes d'Excel.
- Le classeur n’est pas un membre de la collection Workbook, mais de la collection AddIns.
Tout fichier XLSX peut être converti en macro complémentaire, mais tous ne sont pas de bons candidats. Comme les compléments sont toujours masqués, vous ne pouvez pas afficher leurs feuilles de calcul ou de graphique. Il est toutefois possible d'accéder aux procédures Sub et Function et d'afficher les boîtes de dialogue contenues dans les objets UserForm. Les fichiers des macros complémentaires d'Excel ont habituellement une extension XLAM (XLA avant Excel 2007 qui les distingue des classeurs XLSM).
Pourquoi créer des macros complémentaires ?
[modifier | modifier le wikicode]Vous pouvez décider de convertir une application Excel en macro complémentaire pour l'une des raisons suivantes :
- Rendre l'accès au code plus difficile : quand une application est distribuée sous la forme d'une macro complémentaire (et que vous protégez son projet VBA), l'utilisateur lambda ne peut pas voir ses feuilles dans le classeur. Si vous l'avez programmé avec des techniques bien à vous, il sera plus difficile à autrui de copier votre code. Les fonctions de protection d'Excel ne sont pas parfaites et il existe des utilisateurs conçus pour casser le mot de passe.
- Eviter la confusion : quand une application est chargée en tant que macro complémentaire, le fichier est invisible, ce qui évite à l'utilisateur novice de s'y perdre et de faire n’importe quoi. Contrairement à un classeur masqué, une macro complémentaire ne peut pas être révélée.
- Simplifier l'accès aux fonctions de la feuille de calcul : les fonctions de la feuille de calcul personnalisées stockées dans une macro complémentaire n'ont pas besoin d’être qualifiées par un nom de feuille. Par exemple, si vous stockez une fonction personnalisée nommée DEFMOY dans un classeur nommé NouvFonc.xlsm, vous devez utiliser la syntaxe suivante lorsque cette fonction est utilisée dans un autre classeur : =NOUVFONC.XLS!DEFMOY (A1:A50). En revanche, si la fonction est située dans un fichier de macro complémentaire ouvert, vous utiliserez une syntaxe plus simple, car vous n'avez plus à faire référence au fichier : =DEFMOY (A1:A50)
- Faciliter l'accès aux utilisateurs : une fois identifié l'emplacement de la macro complémentaire, celle-ci apparaît dans la boite de dialogue Macro complémentaire, avec un nom évocateur et une brève description de son usage. L'utilisateur peut facilement activer ou désactiver votre complément.
- Mieux contrôler le chargement : les macros complémentaires peuvent être ouvertes automatiquement au démarrage d'Excel, peu importe le dossier où elles sont stockées.
- Eviter l’affichage d'une invite au déchargement : quand une macro complémentaire est refermée, l'utilisateur ne voit jamais apparaître l'invite Enregistrer sous.
Travailler avec des macros complémentaires
[modifier | modifier le wikicode]Le moyen le plus simple et le plus efficace de charger et décharger les macros complémentaires consiste à suivre les instructions suivantes :
- Fichier → Options → Compléments
- Compléments Excel (qui se trouve en bas de la fenêtre Options Excel) → cliquer sur le bouton Atteindre
N.B. : on peut directement passer par l'onglet Développeur et cliquer sur le bouton Compléments
Une fois ces actions réalisées, la boîte de dialogue ci-dessous (figure 1) s'affiche :
Elle recense toutes les macros complémentaires reconnues par Excel. Afin d'ouvrir ou fermer une macro complémentaire, il suffit seulement de les cocher ou décocher, directement depuis cette boîte de dialogue. De manière générale, lorsque vous allez ouvrir une macro complémentaire, le ruban rouge change quelque peu :
- Excel affiche un nouvel onglet
- Excel ajoute des commandes à un onglet existant
Par exemple, lors de l'ouverture de l'Utilitaire d'analyse, un nouvel élément Utilitaire d'analyse s'ajoutera à l'onglet Données. Cependant, il se peut également qu’il ne se passe rien, c’est notamment le cas lorsque la macro complémentaire ne contient que des fonctions personnalisées de feuille de calcul. En effet, ces dernières apparaîtront dans la boite de dialogue Insérer une fonction et il n'y aura donc aucun changement visible directement depuis l'interface utilisateur.
Notez qu’il existe un autre moyen pour utiliser les macros complémentaires, grâce à la commande Ouvrir. Cependant, en passant par celle-ci, vous ne les retrouverez pas dans la boîte de dialogue Macro complémentaire et il ne sera pas possible de les quitter avec la commande Fermer. En effet, la macro complémentaire ne pourra être supprimée qu'en quittant ou redémarrant Excel ou encore, en utilisant une macro qui aura pour but de fermer cette macro complémentaire.
Les bases des macros complémentaires
[modifier | modifier le wikicode]Par déduction, afin de créer des macros complémentaires il faut utiliser un classeur contenant des macros. En effet, bien que l’on puisse convertir n’importe quelle feuille de calcul en macro complémentaire, tous les classeurs ne peuvent pas en bénéficier. Ceux dont les feuilles ne contiennent pas de macros n'ont aucun intérêt puisque les macros complémentaire sont masquées. Du coup, l'idéal est d’utiliser un classeur avec des macros à usage général telles que les procédures Sub et Function. À partir de là, créer une macro complémentaire est assez simple, il suffit de suivre la procédure suivante :
- Développez votre application et assurez-vous que tout fonctionne correctement. Attention ! N'oubliez pas d'inclure une méthode d'exécution des macros (ajout de nouvelles commandes dans le ruban ou création raccourci). Cependant, il est inutile d'inclure une méthode d'exécution si le complément ne contient que des fonctions. En effet, elles apparaîtront dans la boîte de dialogue Insérer une fonction.
- Testez l’application en l'exécutant quand un classeur différent est actif. Cela permet de simuler le comportement de l’application lorsqu'elle est utilisée comme macro complémentaire. En effet, une telle macro n'est jamais le classeur actif.
- Activez l'éditeur VBE → sélectionner le classeur (fenêtre Projet) → Outils/Propriétés de VBAProject → onglet Protection → Cochez "Verrouiller" le projet → Entrez un mot de passe → OK. Cette étape a pour objectif d'empêcher les autres utilisateurs de visualiser ou modifier vos macros et vos objets UserForm. Elle n’est pas obligatoire.
- Dans Excel : ouvrir l'onglet Développeur → Panneau de document → OK. Le panneau apparaît sous le ruban.
- Choisir un nom dans le champ "Titre" ainsi qu'une description précise dans le champ Commentaires. Encore une fois, ces étapes (4 et 5) ne sont pas obligatoires. Cependant, elles permettent une meilleure utilisation de la macro complémentaire, notamment votre description puisqu'elle sera affichée dans la boîte de dialogue Macro complémentaire, que l’on a vu un peu plus haut.
- Fichier → Enregistrez sous.
- Boîte de dialogue → Enregistrez sous → déroulez le menu Type de fichier → choisir Macro complémentaire Microsoft Excel (*.xlam).
- Choisissez le dossier de stockage de la macro complémentaire. Excel propose par défaut un dossier nommé AddIns, mais la sauvegarde peut se faire dans n’importe quel autre emplacement.
- Enregistrer. Et voila ! Vous avez créé une macro complémentaire !
Un exemple de macro complémentaire
[modifier | modifier le wikicode]
L'ensemble de cette partie sera consacré à notre exemple. Nous allons étudier toutes les étapes fondamentales pour la création d'une macro complémentaire utile. Pour une compréhension simple de l'utilité des macros complémentaires, nous utiliserons un cas comprenant une macro avec un UserForm proposant plusieurs options de changement de texte et nous la transformerons en macro complémentaire.
Configurer le classeur
[modifier | modifier le wikicode]Le classeur est constitué d'une feuille de calcul vierge, d'un module VBA et d'un objet UserForm.
Prenons l'exemple simple d'un UserForm avec les options suivantes :
- Majuscules : met toutes les lettres en majuscules
- Minuscules : met toutes les lettres en minuscules
- 1ère lettre en majuscules : met la première lettre en majuscule, et toutes les autres en minuscules, et ce pour chaque mot d'un texte
- Phrase : seule la première lettre du texte est mise en majuscule, tout le reste en minuscules
- Inverser la casse : met les majuscules en minuscules, et vice versa
La figure 2 ci-contre montre à quoi va ressembler notre objet UserForm1. Les cinq contrôles OptionButton sont placés à l'intérieur d'un contrôle Frame. Vous trouvez aussi, comme il se doit, un bouton Annuler(appelé BoutonAnnuler) et un bouton OK (appelé BoutonOK).
Le code exécuté lorsque l'utilisateur clique sur le bouton Annuler est très simple :
Private Sub BoutonAnnuler_Click()
Unload UserForm1
End Sub
Tout le travail est effectué par le code suivant, qui est exécuté lorsque l'utilisateur clique sur le bouton OK :
Private Sub BoutonOK_Click()
Dim TextCells As Range
Dim cell As Range
Dim text As String
Dim i As Long
'Créer un objet avec juste des constantes de texte'
On Error Resume Next
Set TextCells = Selection.SpecialCells(xlConstants, xlTextValues)
'Désactiver la mise à jour de l'écran'
Application.ScreenUpdating = False
'Boucle sur les cellules'
For Each cell In TextCells
text = cell.Value
Select Case True
Case OptionLower 'minuscules
cell.Value = LCase(cell.Value)
Case OptionUpper 'MAJUSCULES'
cell.Value = UCase(cell.Value)
Case OptionProper '1ère lettre en majuscule'
cell.Value = Application.WorksheetFunction.Proper(cell.Value)
Case OptionSentence 'Phrase'
text = UCase(Left(cell.Value, 1))
text = text & LCase(Mid(cell.Value, 2, Len(cell.Value)))
cell.Value = text
Case OptionToggle 'inverser la casse'
For i = 1 To Len(text)
If Mid(text, i, 1) Like "[A-Z]" Then
Mid(text, i, 1) = LCase(Mid(text, i, 1))
Else
Mid(text, i, 1) = UCase(Mid(text, i, 1))
End If
Next i
cell.Value = text
End Select
Next
'Décharge la boîte de dialogue'
Unload UserForm1
End Sub
Tester le classeur
[modifier | modifier le wikicode]Tester la macro complémentaire avant de convertir ce classeur. Pour simuler ce qui se produira lorsque le classeur sera devenu une macro complémentaire, vous devez le tester alors qu'un autre classeur est actif. Rappelez vous qu'une macro complémentaire n'est jamais le classeur actif. C'est donc uniquement en la testant depuis un autre classeur que vous pourrez détecter d'éventuelles erreurs.
- Ouvrez un nouveau classeur et entrez des données dans ces cellules. Pour ce test, entrez divers types d'information : du texte bien sûr, mais aussi des formules ou des valeurs. Ou alors, ouvrez un classeur existant et utilisez-le pour vos essais. En fait, il vaut mieux partir dans ce cas d'une copie de classeur, car les modifications dues à la macro complémentaire ne pourront pas être annulées.
- Sélectionnez une ou plusieurs cellules (voire des lignes ou des colonnes entières).
- Exécutez la macro en choisissant la nouvelle commande Changer la casse dans le menu contextuel de votre sélection.
Si la commande Changer la casse n'apparaît pas en bas du menu Outils, c’est très certainement parce que vous n'avez pas autorisé l'activation des macros à l'ouverture du classeur contenant la macro complémentaire. Fermez-le puis rouvrez-le, en veillant cette fois à autoriser l'activation des macros.
Ajouter des informations utiles
[modifier | modifier le wikicode]Il est recommandé d'ajouter une description aux macros complémentaires, bien que ce ne soit pas obligatoire :
- Activez le classeur contenant la macro complémentaire Changer la casse (change casse. xlsm)
- Sous l'onglet Développeur, cliquez sur le bouton Panneau de documents dans la catégorie Modifier. Cliquez ensuite sur OK. Excel affiche le panneau Propriétés du document au-dessus de la barre de formule (comme l’on peut le voir sur la figure 3 ci-dessous).
- Tapez un nom dans le champ Titre. Il apparaîtra dans la boite de dialogue Macro complémentaire. Tapez par exemple Changer la casse.
- Entrez une description dans le champ Commentaires. Elle apparaîtra en bas de la boîte de dialogue Macro complémentaire lorsque la macro sera sélectionnée. Saisissez par exemple : Change la casse du texte sélectionné. Pour accéder à cet outil, cliquez droit pour ouvrir le menu contextuel.
- Cliquez sur la croix qui se trouve à droite du panneau pour le refermer.
.png)
Pour protéger votre code VBA, procédez comme expliqué plus haut, au début de la section "Les bases des macros complémentaires"
Générer la macro complémentaire
[modifier | modifier le wikicode]À ce point, vous avez testé la macro Changer la casse et les essais sont concluants. Il s'agit à présent de créer la macro complémentaire :
- Activez l'éditeur VBE et , dans la fenêtre Projet, sélectionnez le classeur contenant la macro Changer la casse (normalement, changer la casse.xslm)
- Choisissez Outils/Propriétés de VBAProject puis cliquez sur l'onglet Protection.
- Cochez la case Verrouillez le projet pour l'affichage. Entrez ensuite un mot de passe (à deux reprises pour le confirmer)
- Cliquez sur OK.
- Enregistrer le classeur.
- Revenez sous Excel.
- Activez le classeur contenant la macro complémentaire puis choisissez Fichier/Enregistrer-sous.
- Déroulez la liste Type de fichier et choisissez Macro complémentaire Microsoft Excel (*.xlam)
- Cliquez sur Enregistrer.
Une nouvelle macro complémentaire est créée, avec l'extension de fichier .xlam. Le fichier XLSM original reste ouvert.
Ouvrir la macro complémentaire
[modifier | modifier le wikicode]Pour éviter toute confusion, fermez le classeur XLSM avant d'ouvrir la macro complémentaire créée à partir de lui.
Ouvrez la macro complémentaire en procédant comme suit:
- Sous l'onglet Développeur, cliquez sur le bouton Compléments dans la catégorie de même nom. Excel affiche la boîte de dialogue Macro complémentaire.
- Cliquez sur le bouton Parcourir.
- Localisez et sélectionner la macro complémentaire que vous venez de créer.
- Cliquez sur OK pour fermer la boîte de dialogue Parcourir. La nouvelle macro complémentaire apparaît dans la boîte de dialogue Macro complémentaire comme on peut le remarquer sur la figure ci-dessous. Par ailleurs notez que la description que vous avez inscrit dans le panneau Propriétés du document (comme vu plus haut dans cette leçon), seront visible en bas de cette boîte de dialogue.
- Vérifiez bien que la case qui se trouve devant le nom de votre macro complémentaire est bien cochée.
- Cliquez sur OK pour fermer la boîte de dialogue et ouvrir la macro complémentaire.
Votre macro complémentaire est maintenant disponible dans tous vos classeurs. Tant que la case correspondante reste cochée dans la boîte de dialogue Macro complémentaire, elle sera disponible chaque fois que vous ouvrirez Excel (et que son emplacement sera accessible).
Distribuer la macro complémentaire
[modifier | modifier le wikicode]Votre macro complémentaire peut être distribuée auprès d'autres utilisateurs d'Excel (si vous le souhaitez). Il suffit pour cela de leur remettre un exemplaire du fichier XLAM (ils n'ont pas besoin de la version XLSM). Dès qu'ils ouvrent la macro complémentaire, l'option Changer la casse apparaît en bas du menu contextuel associé aux cellules, aux lignes et aux colonnes. Si, et comme, le fichier est protégé, le code de la macro ne peut être examiné (à moins évidemment de connaître le mot de passe).
Modifier la macro complémentaire
[modifier | modifier le wikicode]Une macro complémentaire enregistrée dans un fichier XLAM peut être éditée comme tout autre classeur (vous n'avez pas besoin du fichier XLSM). Il en conviendra qu’il faudra la déprotéger de la manière suivante :
- Ouvrez le fichier XLA si ce n’est pas déjà fait.
- Activez l'éditeur VBE
- Dans la fenêtre Projet, double-cliquez sur le nom du projet. Le mot de passe vous est demandé
- Entrez le mot de passe puis cliquez sur OK
- Modifiez le code selon vos besoins
- Enregistrez le fichier à partir de l'éditeur VBE, en cliquant sur Fichier/Enregistrer.
Avec cette leçon vous savez dorénavant comment créer des macros complémentaires ainsi que leur utilitées. Grâce à l'exemple proposé dans la leçon, vous avez pu apprendre les étapes à suivre afin de modifier la casse des caractères dans les cellules sélectionnées. Pour en apprendre davantage, le moyen le plus simple est de s'exercer en créant d'autres macros complémentaires par vous-même. Enfin, nous vous invitons à répondre au Quizz Macros Complémentaires, pour vous tester sur le sujet.
Annexes
[modifier | modifier le wikicode]Bibliographie
[modifier | modifier le wikicode]- John Walkenbach, 2013, Programmation VBA pour Excel Pour Les Nuls, FIRST
Liens internes
[modifier | modifier le wikicode]Liens externes
[modifier | modifier le wikicode]
