免费的 VB6/VBA 分析器和 Excel 最佳实践
我们有很多通过 VBA 和 VBA 生成的报告。 优秀。 只有一小部分报告是实际计算 - 大部分工作是 SQL 调用和单元格的格式化/写入。 其中最长的需要几个小时，大多数每次大约需要20-30分钟。
VBA/Excel 代码插入 VB6 桌面应用程序使用的 dll 中 - 所有 sql 调用都是在这里进行的。 虽然我确信这里还有改进的空间，但我担心的不是这个 - 桌面应用程序相当敏捷。
有两个 VBA 函数被大量使用：它们称为 GetRange 和 SetupCell，它们几乎总是一起出现。 GetRange 函数是 Excel.Range 对象的包装器。 它需要一张表和 4 个表示范围范围的值。 它的主要用途是选择要编辑的单元格。 似乎优化它的机会不大，但这就是最好的方法吗？
它的合作伙伴是SetupCell。 这需要一个 Excel.Range 对象、文本和有关单元格的十几个参数（字体、边框等）。 这些参数中的大多数都是可选的布尔值，但同样，这似乎非常浪费。 其中一些可以在死后设置，但另一些则取决于单元格中包含的值。
这些函数中包含相当多的代码，主要是 if 语句和工作不喜欢我发布它。
我想我有两个问题：是否有更好的方法，它是什么和是否有免费的分析器，我可以用它来查看大部分时间是在这里还是在dll？
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.
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.
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?
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.
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 it and is there are free profiler that I can use to see if the bulk of the time is here or in the dll?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
对于一份报告来说，几个小时是荒谬的。
如果问题是 VBA，请购买“Professional Excel Development”（stephen Bullen、Rob Bovey 等人）：这有一个名为 PerfMon 的免费 VBA 分析器。
如果问题出在 Excel 计算上，请参阅 http://msdn.microsoft。 com/en-us/library/aa730921.aspx?ppud=4
但我猜问题是与逐个单元引用事物相关的高开销：您应该始终在大块单元中工作一次。
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.
If the problem is Excel Calculation see 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.
您是否考虑过使用实际的报告解决方案？ 你的后端数据库是什么？ 如果您使用 MSSQL 2000 或更高版本，则可以免费使用相当不错的报告解决方案。 SQL Server 报告服务。
听起来好像报表大部分时间都花在格式化单元格上。 这可能就是为什么报告看起来如此缓慢而桌面应用程序却不然的原因。
或者，如果您事先知道格式并且它相当静态，您可以预先格式化工作表以减少一些工作。
我也会把这个扔在那里。 大多数报告解决方案都允许条件格式等，但由于它们被设计为这样的性能将比 Excel 更好。
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.
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 宏的建议。 通过在宏运行时关闭屏幕更新，我获得了出色的结果：设置 Application.ScreenUpdating= False 和 还使用了许多其他类似的设置。 请务必在宏完成后再次打开它们：P
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
它不是免费的，但您可以使用它进行分析。 我怀疑该演示足以满足您的需求： http://www.aivosto.com/vbwatch.html
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
听起来 VBA 代码（或写入工作表的 VB 代码）正在逐行执行此操作，这可能需要很长时间，而且设计很差。 一次性将其作为变体写入 Excel。 数据全部导入后格式化工作表。
谢谢
罗斯
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