Enregistrer les fonctions VBA dans la bibliothèque Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fonctions VBA Excel portables avec descriptions

Dans la formation précédente, nous avons créé deux nouvelles fonctions de feuille de calcul, grâce au code Visual Basic Excel. A l'instar des fonctions conditionnelles Nb.si et Somme.Si, ces fonctions permettent de dénombrer et sommer en fonction de la couleur de remplissage des cellules. Nous les avons respectivement nommées nbCouleurs et sommeCouleurs. Cependant, à ce stade, ces fonctions étant attachées au classeur dans lequel elles ont été créées, ne sont disponibles que pour ce dernier.



L'objectif de cette formation consiste à les rendre disponibles pour toute utilisation d'Excel, quel que soit le classeur. Et comme l'illustre la capture ci-dessus, nous souhaitons qu'un descriptif apparaisse dans l'assistant fonctions d'Excel. Ainsi, l'utilisateur saura comment les exploiter, sans même les connaître.

Sources et présentation
Il n'est pas question ici de reconstruire ces fonctions. Nous proposons de les récupérer avec le classeur à partir duquel elles ont été construites.

• Télécharger le classeur fonctions-vba-couleurs.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué des feuilles Classement et Sommes_couleurs sur lesquelles nous avions testé les fonctions créées par le code VBA. La seconde feuille notamment avait permis de tester la version surchargée de la fonction sommeCouleurs. En effet, à l'instar de la fonction Somme.Si, nous avions déclaré son troisième argument comme facultatif. De fait, elle peut être exploitée pour réaliser des additions sur une plage où le critère est compté. Mais elle peut aussi calculer la somme sur une plage de cellules en fonction des couleurs décelées sur une autre plage.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet sur la gauche, déployer l'affichage du dossier Modules,
• Puis, double cliquer sur l'élément Module1 pour afficher sa feuille de code,

Vous y notez effectivement la présence des fonctions VBA nbCouleurs et sommeCouleurs. Dans le classeur actif, si vous débutez le calcul dans une cellule vierge par le symbole égal et que vous tapez les premières lettres de l'une ou l'autre, Excel les propose contextuellement, sous forme d'info-bulle.

• Revenir sur la feuille du classeur (ALT + F11),
• Créer un nouveau classeur à l'aide du raccourci clavier CTRL + N par exemple,
• Dans une cellule, taper le symbole = pour débuter le calcul,
• Puis, saisir les premières lettres de la fonction nbCouleurs,

Comme l'illustre la figure ci-dessus, dans ce nouveau contexte, nos fonctions ne sont pas reconnues par Excel. Seule la fonction NbCar qui commence par les mêmes lettres, est proposée.

Le classeur de macros personnelles
Une solution pour rendre ces fonctions portables, consisterait à les enregistrer dans le classeur de macros personnelles, soit le fichier personal.xlsb. C'est d'ailleurs ce que nous avions fait dans la formation pour personnaliser l'environnement de travail, afin que les boutons du nouveau ruban restent fonctionnels, pour toutes les utilisations d'Excel.

• Fermer le nouveau classeur (CTRL + W) pour revenir sur le précédent,
• Puis, basculer dans l'éditeur de code VBA Excel (ALT + F11),

Dans l'explorateur de projet sur la gauche de l'éditeur, vous devriez noter la présence du projet associé au classeur de macros personnelles (Personal.xlsb).



Si tel n'est pas le cas, deux actions permettent d'y remédier. Soit depuis le ruban Affichage d'une feuille Excel, vous cliquez sur le bouton Afficher. Dans la boîte qui suit, vous sélectionnez Personal.xlsb et vous cliquez sur Ok. Soit, vous créez une nouvelle macro automatique temporaire que vous enregistrez dans le classeur de macros personnelles. Cette dernière action aura aussi pour effet de le réactiver.

