why does my code loop?

[MLT]

I have the following code that's meant to calculate average times
between open and close dates. But for some reason that I can't figure
out its looping where I don't expect it to loop. I've added MsgBoxes
throughout to try to help but I'm stuck. Does anyone know why this
appears to be looping outside of the for loop?

Function LagAverage(OpenDates As Range, PNum As Range) As Single

MsgBox "starting now"

EOPeriod = Sheets("Hub").Range("B8").Value - 1 + PNum * 28

TimeSum = 0
Counter = 0

For Each ODate In OpenDates

MsgBox ODate.Value

If Not IsEmpty(ODate) Then

MsgBox "past 1"

If ODate.Value <= EOPeriod Then

MsgBox "past 2"

If Not IsEmpty(ODate.Offset(0, 6)) Then

MsgBox "past 3"

If ODate.Offset(0, 6).Value <= EOPeriod Then

TimeSum = TimeSum + (ODate.Offset(0, 6).Value
- ODate.Value)
Counter = Counter + 1
MsgBox Counter
MsgBox TimeSum

ElseIf (EOPeriod - ODate.Value) >= 30 Then

TimeSum = TimeSum + (EOPeriod - ODate.Value)
Counter = Counter + 1

End If

ElseIf (EOPeriod - ODate.Value) >= 30 Then

TimeSum = TimeSum + (EOPeriod - ODate.Value)
Counter = Counter + 1

End If

End If

End If

Next ODate

MsgBox "exited for loop"

AverageDates = TimeSum / Counter

MsgBox TimeSum
MsgBox Counter

LagAverage = AverageDates

End Function

 

[Bob Phillips]

I can't get it to loop except where I would expect it. Where are you seeing
extra loops, and or what data?

 

[Bernie Deitrick]

Custom User-Defined-Functions might be run multiple times depending on the
re-calculation dependency tree, event code, and other formulas within your
workbook. Use a clean workbook for code checking, and if it doesn't loop in
an otherwise empty workbook, then your code is as good as possible.

One other item to considcer is passing

Sheets("Hub").Range("B8").Value

as a parameter rather than hard-coding it. If the cell changes, a re-calc
will not be fired, and it the cell is moved (possible due to row insertion
or deletion) the function will not be able to call the value of the new
location. Using a named range will fix part of that (the movement, not the
re-calc.)

HTH,
Bernie
MS Excel MVP

 

[MLT]

That must have been it. I put this same code in clean workbook and it
ran as I expected (didn't run through multiple times). Before, it was
running through the entire code (not just the for loop) 3 times.
Thanks for the help!