Pass a range into a custom function from within a cell

Question

Hi I'm using VBA in Excel and need to pass in the values from two ranges into a custom function from within a cell's formula. The function looks like this:

Public Function multByElement(range1 As String, range2 As String) As Variant
    Dim arr1() As Variant, arr2() As Variant
    arr1 = Range(range1).value
    arr2 = Range(range2).value
    If UBound(arr1) = UBound(arr2) Then
        Dim arrayA() As Variant
        ReDim arrayA(LBound(arr1) To UBound(arr1))
        For i = LBound(arr1) To UBound(arr1)
            arrayA(i) = arr1(i) * arr2(i)
        Next i
        multByElement = arrayA
    End If
End Function

As you can see, I'm trying to pass the string representation of the ranges. In the debugger I can see that they are properly passed in and the first visible problem occurs when it tries to read arr1(i) and shows as "subscript out of range". I have also tried passing in the range itself (ie range1 as Range...) but with no success.

My best suspicion was that it has to do with the Active Sheet since it was called from a different sheet from the one with the formula (the sheet name is part of the string) but that was dispelled since I tried it both from within the same sheet and by specifying the sheet in the code.

BTW, the formula in the cell looks like this:

=AVERAGE(multByElement("A1:A3","B1:B3"))

or

=AVERAGE(multByElement("My Sheet1!A1:A3","My Sheet1!B1:B3"))

for when I call it from a different sheet.

Solution

First, see the comment Remou left, since that's really what you should be doing here. You shouldn't need VBA at all to get an element-wise multiplication of two arrays.

Second, if you want to work with Ranges, you can do that by declaring your function arguments to be of type Range. So you could have

Public Function multByElement(range1 As Range, range2 As Range)

and not need to resolve strings to range references yourself. Using strings prevents Excel from updating references as things get moved around in your worksheet.

Finally, the reason why your function fails the way it does is because the array you get from taking the 'Value' of a multi-cell Range is two-dimensional, and you'd need to acces its elements with two indices. Since it looks like you're intending to (element-wise) multiply two vectors, you would do either

        arrayA(i) = arr1(i,1) * arr2(i,1)

or

        arrayA(i) = arr1(1,i) * arr2(1,i)

depending on what orientation you expected from your input. (Note that if you do this with VBA, orientation of what is conceptually a 1-D array matters, but if you follow Remou's advice above, Excel will do the right thing regardless of whether you pass in rows or columns, or range references or array literals.)

As an epilogue, it also looks like you're not using 'Option Explicit'. Google around for some rants on why you probably always want to do this.