MS项目VBA -如何避免错误时，过滤器返回什么

Question

我的代码对项目应用了一个过滤器(仅显示下一周特定资源的活动)，并且我需要捕获过滤器不返回任何内容的实例。

我找到了这篇文章，这篇文章导致了这个MS条目。由此我想出了这个测试:

If ActiveSelection.Tasks Is Nothing Then GoTo NextResource

退出当前资源循环并进入下一个资源循环，但是，这不起作用。错误(424)仅在我尝试使用activesselection

时生成。上下文代码片段:

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

Answer 1

您的问题是，如果没有任务满足所应用的过滤器的标准，activesselection对象无法解析. tasks属性。我不喜欢使用GoTos在VBA中不是错误处理程序的任何东西，所以我建议创建一个单独的函数来检查过滤器中是否有任何任务:

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

现在你可以在你的代码中调用这个函数了:

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

另外，我可能会卸载你的代码来创建并应用过滤器到它自己的方法中，这样你就可以检查那里的任何错误，而不是你的主方法。