Fonction personnalisée qui se recalcule automatiquement

En utilisant une fonction personnalisée, on peut parfois rencontrer un problème, qui est que la fonction ne se recalcule pas automatiquement lorsque l’on change des valeurs. Prenons l’exemple de la fonction suivante :

Code VBA


Function Calcul_Indicateur(Objectif As Double)

'Fonction qui calcul le taux de rendement d'une chaine de production pendant 10 jours

 

Dim Nombre_Jour As Integer

Dim Somme As Double

 

Nombre_Jour = Application.WorksheetFunction.Count(Range("B2:B11")) 'Nombre de jour de travail

Somme = Application.WorksheetFunction.Sum(Range("B2:B11")) 'Somme de production

 

Calcul_Indicateur = Somme / (Nombre_Jour * Objectif) 'Formule de calcul

End Function

 

 

L’utilisation de cette fonction dans la feuille de calcul donne le résultat suivant pour cet exemple :

 

En modifiant ou en ajoutant des nouvelles valeurs, l’indicateur n’est pas actualisé :

 

Ce genre de problème apparait quand la fonction utilise des plages de cellules qui ne sont pas passés en paramètre, dans notre exemple la plage «B2 :B10 » , pour remédier à ça, deux façons sont envisageables :

1. Passer toutes les plages utilisées par la fonction en paramètre, dans notre exemple le code devient :

Code VBA

Function Calcul_Indicateur(Objectif As Double, Plage_Reference As Range)

'Fonction qui calcul le taux de rendement d'une chaine de production les journées dans la plage référence

 

Dim Nombre_Jour As Integer

Dim Somme As Double

Nombre_Jour = Application.WorksheetFunction.Count(Plage_Reference) 'Nombre de jour de travail

Somme = Application.WorksheetFunction.Sum(Plage_Reference) 'Somme de production

Calcul_Indicateur = Somme / (Nombre_Jour * Objectif) 'Formule de calcul

End Function

 

 

Reprenons l’exemple avec le nouveau code :

 

2Utiliser l’instruction Application.Volatile

Si on commence le code de la fonction par Application.Volatil, cette fonction devient volatile, c’est-à-dire elle se recalcul après chaque modification sur la feuille de calcul. Reprenons le premier code en ajoutant cette ligne de code :

 

Code VBA


Function Calcul_Indicateur(Objectif As Double)

'Fonction qui calcul le taux de rendement d'une chaine de production pendant 10 jours

Dim Nombre_Jour As Integer

Dim Somme As Double

Application.Volatile

Nombre_Jour = Application.WorksheetFunction.Count(Range("B2:B11")) 'Nombre de jour de travail

Somme = Application.WorksheetFunction.Sum(Range("B2:B11")) 'Somme de production

Calcul_Indicateur = Somme / (Nombre_Jour * Objectif) 'Formule de calcul

End Function

 

 

L’utilisation de la fonction avec cette modification donne dans notre exemple :

 

Utiliser une fonction personnalisée dans un autre classeur.

Si on crée une fonction personnalisée dans un classeur, cette fonction figure parmi les autres fonctions de Excel sur n’importe quelle cellule de ce classeur, et seulement ce classeur. Si on veut utiliser une fonction personnalisée crée dans un classeur « Classeur1 » dans un autre classeur, il suffit de faire référence au « classeur1 » avant d’écrire la syntaxe de la fonction. On prend l’exemple précédant de la fonction « Calcul_Indicateur » et on suppose qu’on veut l’appliquer dans un classeur « Classeur2 » :