[VBA] Etirer une fonction personnalisée

Bonjour le forum

La question est dans le titre, je me suis crée une fonction personnalisée pour obtenir une liste sans doublons. J'aurai aimé que quand j'étire vers le bas la formule, je retrouve une valeur unique dans une cellule.

Aujourd'hui j'utilise INDEX pour travailler avec ma fonction : =INDEX(UNIQUE(A$1:A$100);LIGNE(1:1)). Ci dessous le code actuel

Option Explicit
Function UNIQUE(Plage As Range)
Dim Cel As Variant
Dim DICO As Object

Set DICO = CreateObject("Scripting.Dictionary")
DICO.RemoveAll
For Each Cel In Plage.Value
    If Cel <> "" Then DICO(Cel) = ""
Next Cel
UNIQUE = DICO.Keys
End Function

J'ai bien cherché avant de venir ici mais sans succès J'avais même imaginé quelque chose (purement symbolique) comme ça

UNIQUE = "Liste sans doublons"
ActiveCell.Offset(1).Resize(DICO.Count, 1) = Application.WorksheetFunction.Transpose(DICO.Keys)

En tout cas merci de l'interet porté au sujet

Bonjour GGautier,

Je ne suis pas certain d'avoir bien compris et donc de savoir répondre à ta question mais je dirais à première vue qu'un simple If pourrait suffire :

Function UNIQUE(Plage As Range)

Set DICO = CreateObject("Scripting.Dictionary")

For Each Cel In Plage.Value
    If Cel <> "" Then DICO(Cel) = ""
Next Cel

If DICO.Count > 0 Then
    UNIQUE = Application.Transpose(DICO.Keys)
Else
    UNIQUE = Array("No value")
End If

End Function

Pour renvoyer toutes les valeurs uniques, il faut sélectionner la plage de renvoi, saisir la formule :

=UNIQUE($A$1:$A$100)

dans une cellule et valider en matriciel. Il est possible de connaitre à l'avance le nombre de cellules uniques avec la formule :

=NBVAL(UNIQUE(A1:A100))

Ca permet d'éviter les #NA en sélectionnant directement le bon nombre de cellules.

Mais l'utilisation avec INDEX me parait la meilleure. Il suffit de placer le INDEX(UNIQUE) dans un SIERREUR.

Cdlt,

Bonjour 3GB

Effectivement il me semblais bien que le combo INDEX et ma fonction UNIQUE était la meilleur solution. Cependant la validation en matriciel ne fonctionne pas

Si ma question n'est pas clair, je cherche un moyen de reproduire la fonction UNIQUE qui existe nativement avec Office 365.

Salut GGautier,

Oui, l'avantage avec INDEX, c'est que tu gardes le "contrôle" sur le résultat lorsque celui-ci est évolutif... Je trouve que c'est le plus simple.

Si, la validation matricielle fonctionne mais à condition de modifier UNIQUE = DICO.keys par UNIQUE = application.transpose(DICO.keys). Sinon, tu peux rajouter la fonction excel transpose de manière à obtenir un vecteur colonne :

=TRANSPOSE(UNIQUE(A:A))

Je ne connais pas 365 malheureusement mais j'imagine qu'à l'instar de la fonction FILTRE (que je ne connais pas non plus mais dont j'ai furtivement entendu parler), il est possible de renvoyer plusieurs valeurs. Là, je ne vois pas trop comment faire.

En tout cas, je pense à 2 possibilités :

1) Le faire par une macro via une inputbox de sélection de range. Le but étant d'obtenir une liste sans doublon directement. Par exemple, on double clic sur une cellule, une inputbox s'ouvre, on sélectionne la plage à uniciser, on appuie sur ok et on obtient une nouvelle plage à partir de la cellule double-cliquée.

2) Sinon, on garde la fonction unique et on ajoute une évènement change portant sur la formule de la target :

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 Then 'si la cible est une cellule unique
    If UCase(Target.Formula) Like "=UNIQUE(*)" Then 'si on saisit la fonction UNIQUE
        renvoi = Evaluate(Target.Formula) 'tableau contenant les valeurs de UNIQUE
        Application.EnableEvents = False 'desactivation evenements
        Target.Resize(UBound(renvoi), 1).FormulaArray = Target.Formula 'renvoie une matrice avec toutes les valeurs de la presente fonction UNIQUE
        Application.EnableEvents = True 'reactiv events
    End If
End If

End Sub

Le seul problème, c'est qu'en cas d'ajout ou de suppression, ça ne se mettra pas à jour comme tu pourrais le souhaiter idéalement. Il faudrait effacer et recommencer ou sinon essayer de passer par un évènement calculate avec un recherche (find) de tous les UNIQUE pour les mettre à jour...

Voici le fichier avec mon idée.

Cdlt,

Ta méthode est intéressante mais il est vrai que dans mon cas c'est pas très pratique, le but de ma fonction est que tout ça reste dynamique

