Codematic Excel developers logo
Codematic spreadsheet-excel development image

Rapid Application Development / Advanced Excel Development

 
Products for Excel
Commercial Products:
  - Classic Ribbon
  - Alt-FileSearch
  - Password Remover
Spreadsheet Quality Products
Free Products
Excel Development
Excel Development Index
Excel VBA
- VBA IDE (editor)
- VBA Training
- VBA Best Practice
- VBA Performance
- COM Performance
- VBA Security
Excel and Databases
Excel and Pivot Tables
Excel Add-ins
Worksheet Functions
Excel and xlls
Excel (in)security
Excel testing
Excel and .net
Excel External Links
Excel Developer Types
Professional Excel Development
Excel 2007
Excel 2010
Excel Development Archive
Spreadsheet Services
Spreadsheet Development
Spreadsheet Migration
Spreadsheet Maintenance
Spreadsheet Review
Spreadsheet Management
Resources
Excel User Confs
Consultant Profile
Book Reviews
Links
Other
Site Map

Working with Excel and VBA

COM VBA performance

There are 2 ways to use COM components from VBA, Early Bound and Late Bound.

Early binding (dim x as Areferenced.ObjectType) is generally considered better than late binding (dim x as object; x= CreateObject("AnUnreferenced.ObjectType") the most common reasons given being performance, error/syntax checking and intellisense. Our testing suggests the language chosen to write the COM component may well have more impact than the binding choice. To test we built a simple COM server in VB and in C++ using ATL (the Active Template Library).

There are 2 aspects to COM performance, the time to create objects and time to call their methods.

This was an attempt to measure the performance of using different COM calling approaches rather than the execution speed of any particular language. As such we chose a very simple function:

Cpp code:
STDMETHODIMP Cautomtest::SpeedTest1(long theValue, long* theResult)
{
*theResult = theValue;
return S_OK;
}

VB code:
Public Function SpeedTest1(ByVal theValue As Long, theResult As Long) As Long
theResult = theValue
End Function

This takes a long as the first parameter and returns it in the second. This approach was used as the COM standard in C++ is to use the return value to indicate success/failure. There were 5 versions of the function (SpeedTest1-SpeedTest5) to create a worthwhile function table. As a matter of interest calling the first function in the table will be fractionally faster than calling the last one. And the longer the function list the worse the performance of the later functions. Think of it as a long Select Case statement, the sooner it finds the right one the less cases it has to try.

 

We have done 2 sets of tests. The first, original findings are included below with conclusions drawn at that time, but it became apparent that this test, whilst not unreasonable, could be made more meaningful.

To do that we split out the creation aspect and the calling aspect and measured them separately, here are the results.

Results for 500,000 iterations Relative function call performance
Creation (Seconds) 5 function calls (Seconds) % of Early bound C++ Factor v VBA Factor v win 32
Early Bound
  VB 9 0.3 1.8 0.6 1.5
  C++ 2 0.17 1.0 0.3 0.9
Late Bound
  VB 11 9 52.9 18.0 45.0
  C++ 4 3 17.6 6.0 15.0
Others
Win32 C XLL n/a 0.2 1.2 0.4 1.0
Win32 C DLL n/a 0.2 1.2 0.4 1.0
Excel VBA n/a 0.5 2.9 1.0 2.5

This shows how expensive it is to create COM objects, in all cases it took longer to create the object than to call the 5 functions. In the case of early bound objects, up to 10 times as long. It took longer to create the late bound objects than the early ones, in the case of C++ twice as long.

The calling times are interesting with late bound being in the order of 20 times slower than early. The message is clear if you go late bound you pay twice - at creation time and at use time.

The initial tests below seemed to suggest the late bound C++ was faster than early bound VB, it can be seen that this is to do with the better creation performance of ATL objects over VB, because actually early bound VB function call performance is 10x better than late bound C++.

Interestingly the COM calls to an early bound C++ object would seem to be faster than win32, assuming the object was not created many times. Not really sure why that would be, possibly registry optimisations compared to the file system? Also no real idea why the early bound creation performance for the VB component was almost 5x slower than the ATL equivalent.

The biggest surprise (for us) was that VB6 function performance was actually better than VBA. We have never seen this is the field, even though it is almost twice as fast. Of course the function call benefit is offset by the object creation cost. It would seem though, that there could be some benefit in moving complex routines to VB6 in certain cases. Well except that VB6 has been retired, and for that reason we probably won't do further testing of this option.

In some further testing not covered here, the relative speed of the individual functions SpeedTest1-5 was compared. Well actually a different dll with 10 functions in (imaginatively called SpeedTest1 - SpeedTest10) was used to increase the function table position effect. For late bound calls, 500k calls to SpeedTest10 took twice as long as calls to SpeedTest1. I.e if you are using late binding, get your most frequently used functions to the the top of the function table. It also seems to take slightly longer to create bigger objects, no surprise there, but worth remembering.

The difference in performance of the two languages is still a surprise, with C++ significantly better in all areas. The xll version has the same performance as the win 32 dll, but is easier to manage as it registers itself with Excel on opening. This means you don't need to mess with the VBA to try it out.

A second set of workbooks with different test VBA are here. For fun try messing with the function calls (change all to SpeedTest1 and then SpeedTest5) it makes a significant difference with the late bound versions. As below all the activex dlls will need regsvr32-ing. This set also includes an xll version.

 

Test Version 1 results

These are the original findings and conclusions. Valid, but maybe potentially misleading - very dependent on create/call mix. ie if you are not creating a component calling 5 functions and destroying it, your performance will vary.

We then wrote a simple VBA client to call it multiple times using early binding and late binding. For completeness we also included a pure VBA solution and a win32 dll version. Note this is the complete cycle - create object, call some (5) methods and destroy it. The results were quite surprising:
Results for creating object and calling 5 functions 500,000 times
Seconds % of Early bound C++ Factor v VBA Factor v win 32
Early Bound
  VB 10 500% 20.0 50.0
  C++ 2 100% 4.0 10.0
Late Bound
  VB 21 1050% 42.0 105.0
  C++ 7 350% 14.0 35.0
Others
Win32 C DLL 0.2 10% 0.4 1.0
Excel VBA 0.5 25% 1.0 2.5

So COM is slower than win32, we knew that, its safer and more flexible too. Pure VBA is 20 times faster than using an early bound VB6 COM dll, this means your code must be doing something pretty intensive to recover the time cost of calling out of VBA to VB6.

The difference between early and late binding was no real surprise, but its nice to be able to put a figure on it. Its roughly twice as slow to use late binding over early binding. The VBA loop overhead in all cases was approx 0.006 seconds, so a fairly insignificant proportion.

The big surprise was the C++ version, calling a C++ COM component late bound is 3 times faster than calling VB the same way, and 30% faster than calling VB early bound. I.e. C++'s worst is 30% better than VB's best! And remember this test had very minimal calculation. Also note this testing was just VBA to external code, there was no Excel element, and no worksheet formula element.

To keep these results in perspective, it important to remember these timing differences are over 2.5 million function calls. If the system only makes a few thousand calls then for all but the most time critical applications this interface performance is probably not the most important factor. If the app is that time critical then something other than Excel/VBA may be more appropriate.

The test workbooks and the dlls are here, note the COM ones will need Regsvr32'ing, and the path to the win32 version will need changing in the VBA.

Of course, with any performance testing you should test for yourself in your own environment, to be sure the results are valid for your specific project and application.

If you'd like to discuss C or C++ based Excel work, please get in touch.

Please contact us with any questions.

 

Upcoming Events:

25 January 2012 - UK Excel Developer Conference - London


Products for sale:

AltFileSearch

Office 2007 FileSearch replacement logo

New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)


wsUnprotector

worksheet password remover logo

Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)


Classic Ribbon Tab

classic ribbon for office 2007 logo

Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)


 

Products coming soon:

Link Manager

(Find and control external links in Excel Workbooks)

Due by Q1 2111.

XLAnalyst Pro

(Excel VBA based spreadsheet auditing tool)

Due before the end of 2111.

  ;-)
This page was last reviewed on December 21, 2011

©Codematic Ltd 1999-2011