Comment créer des fonctions définies par l'utilisateur dans Microsoft Excel

2024 Auteur: Jason Gerald | [email protected]. Dernière modifié: 2023-12-16 11:11

Bien qu'Excel dispose déjà de centaines de fonctions intégrées telles que SUM, VLOOKUP, LEFT, etc., les fonctions intégrées disponibles ne sont généralement pas suffisantes pour effectuer des tâches assez complexes. Cependant, ne vous inquiétez pas car vous n'avez qu'à créer vous-même les fonctions requises.

Étape

Étape 1. Créez un nouveau classeur ou ouvrez le classeur que vous souhaitez traiter avec les fonctions définies par l'utilisateur (UDF)

Étape 2. Ouvrez Visual Basic Editor dans Microsoft Excel via Outils->Macro->Visual Basic Editor (ou appuyez sur le raccourci Alt+F11)

Étape 3. Cliquez sur le bouton Module pour ajouter un nouveau module à votre feuille de calcul

Vous pouvez créer une FDU dans un classeur sans ajouter de nouveau module, mais la fonction ne fonctionnera pas dans d'autres feuilles de calcul du même classeur.

Étape 4. Créez la "tête" ou le "prototype" de votre fonction

Le prototype de fonction doit suivre la structure suivante:

fonction publique "Nom de la fonction" (paramètre1 As type1, paramètre2 As type2) As Type de résultat.

Les prototypes peuvent avoir autant de fonctions que possible et leurs types peuvent être tous des types de données de base ou des types d'objets Excel sous forme de plage. Vous pouvez considérer les paramètres comme des « opérateurs » (opérateurs) sur lesquels la fonction agira. Par exemple, lorsque vous écrivez SIN(45) pour calculer le sinus de 45 degrés, le nombre 45 sera pris comme paramètre. Ensuite, le code de fonction utilisera ces valeurs pour effectuer des calculs et afficher les résultats.

Étape 5. Ajoutez le code de fonction pour vous assurer que vous: 1) utilisez la valeur donnée par le paramètre; 2) transmettre le résultat au nom de la fonction; et 3) fermer la fonction avec la phrase « end function ». Apprendre à programmer en VBA ou dans toute autre langue prend beaucoup de temps et des conseils détaillés. Heureusement, ces fonctions ont généralement de petits blocs de code et n'utilisent pas beaucoup les fonctionnalités du langage de programmation. Voici quelques éléments du langage VBA qui peuvent être utilisés:

1. Le bloc If (if), qui permet d'exécuter une portion de code uniquement si la condition est remplie. Par exemple:

Résultat du cours de fonction publique (en tant que valeur entière) en tant que chaîne

Si valeur >= 5 Alors

Résultats du cours = "Accepté"

Autre

Résultats du cours = "Rejeté"

Fin si

Fonction de fin

Notez les éléments dans le bloc de code If:

SI condition ALORS code ELSE code FIN SI

2. . Vous pouvez omettre le mot-clé Else avec la deuxième partie du code car il est facultatif.
3. Le bloc Do (do), qui exécute une partie du code While ou Until lorsque ou jusqu'à ce que la condition soit remplie. Par exemple:

Fonction publique BilPrima(valeur en nombre entier) en tant que booléen

Diminuer en tant qu'entier

je = 2

BilPrima = Vrai

Faire

Si valeur / i = Int(valeur / i) Alors

BilPrima = Faux

Fin si

je = je + 1

Boucle tant que i < valeur et NumberPrima = True

Fonction de fin

Revoyez les éléments:

DO code LOOP WHILE/UNTIL condition

4. . Notez également la deuxième ligne qui "déclare" la variable. Vous pouvez ajouter des variables à votre code pour une utilisation ultérieure. Les variables agissent comme des valeurs temporaires dans le code. Enfin, considérez la déclaration de fonction comme BOOLEAN, qui est un type de données qui n'autorise que les valeurs TRUE ou FALSE. Cette méthode de détermination des nombres premiers est loin d'être optimale, mais le code a été écrit de manière à être facile à lire.
5. Pour bloc (to), qui exécute une certaine quantité de code. Par exemple:

