UDF sees cell in passed range as alternating numberic and empty

[JenniferMurphy]

I may be in over my head here, but I cannot figure out what the heck is going on.

I have a complicated UDF that gets an error because one of the variables that gets data from a passed range is sometimes Empty and sometimes not. The UDF is called from several locations in the sheet. If I set the sheet calculation setting to Manual, the error never occurs. I select a cell that calls the UDF, press F2 to open it up, then tab out and it executes. Never an error.

If I switch it to Automatic, each cell that calls the UDF immediately calls it twice. The first time, the variable is empty. The second time, it has the correct data. I have verified this by stepping through the code. It tracks the address of the calling cell and the address of the cell in error. These are always the same.

The actual data in the cells are numeric. They are definitely not empty.

As I said, the UDF is very complicated (possibly too complicated for me to handle), so I will show snippets of the code that I think are relevant.

The UDF processes data in 5 fixed ranges and 1 variable range that are all passed as parameters. Here's the Function statement:

Public Function WtdRtg(pRatings As Range, _
                       pRtgsBest As Range, _
                       pRtgsWrst As Range, _
                       pRtgTypes As Range, _
                       pRtgReq As Range, _
                       pRtgWts As Range, _
                       ParamArray PArgs() _
                     ) As Variant

All of the first 6 range parameters are 1 row by 16 columns. The columns are D:S.

And here is a typical calling expression in a cell (D16 in this case).

=wtdrtg(Ratings,RtgsBest,RtgsWrst,RtgTypes,RtgReq,RtgWts)

pRatings is on the same row as the cell from where it is called. If called from anywhere on row 22, it is defined as:

='Sheet1'!$D22:$S22

The other ranges are fixed.

pRtgsBest ='Sheet1'!$D$10:$S$10
pRtgsWrst ='Sheet1'!$D$11:$S$11
pRtgTypes ='Sheet1'!$D$12:$S$12
pRtgReq   ='Sheet1'!$D$13:$S$13
pRtgWts   ='Sheet1'!$D$15:$S$15

In the UDF code, I capture the address of the calling cell and the address of each cell in the ranges as they are being processed. Here's the code for that:

Dim CallerAddr As String          'Address of calling cell
  CallerAddr = Application.Caller.Address

Dim iCol As Long                'Loop index
Dim RBestIAaddr As String
For iCol = Col1 To Coln         'Loop through columns
   . . .
  RBestI = pRtgsBest(1, iCol)     'Get the next best (max) rating
  RBestIAaddr = pRtgsBest(1, iCol).Address
  RWrstI = pRtgsWrst(1, iCol)     'Get the next worst (min) rating
  RTypeI = pRtgTypes(1, iCol)     'Get the next rating type
  RReqI = pRtgReq(1, iCol)        'Get the next required/optional setting
  RtgI = pRatings(1, iCol)        'Get next rating
   . . .
  RtgINew = WtdRtgNum(RBestI, RWrstI, RReqI, RtgI, ScaleAve, pErrMsgSw, MyName)
  . . .
Next iCol

Private Function WtdRtgNum(RBestI, RWrstI, RReqI, RtgI, ScaleAve, pErrMsgSw, MyName)
   . . .
'***** This is where the error occurs *****
If Not IsNumeric(RBestI) Or IsEmpty(RBestI) Or _
   Not IsNumeric(RWrstI) Or IsEmpty(RWrstI) Then
  If pErrMsgSw Then MsgBox "Invalid Best (" & RBestI & ") or Worst (" & RWrstI & ")", , MyName
  WtdRtgNum = CVErr(xlErrValue)   'Return error
  Exit Function
End If
   . . .
End Function

The error occurs in the WtdRtgNum routine. Both RBestI and RWrstI test as Empty. The MshgBox message is:

Invalid Best () or Worst ()

I set breakpoints in the code that calls WtdRtgNum and it also says that the variables are Empty.

Can anyone suggest what might be wrong?

 

[JenniferMurphy]

I just noticed another odd behavior. I was working on the problem in workbook A and had the VBA editor open with breakpoints in a couple of places. I opened another completely unrelated workbook (B). Every time I enter anything in B or even just execute a cell formula, A recalculates and the breakpoints are triggered. I don't know if this is typical.

 

[JenniferMurphy]

Something mysterious (to me) is going on.

I changed the sheet so that it only calls the UDF (WtdRtg) from one cell (D16). When called, the UDF should only access data on row 16 plus the fixed ranges.

When I ran it (by editing D16 then tabbing out), the UDF does what it is supposed to do on row 16, which I verified by stepping through several sections of code using breakpoints (F9). I verified the caller address (D16) and the data being processed.

But, after that call completed, the UDF was reinvoked 16 more times -- once for each cell in E6:R6. None of those cells contain calls to the UDF. They all contain the text string "n/a", but when the UDF stopped on the breakpoints, the CallerAddr showed that it was called from one of those cells.

Help!!! Does anyone have any suggestions as to what I can try to figure out what is going on?

I realize that this is difficult to analyze without seeing the actual workbook and code. I am happy to post it to a Dropbox folder, but it several screens of code and it is littered with commented out code that I am debugging.

 

[JenniferMurphy]

Here's another clue or, at least, another symptom. This behavior started about the time that I decided to move all error processing to a sub-function. Here's that code.

Private Sub WtdRtgErr(CallerName As String, CallerAddr As String, ErrMsg As String, _
                      Optional ErrCellAddr As String)
  
Const buttons As String = vbCrLf & vbCrLf & "Click:" _
                                 & vbCrLf & "     Yes = Stop" _
                                 & vbCrLf & "      No = Continue"
Dim Prefix As String      'The address of the cell in error, if any
If ErrCellAddr = "" Then  'If there is no cell address,
  Prefix = ""               'There is no prefix
Else                      'If there is a cell address,
  Prefix = Prefix & ": "    'Prefix = "Addr: "
End If

Dim msg As String
msg = Prefix & ErrMsg & buttons
Dim Button As Integer   'The button that was clicked
Button = MsgBox(msg, vbYesNo, CallerName & " (" & CallerAddr & ")")

If Button = vbYes Then Stop

End Sub

I just noticed that if an error occurs and I take the "Yes" option causing WtdRtgErr to execute the Stop statement, when I resume (F5), the extra erroneous errors then occur. But it doesn't happen all the time. I just tested it again and it worked correctly.

 

[Norie]

Are you sure the code is referring to cells/ranges in the correct worksheet/workbook?

 

[JenniferMurphy]

Are you sure the code is referring to cells/ranges in the correct worksheet/workbook?

At this point, I am not even sure of my name.

There is only the one workbook open, but it does contain several worksheets including a couple that are prior versions of the one I am working on.

I'll try moving everything but one worksheet to a different workbook.

 

[JenniferMurphy]

OK. I moved all but that one sheet to a different workbook and closed it. The open workbook has one sheet and it has one call to the UDF. The UDF is still getting called multiple times. On one call, I entered this into the Immediate window.

?application.Caller.address
Error 2023

But the same code in the UDF returns the calling cell address.

How can I query the name of the sheet and workbook where the UDF is called?