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]

@Dranreb
Re
Voici un fichier sur le principe qui je l'espère te sera plus explicite sur ce que je fais dans mon UDF.
Comme je l'ai dit dans mon UDF (le vrai) tout fonctionne SAUF ce de formatage des cellules de destination recevant un tableau de résultat.
Le fichier est un xlsm afin que déjà vous puissiez voir ce qu'il en est.
Donc à convertir en xlam pour que ce soit un "vrai" UDF.
Si besoin, je reste à votre disposition pour d'autres infos.

 

[Dranreb]

Votre classeur équipé de ma suggestion.

 

[eriiic]

Votre solution fonctionne mais :

teste le classeur que j'ai joint.
Ajoute 2 valeurs, la formule et tu verras.
Normalement aucune de tes remarques ne tient.
eric

 

[p'tit vieux]

Votre classeur équipé de ma suggestion.

Bonsoir
@Dranreb
J'ai commencé à regarder la piste indiquée sur vos commentaires.
Y a de l'idée mais faut que je creuse car lorsque je passe votre fichier XML en xalm (IsAddIn=True) l'événement ne se produit plus.
Donc pas d'action. Sinon en tant que code dans la feuille ça marche bien.
Donc je vais tenter cette expérience.
Essayer "d'encapsuler" la feuille active du classeur de l'utilisateur dans une classe déclarée avec WithEvent dans le Workbook de L'UDF. Voir si les événements "xxxChange" ou autre de la feuille active sont bien "remontés" dans le Workbook OU un UserForm (Comme PatrickToulon le suggérait) pour y mettre ton code.
Vous voyez le principe du truc?
On va voir ce que ça peut donner. (Au moins pour le fun.)
Qui a dit que c'est tordu?
Je vais voir celademain et je envoie le résultat des essais.

 

[p'tit vieux]

teste le classeur que j'ai joint.
Ajoute 2 valeurs, la formule et tu verras.
Normalement aucune de tes remarques ne tient.
eric

Oui ça marche tant que c'est un classeur avec les fonctions.
Mais lorsque je le convertis en fichier UDF chez moi ça ne marchait plus.
Peut-être n'ai-je pas tout compris ou loupé un truc C'est possible ... à mon âge On a perdu la main quand on arrête si longtemps (15 ans)
Demain je vais revoir votre fichier.

 

[Dranreb]

Vous voyez le principe du truc?

Oui. Vous pouvez peut être aussi travailler avec un WithEvents As Excel.Application qui propose pratiquement les mêmes évènements que l'objet Workbook sans pour autant être un objet implanté par l'application hôte.

Option Explicit
Private ClnConsgn As New Collection, WithEvents WthEvnXL As Excel.Application
Public Sub AjoutConsigne(ByVal Quoi)
   If WthEvnXL Is Nothing Then Set WthEvnXL = Application
   ClnConsgn.Add Quoi
   End Sub
Private Sub WthEvnXL_SheetCalculate(ByVal Sh As Object)
   Dim T()
   While ClnConsgn.Count > 0
      T = ClnConsgn(1)
      ClnConsgn.Remove 1
      T(0).NumberFormat = T(1)
      Wend
   End Sub

 

[p'tit vieux]

Bonjour tout le monde

Voici un exemple de la mise en application de nos discussions.
Je voulais réaliser une classe qui pourrait regrouper toute cette "affaire".
mais j'ai eu quelque soucis car (parfois) les évènements ne se déclenchent plus.
Dés que je pourrai je me repencherai sur la possibilité de mise en place de cette classe. Pas tout de suite.

Donc l'exemple ci-joint comprend 2 fichiers:
- l'UDF: ConsgnV2.xlam
- et un fichier qui l'utilise: Utilise_ConsgnV2.xlsm
A priori tout marche.
Il me reste à l'implémenter dans mon appli.
C'est toujours là que l'on se marre n'est ce pas ?

A vos avis SVP!
Merci à tous

 

[Dranreb]

Bonjour.
Je ne comprend pas bien ce que vous voulez dire, pour moi votre Utilise_ConsgnV2.xlsm c'est le brouillon de votre appli, non ? elle n'a besoin de rien d'autre que de formules utilisant la fonction du xlam.
Pourquoi une classe ? Le ThisWorkbook semble convenir comme module objet supportant la Sub WthEvnXL_SheetCalculate.

 

[p'tit vieux]

Bonjour.
Je ne comprend pas bien ce que vous voulez dire, pour moi votre Utilise_ConsgnV2.xlsm c'est le brouillon de votre appli, non ? elle n'a besoin de rien d'autre que de formules utilisant la fonction du xlam.
Pourquoi une classe ? Le ThisWorkbook semble convenir comme module objet supportant la Sub WthEvnXL_SheetCalculate.

Bonjour Dranreb
Oui c'est cela. ce n'est juste qu'un fichier exemple de l'utilisation de l'UDF.
Tout à l'air bon en l'état. Les évènements sont bien là et je peux contrôler les appelants.
A propos de la classe c'est juste pour le fun et voir si je pouvais intégrer l'ensemble.
Mais bon j'ai parfois l'esprit tordu.
Ici créer une classe genre "Ecriture" ne s'impose pas .
Avez-vous remarqué quoique ce soit dans les fichiers? Un avis complémentaire?

Merci beaucoup pour le (gros) coup de main.

 

[eriiic]

Bonsoir,

quant à ma proposition, si tu fais un xlam il faut utiliser les événements Application et non les événements classeur.
Dans ThisWorkbook ça devient :

Option Explicit

Private WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.HasFormula Then
        If InStr(Target.Formula, "fnPerso(") > 0 Then
            ' la cellule validée contient une formule avec la fonction personnalisée
            ' compléter les tests si plusieurs formats possibles
            Target.NumberFormat = "0.0%"
        End If
    End If
End Sub

Private Sub Workbook_Open()
    'récupérer l'application Excel à l'ouverture
    Set App = Application
End Sub

et tes fonctions toujours dans un module standard, j'y ai mis fnPerso() pour tester.
Au passage, avec SheetChange la fonction n'est recalculée que si les antécédents ont changé, c'est à dire que si c'est impératif.
Calculate est appelé tout le temps et rend ta fonction volatile même si ce n'est pas nécessaire. Tu confirmes Dranreb ?

Je ne suis pas fan des xlam qui oblige à installer sur tous les PC, alors qu'intégrée aux classeurs une fonction est disponible à tout le monde là où elle est nécessaire.
eric

 

[Dranreb]

@eriiic, ce code ne me parait pas devoir s'exécuter en cas de réévaluation de la Function, surtout si elle avait pour agument un heure par exemple, n'impliquant aucun changement de contenu de cellule. Par ailleurs ça m'a paru insuffisant d'initialiser l'objet WithEvents As Application lors de l'ouverture du classeur de macro car il pourrait être fusillé en cas de réinitialisation du projet VBA, qui peut se produire fréquemment en phase de mise au point. Aussi est-ce dans la procédure qui note la consigne en collection que je l'initialise s'il le faut. L'examen de la collection a lieu à chaque calcul dans n'importe quelle feuille de n'importe quel classeur, ce qui reste sans effet si elle est déjà vide.
Il est bien sûr possible de mettre le même dispositif dans et pour un seul classeur voire une seule feuille. Tout dépend de la …_…Calculate utilisée et de l'emplacement de la collection.

 

[p'tit vieux]

... Je ne suis pas fan des xlam qui oblige à installer sur tous les PC ...

Bonsoir Éric
Étant sur mon téléphone, je fais juste une réponse rapide au sujet des UDF
A mon humble avis, si c'est pour un usage interne et des feuilles/classeurs (très) "standardisés" je serais assez d'accord avec vous. Surtout si, en plus, on doit transmettre à d'autres personnes "extérieures" ces classeurs. Personnes qui n'ont pas envie de faire des l'installations pour lire des tableaux de bord (par exemple).
Par contre dès qu'il s'agit d'offrir des outils utilisables dans de multiples situations là je pense qu'il n'y a plus beaucoup d'autres choix.
Au plus "simple" on crée des fonctions personnalisées en VBA ou mieux/pire (chacun ses goûts et possibilités) on passe par Visual Studio/DNA ou autres.
Mais au final il faut mettre à disposition les fonctions "autonome" des classeurs.

Bon ce n'est que mon avis. Peut-être que vous et d'autres voient les choses autrement.
Bonne nuit à tous.
Demain je vous dirai pour votre code mais j'avoue que que les arguments de Dranreb me semble convaincants.
Merci à vous tous

 

[eriiic]

Ok, je craignais ça à tort.
J'ai eu le temps de tester et je confirme.
Merci
eric

 

[p'tit vieux]

.... /...

Bonjour Patrick
Merci pour ton aide.
Juste une question HORS SUJET mais comme je n'ai pas trouvé comment vous envoyer un message privé je le fais ici.
Je suis curieux de savoir comment (Softs?) vous créez votre gif animé ci-dessus. Je trouve que c'est intéressant pour parfois donner des explications/aides.
Merci et bonne journée.

 

[eriiic]

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