XL 2019 Application.Volatile - Calcul de données et perte des résultats

Merlin258413

XLDnaute Occasionnel
Bonjour à tous
J'ai un fichier qui calcule une sorte de somme.si.ens en VBA avec Pour un critères 1 une couleur et critères 2 un texte. Cette fonction parfaitement.
Je ne sais pas si application.Volatile en est la cause mais au bout d'un certain temps les résultats des calculs disparaissent. Par exemple quand je suis sur synthèse j'effectue une sauvegarde est tout disparait.

Je suis obligé d'aller sur ma feuille de données valider une cellule et revenir dans mon état de synthèse et ne plus rien toucher :(

La structure de mon classeur
Onglet DETAIL PREVISIONS==> Feuille de données
Onglet SYNTHESE REGION ==> Feuille de résultat plage J3 : L12

Voici les résultats qui sont corrects

1582128376107.png

Lecode utilisé pour la fonction qui donne en J3 =SomCoul('DETAIL PREVISIONS'!$E$3:$E$1994;J$2;$A3)

VB:
Public Function SomCoul(p As Range, c As Range, val As String)
Dim coul As Long, s, Cel
Application.Volatile
coul = c.Interior.ColorIndex
s = 0
For Each c In p
  If c.Interior.ColorIndex = coul And val Like Cells(c.Row + 1, (c.Column) - 4) Then s = s + c.Value
Next c
SomCoul = s
End Function

Pouvez-vous m'aider afin que que je ne passe pas mon temps à balader entre la synthese aller dans le détail valider retourner à la synthese pour voir les résultats
En vous remerciant pour votre aide
 

Pièces jointes

  • PREVISION FACTURATION 2020 02 test.xlsm
    38.9 KB · Affichages: 8
Dernière édition:

Dudu2

XLDnaute Barbatruc
Bonjour,

La 1ère question est pourquoi Application.Volatile ?
Les paramètres de la fonction ne sont pas les seuls éléments qui conditionnent le recalcul ?
Déjà qu'Excel lance le recalcul des fonctions personnalisées un nombre sur-nécessaire de fois !