Je vais tester et éprouver la méthode en matricielle, je verrais bien ce qui me convient le plus, laquelle des méthodes est la plus pertinente. Merci pour toutes ces infos constructive

ah oui une autre question mais de manière plus général au fonctions personnalisées. Est ce que c'est possible de créer des "catégories" et des "sous catégories" de fonction personnalisées ? Je donne un exemple : Sous Excel quand ont tape =SOMME et que l'on rajoute un "." les "sous-catégories" de SOMME apparaissent (SOMME.SI ; SOMME.SERIES ; etc).

Je cherche à produire quelque chose de similaire avec NOM et utiliser des fonctions personnalisées NOM.FEUILLE ou encore NOM.CLASSEUR.

Est-ce possible ?

Salut GGautier,

Voici l'idée que j'avais avec l'évènement calculate sur le fichier joint. Il n'est pas impossible qu'il y ait des bugs et c'est sûrement perfectible mais ça fonctionne.

Je trouve quand même que c'est lourd et pas pratique comme solution, ça reste du bricolage... Je me dis qu'une procédure ou insérer la fonction dans un INDEX apporterait plus de flexibilité mais, si ton problème ne concerne qu'une ou des feuilles précises, ça peut le faire.

Je suis pas sûr de bien savoir répondre ni de bien comprendre. Mais avec une fonction personnalisée, dès qu'on tape le début de son nom, elle apparait normalement dans la liste des fonctions proposées. Ensuite, bien sûr, tu peux décliner comme tu veux (en revanche, sans "." si je ne m'abuse, mais qu'on remplace habituellement par un "_").

S'il s'agit de rendre les fonctions exploitables sur n'importe quel fichier, il faut mettre les codes dans le classeur Personal.xlsb. C'est en fait un classeur de fond qui s'ouvre à chaque utilisation d'excel. Par exemple, je pense que le Pack de fonctions de ce forum doit être de type xlsb. Je ne saurais pas donner beaucoup plus de détails car je ne l'ai jamais fait (même si j'y ai souvent pensé) mais je vais m'y intéresser prochainement, comme ça, comme toi, je transformerai ma version 2010 en 365 .

Cdlt,

par défauts c'est ce que j'ai fait avec les "_" et j'exporte mon fichier en ficher "complément Excel" comme ça il est exploitable sans forcément enregistrer en .xlsm.

Apres la fonction est exploitable, le sujet porte juste sur des questions esthétique. Je regarde ton fichier cette aprèm car c'est toujours intéressant 😉

Tu exportes ton fichier en fichier "Complément excel" ? C'est-à-dire ? Comment ça marche ?

Pas de souci, j'attends ton retour patiemment

Bonjour,

tu ne pourras jamais émuler les fonctions Unique() ou Filtre()
MS a dévoyé le rôle des fonctions en s'autorisant à retourner un résultat au-delà de la zone où est inscrite la fonction.
Une fonction retourne un résultat dans la cellule (ou la plage si validation d'une plage en matriciel) où elle est.
Toi tu ne pourras pas, à moins de sélectionner une plage capable de restituer le maximum des résultats possibles. Avec des cellules inutiles si moins de résultats.
Je parles bien des fonctions personnalisées. Avec un sub c'est autre chose...
eric

Bonjour eriiic,

Et bien, j'y suis un peu parvenu quand même même si c'est de la bidouille...

Mais je suis clairement d'accord avec toi, c'est un problème à résoudre par procédure idéalement.

Ah bon ?
En partant d'une cellule unique avec une fonction personnalisée, tu arrives à en remplir 10 ou 15 si besoin ?
Je n'ai pas 365 mais si j'en crois l'aide c'est ce qu'il se passe :
https://support.microsoft.com/fr-fr/office/unique-unique-fonction-c5ab87fd-30a3-4ce9-9d1a-40204fb85e...
On est bien d'accord que Sub Worksheet_Change c'est un Sub et non une Function hein
eric

Oui en partant d'une cellule unique mais en effet, on est d'accord sur le fait que ça nécessite des procédures. Mais on approche du résultat en gardant un comportement plus proche de la fonction que de la procédure (c'était l'objectif).

je n'avais pas vu ton fichier. Effectivement c'est approchant..
Tu t'es bien amusé

Si je peux me permettre, les utilisateurs sachant modifier une plage matricielle n'étant pas nombreux, tu pourrais ajouter la sélection de la zone en cours en cas de double-clic dedans pour faciliter l'édition de la formule (avec un rappel sur comment valider tant qu'à faire)
eric

Et oui, je me suis donné du mal, c'est un petit exploit pour moi .

Merci pour ces conseils mais je pense que je vais m'arrêter là, GGautier saura très bien se débrouiller avec. Et l'avantage, c'est qu'il suffit de modifier la 1ère cellule d'une matrice (en validant) pour que la matrice se mette à jour.