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

p'tit vieux · 22 Février 2022

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:

VB:

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

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

Code:

 '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.

Staple1600 · 22 Février 2022

Bonjour

Ca devrait mieux fonctionner ainsi

VB:

Sub FormatRange(aRange As Range, aFormat As String)
aRange.NumberFormat = aFormat
End Sub
Sub test()
FormatRange Range("A1"), "0.00%"
End Sub

p'tit vieux · 23 Février 2022

Staple1600 à dit:
Bonjour

Ca devrait mieux fonctionner ainsi

VB:

Sub FormatRange(aRange As Range, aFormat As String) aRange.NumberFormat = aFormat End Sub Sub test() FormatRange Range("A1"), "0.00%" End Sub

Bonsoir

p'tit vieux · 23 Février 2022

Bonsoir Staple
Merci pour ta réponse.
Ok ça marche dans les cas standards
Mais là je désire changer le format d'une range créée dans mon UDF NON matriciel.
Précision, normalement, officiellement, ce n'est pas possible. Mais ça ce fait....
Schématiquement :
Donc mon UDF fait ses trucs et j'écris les résultats dans des cellules (ailleurs. Voir bout de code plus haut). Tout marche.
Sauf que je n'arrive pas à mettre en forme les cellules résultats que je viens d'écrire.
Voilà. Ton info je la connais mais ça ne veut pas.
Mais merci... Une autre idée ?

eriiic · 23 Février 2022

Bonjour à tous,

une fonction est conçue pour retourner une valeur et ne peut changer son environnement. Ca c'est pour les Sub.
Il y a des bidouillages pour essayer d'outrepasser cette limitation, mais complexes à mettre en oeuvre pour une stabilité douteuse...
Par contre la validation d'une formule déclenche l'événement Worksheet_Change. Si tu as les moyens de détecter quel format appliquer, tu peux le faire ici.
eric

p'tit vieux · 23 Février 2022

eriiic à dit:
Bonjour à tous,

une fonction est conçue pour retourner une valeur et ne peut changer son environnement. Ca c'est pour les Sub.
Il y a des bidouillages pour essayer d'outrepasser cette limitation, mais complexes à mettre en oeuvre pour une stabilité douteuse...
Par contre la validation d'une formule déclenche l'événement Worksheet_Change. Si tu as les moyens de détecter quel format appliquer, tu peux le faire ici.

p'tit vieux · 23 Février 2022

Bonsoir Éric
J'ai tenté en appelant à partir de mon UDF.
Sub FormatRange(aRange As Range, aFormat As String)
aRange.NumberFormat = aFormat
End Sub
Marche pas.
Idem lançant la Sub avec application.evaluate
Marche pas
A voir comment faire pour intercepter l'événement Change dans mon UDF LÀ PAS ÉVIDENT POUR MOI. Si tu sais, merci
A voir demain.
Bonne soirée.

Dranreb · 23 Février 2022

Bonsoir.
Essayez dans la Function d'enregistrer dans une Public New Collection VBA globale le Range donné par Application.Caller ainsi que les données de format que vous voulez changer.
Effectuez en différé ces changements dans une Worksheet_Calculate qui épuisera la dite Collection.

patricktoulon · 23 Février 2022

re
bonjour

A voir comment faire pour intercepter l'événement Change dans mon UDF LÀ PAS ÉVIDENT POUR MOI. Si

et bien c'est simple
ton module userform c'est un module classe avant tout
et bien classe ta feuille et tu aura les events a dispo dans le userform

démonstration
en haut du module userform tu met

VB:

Public WithEvents Feuille As Worksheet
' et ta variable aFormat
Public aFormat

dans le activate ou initialise de ton userform tu met

VB:

Private Sub UserForm_Activate()
Set Feuille = ActiveSheet
End Sub

et enfin l’événement

Code:

Private Sub Feuille_Change(ByVal Target As Range)
Target.NumberFormat = UserForm1.aFormat
End Sub

un exemple
dans un bouton dans le userform tu met

VB:

Private Sub CommandButton1_Click()
aFormat = """ca fait ""# ##0.000"" kilos de patates"""
[A1] = 14523665
End Sub

et voilà
démonstration
écriture direct sur feuille puis écriture par le bouton du userform

tu peux bien évidemment ajouter des conditions sur le target address colonne ligne etc...etc...
et décider le format dans l'event de substitution de la feuille

