Interférence entre une fonction personnalisée et Worksheet_Change

[i0raek]

Bonjour Forum !

Voici mon problème :
j'ai créé une fonction personnalisée que j'utilise dans une feuille soumise à une procédure Worksheet_Change.

Lorsque j'exécute ma procédure en pas à pas, je m'apperçois que celle-ci actualise un paramètre de la fonction ce qui conduit la macro à exécuter son code (de la fonction). Et c'est à ce moment que mon problème apparaît : à la fin du code de la fonction, la macro ne retourne pas dans la procédure Worksheet_Change et celle-ci ne s'exécute donc pas entièrement... 😡

En espérant avoir était suffisament clair... 😱

Est ce que quelqu'un aurait une idée ???

Merci d'avance pour votre aide !

 

[Cousinhub]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Bonjour,
difficile de travailler dans le noir...
peux-tu joindre ton code?

 

[i0raek]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Bonjour,

je suis assez confus, j'ai beau essayer de reproduire, en plus simplifié mon fichier, je n'arrive pas à obtenir la même situation qui génère mon problème... j'ai du, sans m'en rendre compte régler le problème...

Je vais me plonger un peu plus dedans du coup...

Désolé...😱

 

[wilfried_42]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Bonjour à tous


Au debut de ta macro : Worksheet, bloquer les calculs

Application.Calculation=xlCalculationManual

à la fin :

Application.Calculation = xlCalculationAutomatic

pour lancer le calcul

Application.Calculate

 

[i0raek]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Re,

Je suis désolé, je n'arrive pas à reproduire le fichier à problème... et je ne trouve pas ou j'ai "résolu" le problème dans mes essais d'exemples... 😡

Wilfried, ton idée fonctionne à merveille !!!

par contre, le code ne fonctionne qu'avec :

Application.Calculation=xlManual

c'est peut être la version d'excel???...

En tout cas merci beaucoup !!!!

 

[wilfried_42]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

re:

perso, j'ai la version 2002, je n'ai pas regardé avec 2007

 

[i0raek]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Re,

je pensais que le problème était résolu, mais en fait... pas du tout !!! 😡
Et pour faire mieux, il s'est amplifié !

1er point, bien que j'ai changer le mode de calcul en manuel, dès que je le remets en automatique à la fin du code, la fonction prend le pas...

2ème point, dès que je change la valeur d'une cellule quelqu'elle soit dans la feuille liée au Worksheet_change ou même sur une autre feuille d'une nouveau classeur sans lien avec le 1er et sans maccro, la fonction s'active !!! 😱

Ca fait un bon moment que je suis dessus et je ne comprend rien de ce qui se passe !!! c'est comme si au moindre changement d'une cellule dans l'application entière, la fonction s'activait...


Si vous aviez une quelconque idée... merci infiniment d'avance !!!


ps 1 : je n'arrive pas à reproduire un exemple avec ce bug... mais j'y travail...

ps 2 : pour info, voici le code que ma fonction perso :

Function FctProdFor(Plage As Range, Iteration As Integer, Optional RangToStart As Integer = 1) As Variant

    Dim Result As Variant
    Dim VarComptable As Integer
    
    Result = 1
    For VarComptable = RangToStart To Iteration
        Result = Result * (1 + Plage(VarComptable))
    Next

    FctProdFor = Result

End Function

 

[pierrejean]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

bonjour a tous

mon analyse:

la fonction n'y est surement pour rien
par contre , avec une Worksheet_Change on est très vite parti dans une boucle si a l'interieur de cette macro queque chose produit un changement dans la feuille
dans ce cas , je procede de la façon suivante:

Public flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If flag Then Exit Sub
flag = True
'...ci la macro peut effectuer une modification de la page
flag = False
End Sub

 

[i0raek]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Re,

Au risque de paraitre pour un inculte... 😱 je ne comprend pas très bien ton code PierreJean...

Tu déclares la variable Flag en tant que boolean et si elle est à true alors exit sub sinon tu autorise les modification en la mettant à false ?

Que veut dire :

If Flag then exit sub

?

Merci pour ton aide !

 

[pierrejean]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Re
Tentative d'explication:

normalement le flag est False
lors du declenchement de la macro Worksheet_Change, flag devient True
Si la macro produit un changement dans la feuille cette même macro est a nouveau sollicitée (les macros sont recursives)
c'est alors qu'on en sort puisque le flag est True ce qui evite de reediter la modif
en fin de macro on remet flag a false pour autoriser a nouveau l'usage de la macro

pour t'en convaincre vois le fichier ci-joint
change une valeur et observe ce qui se passe pour A1 selon que tu auras gardé ou non les lignes relatives a flag dans la worksheet_change

 

[i0raek]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Bonjour Forum !
Bonjour PierreJean !

Merci pour l'astuce c'est vrai que ça supprime un grand nombre de bugs très déplaisants !!!

Mais j'ai peur que la procédure worksheet_change ne soit pas en cause : en plaçant des points d'arrêts sur l'ensemble du code, on s'aperçoit que, avant même d'exécuter le code de worksheet_change, la fonction est activée et ce n'est que seulement après son exécution que la procédure worksheet_change s'active... 😡

Je n'y comprend plus rien... 🙁

je vais essayer de vous envoyer ce fichier, sinon je pense que ça va être vraiment dur de trouver une solution...

Merci encore pour votre aide !!!

 

[i0raek]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Re bonjour tout le monde !

J'ai enfin trouver la source du problème !!! 😀

mais je ne comprends toujours pas pourquoi il y avait un problème...😕
Enfin bref... donc voici ce que j'ai trouvé :

La cellule qui utilisait la fonction personnalisée contenait la formule suivante :

=SI(OU($E$4=0;BK37=0;BV37=0);0;BK37*FctProdFor($D$48:$W$48;DATEDIF($E$4;DATE(ANNEE(BQ37)+BV37;MOIS(BQ37);JOUR(BQ37));"y")))

Et la cellule BK37 contenait la formule suivante :

=SI(F37=0;0;SOMMEPROD(H37:M37;BB37:BG37)+SOMMEPROD(O37:P37;BI37:BJ37)+DECALER(H37;0;EQUIV(F37;Affectation;0)-1)*BH37)

Pour résoudre mon problème j'ai trouver deux solutions :
-> supprimer la formule de la cellule BK37 mais cette solution n'est pas acceptable...
-> scinder le produit BK37*FctProdFor(...) en :

XX=FctProdFor(...)
YY=BK37*XX

Est ce que quelqu'un comprend pourquoi il y avait problème ???

Pour vous aider, maintenant que j'ai enfin trouver la source voici un fichier d'exemple !!!

Vous pourrez remarquer qu'en remplissant une cellule quelqu'elle soit, la formule s'active avant la procédure worksheet_change !!!

Merci d'avance pour votre aide !!!

 

[pierrejean]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Re

En ce qui me concerne , je suis sous Office 2000 et par consequent je ne peux que lire ton fichier

peux-tu l'enregistrer en .xls ?

de toute façon l'essentiel est que cela fonctionne !!!

 

[i0raek]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Re,

Oui bien sur, je n'avais pas pensais à l'extension...

le voici !

 

[Cousinhub]

Re : Interférence entre une fonction personnalisée et Worksheet_Change

Bonjour,
pour effectuer l'évènement avant le calcul de la fonction, tu peux rajouter une valeur booléenne.
Si cette valeur est à false, tu quittes la fonction, l'évènement se déroule donc, tu mets la valeur à true, puis application.calculate, la fonction se déroule, tu mets la valeur à false en fin de fonction.
Un peu tiré par les cheveux, mais ....