I got asked how/why to create an xll.
Why
The why is easy – they are fast
In detail: xlls avoid the COM interface that VBA has to use to move values in and out of worksheet cells. That COM interface is slooww.
How
The how is a bit more involved
1. Get a copy of a C or C++ editor, there are plenty but VC2008 (Express) is a good bet:
2. Get a copy of the Codematic starter project here (halfway down):
Read the readme included in the zip.
Open 2. in 1. (say yes to convert the project) and build it to create an xll.
There is a bit more info here which I’ll be adding to in time.
In time you will want to look at the proper Excel xll SDK from MSDN here (currently)
cheers
simon
Smurf on Spreadsheets 
Friday, 7th November, 2008 at 2:48 pm |
Thanks Simon, very useful :)
I looked at the page in your codematic link and there’s a section at the bottom that says “Base Project” but doesn’t include a link to the project.
That page mentions that XLLs are based on the XLM language. Would that mean that if MS pulls XLM support, then XLLs wouldn’t be possible any longer?
Also, do you think XLLs can be written in C#? I was going to learn that language anyway and thought I could kill two birds at once.
Thx,
JP
Friday, 7th November, 2008 at 4:19 pm |
JP
I spotted that missing link as I was writing this, I’ll sort it over the w/e. You can get it from the free stuff page, half way down (link 2 above).
XLL v XLM – Xlls are the recommended addin technology for E2007, I can’t see MS doing anything to break them for many years. They have also given assurances that XLM will not be removed until all its functionality is implemented in something else. VBA/COM can’t touch XLM for UDF performance so in theory we are safe for a while. Even if they do retire XLM they will likely just prevent Excel UI based access to it. There is too big an investment in xlls for it to go away any time soon.
The main tech for Xlls is C/C++ (and Delphi), you can also use C# and ExcelDNA, which I have been meaning to try for ages. Learning C# will stand you in good stead for all sorts of things, including C++
cheers
Simon
Saturday, 8th November, 2008 at 1:18 am |
Simon – …”so in theory we are safe for a while.”
Don’t confuse performance with functionality. As soon as XLM is somehow covered by VBA or VSTO or next great thing, it’s ogne, regardless of “performance”.
Saturday, 8th November, 2008 at 5:41 am |
” – Xlls are the recommended addin technology for E2007″
Should we not just be able to say save as xll
Saturday, 8th November, 2008 at 10:19 am |
Jon – still no sign of (a non XLM way of) being able to get the number of printed pages of the current print area though is there?
Having had projects cancelled for poor VBA performance I would happily argue that performance is part of functionality. I guess MS see things differently.
The MS benefit (reduced maintenance of the XLM UI only – they can’t remove the whole thing – everything will break) in tiny compared to the burden it would create on their customers, and therefore the loss of sales it would cause. You’re right they will do it once they have a .net replacement, or partial replacement.
Sam there is nearly a 1 to 1 mapping from XLM to C API so a translator would have been great 10 years ago. Calc4Web/TurboExcel does pretty much what you are after I think.
Saturday, 8th November, 2008 at 7:56 pm |
> still no sign of (a non XLM way of) being able to get the number of
> printed pages of the current print area though is there?
nor various formulas in chart elements, etc.
> Having had projects cancelled for poor VBA performance I would happily
> argue that performance is part of functionality. I guess MS see things differently
It’s that other point of view I was alluding to.
Sunday, 9th November, 2008 at 2:34 pm |
In my opinion XLLs is the future:
Creating automation add-ins with VB.NET is at best acceptably but does not give us 100 % professional solutions.
It’s not possible to create automation add-in with VSTO. UDFs in VSTO needs VBA wrappers to be accessible in worksheet.
As long VBA is still available it may be acceptable to use for less fast performance UDF solutions.
Simon – Continue to share You knowledge about XLLs.
Kind regards,
Dennis
Sunday, 9th November, 2008 at 5:17 pm |
As long as you cant create XLL’s / DLLs / COM Addins using just Excel I don’t see them becoming popular with “hobbyist” Developers
Its going to remain “niche” technology
MS has to bring back the “Developer Edition” of office which they abruptly discontinued after XP and allow for these addins to be created via native excel – It has to become as simple as save as – XLA/XLL/COM
Monday, 10th November, 2008 at 10:02 pm |
Thanks for this, Simon.
It might be something obvious I’m missing, but is there some way to debug this XLL using Visual C++ Express 2008?
Tuesday, 11th November, 2008 at 6:19 pm |
Although XLL functions are definitely the fastest performing solution VBA/Automation Addin functions are fast enough when sensibly programmed for everything except maybe real-time solutions/market trading IMHO.
Tuesday, 11th November, 2008 at 9:00 pm |
Egon – give me a couple of days and I’ll put something up.
(set to debug configuration, F5 – browse to Excel, Excel opens, find your xll in the \debug folder and open it, put a breakpoint in your function, enter the function in a cell.)
Charles – agreed if you only have a few instances, if you want thousands and auto calc xlls (or XLM) are the only way. For manual calc, VBA etc are plenty adequate, especially as you have access to a richer OM.
Wednesday, 12th November, 2008 at 8:02 pm |
Simon,
You don’t get the VBE slowdown bug with functions in VB6 Automation addins so thousands of instances of a UDF in Automatic Calc mode is not necessarily a problem (except for Excel 97 and Excel2000 which don’t support Automation addins).
Monday, 17th November, 2008 at 7:45 pm |
Didn’t get a chance to play with it again until today, but I see how to debug now. I must have been opening the Release copy accidentally before. Thanks kindly, sir!
Tuesday, 18th November, 2008 at 10:28 am |
I was wondering if this is possible using C# Visual Studio Express or even VB Visual Studio Express. I have a function used 1000’s of times and it’s slow becasue it deals with Range manipulation and I need it sped up!
Tuesday, 18th November, 2008 at 2:26 pm |
Brendan
take a look at ExcelDNA that lets you write UDFs in C# and pass them through the fast C API.
Or drop me a line and I’ll quote to convert it to C.
Monday, 22nd June, 2009 at 12:59 pm |
Video Clips demonstrating how easy it is to use XLW to Create Excel XLL addins
Creating a C++ XLL with Visual Studio
Creating a C++ XLL with Code::Blocks & MinGW
Creating a C# XLL with Visual Studio
Creating a hybrid C++/C# XLL with
Visual Studio (Professional)
Debugging a C# XLL with Visual C# Express
XLW (xlw-4.0.0b0.exe) can be downloaded from here:
http://sourceforge.net/project/showfiles.php?group_id=45222&package_id=37893&release_id=690866