Function tbl(rng)
tbl = rng.Value
End Function
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 |
Euh...As tu essayé cette formule : A1 = 0 et B1 = 10 soit 0/10 = #DIV/0!
Sub test()
MsgBox CInt(CVErr(xlErrNA)) & " = " & xlErrNA
End Sub
Function TblNonNA(ByVal Rng As Range) As Variant()
RngValNonNA(TblNonNA) = Rng
End Function
Private Property Let RngValNonNA(T(), ByVal RHS As Range)
Dim L As Long, C As Integer
T = RHS.Value
For L = 1 To UBound(T, 1): For C = 1 To UBound(T, 2)
If IsError(T(L, C)) Then If CInt(T(L, C)) = xlErrNA Then T(L, C) = Empty
Next C, L
End Property
Function tbl(rng)
Dim tbl2, T
tbl = rng.Value
'astuce 1 on cré un array (2 dimensions (MAX DE LIGNES POUR UN XLS,1 colonne)) de nombres de 1 à 65535
x = Evaluate("row(1:65535)")
' astuce 2 je redimensionne MAINTENANT le tableau avec (l'astuce 1) pour les lignes et (array(1)) pour 1 colonne
tbl2 = Application.Index(tbl, x, Array(1))
tbl = tbl2
End Function
Private Function PlgUti(ByVal PlageDép As Range, Optional ByVal PlagExam As Range = Nothing, _
Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
Dim LMax As Long, CMax As Long, NbL As Long, NbC As Long
On Error GoTo RienTrouvé
If PlagExam Is Nothing Then Set PlagExam = PlageDép.Worksheet.UsedRange
LMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
CMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByColumns, xlPrevious).Column
On Error GoTo 0
NbL = LMax - PlageDép.Row + 1: If NbL < LMin Then NbL = LMin
NbC = CMax - PlageDép.Column + 1: If NbC < CMin Then NbC = CMin
If NbL < 1 Or NbC < 1 Then GoTo CEstToutVide
Set PlgUti = PlageDép.Resize(NbL, NbC)
Exit Function
RienTrouvé: Resume CEstToutVide
CEstToutVide: Set PlgUti = Nothing
End Function
Private Function ColUti(ByVal PlageDép As Range, Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
Set ColUti = PlgUti(PlageDép, Intersect(PlageDép.Worksheet.UsedRange, PlageDép.EntireColumn), LMin, CMin)
End Function
Function Tbl(ByVal Rng As Range) As Variant()
Tbl = Rng.Resize(Application.Caller.Rows.Count).Value
End Function[Code]
Sub testx()
Dim tablo(1 To 3, 1 To 2) 'tableau de 3 lignes et 2 colonne
tablo(2, 1) = "toto"
MsgBox UBound(tablo) & " ligne(s) et " & UBound(tablo, 2) & " colonne(s)" & vbCrLf & "tablo(2,1) =" & tablo(2, 1)
t = redim_PreserveXY(tablo, 100, 5)
MsgBox UBound(t) & " ligne(s) et " & UBound(t, 2) & " colonne(s)" & vbCrLf & "t(2,1) =" & t(2, 1)
End Sub
Function redim_PreserveXY(t, Optional lignes& = 0, Optional colonnes& = 0)
Dim X, Y
If lignes = 0 Then lignes = UBound(t)
If colonnes = 0 Then colonnes = UBound(t, 2)
X = Evaluate("ROW(1:" & lignes & ")")
Y = Evaluate("COLUMN(" & Cells(1).Resize(, colonnes).Address(0, 0) & ")")
redim_PreserveXY = Application.Index(t, X, Y)
End Function
Function Tbl(ByVal Rng As Range) As Variant()
Dim RngAC As Range, TSor(), TEnt(), LMax As Long, CMax As Integer, L As Long, C As Integer
Set RngAC = Application.Caller
ReDim TSor(1 To RngAC.Rows.Count, 1 To RngAC.Columns.Count)
TEnt = Rng.Value
LMax = Borné(1, UBound(TEnt, 1), UBound(TSor, 1))
CMax = Borné(1, UBound(TEnt, 2), UBound(TSor, 2))
For L = 1 To LMax
If L > LMax Then Exit For
For C = 1 To CMax: TSor(L, C) = TEnt(L, C): Next C, L
Tbl = TSor
End Function
Private Function Borné(ByVal LimInf As Double, ByVal V As Double, ByVal LimSup As Double) As Double
Borné = (LimInf + Abs(V - LimInf) - Abs(LimSup - V) + LimSup) / 2
End Function
c'est justement l'inverseMais je ne comprends pas trop le problème. Si on récupère la valeur d'une plage trop grande ça ne génère pas de #N/A comme si on affecte un tableau à une plage trop grande. Et si on la retourne dans une plage matricielle trop petite il est simplement tronqué.
Function Tbl(Rng)
Dim tbl2, t
Tbl = Rng.Value
'astuce 1 on cré un array (2 dimensions ( du meme nombre de lignes que la plage caller +1 pour etre sur et 1 colonne)) de nombres de 1 à 65535
X = Evaluate("ROW(1:" & Application.Caller.Rows.Count + 1 & ")")
' je redimentionne MAINTENANT le tableau avec (l'astuce 1) pour les lignes et (array(1)) pour 1 colonne
tbl2 = Application.Index(Tbl, X, Array(1))
Tbl = tbl2
Debug.Print Application.Caller.Rows.Count
End Function