PDA

View Full Version : [SOLVED] Ranges as arguments in a VBA function called from the formula bar



calculin
06-09-2008, 12:59 PM
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
06-09-2008, 01:11 PM
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
06-09-2008, 01:13 PM
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