IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Philippe Tulliez

VBA Excel - Créer une macro complémentaire

Noter ce billet
par , 13/12/2020 à 15h05 (405 Affichages)
PREAMBULE

Lorsque l’on écrit une procédure Sub (appelée communément macro) ou une fonction, il y a trois types de fichiers dans lesquels on peut les stocker.
  • Dans un classeur normal suffixé, xlsm ou xlsb pour les versions 2007 et suivantes, (xls pour les versions antérieures à 2007)
  • Dans un classeur modèle xltm (xlt pour les versions antérieures à 2007)
  • Dans un classeur de macro personnelle dont le nom est Personal.xlsb depuis la version 2007 et Personnal.xls pour les versions antérieures à 2007
  • Dans un classeur de macro complémentaire suffixé xlam depuis la version 2007 ou xla pour les versions antérieures à 2007

Les macros complémentaires sont également appelées Compléments ou Add-In

Type de fichier Avantages Inconvénients
xlsm, xlsb Il peut être utilisé par toute personne qui ouvre le classeur Les procédures sont utilisables tant que le classeur est ouvert
Personal.xlsb Les procédures peuvent être utilisées depuis n’importe quel classeur ouvert
Il s'ouvre automatiquement à l'ouverture d'excel
Ne fonctionne que sur le PC où se trouve le fichier Personal.xlsb
xlam Les procédures peuvent être utilisées depuis n’importe quel classeur ouvert.
Il s'ouvre automatiquement à l'ouverture d'excel
On peut distribuer cette macro complémentaire et ainsi permettre son utilisation à d’autres utilisateurs
Le classeur doit être installé (chargé)

COMMENT ECRIRE SES CODES DANS UN FICHIER MACROS COMPLEMENTAIRES ?

L’écriture est la même que pour les autres classeurs.
Une macro complémentaire est un classeur suffixé XLAM dont les feuilles sont cachées.
Il suffit donc d'écrire ses procédures (Sub et Function) de la même manière que pour un classeur classique

Attention toutefois qu'il faut avoir à l'esprit que les procédures présentes dans un classeur xlam peuvent fonctionner avec d'autres classeurs ouverts.
Si nous avons pour habitude d'utiliser l'objet ThisWorkbook pour écrire et lire dans un classeur, on devra plutôt utiliser ActiveWorkbook car dans un classeur xlam, ThisWorkbook c'est lui.
On utilisera donc ThisWorkbook si nous devons lire des informations dans une feuille paramètre du classeur XLAM comme par exemple pour afficher un menu mais ActiveWorkbook pour lire et écrire dans le classeur actif ou bien entendu en utilisant une variable objet référençant un autre classeur ouvert.

ENREGISTRER UNE MACRO COMPLEMENTAIRE

Pour enregistrer un classeur xlsm ou xlsb dans le format xlam
  • Cliquer sur l’onglet [Fichier] et choisir Enregistrer sous... (Raccourci F12)
  • Donner un nom au classeur (par exemple MagicTools)
  • Sélectionner comme type de fichier "Macro complémentaire Microsoft Excel (*.xlam)" ou comme illustré ci-dessous Complément Excel (*.xlam)
    et valider
  • Fermer ensuite le classeur actif (.xlsm) que l’on pourra éventuellement supprimer par la suite pour éviter de faire doublon avec le classeur XLAM.

Avant d'enregistrer le XLAM, vous pouvez supprimer toutes les feuilles de calcul inutilisées en n'en laissant plus qu'une, afin de réduire la taille du fichier.
Nous verrons dans un autre billet qu’il peut être utile d’utiliser des feuilles de ce classeur comme paramètres

Nom : SaveAs xlam.png
Affichages : 147
Taille : 4,5 Ko

Où enregistrer le classeur de macro complémentaire ?
Un fichier suffixé XLAM peut être sauvé n'importe où mais par défaut, il est proposé le répertoire AddIns

INSTALLER OU DESINSTALLER UNE MACRO COMPLEMENTAIRE

Bien que l’on puisse lancer une application écrite dans un fichier de macro-complémentaire directement en ouvrant le classeur, il est préférable de l’installer en tant que complément à Excel.
"Installer" est en fait un terme mal choisi, on devrait plutôt utiliser "Charger".

