Ranges as arguments in a VBA function called from the formula bar

Hi all,

I have coded a small function that adds pairs of cells and outputs the total number of pairs that have been added. The idea is: write the results of the sums in a range of cells, and give as a result the amount of operations that you have done:

Code:

---

Function setOfSums(ByVal sum1 As Range, ByVal sum2 As Range, ByRef results As Range) As Integer
Dim i, k
For i = 1 To sum1.Rows.Count
    results(i) = sum1(i) + sum2(i)
Next i
setOfSums = sum1.Rows.Count
End Function

---

My question is: which is the correct way of calling this function from Excel's formula bar?. I have tried this

Code:

---

=setofsums(G1:G2;H1:H2;J1:J2)

---

but I get a #VALUE! error. However, if I select the "Show steps" option from the "!" sign beside the cell, the sums are done and the results written in the correct cells (although the error remains)


Any suggestions?

Thanks in advance for your help

?ngel-Luis

Code:

---

Function SetOfSums(ByVal sum1 As Range, ByVal sum2 As Range)
  Dim i As Long
  Dim results As Variant
If sum1.Rows.Count <> sum2.Rows.Count Or _
        sum1.Rows.Count <> Application.Caller.Rows.Count Then
    SetOfSums = CVErr(xlErrValue)
    Exit Function
    End If
ReDim results(1 To sum1.Rows.Count)
    For i = 1 To sum1.Rows.Count
    results(i) = sum1.Cells(i, 1).Value + sum2.Cells(i, 1).Value
    Next i
SetOfSums = Application.Transpose(results)
End Function

---

You select the target cells and block array enter this formula

PHP Code:

---

=setofsums(A1:A3,B1:B3) 


---

You can only use a function to return a value to the cell that contains it. ie, you cannot write a function in A1 that will write a value into B1