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

[Merlin258413]

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

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

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

 

[Dudu2]

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]

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

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

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?

 

[Dudu2]

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]

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]

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]

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]

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.

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

 

[Merlin258413]

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

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

En vous remerciant

 

[Ikito]

Post #10 :

XL 2019 - Somme.si.ens avec 1 critère couleur et 1 critère texte

Bonjour à tous je viens solliciter votre aide car je suis à la recherche d'une fonction je pense par VBA qui me permettrait de faire un somme.si.ens avec 1 critère couleur et un 2ème texte. Si je reviens dans le concret j'ai plusieurs agences qui doivent réaliser un certain objectif de...

www.excel-downloads.com

 

[Dudu2]

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 ?

 

[Dranreb]

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]

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]

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