UDF gets called 3 times

JenniferMurphy

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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top