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

 

[JenniferMurphy]

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.

That's what I thought. But I carefully searched the entire workbook and it is only mentioned (called) twice.