title: Pass a range into a custom function from within a cell
body: <p>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:</p>
  
  <pre><code>
  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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>BTW, the formula in the cell looks like this:</p>
  
  <p>=AVERAGE(multByElement("A1:A3","B1:B3"))</p>
  
  <p>or</p>
  
  <p>=AVERAGE(multByElement("My Sheet1!A1:A3","My Sheet1!B1:B3"))</p>
  
  <p>for when I call it from a different sheet.</p>