VBA Function: One range as input, two ranges as output

Re: VBA Function: One range as input, two ranges as output

Can you post an example or illustration?

The way I would generally approach this sort of situation would be to apply a named range (typically a dynamic named range) to the inputs. I would read the dynamic named range into an array, perform the calculations then send the output array back to a [wider] named range.

Generally I find this approach results in very quick code.

Cheers
Robert

Re: VBA Function: One range as input, two ranges as output

It's not really two array outputs that you want from the function. You just have to realize that a range is equivalent to a 2D array in the form of: xArray(1 to r, 1 to c)

A simple example function...

Function xyz(a As Range) As Variant


    Dim VArray() As Double
    r = a.Rows.Count
    c = 2
    ReDim VArray(1 To r, 1 To c)


    For r = LBound(VArray, 1) To UBound(VArray, 1)
        VArray(r, 1) = a(r, 1) * 2
        VArray(r, 2) = a(r, 1) * 10
    Next r


    xyz = VArray


End Function

This assumes that a is a column of data with n rows and the function output will be to a range with dimension n rows and 2 columns. I think it's easy to see how the function could be modified to return any number of columns (or as you say, different arrays).

In the example above, if you set B1:B3 as the "a" range and then you array-enter (ctrl-shift-enter) the =xyz(B1:B3) function across the range C1:D3, it will give you the corresponding value in column B multiplied by 2 in column C and multiplied by 10 in column D.

Of course, the nastiness of this is the array-entry of the formula...

Re: VBA Function: One range as input, two ranges as output

Ummm, let's see...

Quote from schizoidman

Am I correct in assuming that with array enter (Shift-Ctrl-Enter), the entire array will be calculated only once and each cell will be assigned one element from the array, i.e. the function is doing exactly what the procedure does and there are not as many function calls as there are cells?

My final spreadsheet is going to be huge and I need something that is computationally inexpensive.

Yes, that's correct. One call to the function returns the entire array.

Quote from schizoidman

On a different note, is there a work around to using the Variant data type? Perhaps it's the C programmer in me, but I don't like non-explicit data types! : D

Yeah... I was trying for that. The nice thing about the variant data type is that it allows me to assign the array values without stepping thru the array: xyz = VArray

I'm not sure if a double would allow that. I s'pose you could step thru the VArray and assign it. ...or maybe someone else has a bright idea.

Re: VBA Function: One range as input, two ranges as output

How about this?
According to the watch window xyz and VArray are both true Double arrays.[vba]Function xyz(a As Range) As Double()
Dim r, c
Dim VArray() As Double
r = a.Rows.Count
c = 2
ReDim VArray(1 To r, 1 To c)

For r = LBound(VArray, 1) To UBound(VArray, 1)
VArray(r, 1) = a(r, 1) * 2
VArray(r, 2) = a(r, 1) * 10
Next r

xyz = VArray

End Function[/vba]

Re: VBA Function: One range as input, two ranges as output

Thanks Andy...

I can never remember that syntax.
Need to jot it down somewhere.