I have a macro I use for Microsoft Project that loops through each task in the project, and performs several checks to find any problems with the tasks. These checks include several IF and Select Case statements. When dealing with large projects with more tasks, the macro can get lengthy. Is there anything I can do to improve the speed up the macro? I have already turned off screen updating and manual calculation.
我有一个用于Microsoft Project的宏,它循环遍历项目中的每个任务,并执行几个检查以查找任务的任何问题。这些检查包括几个IF和Select Case语句。处理具有更多任务的大型项目时,宏可能会变得冗长。我能做些什么来提高宏的速度吗?我已经关闭了屏幕更新和手动计算。
3 个解决方案
#1
Turning off screen updating and setting calculation mode to Manual are the only application settings you can use to improve performance; the rest depends on your algorithm.
关闭屏幕更新并将计算模式设置为手动是您可以用来提高性能的唯一应用程序设置;其余的取决于你的算法。
Your description of the problem is a bit vague: How large are your projects and how long does the macro take? If your projects are 1,000 tasks and you are making a dozen checks and your code takes more than five minutes, then yes, there is surely room for improvement. But if it's 20,000 tasks and 50 checks and the macro takes two minutes, stop trying to improve it--that's great performance.
您对问题的描述有点模糊:您的项目有多大以及宏需要多长时间?如果你的项目是1000个任务,并且你正在进行十几次检查而你的代码需要超过五分钟,那么肯定还有改进的余地。但是,如果它是20,000个任务和50个检查,并且宏需要两分钟,请停止尝试改进它 - 这是很好的表现。
Bottom line: it is impossible to tell if there is room for improvement without seeing your code.
一句话:如果没有看到您的代码,就无法判断是否有改进的余地。
#2
If you use the same property (e.g. objTask.Start) in several different comparisons in your code then set the property into a local variable once and then perform your comparisons on the local variable.
如果在代码中的几个不同比较中使用相同的属性(例如objTask.Start),则将属性设置为局部变量一次,然后对局部变量执行比较。
For example:
Slow code:
If objTask.start < TestDate1 and objTask.Start > TestDate2 then ...
Fast code:
Define dteStart as Date
dteStart = objTask.Start
if dteStart < TestDate1 and dteStart > testdate2 then ...
Calls to the COM object model are expensive. The second code example will be quite a bit faster (although as noted by Rachel above) it really does depend on the volume of data being processed.
调用COM对象模型很昂贵。第二个代码示例将会快得多(尽管如上面的Rachel所述),它确实取决于正在处理的数据量。
Also, make sure you define your variables with appropriate types as relying on the default Variant data type is very slow.
此外,请确保使用适当的类型定义变量,因为依赖于默认的Variant数据类型非常慢。
#3
if you have some variables with lot of data like collections think about setting it to nothing and the end of your function
如果你有一些带有大量数据的变量,比如集合,那就考虑将它设置为空,并将函数结束
Set TasksCollection=Nothing
#1
Turning off screen updating and setting calculation mode to Manual are the only application settings you can use to improve performance; the rest depends on your algorithm.
关闭屏幕更新并将计算模式设置为手动是您可以用来提高性能的唯一应用程序设置;其余的取决于你的算法。
Your description of the problem is a bit vague: How large are your projects and how long does the macro take? If your projects are 1,000 tasks and you are making a dozen checks and your code takes more than five minutes, then yes, there is surely room for improvement. But if it's 20,000 tasks and 50 checks and the macro takes two minutes, stop trying to improve it--that's great performance.
您对问题的描述有点模糊:您的项目有多大以及宏需要多长时间?如果你的项目是1000个任务,并且你正在进行十几次检查而你的代码需要超过五分钟,那么肯定还有改进的余地。但是,如果它是20,000个任务和50个检查,并且宏需要两分钟,请停止尝试改进它 - 这是很好的表现。
Bottom line: it is impossible to tell if there is room for improvement without seeing your code.
一句话:如果没有看到您的代码,就无法判断是否有改进的余地。
#2
If you use the same property (e.g. objTask.Start) in several different comparisons in your code then set the property into a local variable once and then perform your comparisons on the local variable.
如果在代码中的几个不同比较中使用相同的属性(例如objTask.Start),则将属性设置为局部变量一次,然后对局部变量执行比较。
For example:
Slow code:
If objTask.start < TestDate1 and objTask.Start > TestDate2 then ...
Fast code:
Define dteStart as Date
dteStart = objTask.Start
if dteStart < TestDate1 and dteStart > testdate2 then ...
Calls to the COM object model are expensive. The second code example will be quite a bit faster (although as noted by Rachel above) it really does depend on the volume of data being processed.
调用COM对象模型很昂贵。第二个代码示例将会快得多(尽管如上面的Rachel所述),它确实取决于正在处理的数据量。
Also, make sure you define your variables with appropriate types as relying on the default Variant data type is very slow.
此外,请确保使用适当的类型定义变量,因为依赖于默认的Variant数据类型非常慢。
#3
if you have some variables with lot of data like collections think about setting it to nothing and the end of your function
如果你有一些带有大量数据的变量,比如集合,那就考虑将它设置为空,并将函数结束
Set TasksCollection=Nothing