• Dans l'éditeur VBA Excel, sélectionner le code des deux fonctions nbCouleurs et sommeCouleurs (Du Sub au End Sub pour les deux),
• Couper les éléments sélectionnés grâce au raccourci clavier CTRL + X,
• Dans l'explorateur de projet sur la gauche, sélectionner VBAProject (Personal.xlsb),
• Ensuite, cliquer sur le menu Insertion en haut de l'éditeur,
• Puis, choisir Module dans la liste,
• Dans la feuille de code du nouveau module qui apparaît, coller (CTRL + V) le code VBA précédemment coupé,
• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + F11),
• Créer un nouveau classeur (CTRL + N),
• Dans une cellule, taper le symbole = pour débuter le calcul,
• Puis taper les premières lettres de l'une des deux fonctions,

Comme vous le remarquez, Excel ne les suggère toujours pas. Elles ne semblent pas être reconnues. Pourtant, nous les avons bien collées dans le modèle Excel, celui qui porte toutes les préférences pour toutes les utilisations du tableur.

En réalité, elles sont bien présentes mais sont préfixées. C'est ce que nous proposons de constater.

• Cliquer sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Insérer une fonction,
• Dans la boîte de dialogue qui suit, choisir la catégorie Personnalisées à l'aide de la liste déroulante,

Comme vous le remarquez et comme l'illustre la capture ci-dessus, les deux fonctions font désormais bien partie de la librairie des fonctions de feuille de calcul Excel. Mais un problème de taille surgit. Elles sont préfixées du nom du modèle Excel, soit Personal.xlsb!. Et, pour des raisons d'ergonomie et de simplicité, nous souhaitons les exploiter directement par leur nom.

Créer un complément de fonctions Excel
Le classeur de macros personnelles n'est donc pas une solution satisfaisante pour rendre disponibles nos fonctions, par leurs noms, pour chaque classeur Excel. La solution consiste à créer un complément Excel puis à l'ajouter en référence de l'application. Comme nous l'avait appris la formation VBA pour créer des fonctions disponibles partout, une fois la référence ajoutée, les fonctions s'exploitent naturellement par leur nom et pour tout classeur.

• Fermer la boîte de dialogue des fonctions,
• Conserver le nouveau classeur ouvert,
• Basculer dans l'éditeur de code VBA Excel,
• Sélectionner de nouveau l'intégralité des deux fonctions précédemment collées dans le nouveau module du classeur de macros personnelles,
• Les couper à l'aide du raccourci clavier CTRL + X,
• Dans l'explorateur de projet, sélectionner l'élément VBA Project du nouveau classeur,
• Cliquer sur le menu Insertion en haut de l'éditeur puis sur Module dans la liste,
• Dans la feuille de code vierge qui apparaît, coller le code VBA des deux fonctions (CTRL + V),

Pour que le complément soit pleinement fonctionnel, nous souhaitons réaliser quelques opérations avant de l'enregistrer. Il s'agit de décrire les deux nouvelles fonctions afin que des indications soit fournies à l'utilisateur depuis la boîte de dialogue des fonctions Excel.

• Cliquer sur le menu Affichage en haut de l'éditeur de code VBA,
• Dans la liste, choisir Explorateur d'objets,

Une nouvelle fenêtre apparaît. Elle offre la liste complète de tous les objets, propriétés, méthodes et fonctions disponibles en VBA Excel.

• Faire défiler la liste de droite vers le bas jusqu'à trouver la fonction nbCouleurs,
• Cliquer avec le bouton droit de la souris sur cette dernière,
• Dans le menu contextuel, choisir Propriétés,
• Dans la zone Description de la boîte de dialogue qui apparaît, saisir l'indication suivante :

Compte les cellules de la couleur portée par la cellule définie en deuxième argument. -1er argument : Plage de cellules où compter. - 2nd argument : Cellule avec la couleur de fond de référence.

• Puis, cliquer sur Ok pour valider,

De la même façon, il s'agit d'ajouter une description pour la seconde nouvelle fonction.

• Faire défiler la liste de droite vers le bas jusqu'à trouver la fonction sommeCouleurs,
• Cliquer avec le bouton droit de la souris sur cette dernière,
• Dans le menu contextuel, choisir Propriétés,
• Dans la zone Description de la boîte de dialogue qui apparaît, saisir l'indication suivante :

