Tutoriel sur les fonctions Excel VBA : retour, appel, exemples

Par : Charlotte Miller Charlotte Miller
Hours Le kit de préparation mis à jour

⚡ Résumé intelligent

Une fonction VBA Excel est un bloc de code qui effectue une tâche et renvoie un résultat à l'appelant. Cette page aborde la syntaxe de déclaration, le renvoi d'une valeur, un exemple d'addition et l'utilisation d'une fonction dans une cellule de feuille de calcul.

  • (I.e. Définition: Une fonction effectue une tâche spécifique et renvoie un résultat unique au code appelant.
  • 🧾 syntaxe: Nom de la fonction (arguments) As Type ouvre le bloc et End Function le ferme.
  • 🇧🇷 Retourner une valeur : Attribuez le résultat au nom de la fonction, comme dans ajouterNumbers = premierNombre + deuxièmeNombre.
  • (I.e. Type de retour : Déclarer aussi long ou aussi Double évite la variante par défaut, plus lente.
  • 🇧🇷 Appel: Un bouton de commande permet de transmettre deux nombres et d'afficher la somme obtenue dans une boîte de message.
  • (I.e. Utilisation de la feuille de travail : Une fonction publique dans un module standard devient une formule définie par l'utilisateur dans n'importe quelle cellule.
En savoir plus

Fonction VBA Excel

Qu'est-ce qu'une fonction ?

Une fonction est un morceau de code qui exécute une tâche spécifique et renvoie un résultat. Les fonctions sont principalement utilisées pour effectuer des tâches répétitives telles que le formatage des données pour la sortie, l'exécution de calculs, etc.

Supposons que vous soyez développeurping Un programme qui calcule les intérêts d'un prêt. Vous pouvez créer une fonction qui prend en entrée le montant du prêt et la durée de remboursement. Cette fonction calculera ensuite les intérêts et renverra le résultat.

Pourquoi utiliser des fonctions

Les avantages de l'utilisation des fonctions sont les mêmes que ceux énumérés pour les sous-programmes : elles divisent un long programme en parties gérables, elles peuvent être réutilisées n'importe où dans le projet, et un nom descriptif documente ce que fait le code. Tutoriel sur les sous-routines VBA Excel couvre intégralement ces prestations.

Règles de dénomination des fonctions

Les règles de dénomination sont également identiques à celles des sous-programmes. Un nom de fonction ne peut pas contenir d'espace, doit commencer par une lettre ou un trait de soulignement, et ne peut pas être un nom réservé. VBA mots-clés tels que Function, Private ou End.

Syntaxe VBA pour déclarer une fonction

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

ICI dans la syntaxe,

Code Action
  • « Fonction privée maFonction(…) »
  • Ici, le mot-clé « Function » est utilisé pour déclarer une fonction nommée « myFunction » et démarrer le corps de la fonction.
  • Le mot-clé 'Privé' est utilisé pour spécifier la portée de la fonction
  • "ByVal arg1 comme entier, ByVal arg2 comme entier"
  • Il déclare deux paramètres de type de données entier nommés « arg1 » et « arg2 ».
  • maFonction = arg1 + arg2
  • évalue l'expression arg1 + arg2 et attribue le résultat au nom de la fonction.
  • "Fin de fonction"
  • La fonction « End Function » permet de terminer le corps de la fonction.

Comment renvoyer une valeur et définir le type de données de la fonction

Une fonction a une particularité qu'une sous-routine n'a pas : elle renvoie une valeur. Deux détails déterminent cette valeur, et tous deux sont faciles à négliger.

La première chose à noter est l'affectation. VBA ne possède pas d'instruction Return. À la place, vous affectez le résultat au nom même de la fonction, ce qui explique pourquoi la ligne se présente ainsi : maFonction = arg1 + arg2Si cette affectation n'est jamais exécutée, la fonction renvoie silencieusement une valeur vide au lieu de générer une erreur ; par conséquent, chaque branche du code doit la définir.

Le second point concerne le type de retour. La déclaration ci-dessus s'arrête à l'accolade fermante ; la fonction renvoie donc un Variant. L'ajout d'une clause As après les accolades corrige le type, ce qui est plus rapide, consomme moins de mémoire et permet au compilateur de détecter une éventuelle incompatibilité.

Déclaration Retours de produits Quand l'utiliser
Fonction f(x As Long) Variante Uniquement lorsque le type de résultat varie réellement
Fonction f(x As Long) As Long Long Les nombres entiers tels que les comptes et les numéros de ligne
Fonction f(x As Long) As Double Double Tout calcul produisant des décimales
Fonction f(x As Long) As String Chaîne Texte formaté renvoyé pour affichage
Fonction f(x As Long) As Boolean Boolean Un contrôle de validation répondant vrai ou faux

Astuce : Utilisez la fonction Exit pour quitter prématurément une fois la valeur de retour définie, de la même manière que Exit Sub quitte une sous-routine.

Fonction démontrée avec exemple :

Les fonctions sont très similaires à celles du sous-programme. La principale différence entre un sous-programme et une fonction est que la fonction renvoie une valeur lorsqu'elle est appelée. Alors qu'un sous-programme ne renvoie pas de valeur lorsqu'il est appelé. Disons que vous souhaitez ajouter deux nombres. Vous pouvez créer une fonction qui accepte deux nombres et renvoie la somme des nombres.

  1. Créer l'interface utilisateur
  2. Ajouter la fonction
  3. Écrire le code du bouton de commande
  4. Tester le code

Étape 1) Interface utilisateur

Ajoutez un bouton de commande à la feuille de calcul comme indiqué ci-dessous

Fonctions et sous-programmes VBA

Définissez les propriétés suivantes de CommandButton1 comme suit.

Ratio S / N Contrôle Propriétés Valeur
1 Bouton de commande1 Nom btnAjouterNumbers
2 Légende Ajouter Numbers Fonction

Votre interface devrait maintenant apparaître comme suit

Fonctions et sous-programmes VBA

ARTICLES LIÉS

Étape 2) Code de fonction.

  1. Appuyez sur Alt + F11 pour ouvrir la fenêtre de code
  2. Ajoutez le code suivant
Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

ICI dans le code,

Code Action
  • « Ajout de fonction privéeNumbers(...) "
  • Il déclare une fonction privée « add »Numbers» qui accepte deux paramètres entiers.
  • "ByVal firstNumber sous forme d'entier, ByVal secondNumber sous forme d'entier"
  • Il déclare deux variables de paramètre firstNumber et secondNumber
  • "ajouterNumbers = premierNuméro + deuxièmeNuméro »
  • Il ajoute les valeurs firstNumber et secondNumber et attribue la somme à ajouterNumbers.

Étape 3) Écrire Code qui appelle la fonction

  1. Faites un clic droit sur le bouton AjouterNumbers bouton de commande
  2. Sélectionnez Voir Code
  3. Ajoutez le code suivant
Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

