VBA passing arguments to a function as variant and returning a range

Question

Hello i'm trying this code to take a range as input and to display some other values in the worksheet. The parametre of the addtwo function is variant, the first value of TableOfValues is the total number of values in the table. For example what I do: I select cells("B1" to "B5") type my addtwo(A1:A5) the cells for which i want the result are cells"A1" to "A5", in cell "A1" I have 5 (5 cells in the table) I try to display them in cells("B1" to "B5") using ctrl+shift

      Function addtwo(ParamArray TableOfValues() As Variant) As Variant
      Dim UResult() As Double
      'table that would be passed to addtwo
      ReDim UResult(TableOfValues(0)) As Double
      'TableOfValues(0) contains the length of the table
      UResult(0) = TableOfValues(0)
      For i = 1 To 2 * TableOfValues(0)
          UResult(i) = TableOfValues(i) + 2
      Next
      addtwo = UResult
      End Function

Why are you using a ParamArray ? It won't give you the result you seem to expect in this context. — chris neilsen

andrew andrew · Accepted Answer · 2018-03-05T03:36:00

Firstly, I'm not sure that your question makes it clear that you're trying to use an array formula. I gather that from your use of Ctrl-Shift. But people reading this might not realise that (and I might be wrong, in which case my answer won't work).

This assumes the formula is an array formula ={addtwo(vertical_range)}.

Try this:

Function addtwo(TableOfValues As Range) As Variant
    Dim UResult() As Double
    ReDim UResult(1 To TableOfValues.Rows.Count, 1 To 1) As Double
    UResult(1, 1) = TableOfValues.Cells(1)
    For i = 2 To TableOfValues.Rows.Count
        UResult(i, 1) = TableOfValues.Rows(i).Cells(1).value + 2
    Next
    addtwo = UResult
End Function

Differences to your code:

It returns a 2-dimensional array with the desired values in the first element (index 2) of each row (index 1). I happen to use 1-based indexing here for no particular reason (it happens to match Excel's 1-based indexing of rows and cells).
It takes a Range object instead of the ParamArray. I think what you did was possible but it's just nicer not to have to unwrap that ParamArray.
My FOR loop stops at the row count of the input range. I'm not sure why you wanted your code to iterate to twice the number of the value in the first cell (why twice the length of the table?).

I've kept the first cell the same value to match your description, although you do not have to use the first cell as the table size (notice I size the output based on the size of the input range - I just happen to skip the first row when adding 2 to match your intent).

VBA passing arguments to a function as variant and returning a range

1 Answers