Problème avec fonction personnalisée

Bonjour à tous,

J'ai récupéré une fonction personnalisée sur la toile pour calculer les cellules en fonction des couleurs de fonds....

Function SommeCouleurFondTexte2(champ As Range)

Le problème qui se pose, c'est que lorsque que je suis sur un autre classeur et que le classeur qui contient cette fonction est ouvert, Excel calcule à chaque modification de cellule et fausse mes résultats sur le classeur où est la fonction... Je suis obligé de retourner sur le classeur en question et faire F9 pour remettre à jour

Il y a peut-être une ligne à rajouter ou à modifier

Merci d'avance pour votre aide

Bonjour

si Application.caller envoi un range pourquoi ne pas l'utiliser directement ..? du genre :

couleurFond = Application.Caller.Interior.ColorIndex

Bonsoir,

Je ne comprends pas bien où serait ton problème !

La fonction calcule une somme dans le champ qui lui est fourni en argument, pas ailleurs.

Elle fait la somme des valeurs des cellules qui ont même couleur de fond et même couleur de texte qu'elle-même.

Si les valeurs des cellules du champ ne sont pas modifiées, si leur couleur de fond ou de texte n'est pas modifiée, si le champ n'est pas modifié dans la formule, et si la couleur de fond ou de texte de la cellule qui contient la formule n'est pas non plus modifiée, je ne vois pas ce qui pourrait modifier le résultat !

A plus forte raison à partir d'un autre classeur !!

Cordialement.

Bonjour,

Le fichier illustrera mieux mes désirs et problèmes...

Je souhaite faire un planning sur Excel, une personne sera chargé de planifier (rentrer les chiffres) en fonction d'une capacité...

Les opérateurs ne pourront changer que les couleurs de fond en fonction de l'avancement...

"Rose" pour la prépa

"Vert" pour l'emballage

"Bleu" commande expédiée

Une fois les cellules colorées, ils devront faire F9 pour calculer et ainsi mettre les chiffres à jour.

Ce fichier sera mis sur un commun et sera partagé, je saurai donc en temps "réel" l'avancement du planning...

La fonction fonctionne pas mal mais lorsque nous sommes sur un autre classeur où l'on modifie des cellules (valeur, ...) et que ce dernier est ouvert, Excel calcule est modifie les valeurs sur mon classeur où la fonction se trouve.

PS: j'utilise Office 2013

Si vous avez une astuce, je suis preneur...

Je maintiens ce que j'ai dit : sans changement dans les cellules pointées ou la cellule appelante (puisqu'elle intervient) le résultat ne peut pas changer ! C'est une chasse aux fantômes que tu lances !

Par contre, si tu t'en sers pour additionner des cellules définies individuellement, ce n'est pas une utilisation très optimale. Elle est conçue pour que tu lui fournisse un champ complet (toute la colonne par exemple) à additinner, dans lequel elle ne va prélever que les cellules dont les couleurs correspondent pour fournir le résultat.

Cordialement.

Bonjour,

si si, je confirme ce que dit bobbyte. Vba appelle un peu n'importe quand les fonctions personnalisées, même d'un autre classeur qui n'a rien à voir. Même sur une fonction non volatile, l'horreur quoi.