ICI dans le code,

Code Action
"MessageBox +Numbers(2,3) "
  • Il appelle la fonction addNumbers et passe 2 et 3 comme paramètres. La fonction renvoie la somme des deux nombres cinq (5)

Étape 4) Exécutez le programme, vous obtiendrez les résultats suivants

Fonctions et sous-programmes VBA

Téléchargez Excel contenant le code ci-dessus

Téléchargez le fichier Excel ci-dessus. Code

Le bouton ci-dessus appelle la fonction depuis le code VBA. Il est également possible d'appeler une fonction directement depuis la feuille de calcul, sans aucun bouton.

Comment utiliser une fonction VBA dans une cellule de feuille de calcul

Une fonction écrite en VBA peut être saisie dans une cellule exactement comme SOMME ou RECHERCHEV. Excel appelle cela une fonction définie par l'utilisateur (FDU), et c'est pourquoi beaucoup apprennent les fonctions avant les sous-routines. Trois conditions doivent être remplies.

  • Placez-le dans un module standard : Insérez un module dans l'éditeur. Une fonction stockée derrière une feuille de calcul ou dans ThisWorkbook n'est pas visible dans la barre de formule.
  • Déclarez-le public : L'exemple ci-dessus utilise le niveau de confidentialité « Privé », ce qui le masque à Excel. Le niveau de confidentialité « Public » est le niveau par défaut ; il suffit donc de supprimer le mot-clé.
  • Renvoie une valeur, ne modifie rien : Une fonction personnalisée ne peut pas formater les cellules, supprimer des lignes ni écrire dans une autre cellule. Excel bloque ces actions et la cellule affiche #VALEUR!.

La fonction ci-dessous convertit une température et peut être utilisée n'importe où sur la feuille de calcul.

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

Enregistrez le classeur au format .xlsm avec macros, puis saisissez le texte. =CelsiusToF(A1) insérez la formule dans n'importe quelle cellule. Le résultat s'actualise à chaque modification de la cellule A1 et le nom apparaît dans la liste de saisie semi-automatique des formules, sous la catégorie « Définies par l'utilisateur ». Le classeur contenant désormais des macros, toute personne l'ouvrant doit activer le contenu pour que la formule renvoie une valeur plutôt que #NOM?.

Erreurs courantes des fonctions VBA et comment les corriger

Quatre problèmes expliquent la plupart des fonctions qui compilent mais renvoient une réponse incorrecte.

  • La fonction renvoie une valeur vide ou 0 : Le résultat n'a jamais été affecté au nom de la fonction, ou bien une branche d'une instruction if ignore l'affectation. Définissez la valeur de retour pour chaque chemin d'exécution.
  • #NOM ? dans une cellule de feuille de calcul : La fonction est privée, elle se trouve dans un module de feuille au lieu d'un module standard, ou le classeur a été enregistré sans que les macros soient activées.
  • Dépassement de capacité avec des arguments entiers : L'exemple utilise le type As Integer, qui s'arrête à 32 767. Modifiez les deux paramètres et le type de retour en Long pour des données réelles.
  • Un changement d'argument surprend l'appelant : Omettre ByVal permet à VBA de transmettre la variable elle-même, ce qui autorise la fonction à modifier la valeur de l'appelant. Utilisez ByVal sauf si vous souhaitez obtenir ce résultat.

FAQ

Pas directement. Retournez un tableau ou un type personnalisé pour contenir plusieurs valeurs dans un seul résultat, ou déclarez les paramètres supplémentaires par référence (ByRef) afin que la fonction les réécrive dans les variables de l'appelant.

Ajoutez le mot-clé Optional avec une valeur par défaut, comme dans Optional ByVal Rate As Double = 0.05. Tout paramètre suivant un paramètre optionnel doit également être optionnel et doit figurer en dernier dans la liste.

Oui, via Application.WorksheetFunction, par exemple Application.WorksheetFunction.Sum(Range("A1:A10")). Les fonctions fournies par VBA, telles que Left ou Trim, sont appelées directement sans ce préfixe.

Oui. Collez la formule de la feuille de calcul et un assistant IA vous renverra une fonction publique équivalente avec des arguments nommés et un type de retour déclaré. Comparez les deux résultats sur des lignes d'exemple avant de remplacer la formule.

Oui. Indiquez la fonction et la formule, et un assistant IA vous signalera les causes possibles, comme une incompatibilité de type d'argument, une affectation de retour manquante ou une tentative de modification d'une cellule depuis l'intérieur d'une fonction définie par l'utilisateur.

Résumez cet article avec :