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?

 

[mcomp72]

Here is the full code 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

Dim MessageToDisplay As String
Dim SmallestValue As Double
Dim LargestValue As Double
Dim SmallestValue_Base As Double
Dim LargestValue_Base As Double
Dim SmallestValue_Fringe As Double
Dim LargestValue_Fringe As Double
Dim SmallestValue_PHW As Double
Dim LargestValue_PHW As Double
Dim CalcPercent As Double
Dim CalcType As String
Dim Method As String
Dim BaseRange As Range
Dim FringeRange As Range
Dim PHWRange As Range
Dim ItemTotalCells As Range
Dim ItemTotal As Double
Dim FirstRow As Integer
Dim LastRow As Integer
Dim NumOfRows As Integer
Dim TotalToShow As Double
Dim TotalToShowStr As String
Dim NumOfTotalsShowing As Integer
Dim FringeCol As String
Dim PHWCol As String

If ProdFee_Showing = "YES" Then NumOfTotalsShowing = NumOfTotalsShowing + 1
If Ins_Showing = "YES" Then NumOfTotalsShowing = NumOfTotalsShowing + 1
If HandFee_Showing = "YES" Then NumOfTotalsShowing = NumOfTotalsShowing + 1

If NumOfTotalsShowing = 0 Then
    MessageToDisplay = ""
    GoTo Ending
End If

'This will figure out the calcuation type that needs to be displayed.
Select Case Row

    Case Is = 1
   
        If ProdFee_Showing = "YES" Then
            CalcType = "ProdFee"
        ElseIf Ins_Showing = "YES" Then
            CalcType = "Ins"
        ElseIf HandFee_Showing = "YES" Then
            CalcType = "HandFee"
        End If
   
    Case Is = 2
   
        If NumOfTotalsShowing > 1 Then
   
            If Ins_Showing = "YES" Then
                CalcType = "Ins"
            ElseIf HandFee_Showing = "YES" Then
                CalcType = "HandFee"
            End If
           
        Else
            MessageToDisplay = ""
            GoTo Ending
        End If
   
    Case Is = 3
   
        If NumOfTotalsShowing = 3 Then
           
            If PHWCols_Showing = "NO" And LaborSect = True Then
                MessageToDisplay = ""
                GoTo Ending
            End If
       
            If HandFee_Showing = "YES" Then
                CalcType = "HandFee"
            End If
           
        Else
            MessageToDisplay = ""
            GoTo Ending
        End If
       
    Case Is = 4
   
        If NumOfTotalsShowing = 3 Then
   
            If PHWCols_Showing = "YES" Then
                MessageToDisplay = ""
                GoTo Ending
            End If
           
            If HandFee_Showing = "YES" Then
                CalcType = "HandFee"
            Else
                MessageToDisplay = ""
                GoTo Ending
            End If
       
        Else
            MessageToDisplay = ""
            GoTo Ending
        End If

End Select

'This gets the first & last row of the section, and then figures out the number of rows in the section.
FirstRow = GetFirstRow(SectionLetter) 'This is calling a separate function that returns the integer value of a global constant.
LastRow = GetLastRow(SectionLetter) 'This is calling a separate function that returns the integer value of a global constant.
NumOfRows = LastRow - FirstRow + 1

'This will set some variables based on which CalcType it is.
If CalcType = "ProdFee" Then

    MessageToDisplay = "Production Fee: "
    Method = Method_ProdFee
    CalcPercent = CalcPercent_ProdFee
    FringeCol = Fringe_ProdFee
    PHWCol = PHW_ProdFee
   
    Set BaseRange = TheRange.Columns(1).Resize(RowSize:=NumOfRows)
   
    If SectionLetter = "C" Or SectionLetter = "D" Or SectionLetter = "E" Then
        Set ItemTotalCells = TheRange.Columns(2).Resize(RowSize:=NumOfRows)
    Else
        Set FringeRange = TheRange.Columns(2).Resize(RowSize:=NumOfRows)
        Set PHWRange = TheRange.Columns(3).Resize(RowSize:=NumOfRows)
        Set ItemTotalCells = TheRange.Columns(4).Resize(RowSize:=NumOfRows)
    End If
   
