Créer ses fonctions dans Excel (Macros)

I. Introduction à la macro excel

Bien que le tableur Excel propose un nombre important de fonctions intégrées, il se peut qu’aucune ne réponde à un besoin spécifique. Savoir en créer ses propres fonctions, pour les intégrer à Excel, peut alors s’avérer très précieux.

Ce tutorial explique comment créer, enregistrer et intégrer une fonction personnelle dans Excel.

II. Explications

1 – Exemple de contexte

Pour l’exemple nous imaginons que vous travaillons dans Excel un fichier de type base de données dont 3 des colonnes contiennent des informations desquelles nous devons déduire une situation :

tuto excel fonctions image 1

La ligne1 contient les nom de champs nommés « Critère 1 » « Critère 2 » « Critère 3 » pour faciliter les explications. Les données à afficher dans la colonne « Situation » dépendent des valeurs des colonnes critères, par exemple :

Si Critère 1 = 1 et Critère 2 = 1 et Critère 3 = 1 alors Situation = Cas 1

On pourrait tenter de gérer avec les fonctions SI et ET mais ça devient vite un peu lourd. Pour estimer le cas 1, la fonction serait :

tuto excel fonctions image 2

Si l’on pose comme principe que chaque critère ne peut avoir que les valeurs 1 à 5, nous avons 125 (5 * 5 * 5) cas à prévoir . Ceci est impossible à réaliser avec Si (7 imbrications possible au maximum) et de toute façon la formule serait illisible.

Reste la solution de créer sa propre fonction qui, à partir des 3 critères qui lui seront passés en arguments, déterminera le cas à retenir.

2 – Créer une fonction (ou Macro)

Pour créer une fonction nous devons ouvrir l’éditeur Visual Basic : utiliser la combinaison de touche Alt + F11 ou le menu Outils/Macros/Visual Basic Editor :

tuto excel fonctions image 3

Dans l’explorateur de projet nous apercevons notre classeur (MonClasseurExemple.xls) et les 3 feuilles qui le composent. Nous ajoutons un module : menu Insertion/Module. Un module vide apparaît dans le projet du classeur :

tuto excel fonctions image 4

Dans le module nous écrivons quelques lignes de code :

tuto excel fonctions image 5

Nous appelons notre fonction Situation. Elle reçoit 3 entiers (iC1, iC2 et iC3) en arguments (nos 3 critères de la base de données) et renvoie une chaîne de caractères.

Function Situation (iC1, iC2, iC3 As Integer) As String

Nous déclarons ensuite les variables dont nous avons besoin ; ici 4 entiers (i, j, k pour les boucles et iCas qui nous donnera la situation rencontrée en s’incrémentant dans la boucle).

On part du principe que :

Si Critère 1 = 1 ET Critère 2 = 1 ET Critère 3 = 1 ALORS Situation = cas 1

Si Critère 1 = 1 ET Critère 2 = 1 ET Critère 3 = 2 ALORS Situation = cas 2

…..

et ainsi de suite.

Cette condition est testée dans :

If (iC1 = i And iC2 = j And iC3 = k) Then

Situation = "Cas " & iCas

End If

Je ne m’étends pas plus sur le code, l’exemple étant simple et le but du tutorial étant d’expliquer le principe de création et d’utilisation d’une fonction personnelle.

3 – Utiliser la fonction (ou Macro)

Notre fonction est maintenant créée et peut s’utiliser comme n’importe quelle fonction d’Excel.

Dans la cellule D2 vous tapez : =Situation(A2;B2;C2) que vous collez ensuite dans les autres cellules de la colonne D :

tuto excel fonctions image 6

tuto excel fonctions image 7

Vous pouvez également accéder à la fonction par l’assistant (le bouton fx) :

tuto excel fonctions image 8

Dans la boîte de dialogue « insérer une fonction » sélectionnez « Personnalisées » dans catégories , et cherchez « situation » dans « Sélectionnez une fonction » :

tuto excel fonctions image 9

Il suffit ensuite de remplir les champs de la fonction comme vous le faites pour une fonction intégrée :

tuto excel fonctions image 10

Ces méthodes valent si vous devez utiliser votre fonction dans ce seul classeur. Si vous entendez l’utiliser dans d’autres il faut créer un fichier .xla puis intégrer votre fonction dans votre nouveau classeur.

Pour créer un fichier .xla : dans Excel menu Fichier/Enregistrer sous , choisir « Macro complémentaire Microsoft Excel (*.xla) » dans type de fichier et nommer votre fichier « Situation.xla » :

tuto excel fonctions image 11

Le répertoire « Macros complémentaires » est automatiquement sélectionné. Cliquez sur « Enregistrer » .

Fermez votre classeur, ouvrez-en un vide et remplissez quelques cellules de la même manière que pour le premier classeur.

Pour intégrer la fonction « Situation » : menu Outils/Macros complémentaires. Dans la liste déroulante cherchez et cochez « Situation » :

tuto excel fonctions image 12

Cliquez sur OK. Vous pouvez désormais utiliser votre fonction « Situation » comme expliqué plus haut.

III. Conclusion du tutorial

Voilà, après avoir lu ce tutoriel, je pense que vous vous imaginez mieux le temps précieux que l'on peut gagner avec Excel, enfin surtout pour ceux qui utilisent le tableur Excel et qui ne connaissaient pas trop les fonctions (alias Macros). A votre tour de jouer...