Odd breakpoint behavior

[JenniferMurphy]

I have a little UDF that returns a random element from a list. While trying to debug it, I set breakpoints so I can trace the behavior. I then go back to the sheet, open the cell containing the call to the UDF by pressing F2, then tabbing out. This has always worked in the past, thousands of times.

For some reason, it is not working with this UDF with this call from this sheet. The UDF is called many times from many cells on that sheet and others in that workbook. If I execute the code from any cell but one, it stops at the breakpoint and I am able to see the values in the variables. But if I call it from one particular cell, the values in the Range parameter are all zero. If I let the UDF complete once or twice, on the second or third time the values are correct. It's like the first time or two is was actually getting called from a different cell. Curious about that, I changed the sheet from Automatic to Manual. Then it works correctly. What the heck is going on?

Here's a snippet of the code.

Public Function WtdRnd(pValues As Range, Optional pWt As Double = 1) As Long

Dim i As Long
Dim NumVals As Long
Dim CumVal() As Double
NumVals = pValues.Count
ReDim CumVal(0 To NumVals) As Double
CumVal(0) = 0

'Generate the cumulative values
For i = 1 To NumVals
  CumVal(i) = CumVal(i - 1) + pValues(i)
Next i

  . . .

This is the column of data that doesn't work. It's actually much longer (60-70 cells).

???

 

[RoryA]

Are you saying that the breakpoint doesn't actually work when called from that cell, or just that the values aren't what you expect when the breakpoint is hit? If the latter, I suspect a calculation dependency - is the source range made up of literal values or formulas?

 

[mole999]

if you change to more decimal places are they still equal