Ci-dessous, nous examinerons un programme dans Excel VBA qui crée un lien: / vba-exemples-utilisateur-fonction définie [User Defined Function] qui calcule la moyenne d’une plage choisie au hasard sauf une ou plusieurs valeurs qui sont des valeurs aberrantes et ne devrait pas être en moyenne.

Situation:

Custom Average Function in Excel VBA

fonctions définies par l’utilisateur doivent être placés dans un module.

  1. Ouvrez l’éditeur Visual Basic et cliquez sur Insérer, Module.

  2. Ajoutez la ligne de code suivant:

Function CUSTOMAVERAGE(rng As Range, lower As Integer, upper As Integer)

Le nom de notre fonction est CUSTOMAVERAGE. La partie entre les parenthèses signifie que nous donnons Excel VBA une gamme et deux variables entières en entrée. Nous nommons notre gamme RNG, une variable entier que nous appelons plus bas, et une variable entier que nous appelons supérieure, mais vous pouvez utiliser tous les noms.

  1. Ensuite, nous déclarons un objet Range et deux variables de type entier.

Nous appelons la cellule objet Range. Une variable entier que nous appelons au total et une variable entier que nous appelons compter.

Dim cell As Range, total As Integer, count As Integer

  1. Nous voulons vérifier chaque cellule dans une plage sélectionnée au hasard (cette gamme peut être de toute taille). Dans Excel VBA, vous pouvez utiliser la boucle For Each suivante pour cela. Ajoutez les lignes de code suivantes:

For Each cell In rng

Next cell

Note: RNG et cellules sont choisies au hasard ici, vous pouvez utiliser tous les noms.

N’oubliez pas de se référer à ces noms dans le reste de votre code.

  1. Ensuite, nous vérifions pour chaque valeur dans cette plage si elle se situe entre les deux valeurs (inférieure et supérieure). Si cela est vrai, nous accroissement total de la valeur de la cellule et comptons nous incrémenter par 1. Ajoutez les lignes de code suivantes à la boucle.

If cell.Value >= lower And cell.Value <= upper Then

total = total + cell.Value

count = count + 1

End If

  1. Pour retourner le résultat de cette fonction (la moyenne souhaitée), ajouter la ligne de code suivante en dehors de la boucle.

CUSTOMAVERAGE = total / count

  1. Ne pas oublier de mettre fin à la fonction. Ajoutez la ligne:

End Function

  1. Maintenant, vous pouvez utiliser cette fonction comme toute autre fonction Excel pour calculer la moyenne des chiffres qui se situent entre deux valeurs.

Résultat:

Custom Average Function Result

Pour vérifier, vous pouvez supprimer toutes les valeurs qui sont inférieures à 10 et supérieur à 30 et utiliser la fonction standard moyenne dans Excel pour voir si Excel calcule la même moyenne que notre fonction personnalisée moyenne.

Custom Average Function Check

Nos oeuvres fonction personnalisée moyenne! Remarque: cette fonction est uniquement disponible dans ce classeur.