UDF gets executed twice for a single call

JenniferMurphy · Apr 28, 2019

I am working on a UDF that may be more complicated than I can handle. (sigh)

The UDF is called just once from a single cell in the only sheet in the only open workbook. I have break point (F9) set at the top of this section of code:

Code:

  . . .
Dim iCol As Long
Dim RBestI As Variant
Dim RBestIAddr As String  

  . . .
For iCol = RngColBeg To RngColEnd
 . . .
  RBestI = pRtgsBest(1, iCol)
  RBestIAddr = pRtgsBest(1, iCol).Address
  . . .
Next i
  . . .

RngColBeg & RngColEnd are global variables (type Long) calculated by a subroutine.

pRtgsBest is a range passed to the UDF from Excel.

As I step through the code, after executing the two statements inside the loop, the watch window shows that the value of RBestI is "Empty". But the cell that it is supposed to be getting its value from, (E10), contains "900".

I get these results from the Immediate window:

Code:

?rbesti

?pRtgsBest(1, iCol)
 900 
?rbestiaddr
$E$10

How can RBestI be empty when it was just set to the value in E10 (pRtgsBest(1, iCol)) which is 900 according to the Immediate window?

So, then I press F5 to resume execution. I am immediately brought back to the same break point, but this time, RBestI contains 900.

Can anyone suggest why this UDF is getting executed twice. I've checked and it is not calling itself, as far as I can tell. And why is the value in RBestI wrong the first time and right the second?

I'd appreciate any suggestions for things to try.

I'd post the code somewhere, but it is hundreds of lines.

Thanks

offthelip · Apr 28, 2019

If I was faced with this problem i would check that the indexing into prtgsbest is correct the first time through.
check what the start index for prtgsbest so check what range that is looking at, and check what value is in Rngcolbeg.
It looks to me as thought your index is starting with the wrong value.

JenniferMurphy · Apr 29, 2019

offthelip said:
If I was faced with this problem i would check that the indexing into prtgsbest is correct the first time through.
check what the start index for prtgsbest so check what range that is looking at, and check what value is in Rngcolbeg.
It looks to me as thought your index is starting with the wrong value.

OK, I'll look into the indexing. But first, why is the UDF getting executed twice? It is only called once. I've tracked the calling workbook name, worksheet name, and cell address and they are the same on both iterations.

Yongle · Apr 29, 2019

You have not given us very much to work on

A deliberately-created double-trigger

UDF to sum a range which fires once - message box appears once

Code:

Function MySum(aRange As Range) As Double
    MsgBox 1
    MySum = WorksheetFunction.Sum(aRange)
End Function

With a minor modification and a very simple Worksheet_Change event the message box appears twice

Code:

Function MySum(aRange As Range) As Double
    [COLOR=#ff0000]Application.Volatile[/COLOR]
    MsgBox 1
    MySum = WorksheetFunction.Sum(aRange)
End Function
[COLOR=#006400][I]and (in sheet module) .....[/I][/COLOR]
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Calculate
End Sub

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

1