UDF gets executed twice for a single call

[JenniferMurphy]

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:

  . . .
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:

?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]

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]

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]

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

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

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

	A​	B​
1​	1​
2​	2​
3​	3​
4​	4​
5​	5​
6​	15​	=mysum(A1:A5)

Sheet: Sheet1

 

[JenniferMurphy]

You have not given us very much to work on

Yes, I know. But as I said, the UDF is quite lengthy, so I am struggling with which parts to post.

I'm working on a response with more information, but to reply to two of you points:

1. There are no Application.Volatile statements.
2. There are no workbook or worksheet modules. All of the code is in a separate module in one of my add-ins.

 

[RoryA]

Always worth reading Charles Williams: http://www.decisionmodels.com/calcsecretsj.htm#value

 

[JenniferMurphy]

I thought I figured out why my UDF is getting called twice and that it might also explain why the data values are different. There is a reference that could be termed circular. I got it to fail once, but now I cannot repeat it.

Here's a sample table. The application calculates a weighted rating for various products on several features. The UDF is passed 6 ranges. In this table, the calls are in cells D12-D14. The formulas are shown in I12-I14. The ranges defined as follows: Ratings (=Test!$D12:$H12), RtgsBest (=Test!$D$7:$H$7), RtgsWrst (=Test!$D$8:$H$8), RtgTypes (=Test!$D$9:$H$9), RtgReq (=Test!$D$10:$H$10), and RtgWts (=Test!$D$11:$H$11). All but Ratings are absolute in both the rows and columns. Ratings is absolute in the columns, but the rows adjust to match the calling cell row.

R/C	C	D	E	F	G	H	I
3		Features	Price	Wt	Hgt	End	Comments
4		Max Values	$400	260	80	End	E4: =MAX(OFFSET(Header E:E,1,0):OFFSET(Footer E:E,-1,0))
5		Min Values	$236	110	59	End	E5: =MIN(OFFSET(Header E:E,1,0):OFFSET(Footer E:E,-1,0))
6		Ave Values	$295	167	67	End	E6: =AVERAGE(OFFSET(Header E:E,1,0):OFFSET(Footer E:E,-1,0))
7		Best Values	$200	100	80	End	The best (top rated) values for each feature
8		Worst Values	$400	300	59	End	The worst (bottom rated) values for each feature
9		Scale Types	Num	Num	Num	End	Specifies the type of feature
10		Opt/Req Flags	Opt	Req	Opt	End	Specifies if the feature is required or optional
11		Weights	6	2	8	End	Specifies the relative weight for each feature
12	Prod 1	45.89	$250	130	62	End	D12: =wtdrtg(Ratings,RtgsBest,RtgsWrst,RtgTypes,RtgReq,RtgWts)
13	Prod 2	42.63	$236	110	59	End	D13: =wtdrtg(Ratings,RtgsBest,RtgsWrst,RtgTypes,RtgReq,RtgWts)
14	Prod 3	52.50	$400	260	80	End	D14: =wtdrtg(Ratings,RtgsBest,RtgsWrst,RtgTypes,RtgReq,RtgWts)
15	Ftr	Ftr	Ftr	Ftr	Ftr	Ftr

<tbody>
</tbody>

The data columns are E:G. In this example there are just 3 features to be rated, Price, Wt, & Hgt. But the ranges include columns D & H, the column just before the first data column and the one just after the last data column. This allows me to add columns without having to redefine the ranges. They dynamically redefine themselves. So the UDF has to start indexing at "2" and stop at N-1, where N is the number of columns in the extended ranges.

The table as shown above works without any problems. The UDF is not called twice from each cell and all of the values match.

The problem arises when I change the contents of any of the Best or Worst cells from a literal value to a formula. Consider E7. This is the best (highest rated) value for the Price feature. Products that cost $200 or less will get the highest rating. Products that cost $400 or more will get the lowest rating. Products that cost somewhere in between will get a prorated rating.

As long as these are literal values, everything is fine.

Now consider rows 4 & 5. Row 4, as can be seen from the formula I4-I5, is the maximum value for all of the products. Similarly, row 5 in the minimum. Header is defined as "=Test!$11:$11" and Footer as "=Test!$15:$15". These serve the same purpose of allowing me to add products without having to adjust the formulas for max, min, and average. The maximum of $250, $236, & 400 is $400 and the minimum in $236.

Now suppose I want to dynamically adjust the Best/Worst values to be the same as the Max/Min values (or the Min/Max values if lower is better than higher, as in this case with Price). If I add a new product that costs more than $400 or less than $200, I want the ranges to automatically adjust. I could replace the $200 in E7 with "=MinValue", where MinValue is defined as "=Test!$5:$5". The first time I did that, the UDF was called 4 times. I believe the first time the value the UDF saw for E7 was empty or null. The next three times, it was not. I say "I believe", because I didn't pay close attention thinking I could reproduce the condition. But now I have changed to a literal and back to an expression ad I can't get it to fail.

My theory was that since MinValue based on all of the ratings but the one associated with the calling cell was also passed as part of the Ratings range, it resulted in a circular reference. But now that is not happening.

I've had enough for tonight. I'm going to bed. I'll try again in the morning.