XL 2019 Changer le format de cellules à partir d'une fonction personnalisée

[p'tit vieux]

Bonjour à tous,
je reviens vers vous pour trouver une solution.
j'ai fait une fonction personnalisée qui écrit des résultats là ou je veux (merci pour vos infos qui m'ont bien aidées pour arriver à mes fins )
Voilà le souci:
Mon UDF écris les résultats des calculs dans des Ranges. Jusque là c'est bon mais je désire pouvoir changer le format des nombres.
Exemple:
Si on demande des pourcentages je voudrais soit faire un NumericFormat "0.00%" ou un Style = "Percent" sur ces Ranges.
Ben non ca marche pas!
Alors que le code:

 SourceRange.Parent.Evaluate "EcrireRange(" & MonRangeTo & ")"

Lui fonctionne nickel !!
Mais impossible de changer le format avec cette méthode.

 'Appel
 SourceRange.Parent.Evaluate "FormatRange(" & RangeToFormat & "," & "0.00%" & ")"
 
 'Procédure
 Sub FormatRange(aRange as Range, aFormat as string)
     aRange.NumericFormat = "aFormat"
 end sub

Une idée du pourquoi et surtout du comment faire?
Car renvoyer des pourcentages sans le formater c'est moyen, non?
Et, cerise sur le gâteau si vos idées pouvaient aussi marcher pour, par exemple, la couleur ou la fonte. Je prévois pour le futur.
Le P'tit Vieux vous remercie (encore) pour ce coup de main.

 

[p'tit vieux]

Bonjour,

c'était juste une remarque personnelle sur mes préférences.
Ma dernière proposition était bien l'adaptation pour que ça fonctionne dans xlam comme tu le souhaites.
C'est court et fonctionnel, tu peux tester...
eric

Bonjour Eric
je suis en plein debug là sur un autre développement.
Mais ok en début d'A.M. je fais ça.

 

[p'tit vieux]

Bonjour,

c'était juste une remarque personnelle sur mes préférences.
Ma dernière proposition était bien l'adaptation pour que ça fonctionne dans xlam comme tu le souhaites.
C'est court et fonctionnel, tu peux tester...
eric

Re,
je viens de tester.
J'ai modifié la fonction pour écrire dans la cellule déportée. Ce qui est indispensable pour moi.
Les problèmes sont:
- l'événement ne se déclenche pas ou pas toujours.
- La prise en compte de la cellule "déportée" n'est pas prise en compte.
Pour mieux comprendre ce que j'essaie de dire merci de regardez les fichiers ci-joint.
J'ai peut être loupé un truc.
Amicalement

 

[eriiic]

J'ai toujours beaucoup de mal à tester tes fichiers.
Si j'ouvre ton xlam depuis un rep quelconque soit il ne s'ouvre pas, soit il fige excel.
Obligé de le mettre dans le rep Addins, et même là il ne s'ouvre pas automatiquement, obligé d'aller l'ouvrir...

D'autre part, tu ne testes pas ma version, mais ta version.
Tu ajoutes entre autres un CopyRange.... qui n'existe que chez toi et donc plante chez moi, alors que Target suffit pour moi.

- l'événement ne se déclenche pas ou pas toujours
Le Change se déclenche toujours. Par contre la mise à jour du format ne se fait que si nécessaire : une modification des antécédents de la fonction supervisée.

Je ne sais pas ce qu'est une 'cellule déportée'. C'est ton vocabulaire perso.

Donc teste déjà sans rien toucher et je pourrais peut-être répondre aux manques.
eric

 

[p'tit vieux]

Re à tous
@eriiic @Dranreb
re à tous 2
Je pense avoir trouvé une solution.
Suite aux dernières remarques de Dranreb et à la version de Eric j'ai pu constater cela:
1) Eric propose d'utiliser l'évènement _SheetChange(ByVal Sh As Object, ByVal Target As Range)
2) Dranreb lui propose l'événement _SheetCalculate(ByVal Sh As Object)
Pour ce que j'ai vu les remarques de Dranreb plus haut sont exacts. Ca peut gêner.
Mais la solution d'Eric fournit le paramètre Target As Range qui pourrait être utile.

Donc je propose de vous mettre d'accord en faisant ceci:
Dans le ThisWorkbook mettre la déclaration:

Dim FlagChange As Boolean

puis ici

Private Sub WthEvnXL_SheetCalculate(ByVal Sh As Object)
    FlagChange = True
End Sub

Et enfin déplacer le code de Dranreb (celui de mon dernier fichier ConsgnV2.xlam) ici

Private Sub WthEvnXL_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If FlagChange = False Then Exit Sub
' Ajouter ici le code à déplacer
' .../...
    FlagChange = False
End Sub

Ainsi d'après mes tests j'ai l'avantage des 2 sans (à priori) les inconvénients
Qu'en pensez vous?

PS:
Désolé Eric, j'aurais du y penser avant de te donner une réponse

 

[p'tit vieux]

Si j'ouvre ton xlam depuis un rep quelconque soit il ne s'ouvre pas, soit il fige excel.
Obligé de le mettre dans le rep Addins, et même là il ne s'ouvre pas automatiquement, obligé d'aller l'ouvrir...

AIE!
Bon sauf erreur un fichier xlam "ne s'ouvre pas" comme autre fichier Excel (macro ou pas).
Pour y avoir accés "correctement" on doit:
1) ouvrir une feuille classique
2) Aller dans le menu complément pour y référencer le fichier xlam que l'on veut tester
Une fois fait après un magique [Alt]+[F11] on a accés au code de celui-ci et faire tout ce que l'on veut.

