UDF returns #Value Error when other sheets are recalculated.

[ebraatz]

I have a UDF called CountPeriod which is a glorified version of CountIf. The workbook is used to tally student attendance. There is one tab per month with a linear calendar at the top of the page. To the Right, I need to tally the absences and tardies for each student based on which of 3 Periods/Terms that particular month is in. These monthly tallies are all totaled on a finally summary sheet. The Start and End Dates are named ranges on a separate sheet. Based on these dates, the UDF determines what range to include in the CountIf Function. The function is working as needed except that it returns a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] error unless forced to recalculate via Ctrl+Shft+Alt+F9. This Workbook is being designed for an excel novice so I need to figure out how to get this straightened out. The [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] error is only coming in where PeriodRange (in the code below) Is Not Nothing. In essence, where I should get a returned not-Zero value, I get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] . Like I said, when I force the calculation, I get the correct result.

Function CountPeriod(StartDate As Range, EndDate As Range, DateRange As Range, _
                        CountRange As Range, CountWhat1 As Variant, Optional CountWhat2 As Variant, _
                        Optional CountWhat3 As Variant) As Long
                                            
    Dim FirstDate As Range
    Dim LastDate As Range
    Dim MatchED As Range
    Dim MatchSD As Range
    Dim PeriodRange As Range
    
    Dim m As Long
    Dim R As Long
              
    If DateRange.Rows.Count > 1 Then
        MsgBox "DateRange must be a singular row"
    End If
    
    Set FirstDate = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(1).Column)          'First day of selected DateRange
       
    With DateRange                                                      'Find last date in DateRange
        Set LastDate = .Find("*", After:=.Cells(1), _
            LookIn:=xlValues, searchorder:=xlByColumns, Searchdirection:=xlPrevious)
    End With

    'Find where the Period starts and ends in relation to the selected DateRange
    Select Case StartDate.Value
        
        Case Is < FirstDate.Value               'Period begins before the selected DateRange begins
            
            Select Case EndDate.Value
                    
                Case Is > FirstDate.Value       'Period Ends after selected DateRange begins
                    
                    Select Case EndDate.Value
                            
                        Case Is > LastDate.Value    'Period starts before and ends after selected DateRange
                                
                            Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
                                                            
                        Case Is < LastDate.Value    'Period begins before daterange starts, but ends before DateRange ends
                                
                            m = Application.Match(CLng(CDate(EndDate.Value)), DateRange, 0)
                            Set MatchED = Cells(DateRange.Row, DateRange.Columns(m).Column)
                                
                            Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, MatchED.Column))
                             
                        Case Is = LastDate.Value    'Period begins before daterange starts and ends on last day of DateRange
                                
                            Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
                            
                                
                    End Select
                
                Case Is < FirstDate.Value   'Period Ends before selected daterange begins
                    
                    CountPeriod = 0
                    
                Case Is = FirstDate.Value   'Period ends on first day of Daterange
            
                    Set PeriodRange = DateRange.Worksheet.Cells(CountRange.Row, FirstDate.Column)
                        
            End Select
            
        Case Is > FirstDate.Value               'Period begins after the selected DateRange begins
        
            Select Case StartDate.Value
            
                Case Is > LastDate.Value        'Period begins after daterange ends
                
                    CountPeriod = 0
            
                Case Is < LastDate.Value        'Period begins after start, but before end of DateRange
                
                    m = Application.Match(CLng(CDate(StartDate.Value)), DateRange, 0)
                    Set MatchSD = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(m).Column)
                    
                    Select Case EndDate.Value
                        
                        Case Is < LastDate.Value    'Period ends before the DateRange Ends - Period is contained entirely within daterange
                                                
                            m = Application.Match(CLng(CDate(LastDate.Value)), DateRange, 0)
                            Set MatchED = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(m).Column)
                            
                            Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, MatchED.Column))
                        
                        Case Is > LastDate.Value    'period ends after DateRange Ends
                            
                            Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, LastDate.Column))
                                                    
                        Case Is = LastDate.Value    'Period ends on last day of date range
                        
                            Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, LastDate.Column))
                        
                    End Select
                
                Case Is = LastDate.Value        'Period begins on last day of DateRange
                    
                    Set PeriodRange = DateRange.Worksheet.Cells(CountRange.Row, LastDate.Column)
                
            End Select
            
        Case Is = FirstDate.Value               'Period begins on the first day of selected DateRange
        
            Select Case EndDate.Value
            
                Case Is < LastDate.Value        'Period ends before last day of daterange
                
                    m = Application.Match(CLng(CDate(EndDate.Value)), DateRange, 0)
                    Set MatchED = DateRange.Worksheet.Cells(DateRange.Row, DateRange.Columns(m).Column)
                    
                    Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, MatchED.Column))
                                    
                Case Is > LastDate.Value        'Period ends after last day of daterange
                
                    Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
                
                Case Is = LastDate.Value        'period ends on last day of daterange
                
                    Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, FirstDate.Column), Cells(CountRange.Row, LastDate.Column))
                
            End Select
    End Select
    
    
    If PeriodRange Is Nothing Then
    
        CountPeriod = 0
        Else:
        If IsMissing(CountWhat3) = True Then
            If IsMissing(CountWhat2) = True Then
                CountPeriod = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1)
                Else:
                    CountPeriod = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1) _
                                + Application.WorksheetFunction.CountIf(PeriodRange, CountWhat2)
            End If
            Else:
                CountPeriod = Application.WorksheetFunction.CountIf(PeriodRange, CountWhat1) _
                                + Application.WorksheetFunction.CountIf(PeriodRange, CountWhat2) _
                                + Application.WorksheetFunction.CountIf(PeriodRange, CountWhat3)
                                
        End If
    End If
    
                       
