You can use the Microsoft Excel XLL Software Development Kit and the C API to create high-performance worksheet functions.
You can use the SDK to create DLLs that can integrate directly into Excel.
These DLLs have the file extension ".xll".
These DLLs provide the most direct and fastest interface for the addition of high-performance user defined functions.
C Programming Language
Learning how to write XLLs using the SDK requires you to learn the C programming language.
The C programming language is a very low level language and is extremely complicated.
The C API has none of the higher-level rapid development features of VBA, COM, or .NET.
Memory management is low level and, therefore, puts greater responsibility on the developer.
If the memory management is not implemented correctly Excel will freeze / crash or the processes will not terminate.
Many Excel features are not exposed to the C API, like they are with VBA, COM or .NET Framework.
Excel 2007 - Multi Threaded Calculation
Excel 2007 introduced the concept of multi-threaded recalculation of workbooks.
The Excel calculation thread calls UDFs in a serial fashion, one after the other, waiting for each call to complete before proceeding down the calculation chain.
If a UDF takes 2 seconds to complete, calling the function 5 times on a worksheet could take 10 seconds to complete.
Multithreaded recalculation will not really help in this situation because it does not scale very well.
Multiple threads all have their own overhead, and therefore it is not practical to define as many threads as you have calls to the UDF.
Excel 2010 - Asynchronous Functions using the SDK
Excel 2010 introduced support for calling user-defined functions (UDF) asynchronously using the SDK.
This can improve performance by enabling several calculations to run at the same time.
Asynchronous functions still must be completed as part of Excel's normal calculation phase.
Asynchronous functions can be run concurrently, but Excel will still show as calculating until all asynchronous functions have returned.
link - docs.microsoft.com/en-us/office/client-developer/excel/programming-with-the-c-api-in-excel
link - docs.microsoft.com/en-us/office/client-developer/excel/asynchronous-user-defined-functions
link - docs.microsoft.com/en-us/office/client-developer/excel/developing-dlls
link - docs.microsoft.com/en-us/office/client-developer/excel/creating-xlls
link - docs.microsoft.com/en-us/office/client-developer/excel/how-to-access-dlls-in-excel
link - docs.microsoft.com/en-us/office/client-developer/excel/calling-user-defined-functions-from-dlls
link - docs.microsoft.com/en-us/office/client-developer/excel/multithreaded-recalculation-in-excel
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext