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

[A_dje]

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

 

[Hasco]

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]

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]

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]

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]

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:

=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() :

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]

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]

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]

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:

=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() :

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]

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]

Merci, je vais la tester

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

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]

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]

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