免费的 VB6/VBA 分析器和 Excel 最佳实践
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/921543/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Free VB6/VBA profiler and best Excel practices
提问by graham.reeds
We have a lot of reports that are generated via VBA & Excel. Only a small percentage of the reports are actual calculations - the majority of the work is sql calls and formatting/writing of cells. The longest of which takes several hours, the majority takes around 20-30 mins each.
我们有很多通过 VBA 和 Excel 生成的报告。只有一小部分报告是实际计算 - 大部分工作是 sql 调用和单元格的格式化/写入。其中最长的需要几个小时，大多数需要大约 20-30 分钟。
The VBA/Excel code plugs into a dll that the VB6 desktop apps use - it's here that all the sql calls are made. While I am sure that there is room for improvement here, it's not this that concerns me - the desktop apps are fairly snappy.
VBA/Excel 代码插入到 VB6 桌面应用程序使用的 dll 中 - 所有 sql 调用都在这里进行。虽然我确信这里有改进的余地，但我并不关心这个——桌面应用程序相当活泼。
Two VBA functions are used in abundance: These are called GetRange and SetupCell and they nearly always appear together. The GetRange function is a wrapper for the Excel.Range object. It takes a sheet, and 4 values for the extents of the range. Its main use is to pick the cell for editing. There doesn't appear to be much chance of optmising it, but is it the best way?
大量使用了两个 VBA 函数：它们称为 GetRange 和 SetupCell，它们几乎总是一起出现。GetRange 函数是 Excel.Range 对象的包装器。它需要一张工作表，以及范围范围的 4 个值。它的主要用途是选择单元格进行编辑。似乎没有多少机会优化它，但这是最好的方法吗？
Its partner is SetupCell. This takes a Excel.Range object, text and a dozen parameters about the cell (font, borders, etc). Most of these parameters are optional booleans but again, it seems very wasteful. Some of these can be set posthumously but some are dependant on the values contained in the cell.
它的合作伙伴是 SetupCell。这需要一个 Excel.Range 对象、文本和有关单元格的十几个参数（字体、边框等）。大多数这些参数是可选的布尔值，但同样，这似乎非常浪费。其中一些可以在死后设置，但有些取决于单元格中包含的值。
There's quite a lot of code contained in these functions, mainly if statements and work won't appreciate me posting it.
这些函数中包含相当多的代码，主要是如果语句和工作不会感谢我发布它。
I guess I've got two questions: Is there a better way and what is itand is there are free profiler that I can use to see if the bulk of the time is here or in the dll?
我想我有两个问题：有没有更好的方法，它是什么，是否有免费的分析器可以用来查看大部分时间是在这里还是在 dll 中？
回答by Charles Williams
several hours is ridiculous for a report.
几个小时对于一份报告来说是荒谬的。
If the problem is VBA buy "Professional Excel Development" (stephen Bullen, Rob Bovey et al): this has a free VBA profiler called PerfMon.
如果问题出在 VBA 上，请购买“Professional Excel Development”（stephen Bullen、Rob Bovey 等人）：这有一个名为 PerfMon 的免费 VBA 分析器。
If the problem is Excel Calculation see http://msdn.microsoft.com/en-us/library/aa730921.aspx?ppud=4
如果问题是 Excel 计算，请参阅http://msdn.microsoft.com/en-us/library/aa730921.aspx?ppud=4
But I would guess that the problem is the high overhead associated with referencing things cell-by-cell: you should always work in large blocks of cells at a time.
但我想问题是与逐个单元格引用事物相关的高开销：您应该始终一次在大块单元格中工作。
回答by Benjamin Autin
Have you thought about using an actual reporting solution? What's your backend db? If you are using MSSQL 2000 or higher there is a fairly decent reporting solution you can use free of charge. SQL Server Reporting Services.
您是否考虑过使用实际的报告解决方案？你的后端数据库是什么？如果您使用的是 MSSQL 2000 或更高版本，则可以免费使用相当不错的报告解决方案。SQL Server 报告服务。
It sounds as if the reports are spending most of their time formatting cells. This could be why the reports seem so slow and the desktop app doesn't.
听起来好像报告花费了大部分时间来格式化单元格。这可能就是为什么报告看起来很慢而桌面应用程序没有的原因。
Alternatively, if you know the formatting before hand and it is fairly static, you could pre-format the sheets to cut down on some of the work.
或者，如果您事先知道格式并且它是相当静态的，您可以预先格式化工作表以减少一些工作。
I will throw this in there as well. Most reporting solutions will allow for conditional formatting and such, but since they are designed to work as such performance will be much better than having Excel do it.
我也会把这个扔进去。大多数报告解决方案将允许条件格式等，但由于它们被设计为这样工作，性能将比让 Excel 做的要好得多。
回答by MarkJ
This isn't a profiler recommendation, but it is a suggestion for speeding up Excel macros that are spending their time updating the screen. I've had excellent results by turning off screen updating while the macro is running: set Application.ScreenUpdating= False, and also using a number of other similar settings. Just be sure to turn them back on again when the macro finishes :P
这不是分析器建议，而是加速花费时间更新屏幕的 Excel 宏的建议。通过在宏运行时关闭屏幕更新，我得到了很好的结果：设置 Application.ScreenUpdating= False，并且还使用了许多其他类似的设置。请确保在宏完成后再次打开它们：P
回答by Oorang
It's not free but you can profile with this. I suspect the demo will be adequate to your needs: http://www.aivosto.com/vbwatch.html
它不是免费的，但您可以使用它进行配置。我怀疑该演示足以满足您的需求：http: //www.aivosto.com/vbwatch.html
回答by Ross
It sounds like the VBA code (or the VB code that's writing to the sheets) is doing so line by line, this can take ages, and is poor design. Write to Excel as a variant in one go. Format the sheet after the data is all imported. Thanks Ross
听起来 VBA 代码（或写入工作表的 VB 代码）是逐行执行的，这可能需要很长时间，而且设计很差。一次性写入 Excel 作为变体。数据全部导入后格式化工作表。谢谢罗斯
