Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.

[OaklandJim]

Team Mr. Excel

I have an investments portfolio workbook. I have UDFs that I use to do roll-up values for columns in the investments data worksheet. UDFs work well except that they all give a #VALUE! error if I hide empty rows using VBA. That is, I have a sub (see below) that toggles hidden property of the empty rows in the data. Oddly, this happens if I run the sub in the portfolio worksheet OR in other worksheets that do not include the UDFs.

I found that if I go to the formula bar, "touch" an offending formula with the UDF call, then hit the green checkmark, the errors go away. So, I came up with what seems to be a klugey way to address the issue, using the line of code below, at the end of the sub that toggles rows' hidden property.

[Portfolio].Range("RollUpValues").Formula = [Portfolio].Range("RollUpValues").Formula

What might I try to fix this issue, if anything? Should I just live with the kluge-code "solution"?

Option Explicit

Sub ToggleEmptyRows()
    
    Dim oButton As Object
    
    Dim rCell As Range

    Set oButton = ActiveSheet.Buttons(Application.Caller)
    
    Application.ScreenUpdating = False

    If oButton.Caption Like "Hide*" _
    Then
        
        With ActiveSheet
        
    '       Loop all cells in the range named "Symbols" and hide unused rows.
            For Each rCell In .Range("Symbols")
                
                If rCell = "" _
                 Then
                    rCell.EntireRow.Hidden = True
                Else
                    rCell.EntireRow.Hidden = False
                End If
                
            Next rCell
        
        End With
    
        oButton.Caption = "Show Empty Rows"
    
    Else

        ActiveSheet.Range("Symbols").EntireRow.Hidden = False
        oButton.Caption = "Hide Empty Rows"
    End If
    
    [Portfolio].Range("RollUpValues").Formula = [Portfolio].Range("RollUpValues").Formula

End Sub

 

[6StringJazzer]

When a UDF returns #VALUE it's usually because it raises an error. I would write a test driver to call a UDF and try to identify the error. That's better than trying to work around an error when you don't even know what's causing it.

Write this for a UDF returning VALUE. Populate the arguments with the thing being used in the worksheet where it's called. Step through it with F8 to see where it fails. When it's called from a Sub instead of the worksheet you can trace the error.

Sub TestUDF()

   Debug.Print MyUDF(Arg1, Arg2, ...)

End Sub

 

[OaklandJim]

Thanks very much for the response.

I tried to do as suggested. I wrote code below. If the one call to the UDF being tested in a worksheet cell does not show the #VALUE! error then debug.print prints out the correct value. BUT, if that one call to the UDF in the worksheet cell DOES show #VALUE! error then debug.print does nothing. The code executes up to the MsgBox for both scenarios.

Sub TestError()

    Dim rValuesToWeight As Range
    
    Dim rWeightingValues As Range
    
    Set rValuesToWeight = [Portfolio].Range("IsCashFlags")
    
    Set rWeightingValues = [Portfolio].Range("Values")

MsgBox "before debug.print"

Debug.Print PortionCash(rValuesToWeight, rWeightingValues)

End Sub

Here's another seemingly odd behavior. Sometimes I can get rid of an error by clicking on an offending cell -- one in which the UDF gives the #VALUE! error -- and then "resetting" the UDF (my word) by clicking on it in the formula bar then clicking the green checkmark. Sometimes doing that does not get rid of the error. Sometimes such "resetting" of the UDF gets rid of ALL the UDF #VALUE! errors. (There are 12 calls to four different UDFs in worksheet cells.) Sometimes not.

Furthermore, if I cut the code for an offending UDF from its code module, save the file, then paste the code back in THAT seems to clear up the errors. Sometimes just making a minor "cosmetic" change to the code -- no LOGIC changes -- fixes the problem.

I tried making rows zero height instead of hiding rows which does not fix the errors.

Unfortunately, in this apparently unusual case, I'm not sure that debugging the UDFs will help. I conclude that there is seems to be an unusual conflict between my UDF calls and Excel's guts and that the UDFs' code/logic works as desired.

Here is the code that leads to the UDF errors.

Option Explicit

Sub ToggleEmptyRows()
    
    Dim oButton As Object
    
    Dim rCell As Range
    
    Dim rHideRange As Range
    
    Const iAllRowsCount = 100
    
    Dim iRowsToHide As Long

    Set oButton = ActiveSheet.Buttons(Application.Caller)
    
    Application.ScreenUpdating = False

'   If caller button includes the word Hide then user wants to hide unused rows.
    If oButton.Caption Like "Hide*" _
     Then
        
        With ActiveSheet
        
    '       Loop all cells in the range named "Symbols" and count unused rows.
            For Each rCell In .Range("Symbols")
            
                If rCell = "" Then iRowsToHide = iRowsToHide + 1
                                
            Next rCell
        
        End With
        
        Set rHideRange = [Portfolio].Range("Symbols").Cells(iAllRowsCount - iRowsToHide + 1).Resize(iRowsToHide)
        
        'rHideRange.EntireRow.RowHeight = 0    '<= still get errors
    
        'rHideRange.EntireRow.RowHeight = 0.01 '<= still get errors
    
        rHideRange.EntireRow.Hidden = True
        
        oButton.Caption = "Show Empty Rows"
    
    Else

        ActiveSheet.Range("Symbols").EntireRow.Hidden = False
        
        oButton.Caption = "Hide Empty Rows"
    
    End If
    
'   "Reinstating" offending formulas is necessary to avoid #VALUE! errors.
    [Portfolio].Range("RollUpValues").Formula = [Portfolio].Range("RollUpValues").Formula

End Sub