Download (direct link):
This test compares the relative abilities to dynamically allocate memory in the applicationâ€™s process and freeing it again. Given that well-written code should not be doing this too often, the difference here is not significant.
The function called in both cases simply returns its Boolean argument. The ratio here seems to be typical of simple statements and operations.
In this test it was difficult to make a fair comparison without deliberately restraining C and the powerful low-level string manipulation that it makes possible. The C code makes use of Câ€™s powerful pointer arithmetic and null-terminated strings to do the job with typical efficiency. VB, on the other hand, was shackled by its lack of efficient low-level string handling.
Miscellaneous Topics 293
9.2.1 Conclusion of test results
VB is very efficient, all things considered. However, C/C++ is typically 5 to 10 times faster for simple operations. If a function needs to do a lot of array manipulation then the ratio could be closer to 15 to 20. If you are considering writing intensive matrix manipulation functions or functions that are evaluating complex algebraic expressions then C/C++ is the best solution. This is especially true if the resulting spreadsheet needs to be able to recalculate in near real-time or is going to be large (or if youâ€™re the impatient type).
String manipulation is clearly what C excels at (small e). Some might say that test 11 was an unfair test. Not so. If string manipulation is a large part of what you want to do then donâ€™t hesitate to use C or C++. String-intensive activities would include functions that, say, read and analysed all types of cell contents and formulae.
9.3 RELATIVE PERFORMANCE OF C API VERSUS VBA CALLING FROM A WORKSHEET CELL
Apart from the code execution speed of C/C++ versus VB, reviewed in the above section, there is also the difference between the time it takes Excel to call a VBA function, compared to an XLL function registered via the C API. This is easily tested using a simple example function:
double stdcall C_call_test(double d)
Function VBA_call_test(d As Double) VBA_call_test = d End Function
The example spreadsheets Call Speed Test - C API.xls6 and Call Speed Test - VBA.xls on the CD ROM contain replications of this formula with one cell depending on the previous in the same pattern across all columns from row 2 down. Cell A1 drives a recalculation of all cells. The former workbook contains just over 1,000,000 copies of the function (one per cell) and the latter just over 50,000. From a crude test (counting the seconds), it can be seen that each C API call is made approximately 20 times faster than a VBA call with the VB editor closed and a staggering 2,000 times faster than a VBA call with the editor open. Given that the code execution ratio is only
6 Care should be taken when opening and running this example test sheet as it is very large, over 41 Mbytes, and could cause Excel severe performance problems if there is insufficient available memory.
Excel Add-in Development in C/C++
about 7:1, most of this disparity clearly comes from the difference in the speed of the calling interface.
When calling an XLL function, Excel only has to look up the function in an internal table to obtain the address, prepare the arguments on the stack, call the function, read the result back from the stack and deposit it in the cell. The looking-up of the function address is optimised: the position in the table is noted, so to speak, at the point the function is entered into the cell. This is a very fast overall operation.
When calling a VBA function, Excel has to do all the work that it previously did, but must use the COM interface to prepare arguments, call the function and retrieve the result. As can be seen, this is an extremely slow operation.
In conclusion, where there are a large number of calls to user-defined functions, the benefit of using the C API becomes even more compelling, especially in applications that need to run in near real time. The very latest versions of Excel and Windows support a more direct access of COM DLLs, whether written in VB or C++, from the worksheet, but there is still a significant calling overhead compared to the directness of the C API.
9.4 DETECTING WHEN A WORKSHEET FUNCTION IS CALLED FROM THE PASTE FUNCTION DIALOG (FUNCTION WIZARD)
For a number of reasons, you may not want one of your worksheet functions to evaluate when the user is entering or editing arguments using the Paste Function dialog, otherwise known as the Function Wizard. The reason might be performance or that the function communicates with some remote process, for example. Detecting that your function is being called from this dialog is fairly straightforward.
The dialog has a class name of the form bosa_sdm_XLn where n is the current Excel version. Windows provides an API function, GetClassName(), that obtains this name from a Windows handle, an HWND variable type. It also provides another function, EnumWindows(), that calls a supplied callback function (within your DLL) once for every top-level window that is currently open. The callback function only needs to perform the following steps: