Xlls in Excel 14

I was very pleased to see a renewed commitment to xlls in 2007 with the release of an updated SDK.

For Excel 14 I hope that work continues and expands. I would like to see better access to some of the newer features via the C API.

One particular area where xlls are weak I think is in event trapping. The classic missing one is selection change. So instead of doing this through the ultra fast C API we have to dawdle through the COM interface. As this is probably the most common event thats a pain. Access to CalculateFull would be handy too.

Being able to trap workbook open and close would be useful too. I know I’m dreaming when I imagine being able to set the title of an ALERT (a bog basic msgBox), but a bit more control would be nice.

Also I hope that xlls can work natively with whatever clusterfuck UI is foisted upon us in O14. The whole point, or a big part of it (for me anyway) is single file solutions. Thats not possible in E2007, unless you are happy for your add-ins UI to be dumped in the badd ladds corner. Of course I guess this comes into the new mantra: ‘the user shalt have total control of their UI (unless they try to change it by opening a code resource, or otherwise changing it from standard)’.

I know the encouragement is to use xlls for fast worksheet functions only, but I have been doing a lot of command based stuff recently and love the speed gain here too.

What would you like to see if the next revision of the xll SDK?



9 Responses to “Xlls in Excel 14”

  1. Ross Says:

    Better examples, ones that actually worked would be a start! Also More examples, and example that show how to write c code for a VB/Aer

  2. Harlan Grove Says:

    In re Ross’s last comment, wasn’t VB6 able to create DLLs? VB.Net can’t? Or is this purely a speed thing – C/C++ invariably faster then VB*?

    But as for learning how to write C/C++, I don’t think a simple syntax translation table would be sufficient.

  3. Simon Says:

    VB6 and .net can produce something they call dlls, but aren’t the usual native code ones. They are activex ones that don’t export symbols in a way Excel can understand. RealBasic might be able to?

    Ross, yes more content targeted on VB devs would be the most valuable I think.. I have some projects on-going in this area.

  4. dougaj4 Says:

    Ross – I totally agree. I’m currently trying to install and learn C++ Express and the SDK so I can create xlls that will perform intensive maths functions faster, and also to use the vast resources of scientific software code available in C without having to translate it to VB. The whole process is about a million times harder than it should be. The code in the SDK needs to be modified to work, but the instructions don’t work in the Express version, and appear to be incomplete. To make matters worse, every one of the Internet posts I’ve found relating to this issue seems to say something completely different. All I need at this stage is a simple tutorial showing how to pass an array of doubles and/or longs between VBA and an xll. Surely it can’t be that hard.

    Simon – your Codematic site refers to a sample xll project, but I can’t find it. Is it there (or on its way)? Also do you (or others) have any good books they can recommend, or Web sites with reliable information?

  5. Simon Says:

    PED is the best to get started
    Steve Daltons book is an excellent more indepth reference, but you need a bit of an idea of C++ to get the most out of it.

    I’m going to be fleshing out the xll section of codematic, including a demo project.
    I also have a word doc that covers the mess that is installing the sdk.

    The best sites I have found are on my blogroll, if you find any more then let us know.

    I’ve done some in house xll training recently and am thinking of running a 1 or 2 day intro course at some point. I’ll post when its organised.

  6. Simon Says:

    Doug I just re-read your comment
    If you are only going from VBA you don’t need an xll, just a dll will be fine. An xll is just a dll with some registration info excel can use to call it directly from a cell.

    You just need a dll that exports your functions (needs to use stdcall calling convention). And then use VBA declares as normal.

    The big speed up is cutting VBA out completely, but you can do that with a dll by using the call macro function (see here

  7. dougaj4 Says:

    Simon – thanks for the comments. Having spent the entire weekend getting every C++ error message known to man I have at last succeeded in getting Excel to add 2 numbers together, via a dll written in Visual Studio Express c++, and I’m feeling quite illogically pleased with myself :)

  8. Simon Says:

    I’ve just done exactly the same with php. 2 days of banging my head off the wall with meaningless error messages. Then finally the delight of getting it to display its configuration info.

  9. Mike Rosenblum Says:

    Hey Simon,

    What did you mean exactly with respect to:

    (1) “VB6 and .net can produce something they call dlls, but aren’t the usual native code ones. They are activex ones that don’t export symbols in a way Excel can understand.”

    (2) “An xll is just a dll with some registration info excel can use to call it directly from a cell.”

    Other than a little bit of COM doing the marshalling, shouldn’t a VB6 automation add-in be pretty darn close functionally to a XLL? I’m not a C++/XLL guy at all though, so I’d love to understand what you mean here better…

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: