Fonction personnalisée : Note numérique vers note alpha avec mise en forme

[Victor21]

Bonjour les amis

Une fonction personnalisée, incluant la mefc, serait-elle plus économe que cette formule + la MEFC, répétée plus de 600 fois :

=SIERREUR(CHOISIR(EQUIV(DECALER($N4;0;COLONNE()-9+EQUIV($J$1;$P$1:$AD$1;0));{0;1;2;3};1);"NA";"ECA";"AR";"A");"")

dans l'exemple que je joins (colonnes J, K et L).
Si oui, quelle serait-elle ?

D'avance, merci pour vos conseils et suggestions.

Edit : Complément MEFC

 

[Modeste]

Re : Fonction personnalisée

Salut Patrick

J'ai déjà honte de ma question (mais jusque là je n'en suis pas mort ... et pourtant, Dieu sait que je peux m'appliquer ) ... mais comment une MFC serait-elle inclu(s)e dans une fonction personnalisée

 

[Victor21]

Re : Fonction personnalisée

Bonsoir, Modeste

Je ne me suis- à ma grande honte- jamais encore intéressé aux fonctions personnalisées.
Peut-être me fourvois-je , mais je subodore que VBA peut évaluer le résultat, et en fonction de celui-ci appliquer un format à la cellule. Non ?

Si non, je me tournerai vers une macro qui bouclera sur toutes les cellules concernées...

 

[Dranreb]

Re : Fonction personnalisée

Bonsoir Victor21
Pas dans une fonction évaluée lors des calculs. Excel fait l'impasse là dessus et contourner ça au forcing par un vrai timer système qui s'exécute pendant les calculs provoque un plantage immédiat d'Excel.
Mais tant qu'on ne dérange pas Excel pendant les calculs tout est possible: on pourrait imaginer que la fonction empile des ordres de coloriage dans un tableau VBA, lequel serait épuisé dans une procédure planifiée par Application.OnTime, laquelle n'est jamais lancée pendant les calculs.
Cordialement

 

[Victor21]

Re : Fonction personnalisée

Bonsoir, Bernard

Merci pour ces éclaircissements.
Mais comme tu l'auras compris j'ai appris le VBA en Béotie, et je ne souhaite surtout pas une usine à gaz !
Je me contenterais d'une fonction qui ne renvoie que la valeur, à condition d'y gagner en poids et en ressource.
Pour la MEFC, tant pis : à la mimine, comme actuellement.

 

[david84]

Re : Fonction personnalisée

Bonsoir,
j'ai sûrement pas tout compris mais au cas où...