Vérification à faire avant l'installation
Il est possible que pour des raisons de sécurité, Excel soit paramétré pour n'accepter que des compléments provenant d'éditeurs approuvés par certificat.
Si nous souhaitons charger des macros complémentaires ne possédant pas ce type de certificats, il nous faudra vérifier que les paramètres de sécurités le permettent.

Allons dans les options d'excel ([Fichier] - Options)

Procédons dans l'ordre comme illustré ci-dessous en sélectionnant les commandes
  • Centre de gestion de la confidentialité
  • Paramètres du Centre de gestion de la confidentialité...
  • Compléments

Vérifions enfin qu'aucune option n'est cochée dans le groupe Compléments (COM, VSTO et autres)

Nom : Dlg Centre de gestion de confidentialité - Complément.png
Affichages : 111
Taille : 176,3 Ko

Comment charger un complément ?

Pour charger la macro complémentaire :
  1. Ouvrir la boîte de dialogue Options d’excel ([Fichier] - Options
  2. Sélectionner Complément dans le volet et cliquer sur le bouton Atteindre...
  3. Si la macro apparaît dans la liste, la cocher et valider
  4. Dans le cas contraire, cliquer sur Parcourir, sélectionner le fichier XLAM et valider

Illustration des points 1 et 2

Nom : dlg Afficher et gérer les compléments Microsoft Office (Options - Compléments).png
Affichages : 91
Taille : 71,8 Ko

Illustration des points 3 et 4 (Boîte de dialogue Compléments)

Nom : Dlg Compléments.png
Affichages : 101
Taille : 17,7 Ko

Comment désinstaller un complément ?
Il suffit de décocher le nom du complément dans la boîte de dialogue "Compléments"

Si l'on souhaite supprimer définitivement le complément, il y a lieu de procéder dans l'ordre
  1. D'abord décocher le nom du complément comme expliqué ci-dessus
  2. Fermer Excel
  3. Supprimer le fichier xlam, modifier son nom ou le déplacer
  4. Ouvrir Excel et accéder à nouveau à la boîte de dialogue "Compléments"
  5. Sélectionner le complément dans la liste
  6. Un message d'erreur va apparaître
    Impossible de trouver la macro complémentaire c:\le chemin complet\NomClasseur.xlam
    Voulez-vous la supprimer de la liste ?
  7. Confirmer par OK
  8. Fermer la boîte de dialogue "Compléments" en cliquant sur OK

Le complément n'apparaîtra plus dans la liste

UTILISATION DES PROCEDURES D'UN COMPLEMENT

Par VBA
Il est évidemment possible d'accéder à des procédures (Sub ou Function) depuis un classeur quelconque et ouvert par le VBA
Il est également possible d'intercepter un événement qui se produit depuis, n'importe quel classeur ouvert depuis le complément XLAM

Ceci fera l'objet d'un autre billet.

Depuis Excel (sans VBA)
Les fonctions sont utilisables directement comme n'importe quelle fonction personnalisée

Pour les procédures SUB

1 - Ajouter une commande dans la barre d'outil d'accès rapide

Etapes (voir illustration ci-dessous)
  • Aller dans les options d'Excel et choisir Barre d'Outils Accès Rapide ou directement depuis la barre d'accès rapide choisir Autres commandes...
  • Sélectionner Macros dans la liste déroulante
  • Choisir la procédure (macro) à associer au bouton
  • Cliquer sur le bouton Ajouter

Ce bouton est par défaut accessible depuis tous les classeurs (par défaut) mais il est possible de ne l'afficher que pour le classeur actif.

Nom : dlg Personnaliser la barre d'outils Access rapide pour Macros.png
Affichages : 101
Taille : 145,6 Ko

2 - Associer un raccourci clavier à la procédure

Dans le classeur original (xlsb ou xlsm), activer la boîte de dialogue Macro qui liste l'ensemble des procédures Sub (Alt + F8)
  1. Sélectionner la procédure à laquelle doit être associé le raccourci clavier
  2. Cliquer sur le bouton Options...
  3. Assigner le raccourci


Attention toutefois que si le classeur actif a le même raccourci clavier, c'est la procédure associée au raccourci de ce classeur qui sera activée. Dès qu'il n'est plus actif, c'est le raccourci du fichier XLAM qui primera

Nom : dlg Option de macro (Macro).png
Affichages : 87
Taille : 26,9 Ko

COMMENT CHARGER DYNAMIQUEMENT UNE MACRO COMPLEMENTAIRE ?

Lorsque l'on écrit une application nécessitant l'utilisation d'une macro complémentaire particulière (Add-In, Complément), il peut être intéressant de charger dynamiquement un complément à l'aide d'une procédure VBA.

Voir à ce sujet un nouveau billet sur le sujet, titré VBA - Excel - Charger dynamiquement une macro complémentaire - Quelques procédures intéressantes

BILLETS PRECEDENTS SUR LE MÊME SUJET

Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog Viadeo Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog Twitter Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog Google Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog Facebook Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog Digg Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog Delicious Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog MySpace Envoyer le billet « VBA Excel  - Créer une macro complémentaire » dans le blog Yahoo

Mis à jour 17/12/2020 à 16h34 par Philippe Tulliez

Catégories
VBA Excel

Commentaires

  1. Avatar de Patrice740
    • |
    • permalink
    Bonjour Philippe

    Tu cites comme inconvénient des xlsm et xlsb « Ne fonctionne que si le classeur est ouvert ».
    C'est vrai dans tous les cas, xlam et personnal compris, l'ouverture se fait de façon transparente en même temps que l'application.
    C'est un inconvénient des xlam, il doivent être soit ouverts soit "installés"
  2. Avatar de Philippe Tulliez
    • |
    • permalink
    Bonjour Patrice,
    Merci pour ton commentaire.
    Tu as raison, j'aurais dû être plus clair en précisant qu'il fallait bien entendu que le fichier xlam soit chargé et qu'en principe si on a un classeur Personnal.xlsb, il s'ouvre automatiquement sauf si on le supprime du répertoire xlStart
    Je vais corriger cela sans tarder
    Philippe

    Citation Envoyé par Patrice740
    Bonjour Philippe

    Tu cites comme inconvénient des xlsm et xlsb « Ne fonctionne que si le classeur est ouvert ».
    C'est vrai dans tous les cas, xlam et personnal compris, l'ouverture se fait de façon transparente en même temps que l'application.
    C'est un inconvénient des xlam, il doivent être soit ouverts soit "installés"
  3. Avatar de anasecu
    • |
    • permalink
    Bonjour,

    Je ne vais pas dénigrer cette procédure xlam que j'utilise à bon escient mais simplement signaler que cela peut être incompatible avec certains services informatiques qui interdisent ce genre d’installations même si sans doute ils ne savent pas en apprécier l’intérêt. Ainsi j'ai dû dissocier un module complémentaire et incorporer les modules dans chaque classeur : est-ce moins dangereux ?
  4. Avatar de Philippe Tulliez
    • |
    • permalink
    Bonjour Anasecu,
    Il y a des entreprises où même l'utilisation du VBA est interdite. J'ai aussi dispensé des formations dans des sociétés où l'usage de ma clé USB n'était pas autorisée alors que l'entièreté de mes cours s'y trouve
    Pour répondre à ta question, ce n'est pas plus dangereux d'utiliser un xlam qu'un xlsm ou xlsb. C'est simplement une autre façon de s'organiser.
    Tu peux utiliser également pour une application un classeur central ayant toutes les procédures et qui ouvre différents classeurs les traite et les ferme.
    Belle journée
    Philippe

    Citation Envoyé par anasecu
    Bonjour,

    Je ne vais pas dénigrer cette procédure xlam que j'utilise à bon escient mais simplement signaler que cela peut être incompatible avec certains services informatiques qui interdisent ce genre d’installations même si sans doute ils ne savent pas en apprécier l’intérêt. Ainsi j'ai dû dissocier un module complémentaire et incorporer les modules dans chaque classeur : est-ce moins dangereux ?
    Mis à jour 14/12/2020 à 20h11 par Philippe Tulliez (Corr. d'une redite)