Quand vous avez fini vos tests n'oubliez pas de décocher le fichier xlam dans le menu complément pour le déréférencer. Sinon vous risquez d'en avoir un paquet.

D'autre part, tu ne testes pas ma version, mais ta version.
Tu ajoutes entre autres un CopyRange.... qui n'existe que chez toi et donc plante chez moi, alors que Target suffit pour moi.

OK mais l'origine de ma question était justement de pouvoir formater ces cellules du "CopyRange" sinon ce ne serait pas drôle.
Oui votre solution pour "du standard" fonctionne. Pas de souci.

Je ne sais pas ce qu'est une 'cellule déportée'. C'est ton vocabulaire perso.

Oui.
Explication:
Article 1: Excel n'autorise pas d'écrire dans des cellules en dehors de cellule de la fonction. Enfin normalement
Rappel: Excel considère toutes les cellules d'une fonction matricielle comme faisant partie de "sa cellule fonction" donc pas de problème d'écriture.
Mais comme je n'aime pas les articles 1 et qu'en lisant les suivants loin, comme dans la vie, il y a toujours des exceptions ...
Exemple: En A1 j'écris "=Somme(A2:A3) le résultat ne peut-être qu'en A1.
Dans mon vocabulaire si je veux le résultat en D10 au lieu de A1 alors D10 est ce j'appelle la cellule déportée.
Car non autorisée et ... décalée.
Et de là est venu mon problème de format de ces cellules ... que Dranreb et vous avez brillamment résolu.

Mes explications sont elles correctes?
Suis je mieux compris?

PS:
Pour les événements, désolé, cela venait de moi.

 

[Dranreb]

Bonjour.
Si vous voulez le résultat en D10 au lieu de A1, pourquoi n'invoquez vous pas la fonction en D10 au lieu de A1 ?

 

