La fonction personnalisée peut ne pas calculer la valeur attendue dans Excel

• S’applique à:

  Microsoft Excel

Symptômes

Lorsque vous calculez votre feuille de calcul, certaines cellules peuvent sembler avoir calculé la valeur incorrecte.

Cause

Ce problème se produit lorsque les conditions suivantes sont remplies :

• Une cellule de la feuille de calcul contient une fonction personnalisée.

• La fonction personnalisée contient un ou plusieurs arguments qui font référence à une plage de cellules dans la feuille de calcul.

• Le résultat de la fonction personnalisée dépend de plus de cellules que de références directes.

Ce comportement est dû à la conception de Microsoft Excel. Quand Excel calcule la cellule contenant la fonction personnalisée, il recalcule toutes les plages de cellules passées en tant qu’arguments à votre fonction personnalisée. Si le résultat de votre fonction dépend de cellules auxquelles la fonction ne fait pas explicitement référence, ces cellules peuvent ne pas être recalculées.

Solution de contournement

Pour contourner ce problème, utilisez l’une des méthodes suivantes.

Méthode 1 : Modifier votre fonction afin que toutes les plages de cellules pertinentes soient passées

Modifiez votre fonction pour accepter en tant qu’arguments toutes les cellules nécessaires pour calculer le résultat de la fonction.

Méthode 2 : Rendre votre fonction personnalisée volatile

Microsoft fournit des exemples de programmation à titre d’illustration uniquement, sans garantie expresse ou implicite. Cela inclut, sans y être limité, les garanties implicites de commercialisation et d’adaptation à un but en particulier. Cet article considère que vous connaissez le langage de programmation présenté et les outils utilisés pour créer et déboguer des procédures. Les ingénieurs du support technique Microsoft peuvent vous aider à expliquer les fonctionnalités d’une procédure particulière, mais ils ne modifient pas ces exemples pour fournir des fonctionnalités supplémentaires ou construire des procédures pour répondre à vos besoins spécifiques. Si vous rendez votre fonction personnalisée volatile, ce problème ne se produira pas. Pour rendre votre fonction personnalisée volatile, ajoutez la ligne de code suivante à la fonction.

VB

Application.Volatile

Note

Si vous rendez votre fonction personnalisée volatile, elle est recalculée chaque fois que vous apportez une modification à une valeur ou recalculez un classeur ouvert. Cela peut avoir un impact sur les performances de votre modèle de feuille de calcul.

Méthode 3 : Forcer Excel à recalculer tous les classeurs ouverts

Appuyez sur Ctrl+Alt+F9 pour recalculer les valeurs dans tous les classeurs ouverts. Dans Microsoft Office Excel 2007, appuyez sur Ctrl+Alt+Maj+F9 pour recalculer les valeurs de tous les classeurs ouverts.

Informations supplémentaires

Exemple du problème

Pour illustrer ce problème, procédez comme suit :

1. Fermez et enregistrez tous les classeurs ouverts, puis ouvrez un nouveau classeur.

2. Démarrez le Rédacteur Visual Basic (appuyez sur Alt+F11).

3. Dans le menu Insertion, cliquez sur Module.

4. Tapez le code suivant dans la feuille de module :

   VB

   ' This function counts the number of blank cells by starting from the cell 
   ' referred to by the rngStartCell argument and moving up the column.
   
   Function FindTextUp(rngStartCell As Range) As Single
       Dim iIndex As Integer
   
   For iIndex = 0 To 100
           If rngStartCell.Value <> "" Then
               FindTextUp = iIndex
               Exit Function
           Else
               Set rngStartCell = rngStartCell.Offset(-1, 0)
           End If
       Next iIndex
   End Function
   

5. Appuyez sur Alt+F11 pour revenir à Excel.

6. Tapez Test, dans la cellule A2, puis appuyez sur Entrée.

7. Tapez la formule suivante dans la cellule A10, puis appuyez sur Entrée :

   excel

   =FindTextUp(A9)
   

   La formule retourne la valeur 7.

8. Tapez Un autre test dans la cellule A5, puis appuyez sur Entrée.

La formule retourne toujours la valeur 7, lorsqu’une valeur de 4 est attendue. Dans cet exemple, la fonction FindTextUp fait explicitement référence à la cellule A9. Toutefois, la fonction peut dépendre des cellules A1 :A8, en fonction des données entrées dans la feuille de calcul.

Si vous implémentez la méthode 1 dans la section « Solution de contournement » de cet article, la fonction calcule le résultat attendu. La ligne de code suivante montre comment modifier la fonction dans cet exemple afin que le résultat attendu soit calculé.

VB

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single

À présent, si vous remplacez l’appel de fonction à l’étape 7 par l’appel de fonction suivant, la fonction retourne toujours le résultat attendu.

excel

=FindTextUp(A9,A1:A8)