How do I declare a function whose output is an array?

[Schizoid Man]

My current workaround to this problem is to declare the function as

Public Function xyz(abc as Range) as Variant
'Code
'klm is an array
xyz = klm
End Function

This works efficiently enough, but I would like to tidy up my code by
eliminating all non-explicit declarations.

Furthermore, can someone tell me what exactly the difference between an
array and a range is? For example, if I declare the function as

Public Function(abc() as Double) as Variant, how will that make a
difference? Is there any advantage of one over the other?

Will functions like Rows.Count or Application.Count work with an array?

Thank you in advance.

 

[Guest]

You can only pass an array to a variant variable (or in xl2000 and
later/vba6) to a variant array. so there is not clean up necessary for your
first question.

the values in a range can be treated as an array in almost all cases. As to
properties, an array does not have any properties so you can not use
rows.count and so forth except if you go against the original range and just
use the results against an array.

You can certainly loop through an array and manipulate the values much
faster than you can loop through the source range.

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.

 

[Schizoid Man]

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?

 

[Charles Williams]

I agree with Tom, it depends what you are doing:

If you are looping through all the cells in a range then much the most
efficient way is to declare the input parameter as a range, then assign it
to a variant and loop through the resulting array. But you can't do this if
the function input parameter could be a scalar value or an array of
constants etc.

If you are using built-in worksheet functions or other object model methods
to manipulate an input range its usually best to work with a range object
rather than a variant array, although Functions like MATCH can also process
variant arrays as well as Range objects.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Guest]

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.

 

[Schizoid Man]

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.

Hi Tom,

Thanks for the snippets. I'm using Excel Office XP, which I believe is
Excel 2002, so I'm using VBA6.

I still don't understand your second comment. For example, I replaced
all my Variant declarations with Double(), and though I haven't seen
significant improvements in speed, the function works perfectly.

One of my functions is declared as:
Public Function LossDist(defprob As Range, corrInput As Double,
numFactors As Integer) As Double()

Here the defprob range is a single-column range of 125 contiguous cells.
This functions works perfectly for me without having to resort to
declaring LossDist as a Variant, so I'm not sure what you mean.

Thanks.