UDF gets called 3 times

[JenniferMurphy]

I have a UDF that is called from 2 sheets (Undo and Undo (20)) in one workbook. Here's the code:

Function Odds2Match(pWins2Go As Double, pLoss2Go As Long, pProbWin As Double) As Double

Debug.Print "Book:  " & Application.ActiveWorkbook.Name
Debug.Print "Sheet: " & Application.ActiveSheet.Name
Debug.Print "Cell:  " & Application.ActiveCell.Address

'Check the  number of wins & losses and the winning probability
If pWins2Go < 1 Then
  Odds2Match = CVErr(xlErrValue): Exit Function: End If
If pLoss2Go < 0 Then
  Odds2Match = CVErr(xlErrValue): Exit Function: End If
If pProbWin < 0 Or pProbWin > 1 Then
  Odds2Match = CVErr(xlErrValue): Exit Function: End If

Dim W As Long   'Number of wins to pass to binom.dist
W = pWins2Go - 1

'Add up the probability of winning with each of the remaining numbers of losses
Dim G As Long   'Number of games to pass to binom.dist
Dim odds As Double
Dim L As Double
Odds2Match = 0
'Error: Wins & losses for this game not included
For L = 0 To pLoss2Go - 1
  G = W + L
  odds = WorksheetFunction.Binom_Dist(W, G, pProbWin, False) * pProbWin
  Odds2Match = Odds2Math + odds
Next L

End Function

I was getting a wrong answer, so I set a breakpoint on the first line. Then in the Undo sheet, I ran the UDF by editing the cell that calls it and then tabbing out. The UDF runs 3 times. The first time the parameters are as from the other sheet, the second time pProbWin = 0, and the third time it has the correct values from the first sheet.

I then added the 3 debug.print statements. This is the contents of the Immediate window:

Book:  Tally with Undo.xlsm
Sheet: Undo
Cell:  $K$5
Book:  Tally with Undo.xlsm
Sheet: Undo
Cell:  $K$5
Book:  Tally with Undo.xlsm
Sheet: Undo
Cell:  $K$5

According to this, it is called 3 times from the same cell in the same sheet.

Can someone please tell me what the heck is going on?

Thanks

 

[Zot]

Looks like you are using Selection Change event to trigger a macro, right? You probably have Change event that re-trigger the macro. This perhaps the cause.

 

[RoryA]

First, it's not necessarily being called from the same cell each time, it's just that your debug statements refer to the active workbook, sheet and cell, which doesn't change.

Second, have a read of Charles WIlliams' page here: Excel User-Defined Functions - Decision Models

 

[Zyndstoff]

 Odds2Match = Odds2Math + odds

What is "Odds2Math"?

 

[Alex Blakenburg]

The only way that I could reproduce the what you described was to have the function in the spreadsheet being used in the spreadsheet 3 times and have $K$5 being one of the parameters and used by all 3 functions.
ie K5 was changed and hence the active cell, triggering 3 uses / occurences of the function to update.

 

[JenniferMurphy]

Looks like you are using Selection Change event to trigger a macro, right? You probably have Change event that re-trigger the macro. This perhaps the cause.

Sorry, I should have mentioned how it is called. It's called from cell K5 in a sheet that keep track of wins and losses in a game. The sheet has a button control with buttons for Win and Lose that each run a macro to tally the score. But here I am testing it by just opening K5 and tabbing out, which executes the Odds2Match function.

 

[JenniferMurphy]

First, it's not necessarily being called from the same cell each time, it's just that your debug statements refer to the active workbook, sheet and cell, which doesn't change.

When stepping thru the function, the parameters passed to it on the first call are exactly what they would be if called from the backup sheet. On the 2nd call, they don't match any sheet. On the third call, they match the parameters on the "active" sheet. So I thought the debug statements would show if it is being called from a different sheet.

What debug statements would I use to see where the function is being called from?

 

[JenniferMurphy]

Second, have a read of Charles WIlliams' page here: Excel User-Defined Functions - Decision Models

I'm not sure I followed all of that, but it seems like the problem may be that Excel is working its way through various dependencies. The expression in cell that calls the function is kinda complicated:

=IF(Wins2Go>0, Odds2Match(Wins2Go,LossesMax-SetGmsLstTop,LatestWinPC), BINOM.DIST(SetGmsWonTop,SetGmsTop,LatestWinPC,FALSE))

But I still don't understand why it would get called 3 times with completely different parameters.

 

[JenniferMurphy]

 Odds2Match = Odds2Math + odds

What is "Odds2Math"?

It's a typo. I guess that after I pasted the code, I accidentally deleted the "c". It should be:

 Odds2Match = Odds2Match + odds

That's how it is in the actual code.

 

[RoryA]

Application.ThisCell is the cell calling the function.