Autres pour toute version d excel eliminer les #NA du result d'une formule perso

[patricktoulon]

Bonjour a tous
au jourd'hui je fait mumuse avec le return en matricielle d'une fonction perso mais j'ai un soucis pour éliminer le #N/A
pour vous donner un exemple
ma fonction

Function tbl(rng)
    tbl = rng.Value
End Function

Bien sur en vrai elle est beaucoup plus complexe que ça c'est juste pour l'exemple

en colonne A j'ai une liste et en c je met ma formule en matricielle sur toute la hauteur de la liste originale
la formule utilisée =tbl(A1:A7) autrement dit je ne prends pas toute la liste

j'ai bien essayé le si.non.disp ou meme le si(estna(.....
rien n'y fait
des idées ???

original		matricielle
toto		toto
titi		titi	formule validée en matricielle:=tbl(A1:A7)
riri		riri
fifi		fifi
loulou		loulou
truc		truc
bidule		bidule
machin		#N/A​
chouette		#N/A​
blablabla		#N/A​
toto		#N/A​
taratata		#N/A​
turlututu		#N/A​
chapeau		#N/A​
pointu		#N/A​

 

[job75]

Comme Yeahou en a parlé voici une solution avec une matrice (Array) en base 1 comme argument :

Function Matrice(Source)
'Source est une plage de cellules ou une matrice en base 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matrice = tablo
End Function

Ici Application.Caller ne crée pas de référence circulaire.

 

[patricktoulon]

re

@job75 comment gère tu l'erreur si c'est une sub qui l'appelle?

ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)

?

 

[patricktoulon]

re
@job75

'formule   " =matrice(A1:A15)"

Sub testmatrice()
  X = Matrice([A1:A15].Value)
[F1].Resize(UBound(X)) = X
End Sub

Function Matrice(Source)
'Source est une plage de cellules ou une matrice en base 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
On Error Resume Next
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
If Err.Number > 0 Then ReDim tablo(1 To nlig, 1 To ncol)
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matrice = tablo
End Function

 

[patricktoulon]

re
même pas

If Err.Number > 0 Then tablo = Source:exit function

 

[job75]

J'ai constaté (avec DebugPrint) que la fonction se calcule 2 fois quand on la valide.

Pour l'éviter il faut déclarer l'argument ByRef, fichier (5) :

Function Matrice(ByRef Source As Variant)
'Source est une plage de cellules ou une matrice en base 1
Debug.Print 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matrice = tablo
End Function

 

[patricktoulon]

re
et la gestion d'erreur du app.caller.?

 

[job75]

re
et la gestion d'erreur du app.caller.?

La fonction doit être placée dans une plage de cellules, c'est tout, autrement ce n'est plus le sujet de ce fil.

 

[patricktoulon]

rre
oui mais si on peut ajouter une ligne pour s'en servir en vba pourquoi se priver

et en effet dans le debug celle basée sur mapomme Calcul 2 fois la mienne 1 fois
et la tienne avec la gestion d'erreur 6 fois
mais j'ai compris la chose
c'est une matriciel donc elle se repete en cas d' erreur
pour palier au repeat je change la gestion d'erreur
je ne fait pas un resume next et goto 0 mai un goto err
et err : on error goto 0
en fait on sort pas du flux
on apprend des trucs aujourd'hui avec mon bidule
démonstration

Function Matricex(ByRef Source As Variant)
'Source est une plage de cellules ou une matrice en base 1
Debug.Print 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source: tablo = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
On Error Resume Next
'On Error GoTo err
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
err: On Error GoTo 0
'On Error GoTo 0
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matricex = tablo
End Function 'formule   " =matrice(A1:A15)"

Sub testmatricex()
  X = Matricex([A1:A15].Value)
[F1].Resize(UBound(X)) = X
End Sub

 

[job75]

@patricktoulon tu parles d'une procédure Sub pour appeler la fonction.

Mais alors la fonction est inutile et il n'y a pas besoin d'effacer des #N/A.

Dans le fichier joint la plage source est copiée et collée à la demande :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
[H2].Resize(Rows.Count - 1, Columns.Count - 7).Delete 'RAZ
If Int(Val([F1])) < 1 Or Int(Val([F2])) < 1 Then Exit Sub
[A2].Resize(Int(Val([F1])), Int(Val([F2]))).Copy [H2]
Application.EnableEvents = True 'réactive les évènements
End Sub