Table des matières
Même si vous ne savez pas programmer, il existe de nombreux endroits (livres, sites Web, forums) où vous pouvez trouver du code de macro VBA prêt à l'emploi pour un grand nombre de tâches typiques dans Excel. D'après mon expérience, la plupart des utilisateurs collectent tôt ou tard leur collection personnelle de macros pour automatiser les processus de routine, qu'il s'agisse de traduire des formules en valeurs, d'afficher des sommes en mots ou d'additionner des cellules par couleur. Et ici, le problème se pose - le code de macro dans Visual Basic doit être stocké quelque part afin d'être utilisé plus tard dans le travail.
L'option la plus simple est d'enregistrer le code de la macro directement dans le fichier de travail en allant dans l'éditeur Visual Basic à l'aide du raccourci clavier autre+F11 et en ajoutant un nouveau module vide via le menu Insertion – Module:
Cependant, il y a plusieurs inconvénients à cette méthode :
- S'il y a beaucoup de fichiers de travail et qu'une macro est nécessaire partout, comme une macro pour convertir des formules en valeurs, alors vous devrez copier le code dans chaque livre.
- Ne doit pas être oublié enregistrer le fichier au format compatible avec les macros (xlsm) ou au format livre binaire (xlsb).
- Lors de l'ouverture d'un tel fichier macro-protection émettra à chaque fois un avertissement qui doit être acquitté (enfin, ou désactivera complètement la protection, ce qui n'est pas toujours souhaitable).
Une solution plus élégante serait de créer votre propre complément (Excel Add-in) – un fichier séparé d'un format spécial (xlam) contenant toutes vos macros « préférées ». Les avantages de cette approche :
- Ce sera assez connecter le module complémentaire une fois dans Excel - et vous pouvez utiliser ses procédures et fonctions VBA dans n'importe quel fichier sur cet ordinateur. Il n'est donc pas nécessaire de réenregistrer vos fichiers de travail aux formats xlsm et xlsb, car. le code source n'y sera pas stocké, mais dans le fichier de complément.
- Directory vous ne serez pas dérangé par les macros non plus. les modules complémentaires sont, par définition, des sources fiables.
- Peut faire onglet séparé sur le ruban Excel avec de jolis boutons pour exécuter des macros complémentaires.
- Le complément est un fichier séparé. Le sien facile à porter d'ordinateur en ordinateur, partagez-le avec des collègues ou même vendez-le 😉
Passons en revue l'ensemble du processus de création de votre propre complément Microsoft Excel, étape par étape.
Étape 1. Créer un fichier de complément
Ouvrez Microsoft Excel avec un classeur vierge et enregistrez-le sous n'importe quel nom approprié (par exemple Mon complément Excel) au format add-in avec la commande Fichier - Enregistrer sous ou clés F12, en spécifiant le type de fichier Complément Excel:
Veuillez noter que par défaut, Excel stocke les compléments dans le dossier C:UsersYour_nameAppDataRoamingMicrosoftAddIns, mais, en principe, vous pouvez spécifier tout autre dossier qui vous convient.
Étape 2. Nous connectons le complément créé
Maintenant, le complément que nous avons créé à la dernière étape Mon complément Excel doit être connecté à Excel. Pour cela, rendez-vous dans le menu Fichier – Options – Modules complémentaires (Fichier — Options — Compléments), cliquez sur le bouton À propos (Aller) au bas de la fenêtre. Dans la fenêtre qui s'ouvre, cliquez sur le bouton Avis (Parcourir) et spécifiez l'emplacement de notre fichier de complément.
Si vous avez tout fait correctement, alors notre Mon complément Excel devrait apparaître dans la liste des add-ons disponibles :
Étape 3. Ajouter des macros au complément
Notre complément est connecté à Excel et fonctionne correctement, mais il ne contient pas encore une seule macro. Faisons le plein. Pour cela, ouvrez l'éditeur Visual Basic avec le raccourci clavier autre+F11 ou par bouton Visual Basic languette promoteur (Développeur). Si des onglets promoteur non visible, il peut être affiché à travers Fichier – Options – Configuration du ruban (Fichier — Options — Personnaliser le ruban).
Il devrait y avoir une fenêtre dans le coin supérieur gauche de l'éditeur Projet (s'il n'est pas visible, allumez-le via le menu Affichage — Explorateur de projet):
Cette fenêtre affiche tous les classeurs ouverts et les compléments Microsoft Excel en cours d'exécution, y compris le nôtre. Projet VBA (MonExcelAddin.xlam) Sélectionnez-le avec la souris et ajoutez-y un nouveau module via le menu Insertion – Module. Dans ce module, nous stockerons le code VBA de nos macros complémentaires.
Vous pouvez soit taper le code à partir de zéro (si vous savez programmer), soit le copier depuis un endroit prêt à l'emploi (ce qui est beaucoup plus facile). Pour tester, entrons le code d'une macro simple mais utile dans le module vide ajouté :
Après avoir entré le code, n'oubliez pas de cliquer sur le bouton de sauvegarde (disquette) dans le coin supérieur gauche.
Notre macro FormulesVersValeurs, comme vous pouvez facilement l'imaginer, convertit les formules en valeurs dans une plage présélectionnée. Parfois, ces macros sont également appelées procédures. Pour l'exécuter, vous devez sélectionner des cellules avec des formules et ouvrir une boîte de dialogue spéciale Macros de l'onglet promoteur (Développeur — Macros) ou raccourci clavier autre+F8. Normalement, cette fenêtre affiche les macros disponibles de tous les classeurs ouverts, mais les macros complémentaires ne sont pas visibles ici. Malgré cela, nous pouvons entrer le nom de notre procédure dans le champ nom de la macro (Nom de la macro)puis cliquez sur le bouton Courir (Cours) – et notre macro fonctionnera :
| |
Ici, vous pouvez également attribuer un raccourci clavier pour lancer rapidement une macro - le bouton est responsable de cela Paramètres (Options) dans la fenêtre précédente Macro:
Lors de l'attribution des touches, gardez à l'esprit qu'elles sont sensibles à la casse et à la disposition du clavier. Donc, si vous attribuez une combinaison comme Ctrl+Й, alors, en fait, à l'avenir, vous devrez vous assurer que la mise en page est activée et appuyer en plus Shiftpour obtenir la majuscule.
Pour plus de commodité, nous pouvons également ajouter un bouton pour notre macro à la barre d'outils d'accès rapide dans le coin supérieur gauche de la fenêtre. Pour ce faire, sélectionnez Fichier - Options - Barre d'outils d'accès rapide (Fichier — Options — Personnaliser la barre d'outils d'accès rapide), puis dans la liste déroulante en haut de la fenêtre l'option Macros. Après cela, notre macro FormulesVersValeurs peut être placé sur le panneau avec le bouton Ajouter (Ajouter) et sélectionnez-en une icône avec le bouton Modifier (Edit):
Étape 4. Ajouter des fonctions au complément
Mais macro-procédures, il y a aussi macros de fonctions ou comme on les appelle UDF (Fonction définie par l'utilisateur = fonction définie par l'utilisateur). Créons un module séparé dans notre add-on (commande de menu Insertion – Module) et collez-y le code de la fonction suivante :
Il est facile de voir que cette fonction est nécessaire pour extraire la TVA du montant TTC. Pas le binôme de Newton, bien sûr, mais il nous servira d'exemple pour montrer les principes de base.
Notez que la syntaxe d'une fonction est différente de celle d'une procédure :
- construction est utilisée Fonction …. Fonction de fin plutôt ; Sous … Fin Sous
- après le nom de la fonction, ses arguments sont indiqués entre parenthèses
- dans le corps de la fonction, les calculs nécessaires sont effectués puis le résultat est affecté à une variable portant le nom de la fonction
Notez également que cette fonction n'est pas nécessaire et qu'il est impossible d'exécuter la procédure de macro précédente via la boîte de dialogue. Macros et bouton Courir. Une telle fonction macro doit être utilisée comme une fonction de feuille de calcul standard (SUM, IF, VLOOKUP…), c'est-à-dire simplement entrer dans n'importe quelle cellule, en spécifiant la valeur du montant avec la TVA comme argument :
… ou entrer par la boîte de dialogue standard d'insertion d'une fonction (bouton fx dans la barre de formule), en sélectionnant une catégorie Défini par l'utilisateur (Défini par l'utilisateur):
Le seul moment désagréable ici est l'absence de la description habituelle de la fonction en bas de la fenêtre. Pour l'ajouter, vous devrez procéder comme suit :
- Ouvrez Visual Basic Editor avec un raccourci clavier autre+F11
- Sélectionnez le complément dans le panneau Projet et appuyez sur la touche F2pour ouvrir la fenêtre de l'Explorateur d'objets
- Sélectionnez votre projet de complément dans la liste déroulante en haut de la fenêtre
- Faites un clic droit sur la fonction qui apparaît et sélectionnez la commande biens.
- Entrez une description de la fonction dans la fenêtre Description
- Enregistrer le fichier de complément et redémarrer excel.
Après le redémarrage, la fonction devrait afficher la description que nous avons entrée :
Étape 5. Créer un onglet complémentaire dans l'interface
La touche finale, bien que non obligatoire, mais agréable sera la création d'un onglet séparé avec un bouton pour exécuter notre macro, qui apparaîtra dans l'interface Excel après avoir connecté notre complément.
Les informations sur les onglets affichés par défaut sont contenues dans le livre et doivent être formatées dans un code XML spécial. Le moyen le plus simple d'écrire et d'éditer un tel code consiste à utiliser des programmes spéciaux - des éditeurs XML. L'un des plus pratiques (et gratuits) est le programme de Maxim Novikov Éditeur XML de ruban.
L'algorithme pour travailler avec est le suivant :
- Fermez toutes les fenêtres Excel afin qu'il n'y ait pas de conflit de fichiers lorsque nous modifions le code XML du complément.
- Lancez le programme Ribbon XML Editor et ouvrez-y notre fichier MyExcelAddin.xlam
- Avec bouton onglets dans le coin supérieur gauche, ajoutez l'extrait de code pour le nouvel onglet :
- Vous devez mettre des guillemets vides id notre onglet et groupe (tous les identifiants uniques), et dans étiquette – les noms de notre onglet et un groupe de boutons dessus :
- Avec bouton bouton (dans la fenêtre de contrôle qui apparaît maintenant) sur le panneau de gauche, ajoutez un code vide pour le bouton et ajoutez-y des balises :
- étiquette est le texte sur le bouton
— imageMso — c'est le nom conditionnel de l'image sur le bouton. J'ai utilisé une icône de bouton rouge appelée AnimationCustomAddExitDialog. Les noms de tous les boutons disponibles (et il y en a plusieurs centaines !) peuvent être trouvés sur un grand nombre de sites sur Internet si vous recherchez les mots-clés « imageMso ». Pour commencer, vous pouvez aller ici.
- surAction – c'est le nom de la procédure de rappel – une courte macro spéciale qui exécutera notre macro principale FormulesVersValeurs. Vous pouvez appeler cette procédure comme bon vous semble. Nous l'ajouterons un peu plus tard.
- Vous pouvez vérifier l'exactitude de tout ce qui est fait en utilisant le bouton avec une coche verte en haut de la barre d'outils. Au même endroit, cliquez sur le bouton avec une disquette pour enregistrer toutes les modifications.
- Fermer l'éditeur XML du ruban
- Ouvrez Excel, allez dans l'éditeur Visual Basic et ajoutez une procédure de rappel à notre macro KillFormulesafin qu'il exécute notre macro principale pour remplacer les formules par des valeurs.
- Nous enregistrons les modifications et, en revenant à Excel, vérifions le résultat :
C'est tout - le complément est prêt à l'emploi. Remplissez-le avec vos propres procédures et fonctions, ajoutez de beaux boutons - et il deviendra beaucoup plus facile d'utiliser des macros dans votre travail.
- Que sont les macros, comment les utiliser dans votre travail, où obtenir le code de macro dans Visual Basic.
- Comment créer un écran de démarrage lors de l'ouverture d'un classeur dans Excel
- Qu'est-ce qu'un livre de macros personnelles et comment l'utiliser