Somme les valeurs des cellules possédant la même couleur que la cellule définie en deuxième argument. 1er argument : Plage de cellules où chercher la couleur. 2ème argument : Cellule de couleur de référence - 3ème argument [Facultatif] : Plage de cellules correspondantes où sommer. Si ce troisième argument n'est pas défini, la somme est réalisée sur la première.

• Valider cette description par le bouton Ok,
• Fermer l'explorateur d'objets en cliquant sur sa croix en haut à droite,
• Réaliser le raccourci clavier CTRL + S pour enregistrer ce nouveau classeur,
• Dans la zone Type, en bas de la boîte de dialogue qui suit, choisir Complément Excel (*.xlam),

Comme vous le remarquez et comme l'illustre la barre d'adresse de la capture ci-dessus, Excel vous redirige vers l'emplacement centralisé des compléments. Il convient d'accepter cette destination pour que le complément soit reconnu sans difficulté par Excel.

• Dans la zone Nom de fichier, saisir fct-couleurs.xlam,
• Puis, cliquer sur le bouton Enregistrer pour valider,

De retour dans l'éditeur de code, vous remarquez que le nom du classeur n'a pas changé. Nous avons en effet enregistré une déclinaison de ce dernier. Le complément existe mais pour qu'il soit fonctionnel, il doit être lié à l'application Excel. Nous devons donc l'ajouter en référence. Mais avant cela, pour éviter toute confusion, il s'agit de fermer les classeurs ouverts.

• Basculer sur l'application Excel (ALT + F11),
• Fermer le nouveau classeur (CTRL + W) sans l'enregistrer,
• Fermer le classeur source fonctions-vba-couleurs.xlsm, sans l'enregistrer,
• Créer un nouveau classeur Excel (CTRL + N),
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Compléments, cliquer sur le bouton Compléments Excel,
• Dans la boîte de dialogue qui apparaît, cocher la case fct-couleurs,

Il s'agit du nom que nous avons attribué au complément que nous avons créé lors de l'enregistrement. Il est directement reconnu car nous avons accepté la destination centralisée que nous a proposée Excel.

• Cliquer sur le bouton Ok pour valider,

Désormais et jusqu'à nouvel ordre, la référence est ajoutée. Le lien avec le complément existe pour toutes les utilisations d'Excel sur ce poste, sans exception. Nos fonctions doivent désormais être disponibles et exploitables directement par leurs noms.



Pour tester la désormais portabilité des fonctions de calculs que nous avons créées en VBA, il s'agit de réaliser un petit test, comme illustré par la capture ci-dessous.



Nous avons inscrit quelques valeurs d'essai en colonne B, entre les lignes 2 et 10. Nous avons attribué une couleur de remplissage, seulement à certaines d'entre elles. En Cellule D6, après le symbole égal, nous avons tapé les premières lettres de la fonction nbCouleurs. Celle-ci a été reconnue et proposée par Excel, grâce au complément. Nous l'avons sélectionnée. En premier argument, nous avons défini la plage de cellules B2:B10. En deuxième argument, nous avons désigné la cellule D3 que nous avions remplie de la couleur à trouver. La fonction a retourné le chiffre 4, prouvant qu'elle a bien réussi à comptabiliser toutes les cellules portant la couleur de référence, dans la plage désignée.

De la même façon, si vous tapez les premières lettres de la fonction sommeCouleurs après le symbole égal, dans une nouvelle cellule, vous constatez qu'elle est aussi reconnue et ce, indéfiniment. Mais plus intéressant encore, les manipulations que nous avons réalisées depuis l'explorateur d'objets VBA, ont permis de leur ajouter des descriptions. Ces indications doivent aider l'utilisateur non-averti à les exploiter.

• Cliquer sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche de ce dernier, cliquer sur le bouton Insérer une fonction,
• Dans la boîte de dialogue qui suit, choisir la catégorie Personnalisées,

Comme vous le constatez, les nouvelles fonctions de calcul sont proposées et accompagnées d'une indication précieuse pour savoir comment les exploiter. Nous avons donc réussi à ajouter des nouvelles fonctions dans la bibliothèque des fonctions Excel. Elles seront désormais disponibles tout le temps. Si d'aventure vous ne les souhaitiez plus, il suffirait de décocher la case du complément Excel, à partir du ruban Développeur.