XLL’s – Lets make’em… (C++)

When we talk about performance in computing, almost unanimously the views suggest that C++ being the leader in performance oriented computing. Well our lovely excel is built on it, and so forth we also have a route to bring in some more C++ into excel world via UDF and enjoy the flavours of high performance computing.

This brings us to the concept of XLL’s, the XLL is nothing but windows dll (written in C++) with just its extension renamed to xll. This xll can then be picked up by Microsoft excel as an add-in and provide the functionalities provided by the same as UDF’s for the user.

Pros: Fast (speed of complex computation), faster you wanna go; can opt for placing assemble inline codes for saving function call overhead… pheww. (it’s like peeking into your CPU registers and playing around… )

Cons: Steep learning curve (C++), limited to UDF functionality, and very basic windows native message box for user prompt.

So in order to find out more about the same, I tried to build one for myself which is illustrated as below:

1. Navigate and download Excel 2007 SDK (if you are using Excel 2007 as myself)

2. Extract and save it to a location suitable to you on your HDD, you would have to set paths to the location in future steps.(keep in mind, mine is “C:\Excel2007XLLSDK”)

3. In the same navigate to “samples” -> “framewrk” (the spelling mistake is from Microsoft not me…)

4. Open up the project and just build it, and close. Now your environment is all set to develop xll’s

5. Verify is the libraries are present after the previous build

C:\Excel2007XLLSDK\include

C:\Excel2007XLLSDK\lib

6. Fire up the visual studio 2010 and “Create a new Project”

7. On the next screen make the following changes:

Click on the “Application Settings” and select “Application Type” as “DLL”
Select “Additional Conditions” as “Empty Project”

8. Add the following files to the project via “Add” to the project. (Don’t worry about the content, just add.)

9. Select the project and go to its properties and make the following changes:

Debugging: Command – “C:\Program Files\Microsoft Office\Office12\ EXCEL.EXE”

C/C++: General: Additional Include Libraries – “C:\Excel2007XLLSDK\include”
C/C++: General: Debug Information Format – Program Database (/Zi)

Linker: General: Output File –“$(OutDir)$(TargetName).xll”
Linker: General: Additional Library Directories – “C:\Excel2007XLLSDK\lib”

Linker: Input: Additional Dependencies – “xlcall32.lib; frmwrk32.lib”
(also verify if the module defination file is listed in the properties)

10. Finally click on apply, and now your project is ready to take in some code.

11. Take in the code from my sample application (ammend/add as you like) and compile it to generate the xll

12. From the excel add-ins menu “Browse” to the xll location and load it. After which you would be able to enjoy benefits of your C++ in excel via UDF.

XLL debug output folder


Add-in window


UDF/formula window

Well building xll, is not always the best option when those critical mill/micro seconds would be so much crucial to you (except if you are FX/High Frequency Trader/Programmer) and considering the effort and skills required to build the same.
Hence considering some other alternatives like Excel-Dna could save you much of the pain of writing complex C++ with almost similar functionalities achieved via .NET (VB/C#) but with much ease.

Download

References:

http://www.codeproject.com/Articles/15971/Using-Inline-Assembly-in-C-C
http://blogs.msdn.com/b/andreww/archive/2007/12/09/building-an-excel-xll-in-c-c-with-vs-2008.aspx
http://my.safaribooksonline.com/book/programming/vba/9780321579126
http://exceldna.codeplex.com/