Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

[charles_lyon]

Bonjour,

Je vous soumets un problème auquel je n'ai pas trouvé de réponse malgré de nombreuses recherches sur la toile.

J'ai un fichier avec :
- des plages nommées
- des fonctions personnalisées imbriquées
- plusieurs centaines de lignes de calcul
Le recalcul complet nécessite l'appel de Application.CalculateFull par le biais d'un bouton.
A l'issu du recalcul les résultats sont corrects.

Par contre quand deux fichiers sont ouverts, appelons les calcul1 et calcul2,
lorsque l'on provoque le recalcul du fichier calcul1 le fichier calcul2 est modifié et présente des résultats erronnés,
alors qu'il n'y a aucune liaison entre les 2 fichiers.

A la réflexion les seuls points communs entre les 2 fichiers sont les cellules et les plages de cellules nommées.
Effectivement le problème semble survenir dans l'évaluation des cellules nommées.

J'ai recréé ce phénomène sur 2 fichiers très simples que je joints.
Avec une cellule nommée et une fonction personnalisée.
Dans la fonction il y a une boucle de temporisation pour avoir le temps de visualiser le phénomène.

Pour tester :
- ouvrir les 2 fichiers
- selectionner test1 , entrer 100, le résultat 100 s'affiche
- selectionner test2 , entre 1000, le résultat 1000 s'affiche
- selectionner test1, et là vous aurez le temps de voir que le résultat qui s'affiche est 1000, avant de se recalculer à 100 !!!

Dans mon fichier réél, comme le temps de calcul est de 20 à 40 secondes, lorsque l'utilisateur ouvre le 2 ème fichier, et clique sur une cellule, ce qui bloque le calcul, il voit des résultats erronnés ...

La seule solution que j'ai trouvé est de mettre un application.calculate dans le sub activate du worksheet.
Cette solution provoque le recalcul avant l'affichage du fichier.
Cette solution est très pénalisante en temps, et ce n'est pas du tout satisfaisant de devoir recalculer un fichier qui était correct quand on l'a fermé, et qui se trouve modifié par l'ouverture d'un autre fichier.

Si vous avez une idée pour régler simplement ce problème, je suis preneur ^^

Charles

 

[job75]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Bonjour charles_lyon, bienvenue sur XLD,

Dans les fonctions il suffit de préciser le classeur où se trouve le nom défini :

perso1 = Evaluate("'" & ThisWorkbook.Name & "'!_var1")

Le nom est entre quotes ' au cas où il contiendrait des espaces.

A+

 

[Dranreb]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Bonjour.
Dans la mesure du possible évitez la méthode Evaluate de l'objet Application, car comme pour Range ou Cells, elle appelle celle de l'objet Workheet représentant la feuille active. Spécifiez plutôt le CodeName de la feuille suivi d'un point devant Evaluate pour forcer l'emploi de la méthode Evaluate de cet objet Worksheet là, qui représente toujours une feuille du classeur portant la macro.

Edit: Bonjour Job75

 

[job75]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Re,

En fait votre problème ne provient pas du fait qu'il y a 2 noms identiques puisque la fonction se calcule correctement après la boucle de temporisation.

C'est un problème d'affichage : 2 images d'écrans se superposent pendant la boucle de temporisation.

Pour y remédier utiliser DoEvents, mais attention, limitez la boucle à 400000 (ou moins) :

Function perso1()
Application.Volatile

' boucle de temporisation pour avoir le temps de lire à l'écran
For i = 1 To 400000
DoEvents
x = x + i
Next i

perso1 = Evaluate("_var1")


End Function

A+

 

[job75]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Re, salut Bernard, je ne t'avais pas vu,

J'avais mal testé, il y a aussi le problème du même nom dans les 2 fichiers.

Ceci règle donc les 2 problèmes :

Function perso1()
Application.Volatile

' boucle de temporisation pour avoir le temps de lire à l'écran
For i = 1 To 250000
DoEvents
x = x + i
Next i

perso1 = Evaluate("'" & ThisWorkbook.Name & "'!_var1")

End Function

A+

 

[job75]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Re,

En fait il paraît inutile de placer DoEvents dans la boucle, utilisez donc :

Function perso1()
Application.Volatile

DoEvents

' boucle de temporisation pour avoir le temps de lire à l'écran
For i = 1 To 40000000
x = x + i
Next i

perso1 = Evaluate("'" & ThisWorkbook.Name & "'!_var1")

End Function

A+

 

[job75]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Bonjour le fil, le forum,

Avec cette boucle on ajuste la temporisation comme on veut :

Function perso1()
Dim t#
Application.Volatile

DoEvents

'temporisation de 3 secondes pour avoir le temps de lire à l'écran
t = Timer + 3: While Timer < t: Wend

perso1 = Evaluate("'" & ThisWorkbook.Name & "'!_var1")

End Function

Bonne journée et A+

 

[charles_lyon]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Bonjour job75

Merci pour cette réponse pertinente et rapide.
Préfixer les variables nommées résout complètement le problème
Evaluate("'" & ThisWorkbook.Name & "'!_var1")

Cette fonctionnalité est-elle documentée quelque part chez MicroSoft ?
Je suppose que de nombreux utilisateurs utilisent les variables sans les préfixer, notamment avec les [ ] au lieu de Evaluate, sans savoir les effets sur les autres classeurs lorsqu'ils font des 'Enregistrer sous ...'

Encore Merci pour cette aide.
Charles

PS :
La boucle de tempo dans l'exemple transmis n'avait pour rôle que de visualiser le phénomène.
Cette boucle n'existe pas dans l'application réelle.
Le problème posé était bien celui de cellules ou de range nommés avec le même nom dans plusieurs classeur.

 

[job75]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Re,

Si la fonction devait se calculer entre 23:59:57 et 24:00:00 il y aura problème...

Ceci est donc plus sûr :

Function perso1()
Dim t#
Application.Volatile

DoEvents

' temporisation de 3 seconde pour avoir le temps de lire à l'écran
t = Now + 3 / 86400: While Now < t: Wend

perso1 = Evaluate("'" & ThisWorkbook.Name & "'!_var1")

End Function

A+

 

[job75]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Re,

Cette fonctionnalité est-elle documentée quelque part chez MicroSoft ?

On devrait la trouver, mais elle n'a rien de bien extraordinaire.

'NomClasseur'!Nom est assez souvent utilisé, par exemple pour définir des séries de graphiques avec des noms dynamiques.

A+

 

[Dranreb]

Re : Probleme calcul sur cellules nommées avec un même nom dans plusieurs classeurs

Bonjour.

Préfixer les variables nommées résout complètement le problème
Evaluate("'" & ThisWorkbook.Name & "'!_var1")

Puisque vous parlez des crochets, que j'utilise beaucoup, il n'y a pas de problème non plus sur d'autres classeurs en faisant Feuil1.[_var1] si Feuil1 est le CodeName de la feuille contenant cette plage nommée. Il n'est pas nécessaire que ce ne soit un nom défini seulement pour la feuille.