MS Project VBA - how to avoid errors when a filter returns nothing-CodePudding

My code applies a filter to the project (to show only activities for a specific resource in the next week) and I need to trap the instances where the filter returns nothing.

I have found this article which leads to this MS Entry. From this I came up with the test:

If ActiveSelection.Tasks Is Nothing Then GoTo NextResource

to exit the current resource loop and go to the next, however, this doesn't work. The error (424) is only generated when I try to use the ActiveSelection

Code snip for context:

For Each Resource In Proj.Resources
    If Not (Resource Is Nothing) Then
    If Resource.Work > 0 Then
     'setup and apply filter for each resource
     FilterEdit name:="filter4people", TaskFilter:=True, Create:=True, OverwriteExisting:=True, FieldName:="Start", Test:="is less than or equal to", Value:=finish, ShowInMenu:=True, ShowSummaryTasks:=True
     FilterEdit name:="filter4people", TaskFilter:=True, FieldName:="", NewFieldName:="% Complete", Test:="is less than", Value:="100%", Operation:="And", ShowSummaryTasks:=True
     FilterEdit name:="filter4people", TaskFilter:=True, FieldName:="", NewFieldName:="Resource names", Test:="contains", Value:=Resource.name, Operation:="And", ShowSummaryTasks:=True
         
     FilterApply "filter4people" ' apply the filter
        If Not (Err.Number = 91 Or Err.Number = 0) Then            ' saw an error applying filter
             Err.Clear                   ' clear out the error
             GoTo NextResource           ' jump to the next resource
         End If

    Application.SelectSheet 'need to select the sheet so that ActiveSelection works properly
    
    'CStr(ActiveSelection.Tasks.Count)
    If ActiveSelection.Tasks Is Nothing Then GoTo NextResource

CodePudding user response：

Your problem is that the ActiveSelection object cannot resolve the .Tasks property if no tasks meet the criteria of the filter applied. I don't like using GoTos for anything that isn't an error handler in VBA, so I recommend creating a separate function to check if there are any tasks in the filter:

Public Function CurrentFilterHasTasks() As Boolean

   Dim result As Boolean
   On Error GoTo ErrHandler

   Application.SelectAll 'select everything in the current filter

   'Application.ActiveSelection.Tasks will fail if there are only blank rows in the active selection
   If Application.ActiveSelection.Tasks.Count > 0 Then
        result = True
   End If

   CurrentFilterHasTasks = result

   'call exit function here so the code below the error handler does not run
   Exit Function

ErrHandler:
   result = False
   CurrentFilterHasTasks = result
    
End Function

Now you can call this function in your code:

For Each Resource In Proj.Resources
    If Not (Resource Is Nothing) Then
        If Resource.Work > 0 Then
         'setup and apply filter for each resource
         FilterEdit Name:="filter4people", TaskFilter:=True, Create:=True, OverwriteExisting:=True, FieldName:="Start", test:="is less than or equal to", Value:=Finish, ShowInMenu:=True, ShowSummaryTasks:=True
         FilterEdit Name:="filter4people", TaskFilter:=True, FieldName:="", NewFieldName:="% Complete", test:="is less than", Value:="100%", Operation:="And", ShowSummaryTasks:=True
         FilterEdit Name:="filter4people", TaskFilter:=True, FieldName:="", NewFieldName:="Resource names", test:="contains", Value:=Resource.Name, Operation:="And", ShowSummaryTasks:=True
             
         FilterApply "filter4people" ' apply the filter
         
         If Not (Err.Number = 91 Or Err.Number = 0) Then ' saw an error applying filter
             Err.Clear                   ' clear out the error
             GoTo NextResource           ' jump to the next resource
         End If
        
        ''''' Calling the new function ''''''
        If CurrentFilterHasTasks Then
            'whatever you want to do with the filtered tasks here
        End If

Additionally, I would probably offload your code to create and apply the filter into it's own method so you can check for any error in there, rather than your main method.