Microsoft 365 Fonction Filtre() avec comme critère une fonctions crées sous VBA

A_dje · 4 Mai 2022

Bonjour
J'utilise des fonctions VBA qui marchent très bien dans les calculs. Ce que je ne comprends pas, c'est le fait que la fonction filtre sous Excel 365 donne le résultat #Valeur! quand j'utilise les fonctions VBA comme critère. Par exemple +FILTRE(Mots!E11:E500; isPrime(Mots!E11:E500)), isPrime() est une fonction VBA qui vérifie si un nombre est premier.

Merci pour votre aide.

PS. Le code de la fonction en question est:
Public Function isPrime(Number As Long) As Boolean

If (Number = 1) Then
isPrime = False
Exit Function
End If

If (Number = 2) Or (Number = 3) Then
isPrime = True
Exit Function
End If

If (Number < 1) Then
isPrime = False
End If

Dim i As Long
For i = 2 To CLng(Sqr(Number)) Step 1
If Number Mod i = 0 Then
isPrime = False
Exit Function
End If
Next i

isPrime = True

End Function

mapomme · 4 Mai 2022

Bonjour à tous

On peut réécrire la fonction Isprime pour qu'elle retourne une matrice et dans ce cas on peut l'utiliser dans la fonction Filtre(). Elle fonctionne aussi avec une seule cellule (comme avant).

Exemple d'utilisation en 365:

Code:

=FILTRE(E2:E26;isprime(E2:E26))

nota: pour les versions antérieures à 365, Isprime s'utilise comme fonction matricielle à valider par Ctrl+Maj+Entrée.

Code de la fonction Isprime() :

VB:

Public Function isPrime(xrg As Range)
Dim i As Long, j As Long, k As Long, n As Long, t

   t = xrg.Value
   If Not IsArray(t) Then ReDim t(1 To 1, 1 To 1): t(1, 1) = xrg.Value
   For i = 1 To UBound(t): For j = 1 To UBound(t, 2)
      If t(i, j) < 2 Then
         t(i, j) = False
      Else...

Hasco · 4 Mai 2022

Bonjour,

Votre fonction VBA ne traite qu'un nombre à la fois alors que vous l'appelez sur une plage de cellules.

Vous pouvez ajoutez une colonne à votre tableau de données en y mettant votre fonction VBA puis mettre cette colonne en paramètre de la fonction Filtre.

Cordialement

A_dje · 4 Mai 2022

Merci Hasco, c'est effectivement ce que j'ai fait, je voulais par la fonction éviter une surcharge d'un très grand tableau. Bonne journée.

Hasco · 4 Mai 2022

Re,

Je ne peux pas l'affirmer à 100% mais il me semble que ce sera le contraire. Votre fonction VBA aurait été une surcharge pour les temps de calculs. Une fonction native est d'ailleurs préférable.
Personnellement j'emploierai une formule :

=MAX(A2=2;ET(MOD(A2;LIGNE(INDIRECT("2:"&ARRONDI.SUP(RACINE(A2);0))))<>0))=1

le = 1 renvoyant un booleen. Si vous le supprimez vous aurez des 0 ou 1

Si vos nombres ne changent pas tous les jours, vous pouvez faire une boucle VBA et appeler votre fonction pour mettre la réponse au test en dur dans les cellules du tableau.

Cordialement

A_dje · 4 Mai 2022

Hasco à dit:
Re,

Je ne peux pas l'affirmer à 100% mais il me semble que ce sera le contraire. Votre fonction VBA aurait été une surcharge pour les temps de calculs. Une fonction native est d'ailleurs préférable.
Personnellement j'emploierai une formule :

le = 1 renvoyant un booleen. Si vous le supprimez vous aurez des 0 ou 1

Si vos nombres ne changent pas tous les jours, vous pouvez faire une boucle VBA et appeler votre fonction pour mettre la réponse au test en dur dans les cellules du tableau.

Cordialement

C'est effectivement le cas, les codes VBA ralentissent considérablement le classeur.

mapomme · 4 Mai 2022

Bonjour à tous

On peut réécrire la fonction Isprime pour qu'elle retourne une matrice et dans ce cas on peut l'utiliser dans la fonction Filtre(). Elle fonctionne aussi avec une seule cellule (comme avant).

Exemple d'utilisation en 365:

Code:

=FILTRE(E2:E26;isprime(E2:E26))

nota: pour les versions antérieures à 365, Isprime s'utilise comme fonction matricielle à valider par Ctrl+Maj+Entrée.