VB:

Private Sub Feuille_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        aFormat = """ca fait ""# ##0.000"" kilos de patates"""
    ElseIf Target.Column = 2 Then
        aFormat = """ca fait ""# ##0.000"" kilos de Poireaux"""
    End If
    
    Target.NumberFormat = UserForm1.aFormat
End Sub

voilà

eriiic · 23 Février 2022

Bonjour,

Patrick, UDF veut dire fonction personnalisée, pas UserForm.

Voilà ce que j'imaginais.
ta fonction dans un module standard :

VB:

Function fnPerso(v1 As Double, v2 As Double) As Double
    fnPerso = v1 / v2
End Function

dans ThisWorkbook :

VB:

Private Sub Workbook_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

eric

Dranreb · 23 Février 2022

Si, pour repartir de la notion présentée au début, vous voulez un fonction perso semblant renvoyer le format en même temps que la valeur, je vous l'installerai dans le classeur que vous aurez joint.

patricktoulon · 23 Février 2022

re
perso je pige pas le problème de formater une cellule dans une fonction perso dans un userform ou pas

maintenant si c'est pour formater une autre cellule
un event de substitution calculate dans une classe ou le userform et voilà le tour est joué

je ne vois pas le problème

p'tit vieux · 23 Février 2022

bonjour à tous
J'ai testé vos solutions. Merci pour vos réflexions
@Eriic:
Votre solution fonctionne mais :
1) dans le 'ThisWorbook' met la propriété IsAddin = true.
2) Ouvre un nouveau classeur pour tester ton User Define Function
(Yes il 'sprechen' l'anglosh le P'tit Vieux )

3) dans ton UDF au niveau:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
met un arrêt sur ton code If Target.HasFormula Then
4) Dans la nouvelle feuille de test créée saisie dans une cellule ta fonction =fnPerso(5;20)

Le problème que l'on constante est que l'événement Workbook_SheetChange ne se produit pas.
Donc pas de mise en forme.
Dommage j'y croyais!
De mon coté je vais voir si c'est possible de récupérer l'événement autrement

@Dranreb

... je vous l'installerai dans le classeur que vous aurez joint. ...

Donc, si je comprends votre idée, je n'ai plus une UDF mais juste une fonction dans un classeur.

@patricktoulon
je crains que je me sois mal exprimé.
Je ne parle pas de USerForm ou d'une fonction que j'écrirais dans un classeur mais bien d'une Fonction Définie par l'Utilisateur (UDF).
En clair une fonction indépendante donc avec la propriété "IsAddin = True" de ThisWorkbook de la fonction.

Encore merci pour aide.
Je continue à chercher.

Dranreb · 23 Février 2022

p'tit vieux à dit:
Donc, si je comprends votre idée, je n'ai plus une UDF mais juste une fonction dans un classeur.

Oui c'est cela, sauf aussi une procédure qui range les données de format dans une collection et une Sub Workbook.SheetCalculate dans ThisWorkbook ou une Worksheet_Calculate dans un module de Worksheet, tout dépend si la fonction peut être utilisée dans plusieurs feuilles ou non. Joignez une version simplifiée de votre classeur.

p'tit vieux · 23 Février 2022

@Dranreb

tout dépend si la fonction peut être utilisée dans plusieurs feuilles

Ben oui c'est la le problème.
La fonction UDF doit être utilisable partout dans toute feuille de tout classeur Excel.
Dés que je reviens je me repenche là dessus.
Je vais creuser la piste d'une "capture" de la feuille en cours d'utilisation pour voir si je peux récupérer l'événement Change ou Calculate ou autre pour voir si le formatage peux se faire après ou avant l'affichage des résultats.

Je vous tiens au courant.

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

p'tit vieux

XLDnaute Occasionnel

Staple1600

XLDnaute Barbatruc

p'tit vieux

XLDnaute Occasionnel

p'tit vieux

XLDnaute Occasionnel

eriiic

XLDnaute Barbatruc

p'tit vieux

XLDnaute Occasionnel

p'tit vieux

XLDnaute Occasionnel

Dranreb

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

eriiic

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

p'tit vieux

XLDnaute Occasionnel

Dranreb

XLDnaute Barbatruc

p'tit vieux

XLDnaute Occasionnel

Discussions similaires