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 |
'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 :
2. Utiliser 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 |
'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 » :