Ce tutoriel expliquera comment créer des fonctions définies par l'utilisateur dans VBA.
VBA est composé de sous-procédures et de procédures de fonction. Les procédures de fonction renvoient une valeur et peuvent être appelées par des sous-procédures ou peuvent être utilisées dans la feuille Excel, où la valeur produite par la fonction est renvoyée à la feuille Excel. Excel a bien sûr une gamme de fonctions intégrées - comme la fonction Sum ou la fonction If. Les procédures de fonction que vous écrivez dans VBA sont utilisées de la même manière que la fonction intégrée et sont appelées fonctions définies par l'utilisateur (UDF).
Pourquoi créer une FDU ?
Fonction requise manquante
L'une des principales raisons de vouloir créer une FDU dans Excel est qu'il n'y a pas de fonction intégrée existante qui fera la tâche pour vous. Écrire votre propre fonction en VBA est généralement le moyen le plus efficace de résoudre le problème. La fonction ci-dessous convertira une valeur de kilogrammes en livres où un paramètre variable (dblKilo) est utilisé pour obtenir la valeur des kilogrammes afin de faire le calcul.
Remplacer une sous-routine (Macro)
Vous pouvez écrire une sous-procédure (macro) pour résoudre la tâche à votre place - mais les sous-procédures ne renvoient pas de valeur et ne sont pas dynamiques - en d'autres termes, si les valeurs de votre feuille de calcul changent, vous devrez réexécuter le macro afin que les calculs de la macro mettent à jour vos données. La sous-procédure ci-dessous convertirait également nos kilos en livres, mais chaque fois que les données changeaient dans A1, vous deviez réexécuter la macro pour mettre à jour le résultat.
Remplacer une formule
Vous pouvez avoir une formule très compliquée dans Excel que vous devez utiliser de manière répétitive - mettre la formule dans le code VBA facilite la lecture et la compréhension - ainsi que peut-être en supprimant la possibilité d'erreur de l'utilisateur lors de la saisie de la formule.
Création de FDU
Pour créer une FDU, ajoutez d'abord un module à votre classeur Excel ou, si vous avez un classeur de macros personnelles, vous pouvez soit utiliser un module existant, soit en ajouter un nouveau. Pour ce faire, vous devez être dans Visual Basic Editor (VBE). Pour accéder au VBE, appuyez sur ALT + F11 ou cliquez sur l'option Visual Basic dans l'onglet Développeur de votre ruban.
ASTUCE: Si vous n'avez pas activé l'onglet Développeur dans votre ruban, accédez à Fichier, Options et cliquez sur Personnaliser le ruban. Assurez-vous que la case Développeur est cochée et cliquez sur OK.
Pour insérer un nouveau module, sélectionnez le projet VBA dans lequel vous souhaitez insérer le module (soit le projet VBA pour le livre actuel sur lequel vous travaillez, soit le classeur de macros personnelles), cliquez sur le bouton Insérer Menu et cliquez sur Module
Une fois que vous avez créé votre module, vous pouvez commencer à créer votre FDU.
Toutes les FDU commencent par Function, puis le nom de la FDU. Les fonctions peuvent être privées ou publiques, mais vous souhaiterez généralement qu'une FDU soit publique afin qu'elle apparaisse dans la boîte de dialogue Insérer une fonction dans Excel (voir Utilisation d'une fonction à partir d'une feuille Excel plus loin dans cet article). Si vous ne placez pas le mot-clé Private devant la fonction, alors la fonction est automatiquement publique.
| 123 | Fonction TestFunction1(intA en tant qu'entier) en tant qu'entierTestFunction1= intA * 7Fonction de fin |
La fonction ci-dessus a un seul argument (intA). Vous pouvez créer une fonction avec plusieurs arguments
| 123 | Fonction TestFunction2(intA en tant qu'entier, intB en tant qu'entier, intC en tant qu'entier) en tant qu'entierTestFunction2 = (intA * intB) + intCFonction de fin |
Vous pouvez également créer une fonction avec des arguments facultatifs. Si l'argument est omis, vous pouvez définir une valeur par défaut pour l'argument dans la fonction.
| 123 | Fonction TestFunction3(intA en tant qu'entier, intB en tant qu'entier, facultatif intC en tant qu'entier = 10) en tant qu'entierTestFunction3 = (intA * intB) + intCFonction de fin |
Utilisation d'une fonction à partir d'une feuille Excel
Les fonctions que vous avez créées apparaîtront par défaut dans votre liste de fonctions dans la section Défini par l'utilisateur de la liste de fonctions.
Clique sur le fx pour afficher la boîte de dialogue Insérer une fonction.
Sélectionner Défini par l'utilisateur de la liste des catégories
Sélectionnez la fonction dont vous avez besoin parmi les Fonctions définies par l'utilisateur.
Alternativement, lorsque vous commencez à écrire votre fonction dans Excel, la fonction doit apparaître dans la liste déroulante des fonctions.
Enregistrer les fonctions avec votre fichier Excel
Comme les fonctions sont écrites en code VBA, il va de soi que le code doit être disponible pour le classeur afin d'être disponible pour être utilisé dans la feuille Excel. Vous pouvez soit enregistrer vos fonctions dans le classeur dans lequel vous les utilisez, soit les enregistrer dans votre classeur de macros personnelles. Votre classeur de macros personnelles est un fichier caché qui est disponible chaque fois qu'Excel est ouvert et donc disponible pour n'importe quel classeur dans Excel à utiliser. Il est normalement créé lorsque vous enregistrez une macro et sélectionnez l'option permettant de stocker la macro dans le classeur de macros personnelles.
Si vous souhaitez conserver vos fonctions enregistrées dans le classeur sur lequel vous travaillez, vous devrez vous assurer que lorsque vous enregistrez le classeur, il est enregistré en tant que "Classeur activé par les macros" ou un xlsm déposer.
