运行Excel VBA宏的最快处理器 [英] Fastest Processor for Running Excel VBA Macros

问题描述

我在Excel中编写了一系列复杂的VBA宏，作为从大型数据集生成数百（甚至数千）数据报告的系统的一部分。

在我的旧桌面上（p4 @ 3.2; 3gb ram）一些宏需要花费数小时才能执行 - 我甚至让它们在一夜之间运行。

我想要一台可以在更短的时间内运行它们的新计算机。所以我们在新的桌面上投入了5k（双核xeon @ 2.8; 4gb ram;双15k rpm硬盘）。

我在两台计算机上运行了一个简单的宏1分钟 - 新计算机大约比旧计算机快三分之一。

我的假设：处理器时钟速度决定了宏的运行速度，而且它是双核心，而xeon与奔腾不会影响速度。

非常感谢任何输入/建议

谢谢，bret

解决方案

VBA不支持多个theads，而我认为Xeon处理器就像它的多个处理器一样运行。我很惊讶它的速度要慢得多，但我不会想到会有很大的增加，因为你只使用处理器的一部分而不是全部。多数民众赞成我认为正在发生的事情。

您可以通过检查计算机上正在运行的其他内容来加快此过程。可能有另一个应用程序/服务在宏期间启动并减慢进程。还存储了哪些数据？如果数据是通过网络下载的，或者数据库使用得当，那么获取数据可能存在瓶颈。

也可能是代码中的某些因素导致宏的速度变慢。逻辑中的一个小缺陷。阅读其他一周的博客文章，其中一个人写了一个关于他的代码逻辑的简单改变将速度提高了十倍的情况。

说出发生的事情有点困难，所以这些只是理论，但如果你想要，你可以寄给我5k台式电脑，我就试试吧。

i have written a series of complex VBA macros in excel as part of a system to generate hundreds (even thousands) of data reports from large data sets.

on my old desktop (p4 @ 3.2; 3gb ram) some of the macros take hours to execute - i even let them run overnight.

i wanted a new computer that could run them in much less time. so we invested 5k in a new desktop (dual core xeon @ 2.8; 4gb ram; twin 15k rpm hard drives). 

i ran a simple macro for 1 minute on both computers - the new one is about one third SLOWER than the old. 

my hypothesis: processor clock speed is what determines the speed at which a macro runs AND the fact that it's a dual core and xeon vs pentium doesn't affect speed.

any input/advice is very much appreciated

thanks, bret

解决方案

hi Bret,

VBA doesn't support multiple theads while Xeon processors I believe run as though its multiple processors. I'm surprised it's that much slower but I wouldn't have thought an great increase would of happened as your only using part of the processor rather than all of it. Thats what I think is happening.

You could maybe speed up the process by checking what else is running on the computer. Could be there is another application/service that kicks in during the macro and slows the process down. Also where is the data stored? Could be that there is a bottleneck in getting the data if it's downloaded over the network or if the database is fairly well used.

Could also be something in the code that is slowing the macro down. A small flaw in the logic. Read a blog entry the other week in which the guy wrote about a situation where a simple change in his codes logic improved the speed by ten fold.

It's a bit difficult to say what is happening so these are just theories but if you want you can send me the 5k desktop computer I'll give it a go.

这篇关于运行Excel VBA宏的最快处理器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！