XL 2016 Fonction personnalisée VBA

[Nicocotte125]

Bonjour à tous, la bonne année car il est encore temps, je viens d'écrire une fonction personnalisée qui à partir de deux colonnes effectues les opérations suivantes

* Compare la dernière valeur de la colonne "A:A - Ligne Fabrication" pour trouver la lignes de l'avant dernière valeur qui lui est identique
* Récupère sur la même ligne la valeur de la colonne "B:B- Format" et compare si elle correspond ou "NON" à la dernière valeur de la colonne "B:B - Format"
-> Retourne "OUI" ou "NON" en fonction....

Question :
1- Etant plus habitué à écrire des macro que des fonctions j'aurais voulu savoir si mon écriture était la plus efficace possible (avoir recours a des boucles dans une fonction personnalisée que l'on peut appeler plusieurs centaines de fois dans une feuille de calcul XXL, je ne suis pas sûr que ce soit le plus malin en terme de rafraichissement)

2 - Peut-être que dans ma façon d'écrire je peux progresser, n'hésitez pas à critiquer ma façon de coder...

3 - Comment peut-on coder un "prompt / invit" qui lors de la saisie de la sélection dans le formule l'on puisse se remémorer 6 mois après selon quel ordre on doit saisir les cellules / colonnes.

4- Comme vous le verrez dans la pièce jointe les premières lignes retourne le résultat #VALEUR, si je comprends bien pourquoi, j'aurai voulu néanmoins avoir votre avis sur la façon la plus sexy de gérer cela (sans laisser croire à l'utilisateur qu'il à mal paramétré la formule lors de la saisie)...

Code :
Function CHANGEMENT_FORMAT(Plage_Ligne_Fab As Range, Plage_Format As Range)

Dim Premiere_Ligne As Integer: Premiere_Ligne = Plage_Ligne_Fab.Row
Dim Derniere_Ligne As Integer: Derniere_Ligne = Premiere_Ligne + Plage_Ligne_Fab.Count - 1
Dim Format As String
Dim i As Integer: i = 0

'Balayage des valeurs de la plage de donnée en partant de l'avant dernière ligne-----------------------------------------
For i = Premiere_Ligne To Derniere_Ligne
If Cells(Derniere_Ligne - i, Plage_Ligne_Fab.Column).Value = Cells(Derniere_Ligne, Plage_Ligne_Fab.Column).Value Then
Format = Cells(Derniere_Ligne - i, Plage_Format.Column).Value
Exit For
Else
End If
Next

'Comparaison avec le format précédent--------------------------------------------------------------------------------------
If Format = Cells(Derniere_Ligne, Plage_Format.Column).Value Then
CHANGEMENT_FORMAT = "NON"
Else
CHANGEMENT_FORMAT = "OUI"
End If
End Function

¨PS : ma première contribution sur un forum VBA....

 

[mapomme]

Bonjour @Nicocotte125,

Ma petite version:

Option Explicit

Function CHANGEMENT_FORMAT(Plage_Ligne_Fab As Range, Plage_Format As Range)
Dim fabr, form, format As String, i As Long

    'lecture des valeurs de chaque plage
    CHANGEMENT_FORMAT = "Non"
    fabr = Plage_Ligne_Fab.Value
    form = Plage_Format.Value
    If Not IsArray(fabr) Then Exit Function
    
    'Balayage des valeurs de la plage de donnéée en partant de l'anvant dernière ligne
    For i = UBound(fabr) - 1 To 2 Step -1
        If fabr(i, 1) = fabr(UBound(fabr), 1) Then
            If form(i, 1) <> form(UBound(fabr), 1) Then CHANGEMENT_FORMAT = "Oui"
            Exit For
        End If
    Next i
End Function

 

[Nicocotte125]

Bonjour @Nicocotte125,

Ma petite version:

Option Explicit

Function CHANGEMENT_FORMAT(Plage_Ligne_Fab As Range, Plage_Format As Range)
Dim fabr, form, format As String, i As Long

    'lecture des valeurs de chaque plage
    CHANGEMENT_FORMAT = "Non"
    fabr = Plage_Ligne_Fab.Value
    form = Plage_Format.Value
    If Not IsArray(fabr) Then Exit Function
   
    'Balayage des valeurs de la plage de donnéée en partant de l'anvant dernière ligne
    For i = UBound(fabr) - 1 To 2 Step -1
        If fabr(i, 1) = fabr(UBound(fabr), 1) Then
            If form(i, 1) <> form(UBound(fabr), 1) Then CHANGEMENT_FORMAT = "Oui"
            Exit For
        End If
    Next i
End Function

Thanks a lot
Dois en conclure que pour la notion de "prompt", cela n'est pas possible ?

 

[mapomme]

Dois en conclure que pour la notion de "prompt", cela n'est pas possible ?

Je ne vois rien de pertinent et simple.

 

[laurent950]

Dois en conclure que pour la notion de "prompt", cela n'est pas possible ?

Bonjour @Nicocotte125, @mapomme, Le Forum
Si il y a une solution il faut ajouter cela :
Idéalement à placer dans : ThisWorkbook
Puis placer le code ci-dessous à l'intérieur

Private Sub Workbook_Open()
'
Code à placer ici (Mais il y a un Bug à l'ouverture !)
'
End Sub

' A vous de choisir c'est aussi possible de placer se code dans un module de classe aussi. Au Choix

Donc : Placer ici : en Feuil1 (pour chaque changement de selection)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FuncName As String          ' Nom de la fonction
    FuncName = "CHANGEMENT_FORMAT"
Dim FuncDesc As String          ' Description de la fonction
    FuncDesc = "Pour chaque changement de fomat  à la lecture des valeurs de chaque plage" & vbCrLf & "Balayage des valeurs de la plage de donnéée en partant de l'anvant dernière ligne"
Dim Category As String          ' Categorie de la fonction ici personnalisé
    Category = 11
Dim ArgDecr(1 To 2) As String   ' Tableau des descriptions des arguments de la fonction
    ArgDecr(1) = "Ligne de fabrication : " & vbCrLf & "      * Nota Verouiller la Premiére cellule non vide de la plage" & vbCrLf & "      * Exemple : A$2:A2"
    ArgDecr(2) = "Format               : " & vbCrLf & "      * Nota Verouiller la Premiére cellule non vide de la plage" & vbCrLf & "      * Exemple : B$2:B2"
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
argumentdescriptions:=ArgDecr
End Sub

3 - Comment peut-on coder un "prompt / invit" qui lors de la saisie de la sélection dans le formule l'on puisse se remémorer 6 mois après selon quel ordre on doit saisir les cellules / colonnes.

En Bonus :
Il suffit de taper au clavier : =CHANGEMENT_FORMAT( + CTRL + Shift + A
'
=CHANGEMENT_FORMAT(A$2:A2;B$2:B2)
----->>> Reslutat ----->>>
=CHANGEMENT_FORMAT(Plage_Ligne_Fab;Plage_Format)
'
Et pour Affiche la boite de dialogue
'
Il suffit de taper au clavier : =CHANGEMENT_FORMAT( + CTRL + Verr.Maj + A

Cdt

 

[Nicocotte125]

Ah cool ! Je vais essayer ça lundi !
Chapeau bas...!

PS : Je pensais naïvement qu'il existait une propriété VBA pour m'en sortir, mais j'étais loin du compte !

 

[patricktoulon]

Bonjour @Nicocotte125
pour info tu trouve ici comment créer la description de ta fonction perso

Les fonctions personnelles dans Excel

Les fonctions personnelles dans Excel

silkyroad.developpez.com

sans compter aussi qu'il est possible d'ajouter des comentaires a l'interieur meme d'une formule utilisant ta fonction perso
un exemple ici

Ajouter un commentaire dans une formule

Bonjour à tous. J'ai redécouvert récemment cette astuce qui peut parfois s'avérer utile. Il peut arriver que certaines formules de feuilles de calcul soient difficilement lisibles "dans le texte". Il est possible d'insérer des indications dans la formule visibles dans la barre, mais qui...

www.excel-downloads.com