Facteur de fonction publique (valeur en tant qu'entier) aussi long

Dim résultats Aussi longtemps

Diminuer en tant qu'entier

Si valeur = 0 Alors

résultat = 1

Sinon si valeur = 1 Alors

résultat = 1

Autre

résultat = 1

Pour i = 1 À la valeur

résultat= résultat * je

Prochain

Fin si

Factorielle = résultat

Fonction de fin

Revoyez les éléments:

FOR variable = limite inférieure TO limite supérieure du code SUIVANT

. Notez également l'élément ElseIf supplémentaire dans l'instruction If, qui vous permet d'ajouter plus d'options au code en cours d'exécution. Enfin, considérons la fonction et la variable « résultat » déclarées comme Long. Le type de données Long autorise des valeurs beaucoup plus grandes que Integer.

Vous trouverez ci-dessous le code d'une fonction qui convertit de petits nombres en mots.



Étape 6. Revenez au classeur et utilisez la fonction en écrivant le symbole « égal » (=) suivi du nom de la fonction dans la cellule

Écrivez les crochets ouvrants (“(“) après le nom de la fonction, en utilisant le signe coma pour séparer les paramètres et terminer par des crochets fermants (“)”). Par exemple:

=NuméroVersLettre(A4)

. Vous pouvez également utiliser des formules maison en les recherchant dans les catégories Défini par l'utilisateur dans l'option Insérer une formule. Vous n'avez qu'à cliquer sur le bouton Fx à gauche de la barre de formule. Il existe trois types de formes de paramètres dans les fonctions:

1. Une valeur constante qui est tapée directement dans la formule de la cellule. Dans ce cas, le texte (chaîne) doit être entre guillemets.
2. Références de cellules, par exemple B6 ou gamme comme A1:C3 (le paramètre doit être du type de données « Plage »)

3. Une autre fonction incluse dans votre fonction (votre fonction peut également être incluse dans une autre fonction), par exemple: =Factorial(MAX(D6:D8))

   

   Étape 7. Assurez-vous que les résultats sont corrects

   Utilisez-le plusieurs fois pour vous assurer que la fonction est capable de gérer correctement différentes valeurs de paramètres:

   Des astuces

   • Lorsque vous écrivez des blocs de code dans des structures de contrôle telles que If, For, Do, etc., assurez-vous de mettre en retrait (insérez légèrement la bordure de la ligne gauche à l'intérieur) le bloc de code en appuyant plusieurs fois sur la barre d'espace ou sur la tabulation. Cela rendra le code plus facile à comprendre et les erreurs seront beaucoup plus faciles à trouver. De plus, l'augmentation des fonctionnalités devient plus facile à réaliser.
   • Si vous ne savez pas comment écrire du code pour les fonctions, lisez l'article Comment écrire une macro simple dans Microsoft Excel.
   • Parfois, les fonctions n'ont pas besoin de tous les paramètres pour calculer le résultat. Dans ce cas, vous pouvez utiliser le mot clé Facultatif avant le nom du paramètre dans l'en-tête de la fonction. Vous pouvez utiliser la fonction IsMissing(parameter_name) dans votre code pour déterminer si une valeur est affectée ou non à un paramètre.
   • Utilisez des noms inutilisés comme fonctions dans Excel afin qu'aucune fonction ne soit écrasée ni supprimée.
   • Excel possède de nombreuses fonctions intégrées et la plupart des calculs peuvent être effectués à l'aide de ces fonctions intégrées, individuellement ou simultanément. Assurez-vous de jeter un œil à la liste des fonctions disponibles avant de commencer à coder vous-même. L'exécution peut être effectuée plus rapidement si vous utilisez des fonctions intégrées.

   Avertissement

   • Pour des raisons de sécurité, de nombreuses personnes désactivent les macros. Assurez-vous d'informer les destinataires de votre classeur que le classeur envoyé contient des macros et que ces macros n'endommageront pas leurs ordinateurs.
   • La fonction utilisée dans cet article n'est pas la meilleure façon de résoudre le problème connexe. L'exemple est utilisé pour expliquer l'utilisation des structures de contrôle du langage.
   • VBA, comme d'autres langages, a plusieurs autres structures de contrôle en plus de Do, If et For. La structure discutée ici ne décrit que ce qui peut être fait dans le code source de la fonction. Il existe de nombreux guides sur Internet qui peuvent être utilisés pour vous aider à apprendre VBA.