Formule dont le résultat ne se met pas à jour

Le résultat de ma formule ne change pas quand mes données changent. Pourquoi ?

Si tu écris en B1 la formule =A1 et que tu changes la valeur en A1, celle de B1 doit changer en même temps.

  • Si en B1 tu vois = A1 au lieu du contenu de la cellule A1, il y a deux possibilités :
    •  la cellule B1 était au format texte AVANT que tu n'y inscrives ta formule. Pour rétablir le calcul, mets cette cellule au format standard (nombre, scientifique, monétaire... au choix mais pas texte). Ensuite, avec la cellule sélectionnée, appuie sur F2 ou bien encore place le curseur dans la barre de formule et valide. 
    • Si dans tous les cellules contenant des formules tu vois la formule et pas le résultat, c'est que l'option afficher les formules a été sélectionnée.
 
 

Mode de calcul

Il y a plusieurs modes de calcul avec excel (accessibles par le menu  ou le bouton options suivant les versions)
pour excel 2007 :

  • Automatique : un changement dans un des classeurs ouverts provoque le recalcul de tous les classeurs ouverts.
  • Sur ordre : rien ne se produit tant qu'on ne demande pas explicitement à excel de recalculer les classeurs ouverts.
  • Automatique sauf les tables.
On ne peut malheureusement pas dans excel mettre un classeur en mode manuel et un autre, ouvert en même temps en mode automatique.
Donc si les résultats de tes formules ne se mettent pas à jour automatiquement vérifie que le mode de calcul est bien sur automatique, ou lance manuellement un recalcul.
 

Forcer le recalcul

Pour forcer le recalcul d'une cellule, il suffit de la sélectionner et de taper F2 ou de cliquer dans la barre de formule.
Cliquer sur F9 lance le recaclul des classeurs ouverts. Parfois, cela est inopérant alors que la validation manuelle de chaque cellule provoque bien le recalcul.
la combinaison ctrl+alt+F9 force dans ce cas le recalcul.
 

Pourquoi et quand mettre le mode de calcul sur manuel ?

Certaines formules sont très consommatrices de ressources. Notamment toutes les formules matricielles, y compris sommeprod, qui bien que ne nécessitant pas de validation matricielle (ctrl+maj+entrée) est une formule matricielle (travaillant sur des plages de données et non pas sur des cellules isolées). Si dans un classeur tu as aun tableau comportant de nombreuses formules matricielles, il devient vite lassant d'attendre à chaque modification d'un classeur ouvert qu'excel ait fini de calculer ce tableau. L'utilisation du mode de calcul automatique sauf les tables ou du calcul manuel est bien utile dans ce cas.

Si dans ce même classeur tu as des macros, chaque modification apportée par la macro entraîne le recalcul du tableau. Cela peut ralentir considérablement l'exécution de la macro, voire même faire planter excel pour cause de ressources mémoire insuffisantes.
 

Comment désactiver le calcul pendant le déroulement de la macro ?

En inscrivant en début de macro
Application.Calculation = xlcalculationmanual
et surtout en n'oubliant pas de mettre en fin de macro
Application.Calculation = xlCalculationAutomatic
pour rétablir le mode de calcul automatique.

Attention cependant, si ta macro demande que des calculs intermédiaires soient effectués sur la feuille, ceux-ci ne se feront pas.
Précisons :
imaginons que tu aies
12 en A1
et =A1*2 en B1

Dans ta macro, tu modifies la valeur de A1 et tu utilises celle de B1 pour faire un autre calcul.
Le fait de mettre
Application.Calculation = xlcalulationmanual
en début de macro fait que même en changeant A1, B1 conserve la valeur B1 tant que la ligne
Application.Calculation = xlCalculationAutomatic
n'est pas rencontrée.
Tu peux forcer le recalcul au moment opportun dans vitre macro en utilisant l'instruction
Application.Calculate
qui force le recalcul de tous les classeurs ouverts.
Si le temps nécessaire est important, il est plus intéressant de limiter au strict nécessaire ce qui doit être recalculé, par exemple une seule feuille :
worksheets(1).calculate
ou même une seule plage de données
range("A1:A1000").calculate

Associée à l'utilisation de
Application.screenupdating = false
passer le mode de calcul en manuel pendant le déroulement d'une macro fait gagner souvent un temps considérable. C'est la première chose à tester si on se trouve face à une macro vraiment longuette.
 

J'ai mis le mode de calcul sur manuel en début de macro mais cela ne fonctionne pas

Certaines instructions rendent l'instruction
Application.Calculation = xlcalculationmanual
inopérante. C'est le cas par exemple si vous mettez à jour des données sources contenues dans des fichiers csv ou txt externes.
Dans ce cas, vous pouvez quand même empêcher le calcul des feuilles de votre classeur en utilisant l'instruction

Worksheets("mafeuilleavecpleindeformules").EnableCalculation = False

N'oubliez pas de rétablir le calcul en fin de macro :

Worksheets("mafeuilleavecpleindeformules").EnableCalculation = true

Auteur :

Mot clef associé à cette page :