Les fonctions personnelles dans Excel

Tout d'abord, voici comment mettre en oeuvre votre première fonction.

Vous allez employer l'éditeur de macros.

Ouvrez un nouveau classeur vierge.

Pour atteindre l'éditeur, utilisez le menu Outils/Macro/Visual Basic Editor.
Si vous disposez d'Excel2007, sélectionnez l'onglet "Développeur" puis cliquez sur le bouton "Visual Basic" dans le groupe "Code".
Vous pouvez aussi ouvrir l'éditeur à partir du raccourci clavier ALT+F11.

Ajoutez un module standard en sélectionnant le menu Insertion/Module.

Copiez la procédure suivante dans le module. L'exemple multiplie par 2 la valeur transmise à la fonction.

'dblNombre est l'argument (la valeur transmise à la fonction).
Function Multiplication(dblNombre As Double) As Double
    'Multiplie l'argument dblNombre par 2 et renvoie le résultat au nom
    'de la fonction.
    Multiplication = dblNombre * 2
End Function

Vous pouvez ensuite appeler cette fonction par macro.
Ajoutez la procédure ci-dessous dans le module puis déclenchez l'exécution de la macro "Test".

Sub Test()
    'Transmet la valeur 40 à la fonction "Multiplication".
    'La fonction renvoie 80.
    MsgBox Multiplication(40)
    
    'Vous pouvez aussi utiliser la syntaxe suivante:
    MsgBox Multiplication(dblNombre:=40)
    'Vous n'avez pas besoin d'ajouter "dblNombre:=" lorsque les données sont
    'passées à la fonction dans le même ordre que la déclaration des arguments.
End Sub

Lorsque vous ouvrez la parenthèse après le nom de la fonction, afin de saisir les paramètres d'appel, vous constatez que l'éditeur vous aide en affichant sous forme d'info-bulle la structure, le nombre et le type d'arguments, ainsi que le type de résultat attendu (ici, type Double).


D'ou l'importance de nommer clairement les arguments de la fonction.


Maintenant, rebasculez vers la feuille de calcul pour utiliser la même fonction dans une cellule.
Sélectionnez la cellule qui va contenir la formule.
Sélectionnez le menu Insertion/Fonction/Catégorie "Personnalisées":
Choisissez la fonction "Multiplication" dans la liste.
Indiquez une valeur pour l'argument "dblNombre".
Cliquez sur le bouton OK pour valider.

Vous pouvez aussi saisir directement la fonction dans la cellule:
=Multiplication(40)

La formule est valide si elle est placée dans le même classeur que la fonction personnelle, sinon vous devez ajouter la référence du classeur:
=NomClasseur.xls!Multiplication(40)
Une fonction est disponible uniquement lorsque le classeur qui contient la procédure est ouvert. Vous devez créer un complément pour que votre fonction personnelle soit opérationnelle dès l'ouverture de l'application. Reportez vous au chapitre V pour plus de détails.


Assurez-vous que type de donnée qui alimente la fonction est cohérent: Par exemple, il ne faut pas envoyer un type de donnée String vers une fonction qui attend une valeur numérique. Sinon, vous provoquez une erreur d'exécution 13 "Incompatibilité de type", ou une erreur #VALEUR! lorsque la fonction est utilisée dans une feuille de calcul.

Vérifiez aussi que le nombre d'arguments obligatoire est bien respecté. Si un élément est oublié lors de l'appel, vous provoquez une erreur de compilation "Argument non facultatif", dès le lancement de la procédure, ou une erreur #VALEUR! lorsque la fonction est utilisée dans une feuille de calcul.


Les chapitres suivants décrivent les éléments constitutifs d'une fonction.

La description ci-dessous est en partie issue de l'aide Excel, agrémentée de commentaires personnels.