La 2ème: For Each c In p
-c- est un argument ByRef (par défaut d'avoir indiqué ByVal).
Je me demande l'effet que ça produit en amont de modifier le Range passé en paramètre.
Pourquoi ne définis-tu pas une variable fonction Dim cc as Range pour cette boucle ?
 

Merlin258413

XLDnaute Occasionnel
Bonjour Dudu2,
En fait j'aimerai tellement vous suivre mais je suis novice et il me manque le savoir faire. A l'aide d'une personne du forum je cherchais à faire une sorte de somme.si.ens avec 1 critère couleur et un 2ème texte. je crois qu'on y a passé l'après midi o_O

je veux trouver une formule qui me donne dans mon tableau Onglet SYNTHESE REGION ==> Feuille de résultat plage J3 : L12 les résultats suivants
Exemple la cellule J3 la somme de valeurs en rouge dans la colonne E3:E500 Onglet DETAIL PREVISIONS pour l'agence AKFTVI à recopier vers lebas

En K3 la meme chose mais pour la couleur figurant en K2

La structure de mon classeur
Onglet DETAIL PREVISIONS==> Feuille de données
* Ma plage serait de la cellule E3:E500


Onglet SYNTHESE REGION ==> Feuille de résultat plage J3 : L12

MAis je viens de me rendre compte que le code de la fonction doit être éronné
Je ne sais pas pourquoi mais la somme ne s'effectue pas correctement par exemple cellule en rouge
J9 ce montant devrait remonter sur l'agence AKFARB et J12 aussi
De plus dans détail j'ai des sommes qui ne remontent pas
1582133390695.png


De plus dès que je fais une sauvegarde ou une modification sur Synthèse je dois faire un aller retour sur DETAIL valider une cellule pour voir apparaître mes résultats pour certains faux


Pouvez-vous m'aider svp?
 

Pièces jointes

  • PREVISION FACTURATION 2020 02 test2.xlsm
    35.4 KB · Affichages: 8

Dudu2

XLDnaute Barbatruc
Que veut dire "Exemple la cellule J3 la somme de valeurs en rouge dans la colonne E3:E500 Onglet DETAIL PREVISIONS pour l'agence AKFTVI à recopier vers lebas" ?
Je ne comprends pas ce que représente "recopier une somme vers le bas".

Le rouge c'est quoi ? Juste une valeur de Interior.ColorIndex ou une valeur de Interior.Color ?
Où se trouve la référence de cette valeur dans la feuille ? (ex. le rouge de E2 <> rouge de E13).
 

Dranreb

XLDnaute Barbatruc
Bonsoir.
Je pense que Application.Volatile n'est pas suffisant, ni du coup utile, pour que la fonction soit réévaluée lorsqu'on change la couleur de fond d'une cellule qui lui est spécifiée en paramètre.
Il faut faire comme j'ai fait pour la colonne Échantillon de ma ressource : s'engager dans une boucle qui surveille sans arrêt l'Interior.Color de la cellue.
Cela dit il est possible que les résultats disparaissent lorsque la fonction est réévaluée à l'occasion d'autre calculs parce que la feuille active n'est plus celle qui doit être considérée au Cells(c.Row + 1, (c.Column) - 4), où elle n'est pas précisée. Vous feriez mieux de prendre c.Offset(1, -4).Value
 

Dudu2

XLDnaute Barbatruc
Bonjour,
En effet, le changement de couleur de fond ne provoque pas de recalcul des fonctions personnalisées qui font référence à la cellule modifiée en paramètre ou qui utilisent Application.Volatile.

Une fonction de surveillance des couleurs sur un Application.OnTime relancé toutes les 2 ou 3 secondes peut en effet jouer sur la modification d'une cellule prédéfinie qui déclenchera le recalcul des fonctions personnalisées.

Il serait quand même beaucoup plus simple d'ajouter un indicateur en regard des cellules à coloriser et de coloriser avec une MFC. Une simple formule (SOMMEPROD ou SOMME.SI.ENS...à voir) serait alors suffisante.
 

Merlin258413

XLDnaute Occasionnel
Que veut dire "Exemple la cellule J3 la somme de valeurs en rouge dans la colonne E3:E500 Onglet DETAIL PREVISIONS pour l'agence AKFTVI à recopier vers lebas" ?
Je ne comprends pas ce que représente "recopier une somme vers le bas".

Le rouge c'est quoi ? Juste une valeur de Interior.ColorIndex ou une valeur de Interior.Color ?
Où se trouve la référence de cette valeur dans la feuille ? (ex. le rouge de E2 <> rouge de E13).
Bonjour quand je dis recopier vers la bas c'est une erreur d'expression de ma part c'est à dire que je passe à la prochaine agence avec toujours le critère rouge

MErci
 

Dudu2

XLDnaute Barbatruc
Est-il possible d'ajouter une colonne "Code Couleur" qui prendrait des valeurs différentes (1, 2, 3, ... ou R, J, O, ...) peu importe. Des MFC mettraient alors la bonne couleur selon le code dans "Estimation BM" et "Estimation Corrigée CG". Et le code serait utilisable dans une formule.

2020-02-20_092654.jpg


Edit: d'ailleurs un code couleur choisi dans une liste de validation est beaucoup plus sûr que la colorisation directe dont le choix de couleur est source d'erreurs
 
Dernière édition:

Merlin258413

XLDnaute Occasionnel
Bonjour à tous j'ai effectué la modification proposée par Dranbeb qui fonctionne et résout totalement la disparition de l'affichage il me reste donc le problème des calcul qui pour certaines agences ne se fait pas correctement

1- Ce que j'obtiens
1582187653847.png


2- le résultat attendu je comprends pas pourquoi le calcul sur certaines agences se fait correctement et d'autres pas

1582187754214.png


En vous remerciant
 

Pièces jointes

  • PREVISION FACTURATION 2020 02 test2.xlsm
    35.5 KB · Affichages: 5

Dranreb

XLDnaute Barbatruc
Il y a moyen de ruser pour qu'une fonction perso ait tout à fait l'air de renvoyer une couleur de fond pour la cellule qui la porte. Mais c'est pareil, elle ne peut sembler le faire qu'à condition qu'elle soit réévaluée, donc par changement de valeur de son ou ses arguments. Bien entendu elle ne change aucun format, puisqu'aucune modification de quoi que ce soit n'est possible dans le classeur pendant l'évaluation d'une formule, laquelle ne peut renvoyer qu'une valeur. Le principe c'est de lui faire ajouter dans une Collection VBA des infos constituant une consigne de changement de cette couleur. Cette Collection est ensuite épuisée par une Sub Worksheet_Calculate qui applique les consignes.
 

Merlin258413

XLDnaute Occasionnel
Il y a moyen de ruser pour qu'une fonction perso ait tout à fait l'air de renvoyer une couleur de fond pour la cellule qui la porte. Mais c'est pareil, elle ne peut sembler le faire qu'à condition qu'elle soit réévaluée, donc par changement de valeur de son ou ses arguments. Bien entendu elle ne change aucun format, puisqu'aucune modification de quoi que ce soit n'est possible dans le classeur pendant l'évaluation d'une formule, laquelle ne peut renvoyer qu'une valeur. Le principe c'est de lui faire ajouter dans une Collection VBA des infos constituant une consigne de changement de cette couleur. Cette Collection est ensuite épuisée par une Sub Worksheet_Calculate qui applique les consignes.
Bonjour merci ce complément d'informations
Excellent we
 

Merlin258413

XLDnaute Occasionnel
Vous feriez mieux de prendre c.Offset(0, -4).Value

Et un code couleur (post #8) pour simplifier ce système ingérable au niveau du recalcul sauf, comme indiqué avant, à introduire un système de scan permanent des couleurs ?
Bonjour c'est ce que j'ai fait. Il est vrai que votre solution est plus simple et c'était une solution de repli. Je pouvais aussi trouver une codification texte par CDE pour commande et faire simplement un somme.si.ens.
Très bon wee
 

Discussions similaires

Statistiques des forums

Discussions
311 709
Messages
2 081 779
Membres
101 816
dernier inscrit
Jfrcs