Code de la fonction Isprime() :

VB:

Public Function isPrime(xrg As Range)
Dim i As Long, j As Long, k As Long, n As Long, t

   t = xrg.Value
   If Not IsArray(t) Then ReDim t(1 To 1, 1 To 1): t(1, 1) = xrg.Value
   For i = 1 To UBound(t): For j = 1 To UBound(t, 2)
      If t(i, j) < 2 Then
         t(i, j) = False
      Else
         n = CLng(Sqr(t(i, j)))
         For k = 2 To n
            If t(i, j) Mod k = 0 Then Exit For
         Next k
         t(i, j) = (k > n)
      End If
   Next j, i
   isPrime = t
End Function

Un fichier exemple avec 4 utilisations différentes de la fonction Isprime (en excel 365).

Cette fonction devrait être assez rapide sur un micro "moderne" puisqu'elle calcule en une fois la matrice et ne devrait être recalculée que si la plage source est modifiée.

Hasco · 4 Mai 2022

Hello @JHA

nous sommes bien d'accord, il faut réécrire la fonction. Mais je ne l'aurais pas fait sans un classeur exemple du demandeur.

Cordialement

A_dje · 4 Mai 2022

A_dje à dit:
C'est effectivement le cas, les codes VBA ralentissent considérablement le classeur.

Merci, je vais opter pour la solution du test grâce à la fonction native au lieu de compliquer les choses.

A_dje · 4 Mai 2022

mapomme à dit:
Bonjour à tous

On peut réécrire la fonction Isprime pour qu'elle retourne une matrice et dans ce cas on peut l'utiliser dans la fonction Filtre(). Elle fonctionne aussi avec une seule cellule (comme avant).

Exemple d'utilisation en 365:

Code:

=FILTRE(E2:E26;isprime(E2:E26))

nota: pour les versions antérieures à 365, Isprime s'utilise comme fonction matricielle à valider par Ctrl+Maj+Entrée.

Code de la fonction Isprime() :

VB:

Public Function isPrime(xrg As Range) Dim i As Long, j As Long, k As Long, n As Long, t t = xrg.Value If Not IsArray(t) Then ReDim t(1 To 1, 1 To 1): t(1, 1) = xrg.Value For i = 1 To UBound(t): For j = 1 To UBound(t, 2) If t(i, j) < 2 Then t(i, j) = False Else n = CLng(Sqr(t(i, j))) For k = 2 To n If t(i, j) Mod k = 0 Then Exit For Next k t(i, j) = (k > n) End If Next j, i isPrime = t End Function

Un fichier exemple avec 4 utilisations différentes de la fonction Isprime (en excel 365).

Cette fonction devrait être assez rapide sur un micro "moderne" puisqu'elle calcule en une fois la matrice et ne devrait être recalculée que si la plage source est modifiée.

Merci, je vais la tester

mapomme · 4 Mai 2022

Hasco à dit:
nous sommes bien d'accord, il faut réécrire la fonction. Mais je ne l'aurais pas fait sans un classeur exemple du demandeur.

Bonjour @Hasco

Cette après midi, ma pomme est bonne poire (c'est faible comme plaisanterie mais même pas honte, enfin un peu malgré tout ).

A_dje · 4 Mai 2022

A_dje à dit:
Merci, je vais la tester

Merci, génial, grâce au nouveau code, la fonction Filtre() fonctionne à merveille.

A_dje à dit:
Merci, je vais la tester

Merci, génial, grâce au nouveau code, la fonction Filtre() fonctionne à merveille. C'est rapide elle fonctionne sans passer par par Ctrl+Maj+Entrée.

mapomme · 4 Mai 2022

A_dje à dit:
elle fonctionne sans passer par Ctrl+Maj+Entrée.

Ce n'est pas la fonction qui veut ça, c'est office 365 qui le permet...

A_dje · 4 Mai 2022

C'est dans tous les cas très gentil d'avoir répondu aussi rapidement. Bonne fin d'après-midi à tous.

Microsoft 365 Fonction Filtre() avec comme critère une fonctions crées sous VBA

A_dje

XLDnaute Nouveau

mapomme

Hasco

XLDnaute Barbatruc

A_dje

XLDnaute Nouveau

Hasco

XLDnaute Barbatruc

A_dje

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

Pièces jointes

Hasco

XLDnaute Barbatruc

A_dje

XLDnaute Nouveau

A_dje

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

A_dje

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

A_dje

XLDnaute Nouveau

Discussions similaires