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

[calculin]

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:

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

=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

[Bob Phillips]

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

=setofsums(A1:A3,B1:B3) 

[mdmackillop]

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