End Function

Also, There are approximately 900 cells per sheet with this formula and calculation is running slow. If there are any recommendations on speeding this process, that would be appreciated.

 

[JoeMo]

Have you tried adding Application.Volatile immediately after your Dim statements to force the function to recalculate whenever any part of the workbook is calculated?

 

[ebraatz]

I'm a bit leary of using that because it's already running slower than I like. I have 10 sheets that have approx 900 iterations of this formula. As will as multiple formulas on the other sheets. I'm willing to try it, but I'm worried about it slowing everything down.

 

[ebraatz]

So I tried the Application.Volatile, but for whatever reason, it only forced the calculation on a single column, not all 9, and not all sheets.

For speed of data entry, I'm leaning towards using this code:

Sub Worksheet_Activate()

Activeworkbook.EnableCalculation = False

End Sub

Sub Worksheet_Deactivate()

ActiveWorkbook.EnableCalculation = True

Sub

Would the Application.Volatile still work with that? Does the Worksheet_Deactivate still trigger if they close the workbook with the sheet still active?

 

[JoeMo]

So I tried the Application.Volatile, but for whatever reason, it only forced the calculation on a single column, not all 9, and not all sheets.

For speed of data entry, I'm leaning towards using this code:

Sub Worksheet_Activate()

Activeworkbook.EnableCalculation = False

End Sub

Sub Worksheet_Deactivate()

ActiveWorkbook.EnableCalculation = True

Sub

Would the Application.Volatile still work with that? Does the Worksheet_Deactivate still trigger if they close the workbook with the sheet still active?

For question 1: I don't know, you have to try it and see. You can also put the body of your Worksheet_Deactivate event code in a Workbook_BeforeClose event module (in Thisworkbook) to cover closes that come with the sheet still active.

 

[ebraatz]

Okay. I'll play with that some more. I tried to use that code (toggling calculation) earlier and still had a # value error when after leaving the sheet and coming back to it again. Any idea why I'm getting the error?

 

[JoeMo]

Okay. I'll play with that some more. I tried to use that code (toggling calculation) earlier and still had a # value error when after leaving the sheet and coming back to it again. Any idea why I'm getting the error?

Have you tried stepping through the function using the F8 key to see where the #VALUE error occurs?

 

[ebraatz]

Have you tried stepping through the function using the F8 key to see where the #VALUE error occurs?

So I don't know what I'm doing wrong, but I have never been able to make F8 work. Whether using the keyboard or the Menu in VBE.

 

[ebraatz]

Okay, So I did a little more digging around to figure out how to step through the code. (Had to add the Debug Toolbar and use those buttons). Everything worked as it was supposed to. Like I said before, the code does what it's supposed to do, but only when manually forced to calculate. And even if I manually calculate sheet 1, when I manually calculate Sheet 2, Sheet1 reverts to #Value errors again.

 

[ebraatz]

So I've been doing some digging around and hypothesizing. I've discovered that my Test Sub wasn't producing errors because the referenced sheet was active. As soon as I tried with a different active sheet I started getting problems. The problem line is this:

Set PeriodRange = DateRange.Worksheet.Range(Cells(CountRange.Row, MatchSD.Column), Cells(CountRange.Row, LastDate.Column))

I get a Method Range of Object Worksheet Failed. Any ideas?