Function Victor21(Cellule As String, Plage As Range, Rang As Byte) As String
Dim c As Range, d, Adrc, Adrd
With Plage
    Set c = .Find(Cellule, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then Adrc = c.Address
    Set d = c.Offset(3, Rang - 1)
    If Not d Is Nothing Then Adrd = d.Address
    If d.Value = "" Then
        Victor21 = "NA"
        Range(Application.Caller.Address).Font.ColorIndex = 3
    Else
        Victor21 = Application.WorksheetFunction.Choose(d.Value, "ECA", "AR", "A")
        Range(Application.Caller.Address).Font.ColorIndex = d.Value + 3
    End If
End With
End Function

Se placer en J4 (par exemple) et tirer la fonction vers la droite

=Victor21($J1;$P1:$AD1;COLONNE(A:A))

Mais j'ai comme l'impression que je n'ai pas compris ton attente...
A+

 

[Dranreb]

Re : Fonction personnalisée

Si ça marche, c'est que je me suis trompé et que la restriction ne s'applique pas à toutes les propriétés des cellules. Mais elle s'applique notamment à leur contenu. Mais ça m'étonne. Ça a été testé, ça ? Un jour j'avais même essayé un truc qui n'a rien à voir: lancer un Application.OnTime depuis une fonction évaluée lors des calcul. Il n'a jamais voulu me le prendre en compte. Peut être la nouvelle versions d'Excel est elles plus souple ?

 

[eriiic]

Re : Fonction personnalisée

Bonjour à tous,

Et bien, étonnant...
D'autant plus que l'aide excel précise bien :
VBA pour Excel impose des limites très strictes aux fonctions appelées à l'intérieur de formules de feuilles de calcul. Leur seule attribution est de renvoyer une valeur à la cellule hôte. Ces fonctions ne peuvent effectuer aucune autre action susceptible de modifier le contenu du classeur.

En particulier, une fonction complémentaire ne peut pas modifier la valeur d'autres cellules que celles où elles ont été saisies. De même, il est impossible de renommer une feuille, déplacer une plage, changer le format d'une cellule, etc.


Ben tant mieux
Par contre pour .interior on se fait toujours jeter, mais c'est déjà ça de pris.

eric

 

[Modeste]

Re : Fonction personnalisée

Re-bonsoir Patrick,
Salut David , Bernard

Victor, aurais-tu un billet de retour pour la Béotie? David vient de me convaincre que je voulais rentrer à la maison, finalement
Bravo à David!
Un petit Application.Volatile comme me dirait JC ... ?

Edit: salut Eric

 

[Victor21]

Re : Fonction personnalisée

Bonsoir, Bernard, David

Tout d'abord merci de vous intéresser à ce problème

Je m'aperçois que j'ai été un peu avare de précisions...
Il s'agit d'un bulletin de notes, notes allant de 0 à 3, ou rien, qu'il faut transcrire littéralement :
0 = Non Acquis (NA) en rouge
1 = En Cours d'Acquisition (ECA) en orange
2 = A Revoir (AR) en bleu
3 = Acquis (A) en vert
"-" = pas de note ("" ou "-")

La fonction de David semble fonctionner avec cette modif (sauf pour le dernier cas que je n'avais pas soulevé ) :

Function Victor21(Cellule As String, Plage As Range, Rang As Byte) As String
 Dim c As Range, d, Adrc, Adrd
 With Plage
     Set c = .Find(Cellule, LookIn:=xlValues, lookat:=xlWhole)
     If Not c Is Nothing Then Adrc = c.Address
     Set d = c.Offset(3, Rang - 1)
     If Not d Is Nothing Then Adrd = d.Address
     If d.Value = 0 Then
         Victor21 = "NA"
         Range(Application.Caller.Address).Font.ColorIndex = 3
     Else
         Victor21 = Application.WorksheetFunction.Choose(d.Value, "ECA", "AR", "A")
         Range(Application.Caller.Address).Font.ColorIndex = d.Value + 3
     End If
 End With
 End Function

Avec la restriction que la fonction n'est pas dynamique : la modification des chiffres en P, Q et R...AD (qui sera le résultat d'un calcul) ne met pas à jour automatiquement les cellules J, K et L (Il faut les éditer ou, ce qui est plus simple, éditer J1).

Mais avant de poursuivre, pouvez vous me renseigner sur le gain par rapport à la formule d'origine ?
Encore merci à vous deux, et bonne soirée

 

[Dranreb]

Re : Fonction personnalisée

À mon avis un gain de temps et de faisabilité serait surtout atteint par une Worksheet_Change qui s'occuperait de recalculer ce qu'il faut et corriger les formats comme souhaité. La fonction n'est pas de moi: j'avais toutes les raisons de penser qu'elle ne fonctionnerait pas.
À +

 

[david84]

Re : Fonction personnalisée

Re

La fonction de Bernard semble fonctionner avec cette modif (sauf pour le dernier cas que je n'avais pas soulevé )

Euh moi c'est David, mais c'est un honneur que tu me confondes avec Bernard !
Sinon, concernant la gestion des différents cas (dont le "-"), un SelectCase me paraît plus adapté.

Function Victor21(Cellule As String, Plage As Range, Rang As Byte) As String
Dim c As Range, d As Range, Adrc As String, Adrd As String
Application.Volatile'comme préconisé justement par Modeste
With Plage
    Set c = .Find(Cellule, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then Adrc = c.Address
    Set d = c.Offset(3, Rang - 1)
    If Not d Is Nothing Then Adrd = d.Address
    Select Case d.Value
    Case Is = ""
        Victor21 = "NA"
        Range(Application.Caller.Address).Font.ColorIndex = 3
    Case Is = "-"
        Victor21 = "-"
        Range(Application.Caller.Address).Font.ColorIndex = xlColorIndexAutomatic
    Case Else
        Victor21 = Application.WorksheetFunction.Choose(d.Value, "ECA", "AR", "A")
        Range(Application.Caller.Address).Font.ColorIndex = d.Value + 3
    End Select
End With
End Function

A+

 

[Victor21]

Re : Fonction personnalisée

Re à tous.

@Bernard et David : désolé de vous avoir confondus. L'âge sans doute...
@Eriic : Merci pour ces précisions.
@Modeste : Je prends note de l'application du volatile Quant à la béotie, je me soigne -trop lentement à mon goût- à grandes doses d'XLD
@Bernard, enfin : mes yeux clignotent... Mauvais signe. Je prends note de ton idée de procédure événementielle, ...
@ David Le select case me va bien

Et je reprends tout ça demain : Morphée va réveiller les voisins à force de me crier : VIENS ! ! !

Edit : décidément j'ai bien fait d'aller dormir !!!

 

[david84]

Re : Fonction personnalisée

Re

@David, enfin : mes yeux clignotent... Mauvais signe. Je prends note de ton idée de procédure événementielle, ...

Non, là encore c'est Bernard qui en a parlé...

À mon avis un gain de temps et de faisabilité serait surtout atteint par une Worksheet_Change qui s'occuperait de recalculer ce qu'il faut et corriger les formats comme souhaité.

Moi je t'ai parlé de la gestion des différents cas en passant plutôt par un SelectCase.
A+

 

[Victor21]

Re : Fonction personnalisée

Bonjour à tous.

Bon, je crois avoir corrigé mes erreurs de noms.
Au final, ça donne :

Function Note(Cellule As String, Plage As Range, Rang As Byte) As String
' David84 - XLD
    Dim c As Range, d As Range, Adrc As String, Adrd As String
    Application.Volatile    'comme préconisé justement par Modeste
    With Plage
        Set c = .Find(Cellule, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then Adrc = c.Address
        Set d = c.Offset(3, Rang - 1)
        If Not d Is Nothing Then Adrd = d.Address
        Select Case d.Value
        Case Is = "-"
            Note = "Non évaluée"
            Range(Application.Caller.Address).Font.ColorIndex = 1
        Case Is = 0
            Note = "NA"
            Range(Application.Caller.Address).Font.ColorIndex = 3
        Case Is = 1
            Note = "ECA"
            Range(Application.Caller.Address).Font.ColorIndex = 46
        Case Is = 2
            Note = "AR"
            Range(Application.Caller.Address).Font.ColorIndex = 32
        Case Is = 3
            Note = "A"
            Range(Application.Caller.Address).Font.ColorIndex = 10
        Case Else
            Range(Application.Caller.Address).Font.ColorIndex = 1
            Note = "Erreur"
        End Select
    End With
End Function

Pouvez-vous m'aider à remplacer :

Set d = c.Offset(3, Rang - 1)

de manière à adapter cette fonction à la ligne sur laquelle elle est utilisée ?
(Le 3 restreint l'utilistion de cette fonction sur la ligne 4, alors qu'elle doit être utilisée sur n'importe quelle ligne)

Merci d'avance pour vos lumières