Sert-toi de ThisCell (cellule d'appel où est inscrite la fonction) pour faire des exit function

'Nom classeur :
    Application.ThisCell.Parent.Parent.Name
'Nom feuille :
    Application.ThisCell.Parent.Name
'Adresse cellule d'appel :
    Application.ThisCell.Address 

ThisCell étant un Range tu peux combiner avec des Intersect si tu veux contrôler qu'elle ne soit active que sur une plage précise mais déjà avec le contrôle du classeur tu élimines ton pb.

eric

Bonjour, eriiic !

Tu m'épates là ! Je n'ai jamais constaté ça !

A la réflexion cela peut se comprendre, dans la mesure où si un recalcul se fait qu'il s'opére, avec la définition habituelle des plages dans les fonctions, que l'interprétation de VBA soit : plage de la feuille active du classeur actif.

Dans ce cas il devrait suffire d'inclure le nom de feuille pour ce type de fonction qui ne se recalcule pas spontanément.

Eventuellement, on peut aussi mettre une procédure Activate pour recalculer à chaque activation de la feuille.

Pour l'instant le seul élément qui m'avait interrogé en matière de fonction personnalisée est qu'on la déclare volatile ou pas (implicitement = True), elle était par défaut Volatile (note que c'est écrit dans l'aide...) et que donc il est plus important de mettre Volatile à False pour éviter le recalcul que l'inverse.

Je vais tenter de tester la chose.

elle était par défaut Volatile (note que c'est écrit dans l'aide...)

Et bien là tu m'apprends quelque chose, je n'avais jamais prêté attention à ça tellement il me paraissait évident que ce soit l'inverse.

Au moins ça explique complètement les recalculs que pensais être faits à tort.

Je retire donc le 'même sur fonctions non volatiles' (pas vraiment le temps de tester en réel mais ça colle si bien aux constats et ça parait si évident maintenant)

Note pour plus tard : ne jamais lire l'aide en diagonale

eric

edit : finalement j'ai un doute sur l'aide...

Si je fais une fonction toute simple avec un Stop dedans elle est appelée sur le 1er F9, et plus du tout sur les autres F9.

Avec Application.Volatile=true elle est bien appelée sur chaque F9 (!?!)

Il semblerait que la valeur par défaut soit plutôt False

J'avais eu le problème avec des fonctions destinées à des tirages aléatoires. Si ça bougeait sans arrêt, c'était inexploitable...

J'ai tourné longtemps persuadé aussi que c'était False par défaut; mais...

Oui, je l'avais bien vu dans l'aide. J'en étais même très content

Mais ça n'a pas l'air de cadrer avec les tests...

Bonsoir,

je pense qu'en même qu'il y as méprise dans la lecture de l'aide...

un fonction personnalisée est par défaut "NON volatile" ...

c'est l'appel de la méthode Application.Volatile qui prends comme premier paramètre true par défaut .... donc si l'on n'appelle pas la méthode en passant la ligne en commentaire la fonction n'est pas volatile...

mais on sort du sujet de bobbyte qui appelle cette méthode et crée donc une fonction volatile..

@bobbyte as tu essayé d'utiliser Application.Caller comme un range, voir le code que je t'ai proposé plus haut ... où la méthode avec ThisCell d'erric ?

Aah! On va repartir pour de nouvelles batteries de tests...

Premier lot : avec Volatile False ça ne bouge pas...

Mais un élément qui peut être trompeur : lorsqu'on modifie la fonction, elle se recalcule une fois comme si on venait de l'insérer.

Par contre avec rien, ça ne bouge pas non plus ! Ce qui effectivement laisse penser que la valeur par défaut est False.

Il faudra que je retrouve celle qui m'avait fait galérer, mais impossible au pied levé...

Bien vu pour la leçon de lecture pierre.jy

On va y arriver...

Pour revenir à nos mouton.

Déjà pour moi couleurFond = Range(Application.Caller.Address).Interior.ColorIndex n'est pas bon.

Vue que la fonction est volatile, Range s'adressera à la feuille active à ce moment là et on aura la couleur même référence sur cette autre feuille. Ca explique les résultats incohérents.

Avec

    couleurFond = Application.Caller.Interior.ColorIndex
ou
    couleurFond = Application.ThisCell.Interior.ColorIndex

ou récupère bien la couleur de la bonne cellule.

(j'ai une préférence pour ma formulation car on lit le type d'objet)

Si les couleurs sont mises manuellement la feuille est active, on peut éviter de boucler pour rien (vu qu'elle sera évaluée à chaque calculate le gain peut être appréciable surtout si on travaille sur un autre classeur qui n'a rien à voir) :

If Not (Application.ThisCell.Parent Is ActiveSheet) Then Exit Function

Ca règle aussi le 1er problème mais tant qu'à faire autant que ce soit propre.

Si le 'champ' testé est sur une autre feuille que la fonction personnalisée il faudrait tester en listant explicitement la feuille du champ pour la laisser passer mais la fonction sera moins généraliste.

Au moins ce topic m'aura permis de prendre le temps d'approfondir et de comprendre (j'espère )un pb que j'avais déjà rencontré.

eric

edit: pas bon pour le point 2, il faut la laisser recalculer à chaque fois sinon =0 :-s

Re à tous,

OK pour l'interprétation de Pierre-jy que je n'avais pas vu. On est obligé de s'y ranger de toute façon puisque c'est ce qu'on constate

Ceci étant, si les valeurs changent spontanément, le problème n'est pas au niveau de Caller remplacé ou non par ThisCell qui ne réfère qu'à la cellule dans laquelle se trouve la fonction.

Exemple : si je crée une fonction renvoyant la valeur de Application.ThisCell.Address ou également Application.Caller.Address,

Elle m'affiche l'addresse de la cellule, et celle-ci ne variera pas quoique je fasse par ailleurs.

Si le résultat se modifie, c'est que la formule calcule alors sur une autre plage que la plage désignée.

qui ne réfère qu'à la cellule dans laquelle se trouve la fonction.

C'est justement là que se situe la différence et tout l'intérêt.

La couleur à comptabiliser est celle mise sur la cellule de la fonction.

Admettons que la fonction soit en Feuil1!B2, couleur=6

Tu es en Feuil2 (même classeur ou un autre) et tu fais une saisie, la fonction volatile est exécutée.

Avec

couleurFond = Range(Application.Caller.Address).Interior.ColorIndex

tu récupères la couleur de Range($B$2) mais de la la feuille active Feuil2 puisqu'elle n'est pas désignée, soit peut-être =-4162 et ton résultat en Feuil1!B2 devient faux. C'est son pb

Avec

couleurFond = Application.ThisCell.Interior.ColorIndex
'ou
couleurFond = Application.Caller.Interior.ColorIndex[

tu récupères bien la couleur de Feuil1!B2, soit =6, même si c'est ta feuil2 qui est active.

Et si la couleur était à récupérer sur une autre cellule que la fonction mais même feuille il faudrait utiliser With Application.ThisCell.Parent

Et si c'était sur une autre feuille à part la désigner explicitement je ne vois pas.

Tu es en Feuil2 (même classeur ou un autre) et tu fais une saisie, la fonction volatile est exécutée.

Avec Code: Tout sélectionner

couleurFond = Range(Application.Caller.Address).Interior.ColorIndex

tu récupères la couleur de Range($B$2) mais de la la feuille active Feuil2 , soit peut-être =-4162 et ton résultat en Feuil1!B2 devient faux. C'est son pb

Non, je conserve bien toujours la couleur de Feuil1.

[quote]Avec Code: Tout sélectionner

couleurFond = Application.ThisCell.Interior.ColorIndex

'ou

couleurFond = Application.Caller.Interior.ColorIndex[

tu récupères bien la couleur de Feuil1!B2, soit =6, même si c'est ta feuil2 qui est active.[/quote]

Même résultat bien sûr.

Formulation plus élégante et plus sûre.

Dans tous les cas je n'ai de toute façon aucune variation, la bonne couleur reste affichée.

Pourtant je pense que toutes les versions réagissent pareil.

Teste ce fichier pour voir.

Je pondère : j'obtiens en effet des cas ou la valeur change, pas systématiquement.

Mais effectivement cela survient.

eriiic, j'avais pas vu ton post intermédiaire.

J'ai testé en faisant renvoyer la valeur de la couleur par des fonctions.

Tests de base, je n'ai pas observé de changement.

En couplant avec un MsgBox, cela me fait varier la couleur, qui ne revient pas ensuite automatiquement !

En passant les MsgBox par des évènementielles :

-évènementielle dans Feuil1 : j'ai parfois un changement (pas apprécié la fréquence) [le msg ne s'affiche pas puisque je suis sur Feuil2]

-évènementielle dans Feuil2 : VBA n'en veut pas et m'affiche une erreur 9 systématique, mais il arrive que la couleur change, en gros une fois sur 10

En prélevant la valeur par formule : pas de changement observé.

C'est donc au moins instable. L'utilisation directe de l'objet Range devrait donc en principe résoudre la question.

Bonsoir,

MFerrand a écrit :

C'est donc au moins instable. L'utilisation directe de l'objet Range devrait donc en principe résoudre la question.

Ok , on en revient donc à mon premier message dans cette discussion