excel-vba
Fonctions définies par l'utilisateur (UDF)

Syntaxe

1. Fonction functionName (argumentVariable As dataType, argumentVariable2 As dataType, Argument optionnelVariable3 As dataType) As functionReturnDataType
   Déclaration de base d'une fonction. Chaque fonction a besoin d'un nom, mais il n'est pas nécessaire qu'elle prenne des arguments. Il peut prendre 0 argument ou prendre un nombre donné d'arguments. Vous pouvez également déclarer un argument comme facultatif (ce qui signifie que peu importe si vous le fournissez lors de l'appel de la fonction). Il est recommandé de fournir le type de variable pour chaque argument et, de même, de renvoyer le type de données que la fonction elle-même va retourner.

2. functionName = theVariableOrValueBeingReturned
   Si vous venez d'autres langages de programmation, vous pouvez être habitué au mot-clé Return . Ceci n'est pas utilisé dans VBA - au lieu de cela, nous utilisons le nom de la fonction. Vous pouvez le définir sur le contenu d'une variable ou sur une valeur directement fournie. Notez que si vous avez défini un type de données pour le retour de la fonction, la variable ou les données que vous fournissez cette fois doivent être de ce type de données.

3. Fonction de fin
   Obligatoire. Signifie la fin du bloc de code Function et doit donc être à la fin. Le VBE le fournit généralement automatiquement lorsque vous créez une nouvelle fonction.

Remarques

UDF - Hello World

1. Ouvrir Excel
2. Ouvrez Visual Basic Editor (voir Ouverture de Visual Basic Editor )
3. Ajoutez un nouveau module en cliquant sur Insérer -> Module:

4. Copiez et collez le code suivant dans le nouveau module:

Public Function Hello() As String
'Note: the output of the function is simply the function's name
Hello = "Hello, World !"
End Function

Obtenir :

5. Retournez dans votre classeur et tapez "= Hello ()" dans une cellule pour voir le "Hello World".

Autoriser les références de colonne complètes sans pénalité

Il est plus facile d'implémenter des UDF sur la feuille de calcul si des références de colonne complètes peuvent être transmises en tant que paramètres. Cependant, en raison de la nature explicite du codage, toute boucle impliquant ces plages peut traiter des centaines de milliers de cellules complètement vides. Cela réduit votre projet VBA (et votre classeur) à un désordre gelé pendant que les non-valeurs inutiles sont traitées.

Traverser les cellules d'une feuille de calcul est l'une des méthodes les plus lentes pour accomplir une tâche, mais elle est parfois inévitable. Découper le travail effectué jusqu'à ce qui est réellement requis est parfaitement logique.

La solution consiste à tronquer les références complètes de colonne ou de ligne complète à la propriété Worksheet.UsedRange avec la méthode Intersect . L'exemple suivant va répliquer de manière lâche fonction SOMME.SI native d'une feuille de calcul afin que le criteria_range sera également redimensionnée en fonction de la somme_plage puisque chaque valeur dans le somme_plage doit être accompagnée d'une valeur dans la criteria_range.

Application.Caller pour une fonction définie par l'utilisateur utilisée sur une feuille de calcul est la cellule dans laquelle elle réside. La propriété .Parent de la cellule est la feuille de calcul. Ceci sera utilisé pour définir le .UsedRange.

Dans une feuille de code du module:

Option Explicit

Function udfMySumIf(rngA As Range, rngB As Range, _
                    Optional crit As Variant = "yes")
    Dim c As Long, ttl As Double
    
    With Application.Caller.Parent
        Set rngA = Intersect(rngA, .UsedRange)
        Set rngB = rngB.Resize(rngA.Rows.Count, rngA.Columns.Count)
    End With
    
    For c = 1 To rngA.Cells.Count
        If IsNumeric(rngA.Cells(c).Value2) Then
            If LCase(rngB(c).Value2) = LCase(crit) Then
                ttl = ttl + rngA.Cells(c).Value2
            End If
        End If
    Next c
    
    udfMySumIf = ttl

End Function

^Syntaxe:
=udfMySumIf(*sum_range*, *criteria_range*, [*criteria*])

Bien qu'il s'agisse d'un exemple assez simpliste, il montre de manière adéquate le passage de deux références de colonne complètes (1 048 576 lignes chacune), mais uniquement le traitement de 15 lignes de données et de critères.

_{Documentation MSDN officielle liée aux méthodes et propriétés individuelles de Microsoft ™.}

Compter les valeurs uniques dans Range

Function countUnique(r As range) As Long
    'Application.Volatile False ' optional
    Set r = Intersect(r, r.Worksheet.UsedRange) ' optional if you pass entire rows or columns to the function
    Dim c As New Collection, v
    On Error Resume Next   ' to ignore the Run-time error 457: "This key is already associated with an element of this collection".
    For Each v In r.Value  ' remove .Value for ranges with more than one Areas
        c.Add 0, v & ""
    Next
    c.Remove "" ' optional to exclude blank values from the count
    countUnique = c.Count
End Function

Collections