[p'tit vieux]

Bonjour.
Si vous voulez le résultat en D10 au lieu de A1, pourquoi n'invoquez vous pas la fonction en D10 au lieu de A1 ?

Ceci n'était qu'un exemple.
Voici la capture qui vous sera plus parlante quand à la finalité du formatage (et pour Eric le décalage)
La zone verte est la matrice source des justes 2 calculs à titre d'exemple.
Le "Check rows sums" est juste là pour montrer les résultats en utilisant les fonctions d'Excel.

Donc dans la fonction, il y a juste à sélectionner la matrice et indiquer le calcul à réaliser.
autre capture:

Ou cela si l'utilisateur préfère (vraiment) garder les résultats collés à la cellule de la fonction

Cela vous semble t'il plus clair.
Tout commentaire(s) ou idée(s) sont les biens venus … mais pas des trop difficiles hein!

 

[Dranreb]

Non, ce n'est pas du tout plus clair. Je n'y vois même plus de rapport avec le thème de la discussion.

 

[p'tit vieux]

Non, ce n'est pas du tout plus clair. Je n'y vois même plus de rapport avec le thème de la discussion.

Décidément je vieillis dur dur. Je n'arrive même plus à me faire comprendre
(Ne vieillit pas Dranreb. C'est dur ! )
Pour exemple, je désire mettre la colonne et la ligne au format %. Ce que je n'arrivais pas à faire.
D'où ma question première.
En 2éme étape, pour les autres résultats des calculs, je voudrai pouvoir mettre le format en conformité avec le format des données de la matrice. Par exemple le signe $ ou € etc.

 

[Dranreb]

Pourquoi ne pouvez vous pas figer les formats, au fond ?
En tout cas dans une des proposition je transmis dans la collection le String à mettre comme NumberFormat pour que le dispositif puisse être utilisé dans plusieurs fonctions perso différentes.

 

[eriiic]

Pour te répondre, sur mon excel je peux très bien ouvrir un xlam situé n'importe où, il s'installe et est parfaitement fonctionnel sans que je ne fasse rien de plus.

Les instabilités, c'est toi qui les crées en bidouillant les propositions qu'on te fait.
Pourquoi vouloir faire un mix alors qu'elles se suffisent à elles-même ?
Teste les 2 sans essayer de faire des chimères

Sur un post tu précisais fonction NON matricielle, et là je comprend que tu pars sur une matricielle (?!?)
Ma proposition est non matricielle sans adaptation.

Un point que tu ne sembles ne pas connaitre et qui t'a sans doute perturbé :
J'utilise une méthode Application. Si tu édites le code, ces méthodes nécessitent de relancer Workbook_Open() pour que vba récupère l'application Excel à nouveau (automatique à l'ouverture).

Et je rejoins Dranreb. C'est quoi qui empêche l'utilisateur de mettre le bon format lorsqu'il construit son tableau ??

 

[p'tit vieux]

Pourquoi ne pouvez vous pas figer les formats, au fond ?
En tout cas dans une des proposition je transmis dans la collection le String à mettre comme NumberFormat pour que le dispositif puisse être utilisé dans plusieurs fonctions perso différentes.

Ben oui. Votre proposition fonctionne grâce à la collection. Pas de problème.
Là c'est moi qui ne comprend plus vôtre remarque puisque c'est ce que je dis depuis au moins 2 ou 3 messages. J'ai même dis ... Super votre proposition fonctionne.
A propos de ce que dit Éric, Danreb avez vous noté une instabilité dans le xlam?

 

[Dranreb]

C'est bizarre de détourner le dispositif pour qu'il se mette à changer une autre cellule que celle invoquant une fonction s'arrangeant pour faire changer son seul propre format, c'est tout. Une autre cellule n'ayant rien à voir avec ça pourait à la rigueur faire de même pour elle même par une autre fonction utilisant le même dispositif, si vraiment on ne peut pas faire autrement, ce qui est quand même étonnant.

 

[p'tit vieux]

C'est bizarre de détourner le dispositif pour qu'il se mette à changer une autre cellule que celle invoquant une fonction s'arrangeant pour faire changer son seul propre format, c'est tout. Une autre cellule n'ayant rien à voir avec ça pourait à la rigueur faire de même pour elle même par une autre fonction utilisant le même dispositif, si vraiment on ne peut pas faire autrement, ce qui est quand même étonnant.

Bizarre pas vraiment.
Lorsque l'on vous demande de développer un système automatique (en une fonction) dans Excel qui écrit des résultats simultanément "à 90°" comme dans les captures d'écran ci-dessus je n'ai pas trouvé d'autre solution vu les restrictions techniques imposées par Excel.
Par contre, Oui, vous avez raison cela demande énormément de précaution pour envisager "tous" les cas de figures qui pourraient provoquer des "accidents" genre:
- écrasement de données existantes,
- conflits,
- ouvertures de plusieurs classeurs utilisant le même xlam en même temps.
Etc.
En résumé la partie calcul n'est pas la plus difficile. C'est de tenter de penser à tous.
Enfin si, il y a toujours la possibilité de répondre que ce n'est pas possible.
Ça arrive ... Régulièrement.
Tout n'est pas possible n'est ce pas.
Mais il faut quand même essayer avant de donner une réponse définitive.

 

[p'tit vieux]

Bonjour à tous
@Dranreb et @eriiic
Merci beaucoup à vous pour votre aide sur ce sujet.
J'ai implanté le mécanisme de formatage dans mon développement.
A ce stade, malheureusement, au fil des tests j'ai constaté qu'il y a des instabilités et "effets de bord" qui entraineraient trop de temps (de boulot).
Ce n'est pas le seul problème, mais à titre, pour les curieux, juste un exemple.
Cela se produit lorsque l'on ouvre plusieurs classeurs utilisant les mêmes fonctions (et donc le même (nom) événement). Il est peut être possible de le contrôler et à mon avis ce n'est pas une fonctionnalité prioritaire
(De plus je n'ai plus le temps … Peut-être à mes moments perdus).
Donc, pour l'instant, j'abandonne. La mise au format sera manuelle.

Et puis, zut, "Faut pas pousser pépère dans les orties"

Donc encore merci pour votre implication et votre patience.
Au prochain sujet
Bonne journée à tous