User Defined Function returns #VALUE! but code is correct

[mcomp72]

I’ve noticed some strange behavior in a User Defined Function that I use on a few sheets. It will sometimes return “#VALUE!” in certain cells, but not all.

This is a rather complex function that accepts as parameters different cells across a three different sheets – the sheet the function exists on, plus two others. The values of these cells can change, and then they do, what is displayed in the cell that holds the function needs to change.

I’ve tested the code in the function, and it works. If I select the cell that has the function in it, click in the formula bar, go to the end of the text in the formula bar, and then press Enter, “#VALUE!” goes away in that cell and the function displays the proper result. This makes no sense to me, because I didn’t change anything in the formula bar, so the calculation the function was doing shouldn’t have been affected at all.

I saw on another forum post that I could place a breakpoint in the function, then select the cell with the function and press F2 – that would allow me to step through the code to see what line was causing the error. I made the breakpoint the very first line of the function (Public Function…), and no error occurred in the code as I stepped though it. After completing this, the cell no longer displayed “#VALUE!” – it displayed the result of the function correctly.

I’ve noticed that “#VALUE!” is appearing in the cells after I run some VBA code that affects the sheets. I tried putting Application.CalculateFullRebuild at the end of this code, but that hasn’t helped.

I even tried running a sub that only contains Application.CalculateFullRebuild, once I notice “#VALUE!” in the cells. If I do this, it will fix the problem, but only on the ActiveSheet. It doesn’t fix it on any of the others. In fact, some of the other cells that hold this function that were displaying the correct result before (cells that that aren’t on the ActiveSheet), go back to showing “#VALUE!”

If I make a change to one of the cells that is being sent to the function, then “#VALUE!” goes away (in that cell) and the function displays what it should. Then if I set that cell back to what it was before, the function continues to display properly.

So, it doesn’t appear there is anything wrong with the code in the function. This seems to be a problem with Excel itself.

This will become a big problem for my users, so I’d love to be able to solve this. Anyone have any ideas what might be going on here?

 

[6StringJazzer]

I'll bet there is something wrong with the code in the function. It is possible that there is a bug in Excel but more likely that a VBA developer has made an error.

Also, confirm that you have your Calculation option to set to Automatic.

However, we have no hope of diagnosing this without your code. Please provide the code for the function, as well as a description of how you would use it on a worksheet. It would be even better if you could tell us how to use this code to reproduce the error you are seeing.

When I debug a UDF I write a driver for it so I can step through the code, rather than trying to get it to run from the worksheet.

Private Sub TestUDF()

   Debug.Print MyUDF(Range("A1"), Range("B2"))

End Sub

 

[mcomp72]

Yes, I have Calculation set to Automatic in the code when the workbook first opens.

I can't for the life of me figure out what might be wrong with the code, since I stepped through it and never received an error.

As for your suggestion about the driver -- yes, I have done that as well, and when I step through it that way, it also doesn't cause an error. So I've stepped through it with two different ways, and it doesn't cause an error.

Since I made my original post, I've continuing to search online for a solution. I found a reply to a forum post on Microsoft's site that suggested creating a unneeded parameter in the function, and pass "NOW()" to that parameter. I did that, and now it seems to work okay. Well, except for on whatever sheet I was last viewing. But if I switch away from that sheet and then back to it, the cells with the UDF in them display correctly. Apparentely this causes the function to recalculate. No idea why it wasn't recalculating with Application.CalculateFullRebuild.

The one downside I have discovered to this work-around is that one of my buttons, which opens a UserForm, now takes 2-3 seconds to open the UserForm, when before it did it pretty much instantly. I can only assume adding NOW() as a parameter is somehow slowing down the workbook.

 

[6StringJazzer]

What are the arguments to your function? Sometimes a function needs to be Application.Volatile, but that should be used only when necessary since it will cause frequent recalculation of the function regardless of whether it's actually needed.

 

[mcomp72]

Here is the first line of the function:

Public Function PIHMessage(SectionLetter As String, Row As Integer, LaborSect As Boolean, _
SectionTotal As Double, TheRange As Range, CalcPercent_ProdFee As Double, _
CalcPercent_Ins As Double, CalcPercent_HandFee As Double, Method_ProdFee As String, Method_Ins As String, _
Method_HandFee As String, Fringe_ProdFee As String, Fringe_Ins As String, Fringe_HandFee As String, _
PHW_ProdFee As String, PHW_Ins As String, PHW_HandFee As String, ProdFee_Showing As String, _
Ins_Showing As String, HandFee_Showing As String, PHWCols_Showing As String, XXX As Variant) As String

TheRange is 12 columns worth of data. The number of rows in TheRange depends on which sheet the UDF is one. The number of rows is never less than 5, and never more than 50.
SectionLetter is a single letter that I type into the formula bar directly.
Row is either 1, 2, 3, or 4. Again, I type this into the formula bar directly.
LaborSect is a boolean that I type into the formula bar directly.
XXX is where I send NOW() to the function to avoid the #VALUE! error.
All of the others are sent single cells, most of which are on the same page that UDF is one, but a few are on two other sheets.

Here's an example of how I use the UDF on one of the worksheets.

=PIHMessage("F",1,FALSE,AM25,N8:Y24,PFI_Est_ProdFee_F,PFI_Est_Ins_F,PFI_Est_HandFee_F,PIHOptions_Est_ProdFee, PIHOptions_Est_Ins, PIHOptions_Est_HandFee,PIHOptions_Est_ProdFee_Fringe,PIHOptions_Est_Ins_Fringe,PIHOptions_Est_HandFee_Fringe, PIHOptions_Est_ProdFee_PHW, PIHOptions_Est_Ins_PHW,PIHOptions_Est_HandFee_PHW,Options_General_ShowProdFeeBottom,Options_General_ShowInsBottom, Options_General_ShowHandFeeBottom, Options_PHW_ShowPHWCol,NOW())

I have a lot of named ranges in my workbook, but these are referring to single cells. None of the name ranges being passed to this function contain more than one cell each.

 

[RoryA]

As 6SJ said earlier, it's more likely there is an issue with your code, such as an unqualified range reference, particularly since you mention that the behaviour is different when a sheet isn't active.

 

[mcomp72]

As 6SJ said earlier, it's more likely there is an issue with your code, such as an unqualified range reference, particularly since you mention that the behaviour is different when a sheet isn't active.

I could paste the code, but it is 250 lines long, so I hated to ask anyone to look at code that long. But if you'd really like to see it, let me know and I can add it to my next reply.

But let me ask you this: if there was a problem with the code, wouldn't an error occur when I was stepping through it? I have done that several times and no errors occur. Are there cases where stepping through the code won't encounter an error, but then running the code normally will encounter an error?

 

[RoryA]

Yes, there are such cases - usually timing issues. Your initial descriptions of the error sound more like errors with unqualified objects. Are you saying the same code under the exact same circumstances fails without breakpoints?

 

[6StringJazzer]

250 lines is not long. Paste in the code and then apply code tags (select the code and click the VBA button)

 

[mcomp72]

Yes, there are such cases - usually timing issues. Your initial descriptions of the error sound more like errors with unqualified objects. Are you saying the same code under the exact same circumstances fails without breakpoints?

Before I added the parameter that I am now passing NOW() to, the result of the formula that called the function would sometimes be "#VALUE!". But if I then added a breakpoint at the beginning of the function, selected the cell that held the function, pressed F2, and then stepped through the code, no error would happen, and the cell would no longer display "#VALUE!". It would show the correct output from the function. So yes, it did give the error, but then when I stepped through the code, it would not give the error.