Creating xlls

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:

Here

2. Get a copy of the Codematic starter project here (halfway down):

C xll addin

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

Advertisements

16 Responses to “Creating xlls”

  1. JP Says:

    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

  2. Simon Says:

    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

  3. jonpeltier Says:

    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”.

  4. sam Says:

    ” – Xlls are the recommended addin technology for E2007″

    Should we not just be able to say save as xll

  5. Simon Says:

    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.

  6. jonpeltier Says:

    > 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.

  7. Dennis Wallentin Says:

    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

  8. sam Says:

    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

  9. Egon Says:

    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?

  10. Charles Says:

    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.

  11. Simon Says:

    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.

  12. Charles Says:

    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).

  13. Egon Says:

    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!

  14. Brendan Gunning Says:

    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!

  15. Simon Says:

    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.

  16. AlexesDad Says:

    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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: