Le tableur Microsoft Excel propose à ses utilisateurs un nombre très important de fonctions intégrées telles que GAUCHE, RECHERCHEV ou SOMME. Mais si vous avez des tâches plus spécifiques à réaliser, il se peut que vous ayez besoin d'une fonction quelque peu « exotique » qui n'existe pas dans la bibliothèque du logiciel. Vous devrez donc la composer vous-même sous la forme d'une fonction « définie par l'utilisateur ».

Étapes

  1. 1
    Ouvrez ou créez un classeur sur lequel vous pourrez travailler. Il sera préférable d'en créer un nouveau plutôt que d'en ouvrir un contenant des données, au moins jusqu'à ce que vous ayez pu y tester les fonctions personnelles que vous créerez, pour ne pas risquer d'endommager un classeur contenant des informations qui pourraient avoir de l'importance pour vous.
  2. 2
    Ouvrez l'éditeur de Visual Basic pour Applications. Cet éditeur est intégré à Excel, et vous pourrez l'ouvrir en cliquant successivement sur Outils, Macros puis Éditeur Visual Basic ou en pressant simultanément les touches
    Alt+F11 de votre clavier.
  3. 3
    Ajoutez un nouveau module. Cliquez sur l'option intitulée Module après avoir ouvert l'onglet symbolisé par une équerre. Cette option est nécessaire parce que si vous n'ajoutiez pas de module, votre fonction serait strictement limitée à la feuille de calcul sur laquelle vous l'aurez créée et vous ne pourriez pas l'exporter vers d'autres classeurs.
  4. 4
    Créez le prototype ou entête de votre fonction. Le prototype est une ligne déclarative définissant les modalités d'appel de la fonction. Il doit respecter la forme suivante :

    public function nom_de_la_fonction (paramètre1 As type1, paramètre2 As type2 ) As Type retourné

    Vous pouvez ajouter à votre fonction autant de paramètres qu'il sera nécessaire. Les types de données des paramètres transmis à la fonction ainsi ainsi que celui de la valeur qu'elle doit retourner devront correspondre à ceux supportés par Excel. Les paramètres transmis à une fonction en sont les opérandes sur la base desquels elle effectuera sa tâche. Dans l'expression SIN(45), SIN() est le nom de la fonction et le terme 45 en est le paramètre. Le code de cette fonction calculera la valeur du sinus de l'angle qui lui est donné en paramètre (ici 45 degrés) et retournera un résultat sous la forme d'un nombre à virgule flottante.
  5. 5
    Insérez le code de votre fonction. Prenez soin d'appliquer les trois règles suivantes :
    1 : utilisez les valeurs transmises à la fonction comme paramètres
    2 : attribuez le résultat obtenu au nom de la fonction
    3 : terminez l'écriture du code de votre fonction par le mot-clé END FUNCTION

    Les fonctions bien conçues doivent être courtes et ne contenir au plus que quelques lignes de code. Notez qu'en programmation sous Visual Basic, vous ne devez pas utiliser de caractères spéciaux, diacritiques ou accentués pour nommer vos variables et vos fonctions. Bien qu'étant un langage conçu pour être simple, Visual Basic a ses pièges qu'il vous faudra apprendre à contourner, et vous aurez besoin d'un peu de temps pour en maitriser tous les concepts à l'aide de quelques guides bien détaillés, comme celui-ci. Les concepts les plus fréquemment utilisés du langage Visual Basic sont :
    1. Le bloc décisionnel IF-THEN-ELSE, qui vous permettra de n'exécuter une méthode que si une condition spécifiée est remplie ou vraie :

      Public Function Resultat(note As Integer) As String
        If note >= 5 Then
          Resultat = "Qualifié"
        Else
          Resultat = "Disqualifié"
        End If
      End Function


      Notez la séquence d'utilisation des éléments d'un bloc de code conditionnel
      IF-THEN-ELSE :
      IF condition=vraie THEN code_si_vrai ELSE code_si_faux END IF
      Le mot-clé ELSE et la méthode qui le suit peuvent être omis si votre fonction ne doit prendre de décision que sur une condition vraie. Un bloc décisionnel IF-THEN-ELSE doit toujours se terminer par le mot-clé END IF.
    2. La boucle conditionnelle DO-WHILE ou DO-UNTIL, qui répétera un bloc de code pendant (do-while) qu'une condition restera vraie ou jusqu'à (do-until) ce qu'elle le devienne :

      Public Function Est_Nombre_Premier(valeur As Integer) As Boolean
        Dim i As Integer
        i = 2
         Est_Nombre_Premier = True
        Do
          If valeur / i = Int(valeur / i) Then
             Est_Nombre_Premier = False
          End If
          i = i + 1
        Loop While i < valeur And Est_Nombre_Premier = True
      End Function


      Notez la séquence d'utilisation des éléments d'une boucle conditionnelle
      DO-LOOP WHILE ou DO-LOOP UNTIL :
      DO méthode1 LOOP WHILE condition1 = vraie
      ou
      DO méthode2 LOOP UNTIL condition2 = vraie
      Notez aussi la déclaration d'une variable sur la seconde ligne du code. Vous pouvez ajouter à votre code toutes les variables dont vous aurez besoin pendant son exécution. Les variables représentent des emplacements de mémoire où sont stockées les valeurs temporaires d'un programme. Vous devrez les déclarer avant de les utiliser. Notez enfin l'affectation du type BOOLEAN (booléen) à la valeur retournée par la fonction. Ce type de donnée est binaire et ne peut contenir que deux valeurs : TRUE (vraie) ou FALSE (fausse). Votre fonction ne retournera donc vrai que si le nombre passé en paramètre est un nombre premier et faux s'il ne l'est pas. Cette méthode n'est certainement pas la mieux optimisée pour déterminer si le nombre testé est premier ou non, mais elle offre l'avantage d'une plus grande lisibilité du code présenté.
    3. La boucle FOR-NEXT vous permettra d'exécuter un bloc de code un nombre déterminé de fois :

      Public Function Factorielle(valeur As Integer) As Long
        Dim resultat As Long
        Dim i As Integer
        If valeur = 0 Then
          resultat = 1
        ElseIf valeur = 1 Then
          resultat = 1
        Else
          resultat = 1
          For i = 1 To valeur
            resultat = resultat * i
          Next
        End If
        Factorielle = resultat
      End Function


      Notez la séquence d'utilisation des éléments d'une boucle FOR-NEXT :
      FOR limite_basse TO limite_haute – code à exécuter NEXT
      Notez aussi l'instruction ELSEIF (sinon si) dans le bloc décisionnel IF, qui vous permettra d'ajouter une condition de décision supplémentaire pour exécuter le bloc de code qui la suit. Notez enfin la déclaration de la fonction et de la variable resultat As Long. Le type de donnée LONG (entier long) autorise l'utilisation de valeurs numériques entières beaucoup plus importantes que celles du type INTEGER (entier).

      .
  6. 6
    Revenez à votre feuille de calcul. Entrez le signe égal (=) dans une cellule, suivi du nom de la fonction que vous venez d'écrire. Ouvrez une parenthèse immédiatement après le nom de la fonction et insérez les paramètres les uns à la suite des autres, séparés entre eux par une virgule, puis fermez la liste en ajoutant une parenthèse fermante :

    =Nombre_en_lettres(A4)

    Vous pouvez aussi utiliser votre fonction personnalisée en la recherchant dans la catégorie intitulée Fonctions définies par l'utilisateur dans l'assistant de recherche de formules. Cliquez simplement sur le bouton Fx situé à gauche de la barre de formules.Chaque paramètre peut être passé à une fonction de trois manières différentes.
    1. Sous la forme d'une valeur constante entrée directement dans l'expression de la fonction, comme =SIN(45). Les chaines de caractères devront en ce cas être placées entre guillemets.
    2. Sous la forme d'une référence à une cellule, comme B6, ou à une plage de cellules comme A1:C3. Dans ce dernier cas, le paramètre doit être du type range (ce type de données s'applique à une plage entière de cellules).
    3. Sous la forme d'une ou plusieurs autres fonctions imbriquées à l'intérieur de la vôtre en tant que paramètres, comme =Factorielle(MAX(D6:D8)). Notez que vous pourriez aussi imbriquer la fonction que vous venez de créer comme paramètre d'autres fonctions si cela s'avère nécessaire.
  7. 7
    Vérifiez que votre fonction produit les résultats attendus. Appelez votre fonction plusieurs fois de suite en modifiant les valeurs ses paramètres afin d'être certain qu'elle répond correctement à ce que vous en attendez.
    Publicité

Conseils

  • Indentez les blocs de code que vous insérez dans les structures de contrôle telles que IF-THEN-ELSE-ELSEIF, FOR-NEXT, DO-LOOP WHILE ou DO-LOOP UNTIL, etc. Placez deux ou quatre espaces, ou un ou plusieurs caractères de tabulation en début de ligne et selon le niveau d'imbrication des méthodes. Cela aura pour effet de rendre votre code plus lisible et de faciliter la recherche d'erreurs ou sa modification ultérieure.
  • Si vous ne savez pas comment démarrer l'écriture du code d'une fonction, inspirez-vous du contenu de l'article intitulé « Créer une simple macro dans Microsoft Excel ».
  • Il arrive parfois que vous n'ayez pas besoin de certains paramètres pour effectuer un calcul avec une fonction. Dans ce cas, vous pouvez insérer le mot-clé OPTIONAL avant le nom du paramètre facultatif dans le prototype de votre fonction. Vous pouvez également insérer la fonction ISMISSING(nom_du_paramètre [1] ) dans votre code pour déterminer si une valeur était assignée à ce paramètre.
  • Prenez-soin de ne pas donner le nom d'une fonction intégrée d'Excel à la vôtre. Vous risqueriez de la remplacer par celle que vous venez de définir et de ne plus pouvoir utiliser la fonction d'origine. Consultez l'aide du logiciel pour savoir si le nom que vous voulez donner à votre fonction n'existe pas déjà.
  • Excel expose une librairie copieusement fournie de fonctions intégrées, et la plupart des calculs dont vous aurez besoin peuvent être effectués en les utilisant aussi bien indépendamment que combinées entre elles. Vos calculs seront certainement plus rapides si vous utilisez les fonctions intégrées d'Excel que les vôtres. Parcourez la liste des fonctions intégrées à Excel avant de commencer à coder la vôtre.
  • Visual Basic pour Applications, comme beaucoup d'autres langages de programmation, exige certaines précautions typographiques. N'utilisez pas de caractères spéciaux, diacritiques ou accentués dans les noms des variables, des constantes ou des fonctions, ni de mots-clés propres au langage.
Publicité

Avertissements

  • Certains utilisateurs d'Excel inhibent l'utilisation des macros des tableurs provenant de tierces personnes pour des raisons de sécurité. Prenez soin d'informer vos collègues que le classeur que vous leur communiquez contient des macros et qu'ils peuvent être certains qu'elles n'endommageront pas leurs systèmes.
  • Les fonctions présentées ci-dessus n'ont pas la prétention de constituer une référence servant à résoudre les problèmes énoncés. Elles n'ont été présentées ici que dans un but explicatif en ce qui concerne l'utilisation de quelques structures fondamentales de contrôle propres au langage VBA.
  • VBA, comme beaucoup d'autres langages de programmation, propose plusieurs autres structures de contrôle d'exécution que DO-LOOP WHILE, DO-LOOP UNTIL, IF-THEN-ELSE-ELSEIF et FOR-NEXT. Ces dernières ont été exposées ici pour démontrer une partie de ce qui peut être fait dans le code source d'une fonction. Vous pourrez approfondir vos connaissances sur ce langage en consultant les tutoriels accessibles gratuitement sur Internet [2] [3] .
Publicité

À propos de ce wikiHow

wikiHow est un wiki, ce qui veut dire que de nombreux articles sont rédigés par plusieurs auteurs(es). Pour créer cet article, 13 personnes, certaines anonymes, ont participé à son édition et à son amélioration au fil du temps. Cet article a été consulté 8 614 fois.
Catégories: Programmation | Excel
Publicité