JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,687
- Office Version
- 365
- Platform
- Windows
I have a UDF that is called from 2 sheets (Undo and Undo (20)) in one workbook. Here's the code:
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:
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
VBA 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:
Code:
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