Defined functions (2002)

Topic

New Reply

I have defined a function that in a sports scoring program converts data in a single cell to a form needed for the results.

In effect RESULT = bsecs(G6) + bsecs(G7)

I now find I want to do the same addition over a range of 20 cells.

Because of the function bsecs() I can’t work with a range in the conventional way
SUM(G6:G26)

If I try SUM(bsecs(G6):bsecs(G26)) #VALUE! is shown

For it to work I have to use
RESULT = SUM(bsecs(G6),bsecs(G7),bsecs(G8) and so on for 20 cells)

Have I missed something simple here?
Do I really have to have 20 entries for a range of 20 contiguous cells?
Or is it my fault for defining functions?

Tony
Rutland, UK

Reply | Quote

Replies

One possibility is to enter the formula

=bsecs(G6)

in cell H6, and fill down to H26. Then, in the cell where you want the sum, enter

=SUM(H6:H26)

Another possibility would be to write a new function

Function sumbsecs(oRange As Range)
Dim oCell As Range
For Each oCell In oRange.Cells
sumbsecs = sumbsecs + bsecs(oCell)
Next oCell
End Function

and use the formula

=sumbsecs(G6:G26)

Reply | Quote

Thanks Hans

Your function suggestion did the job for me.

You provided the answer within 30 minutes, what a service!

Thanks again,
Tony

Reply | Quote

You might try this minor modification. In this one the ranges do not have to be contiguous:

Function SumbSecs2(ParamArray oRange())
    Dim i As Integer
    Dim oCell As Range
    For i = LBound(oRange) To UBound(oRange)
      For Each oCell In oRange(i)
          SumbSecs2 = SumbSecs2 + bsecs(oCell)
      Next oCell
    Next i
End Function

With this function, you could also use something like:

=sumbsecs2(B1:B4, B6, B8:B10)

Steve

Reply | Quote

Steve

I will tuck that one away for future use.

Arrays and loops in functions were both out side my experience.

So thanks for the extra information.

Tony

Reply | Quote