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

  • Hi,


    I am am relatively new to VB and I have come across a slight problem. I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:


    Public Function xyz(a() as Variant) as Double


    There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?


    A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.


    An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.


    Thank you in advance.
    Schiz

  • 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

    Robert Hind
    Perth - Western Australia

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


    Hi Robert,


    Thank you for the reply. I'm attaching a sample Excel sheet that I prepared. I have written a procedure that reads in two arrays as inputs and writes two arrays as outputs. Both the input array and output array have the same dimensions.


    I would like to enhance this and make it a function instead for added flexibility, even though the trade-off will be computational efficiency.


    I would appreciate any suggestions.


    Thank you.

  • 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...



    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...

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

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


    Hi Aaron,


    Thank you very much for your example. It is immensely useful. You have essentially provided me with the complete solution to my problem.


    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.


    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


    Thank you very much.
    Schiz

  • 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.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • 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.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!