How do I declare a function whose output is an array?
this worked for me in xl2003, so I would assume in xl2000 and later it would
work:
Public Function Test(i As Long) As Double()
Application.Volatile
Dim v() As Double
ReDim v(1 To i, 1 To 2)
For i = 1 To i
For j = 1 To 2
v(i, j) = Rnd()
Next j
Next i
Test = v
End Function
I can't say for xl97 and I haven't tested in xl2000 and xl2002 but xl2000
and later use VBA6 - so my expectation that they would work.
For completeness, this also worked:
Sub ABC()
Dim v1() As Double
v1 = Test(4)
Debug.Print TypeName(v1), VarType(v1)
For i = LBound(v1, 1) To UBound(v1, 1)
For j = LBound(v1, 2) To UBound(v1, 2)
Debug.Print i, j, v1(i, j)
Next
Next
End Sub
Public Function Test(i As Long) As Double()
Application.Volatile
Dim v() As Double
ReDim v(1 To i, 1 To 2)
For i = 1 To i
For j = 1 To 2
v(i, j) = Rnd()
Next j
Next i
Test = v
End Function
and v1 was shown to be a v1() as double
Double() 8197
so my comment about a variant required for passing to an array is pre-VBA6
However, you still need a variant to pick up a multicell contiguous range
from a worksheet in one go.
--
regards,
Tom Ogilvy
"Schizoid Man" wrote:
Tom Ogilvy wrote:
So I wouldn't say there is a simple yes no answer to whether to use a range
or an array. I think it depends on what you are doing.
One example is the Gauss-Hermite integration. I pass an integer N to a
function, which represents the number of points that I am integrating a
certain polynomial over.
The function will create a 2 dimensional array of type Double with the
dimensions (1 to N, 1 to 2).
Currently, I declare the function as
Public Function GaussHerm(N as Integer) as Variant
'Code
'ReDim absWts(1 to N, 1 to 2)... etc`
GaussHerm = absWts
End Function
Is there no way to eliminate the Variant from this declaration?
|