免费的VB6 / VBA剖析器和最佳Excel实践 [英] Free VB6/VBA profiler and best Excel practices

问题描述

我们有很多报告是通过VBA& Excel中。只有一小部分报告是实际计算 - 大部分工作是sql调用和格式化/写入单元格。最长的时间需要几个小时，大多数需要大约20-30分钟。

VBA / Excel代码插入到VB6桌面应用程序使用的DLL中 - 它是这里是所有的sql调用。虽然我相信这里有改进的空间，但这并不是我所关心的 - 桌面应用程序相当厉害。

两个VBA功能丰富：被称为GetRange和SetupCell，它们几乎总是出现在一起。 GetRange函数是Excel.Range对象的包装器。它需要一个工作表，范围的范围是4个值。它的主要用途是选择单元格进行编辑。似乎没有太多的机会选择它，但它是最好的方法吗？

它的合作伙伴是SetupCell。这需要Excel.Range对象，文本和十几个关于单元格（字体，边框等）的参数。大多数这些参数是可选的布尔值，但是再次看起来很浪费。其中一些可以设置为后缀，但有些依赖于单元格中包含的值。

这些函数中包含很多代码，主要是语句和工作

我想我有两个问题：有没有更好的方法，它是什么和是否有免费的分析器可以用来查看大部分时间是在这里还是在dll？

解决方案

如果问题是VBA购买Professional Excel Development（Stephen Bullen，Rob Bovey等人）：这个有一个名为PerfMon的免费VBA剖析器。

如果问题是Excel Calculation，请参阅 http://msdn.microsoft.com/en-us/library/aa730921.aspx?ppud=4

但我猜这个问题是高开销a与单元格引用事物相关联：您应该始终一次在大块单元格中工作。

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?

解决方案

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.

这篇关于免费的VB6 / VBA剖析器和最佳Excel实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！