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.