# Thread: Defined functions (2002)

1. ## Defined functions (2002)

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

2. ## Re: Defined functions (2002)

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)

3. ## Re: Defined functions (2002)

Thanks Hans

Your function suggestion did the job for me.

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

Thanks again,
Tony

4. ## Re: Defined functions (2002)

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

<pre>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</pre>

With this function, you could also use something like:
<pre>=sumbsecs2(B1:B4, B6, B8:B10)</pre>

Steve

5. ## Re: Defined functions (2002)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•