ElseIf CalcType = "Ins" Then

    MessageToDisplay = "Insurance: "
    Method = Method_Ins
    CalcPercent = CalcPercent_Ins
    FringeCol = Fringe_Ins
    PHWCol = PHW_Ins
   
    If SectionLetter = "C" Or SectionLetter = "D" Or SectionLetter = "E" Then
        Set BaseRange = TheRange.Columns(3).Resize(RowSize:=NumOfRows)
        Set ItemTotalCells = TheRange.Columns(4).Resize(RowSize:=NumOfRows)
    Else
        Set BaseRange = TheRange.Columns(5).Resize(RowSize:=NumOfRows)
        Set FringeRange = TheRange.Columns(6).Resize(RowSize:=NumOfRows)
        Set PHWRange = TheRange.Columns(7).Resize(RowSize:=NumOfRows)
        Set ItemTotalCells = TheRange.Columns(8).Resize(RowSize:=NumOfRows)
    End If
   
ElseIf CalcType = "HandFee" Then

    MessageToDisplay = "Handling Fee: "
    Method = Method_HandFee
    CalcPercent = CalcPercent_HandFee
    FringeCol = Fringe_HandFee
    PHWCol = PHW_HandFee
   
    If SectionLetter = "C" Or SectionLetter = "D" Or SectionLetter = "E" Then
        Set BaseRange = TheRange.Columns(5).Resize(RowSize:=NumOfRows)
        Set ItemTotalCells = TheRange.Columns(6).Resize(RowSize:=NumOfRows)
    Else
        Set BaseRange = TheRange.Columns(9).Resize(RowSize:=NumOfRows)
        Set FringeRange = TheRange.Columns(10).Resize(RowSize:=NumOfRows)
        Set PHWRange = TheRange.Columns(11).Resize(RowSize:=NumOfRows)
        Set ItemTotalCells = TheRange.Columns(12).Resize(RowSize:=NumOfRows)
    End If
   
End If

'This will set the MessageToDisplay, based on the Method that is being used.
If Method = "Manual" Then

    MessageToDisplay = MessageToDisplay & "set to manual entry"
   
ElseIf Method = "Calc" Then
   
    TotalToShow = Round(SectionTotal * CalcPercent / 100, 2)
    TotalToShowStr = Format(TotalToShow, "#,##0.00")
    MessageToDisplay = MessageToDisplay & CalcPercent & "%; $" & TotalToShowStr
   
ElseIf Method = "Match" Then

    MessageToDisplay = MessageToDisplay & "set to match Estimate"

ElseIf Method = "Line" Then

    If LaborSect = False Then
   
        SmallestValue = Evaluate("MIN(0+(0&" & BaseRange.Address & "))")
        LargestValue = Evaluate("MAX(0+(0&" & BaseRange.Address & "))")
   
    Else
   
        SmallestValue_Base = Evaluate("MIN(0+(0&" & BaseRange.Address & "))")
        LargestValue_Base = Evaluate("MAX(0+(0&" & BaseRange.Address & "))")
       
        SmallestValue_Fringe = Evaluate("MIN(0+(0&" & FringeRange.Address & "))")
        LargestValue_Fringe = Evaluate("MAX(0+(0&" & FringeRange.Address & "))")
       
        SmallestValue_PHW = Evaluate("MIN(0+(0&" & PHWRange.Address & "))")
        LargestValue_PHW = Evaluate("MAX(0+(0&" & PHWRange.Address & "))")
       
        'This figures out the smallest value between SmallestValue_Base, SmallestValue_Fringe, and SmallestValue_PHW.
        If FringeCol = "YES" And SmallestValue_Fringe < SmallestValue_Base Then
            SmallestValue = SmallestValue_Fringe
        Else
            SmallestValue = SmallestValue_Base
        End If
           
        If PHWCol = "YES" And SmallestValue_PHW < SmallestValue Then SmallestValue = SmallestValue_PHW
       
        'This figures out the largest value between LargestValue_Base, LargestValue_Fringe, and LargestValue_PHW.
        If FringeCol = "YES" And LargestValue_Fringe > LargestValue_Base Then
            LargestValue = LargestValue_Fringe
        Else
            LargestValue = LargestValue_Base
        End If
           
        If PHWCol = "YES" And LargestValue_PHW > LargestValue Then LargestValue = LargestValue_PHW
       
    End If
   
    ItemTotal = Application.WorksheetFunction.Sum(ItemTotalCells)
   
    If SmallestValue <> 0 Then SmallestValue = SmallestValue * 100
    If LargestValue <> 0 Then LargestValue = LargestValue * 100
   
    TotalToShow = Round(ItemTotal, 2)
    TotalToShowStr = Format(TotalToShow, "#,##0.00")
   
    If SmallestValue = LargestValue Then
        MessageToDisplay = MessageToDisplay & SmallestValue & "%; $" & TotalToShowStr
    Else
        MessageToDisplay = MessageToDisplay & SmallestValue & "%-" & LargestValue & "%; $" & TotalToShowStr
    End If
   
End If

Ending:
Set BaseRange = Nothing
Set FringeRange = Nothing
Set PHWRange = Nothing
Set ItemTotalCells = Nothing
PIHMessage = MessageToDisplay

End Function

 

[mcomp72]

I just noticed in the cell where I call the function, on a couple of occasions I have a blank space after a comma. Does a blank space after a comma in a function cause any issue? I assume if it did, Excel would tell me there was an issue with my formula.

 

[RoryA]

All your evaluate calls will evaluate in the context of the active sheet, which is probably not what you intended.

 

[mcomp72]

All your evaluate calls will evaluate in the context of the active sheet, which is probably not what you intended.

Ah... interesting. This is my first time using Evaluate, and honestly, didn't even know about it before trying it here.

Is it a problem with using Evaluate in general, or does it have to do with how the lines of code are written? Any suggestion for how I could get Evaluate to work regardless of what the ActiveSheet is?

Evaluate was actually recommended by another user of this forum, from this thread.

If Evaluate isn't going to work outside of the ActiveSheet, any thoughts on how I could accomplish the same thing without relying on it?

 

[RoryA]

There are two versions of Evaluate. The one you are using is Application.Evaluate, which works in the context of the active sheet. There is also a Worksheet.Evaluate which works in the context of the specified worksheet. So it looks to me like what you want is to use the worksheet version - for example:

SmallestValue = BaseRange.Worksheet.Evaluate("MIN(0+(0&" & BaseRange.Address & "))")

rather than:

SmallestValue = Evaluate("MIN(0+(0&" & BaseRange.Address & "))")

You could also include a full address including sheet name in the Application.Evaluate version, but I prefer the Worksheet version.

 

[Peter_SSs]

Any suggestion for how I could get Evaluate to work regardless of what the ActiveSheet is?

Another way, effectively including the full address as Rory has suggested, is to use the following syntax

SmallestValue = Evaluate("MIN(0+(0&" & BaseRange.Address(External:=True) & "))")

 

[RoryA]

Probably ok here but just bear in mind that Evaluate only accepts a 255 character formula string so when you start including file paths and names, you can run into problems quite quickly.

 

[mcomp72]

Thanks very much to both of you for the help! I tried using the Worksheet version of Evaluate, and it seems to be working. I took out NOW() that I had been passing to the function as a work-around, and it still seems to work. No more #VALUE! appearing in those cells! Thanks again!