[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function

II-B-1. Public▲

II-B-2. Private▲

II-B-3. Friend▲

II-B-4. Static▲

II-B-5. Name▲

Sub Essai()
    'Renvoie 2020
    MsgBox TestFonction(20)
End Sub


Function TestFonction(Arg1 As Double) As Double
    Dim intCalc As Double
    
    intCalc = (Arg1 * 100) + Arg1
    TestFonction = intCalc
End Function

Sub Essai()
    'Renvoie 2020
    MsgBox TestFonction(20)
End Sub

Function TestFonction(Arg1 As Integer) As Long
    Dim intCalc As Double
    
    intCalc = (Arg1 * 100) + Arg1
    'TestFonction = intCalc
End Function

Sub Test()
    Dim Cible As Range
    
    Set Cible = DerniereCellule(ActiveSheet)
    MsgBox Cible.Address
End Sub


'Récupère la dernier cellule utilisée dans la feuille spécifiée
'(Cet exemple ne gère pas les feuilles totalement vides)
Function DerniereCellule(Ws As Worksheet) As Range
    Dim NumLigne As Double, NumCol As Integer
    
    With Ws
        NumLigne = .Cells.Find("*", .Range("A1"), , , xlByRows, xlPrevious).Row
        NumCol = .Cells.Find("*", .Range("A1"), , , xlByColumns, xlPrevious).Column
        
        Set DerniereCellule = .Cells(NumLigne, NumCol)
    End With
End Function

II-B-6. Exit Function▲

II-B-7. arglist▲

Option Explicit

Sub Test()
    Dim Donnee As Integer
    
    Donnee = 50
    
    MaProcedure_1 Donnee
    MsgBox Donnee
    
    MaProcedure_2 Donnee
    MsgBox Donnee
    
    MaProcedure_3 Donnee
    MsgBox Donnee
End Sub


'Passe la référence en argument.
Function MaProcedure_1(ByRef x As Integer) As Integer
    x = x * 2
End Function


'Passe la valeur en argument.
Function MaProcedure_2(ByVal y As Integer) As Integer
    y = y * 2
End Function


'ByRef est la valeur par défaut si non spécifiée.
Function MaProcedure_3(z As Integer) As Integer
    z = z * 2
End Function

Sub Test()
    'Renvoie tous les arguments spécifiés
    MsgBox MaProcedure(5, "fifi")
    'Renvoie 5 et "mimi" car l'argument Optional (strPrenom) n'est pas précisé
    MsgBox MaProcedure(5)
End Sub


Function MaProcedure(lngValeur As Long, Optional strPrenom As String = "mimi")
    MaProcedure = lngValeur & vbCrLf & strPrenom
End Function

II-B-8. Type▲

II-B-9. statements▲

II-B-10. expression▲

II-B-11. Le nom des variables▲

Ajoutez la ligne d'instruction Application.volatile en début de fonction lorsque celle-ci est utilisée dans la feuille et doit être recalculée automatiquement à chaque fois qu'une modification ou un calcul est effectué dans une cellule quelconque du classeur.

Les tableaux de paramètres (ParamArray) permettent de passer un tableau d'arguments à une procédure. Le nombre d'éléments indiqué peut être variable. Vous pouvez de cette manière passer plusieurs informations (et dont le nombre peut varier d'un appel à l'autre) à une fonction.

L'argument tableau ParamArray:
     * Doit obligatoirement être déclaré en type Variant.
     * Ne peut pas coexister avec des arguments Optional.
     * Doit être placé en dernière position dans la liste des arguments.
     * Les arguments ne sont pas facultatifs.
     * Chaque argument peut être d'un type de donnée différent.
     * Le nombre d'arguments n'est pas limité.
     * L'indice inférieur du tableau est toujours 0.

Pour détecter un argument ParamArray vide, effectuez un test qui déterminera si la limite maximale du tableau est inférieure à sa limite minimale.


Lorsque l'appel de procédure est effectué, chaque argument fourni dans l'appel devient un élément indexé du tableau.

Un exemple qui supprime une série de caractères dans une chaîne:
Les caractères ";", ":", "!" sont passés au tableau ParamArray "A_Supprimer1". La procédure boucle ensuite sur les éléments du tableau et supprime chaque caractère trouvé dans la chaîne "LeTexte".

Option Explicit

Sub Test()
    Dim strChaine As String
    
    strChaine = "m!im::i;"
    'La procédure va supprimer les caractères ; : !
    'dans la variable "Chaine"
    strChaine = SupprimeCaracteres(strChaine, ";", ":", "!")
    MsgBox strChaine
End Sub
 
Function SupprimeCaracteres(LeTexte As String, ParamArray A_Supprimer1())
    Dim i As Integer
    'Boucle sur les éléments du tableau
    For i = 0 To UBound(A_Supprimer1())
        'Supprime les caractères spécifiés
        LeTexte = Replace(LeTexte, A_Supprimer1(i), "")
    Next i
    SupprimeCaracteres = LeTexte
End Function

Si vous utilisez ParamArray dans une fonction, ajoutez le chiffre 1 en fin de nom du paramètre. Ainsi, l'assistant de fonctions Excel incrémentera automatiquement chaque nouvel élément indiqué par l'utilisateur.




Ce deuxième exemple crée un tableau d'une dimension, à partir des données passées dans la fonction.
Une fonction qui retourne un tableau doit impérativement être déclarée en type Variant.

Function CreationTableau(ParamArray Cellules1()) As Variant
    'Adapté de:
    'http://support.microsoft.com/?kbid=213403
    '
    Dim VarTab() As Variant
    Dim Temp As Variant
    Dim i As Integer
    Dim w As Integer, X As Integer, y As Integer, z As Integer
    
    i = 1
    
    'Boucle sur les éléments du tableau de paramètres.
    For X = 0 To UBound(Cellules1)
        If TypeName(Cellules1(X)) = "Range" Then
            Set Temp = Cellules1(X)
            'Vérifie si le paramètre passé à la fonction est une cellule simple
            'ou une plage.
            If IsArray(Temp) Then
                'Intègre chaque cellule de la plage dansle tableau.
                For y = 1 To UBound(Temp.Value)
                    For z = 1 To UBound(Temp.Value, 2)
                        'Permet de filtrer les cellules vides.
                        'If Not IsEmpty(Temp(y, z).Value) Then
                            ReDim Preserve VarTab(1 To i)
                            VarTab(i) = Temp(y, z).Value
                            i = i + 1
                        'End If
                    Next z
                Next y
                Else
                    'Permet de filtrer les cellules vides.
                    'If Not IsEmpty(Temp) Then
                        'Intègre la cellule dans le tableau.
                        ReDim Preserve VarTab(1 To i)
                        VarTab(i) = Temp
                        i = i + 1
                    'End If
            End If
        Else
            ReDim Preserve VarTab(1 To i)
            VarTab(i) = Cellules1(X)
            i = i + 1
        End If
    Next X
    
    CreationTableau = VarTab
End Function

Vous pouvez indiquer des cellules uniques (E1), des plages (A1:A10, C1:C10) ou des données (80) lorsque vous appelez la fonction:

Sub Test()
    Dim Tb As Variant, xTab As Variant
    
    Tb = CreationTableau(Range("A1:A10"), Range("C1:C10"), Range("E1"), 80)
    
    '--- Vérifie si le tableau est vide ---
    On Error Resume Next
    'xTab va prendre la valeur Empty si le tableau est vide.
    xTab = UBound(Tb)
    On Error GoTo 0
    
    'Renvoie le nombre d'éléments du tableau
    If Not IsEmpty(xTab) Then MsgBox UBound(Tb)
End Sub

Les fonctions sont constituées d'arguments obligatoires et optionnels. Comme leur nom l'indique, les arguments obligatoires sont nécessaires au bon fonctionnement de la procédure. Quant aux arguments optionnels, lorsqu'ils sont omis, cela n'empêche pas l'exécution de la macro. Les arguments optionnels sont obligatoirement placés en fin de la déclaration.


Voici un exemple de fonction à placer dans un module standard. L'argument Coeff est déclaré Optional:
La fonction MaFonctionPerso renvoie une donnée de type Double.

Function MaFonctionPerso(NbValeurs As Long, Optional Coeff As Variant) As Double
    
    'Spécifie que la fonction doit être recalculée à chaque fois qu'un calcul
    'est effectué dans une cellule quelconque de la feuille.
    Application.Volatile
    
    'Vérifie si l'argument optionnel à été indiqué
        'Nota: Coeff doit être impérativement de type Variant.
    If IsMissing(Coeff) Then
        'S'il n'y a pas d'argument optionnel:
        MaFonctionPerso = (NbValeurs + 3)
    Else
        'S'il y a un argument optionnel:
        MaFonctionPerso = (NbValeurs + 3) * Coeff
    End If
    
End Function

Vous pouvez ensuite appeler cette fonction depuis une procédure Sub.

Sub AppelFonction()
    MsgBox MaFonctionPerso(10)
    MsgBox MaFonctionPerso(10, 5)
End Sub

Mais aussi l'utiliser depuis la feuille de calcul:

=MaFonctionPerso(10)
=MaFonctionPerso(10;5)

'Si le premier argument fait référence à la cellule A1:
=MaFonctionPerso(A1;5)

Rappel:
Il est possible de définir une donnée par défaut pour un argument de type Optional. Il s'agit de la valeur qui sera prise en compte si l'utilisateur n'a pas spécifié l'argument optionnel.
La valeur par défaut est définie de la manière suivante:
Optional [NomVariable] As [TypeVariable] = [Donnée par défaut]

Sub Test()
    'Renvoie tous les arguments spécifiés
    MsgBox MaProcedure(5, "fifi")
    'Renvoie 5 et "mimi" car l'argument Optional (strPrenom) n'est pas précisé
    MsgBox MaProcedure(5)
End Sub

Function MaProcedure(lngValeur As Long, Optional strPrenom As String = "mimi") As Variant
    MaProcedure = lngValeur & vbCrLf & strPrenom
End Function

Il est conseillé d'utiliser un type Variant pour les arguments Optionnels. En effet, vous pourrez ensuite intégrer la fonction IsMissing dans votre procédure afin de vérifier si l'argument a été spécifié ou non.
La fonction IsMissing permet de vérifier si des arguments optionnels sont indiqués lors d'un appel. IsMissing renvoie la valeur True si l'argument n'a pas été spécifié, sinon elle renvoie la valeur False.
Si vous devez utiliser cette fonction, les arguments optionnels doivent impérativement être de type Variant. En effet, une variable Variant prend valeur "Empty" par défaut (vide) et peut donc être identifiée par la fonction IsMissing.

Sub test()
    NomFonction Arg1:=0, Arg2:=56765765
    NomFonction Arg1:=453
    NomFonction Arg2:=56765765
    NomFonction
End Sub


Function NomFonction(Optional Arg1 As Variant, Optional Arg2 As Variant) As Variant
    MsgBox "Arguments manquants:" & vbCrLf & _
        IsMissing(Arg1) & " / " & IsMissing(Arg2)
End Function

Nous avons vu dans les chapitres précédents qu'un type de donnée incohérent entre les paramètres d'appel, les arguments et les variables de la fonction, provoque une erreur d'exécution.
Une solution palliative peut consister à créer tous les arguments en type Variant et intégrer une vérification à l'intérieur de la fonction. Il existe des fonctions prévues à cet effet:

     * TypeName
     * VarType
     * IsDate
     * IsNumeric
     * IsEmpty
     * IsMissing
     * IsArray
     * IsObject
     * IsNull


La méthode Raise, utilisée dans l'exemple suivant, gère une erreur d'exécution dans la macro, si le type de donnée ne correspond pas ce qui est attendu.
La procédure affiche un MsgBox qui reprend des informations d'aide personnalisées que vous aurez préalablement défini. La méthode Raise donne aussi accès à un fichier d'aide .chm (bouton Aide dans le MsgBox). Placez la procédure "PerimetreCercle" dans un module standard, puis la formule suivante dans une cellule de la feuille:
=PerimetreCercle("mimi")
Une erreur va survenir car un argument de type texte (mimi) est indiqué à la place d'une donnée numérique. Un message d'alerte va indiquer que le type de donnée ne convient pas à procédure. La fonction IsNumeric permet d'effectuer cette vérification.

Function PerimetreCercle(Diametre As Variant) As Variant
    
    'Spécifie que la fonction doit être recalculée à chaque fois qu'une modification
    'est effectuée dans une cellule quelconque de la feuille.
    Application.Volatile
    
    
    On Error GoTo Fin
    'Affiche un message d'alerte si l'argument (Diametre) n'est
    'pas correctement saisi. (Si par exemple vous saisissez un texte à la place
    'd'un nombre).
    If Not IsNumeric(Diametre) Then _
        Err.Raise 5010, , vbCrLf & "Attention!" & vbCrLf & _
            "Vous devez saisir une valeur numérique." & vbCrLf & _
            "La donnée '" & Diametre & "' n'est pas valide.", _
            "C:\dossier\fichier d'aide.chm", 9900
 
    PerimetreCercle = Diametre * 3.14
Exit Function
 

Fin:
 
'Affiche un message correspondant aux paramètres indiqués dans la
'méthode Raise.
MsgBox "Erreur: " & Err.Number & vbCrLf & _
    Err.Description, vbExclamation + vbMsgBoxHelpButton, , Err.HelpFile, Err.HelpContext
 
'Force la valeur d'erreur #N/A dans la cellule, si le type de données est erroné.
PerimetreCercle = CVErr(xlErrNA)
 
End Function

Remarque:
Si le contenu de la formule n'est pas ensuite corrigé, le message d'erreur continuera de s'afficher systématiquement à chaque modification ou re-calcul dans la feuille.