Passing a range to a user function from a cell

Question

Hi, I am using VBA in Excel and have to pass values ​​from two ranges to a user function from a cell formula. The function is as follows:

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 a string representation of ranges. In the debugger, I see that they are correctly passed, and the first visible problem occurs when he tries to read arr1 (i) and shows it as "index out of range". I also tried passing in the range itself (i.e. range1 as Range ...), but without success.

, , , ( ), , .

BTW, :

= (multByElement ( "1: 3", "1: 3" ))

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

.

Answer 1

-, . Remou , , . VBA, .

-, , , Range. ,

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

. Excel , .

, , , , , , "Value" , , , , () ,

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

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

, . ( : VBA, , , Remou , Excel , , .)

, , "Option Explicit". Google , , , .