Excel xlls for free

I just wanted the highlight the fact that our friends at Microsoft have made it pretty simple to get set up to write xlls for free. I’m watching my language here as writing xlls is not simple and the documentation is not all that it could be. But getting hold of the tools to do it has never been easier.

Anyway you need Visual C++ express from here.

And the Excel xll SDK from here.

The SDK documentation is here.

There are a few gotchas and you need to roll back some of the newer security features in VS2008. As I recall leaving DEP in default crashed Excel.

Also you need to get all the SDK files into the right places on your machine, I have a paper on this from a training course I delivered a while ago, I’ll dig it out and put it on the Codematic site (currently here) next time I’m on that machine.

Getting the files in the right place will be no problem to us Excel folks – we’re used to the misery created by externally linked workbooks, that only work when you put them in the right directory.

Cheers

Simon

About these ads

17 Responses to “Excel xlls for free”

  1. dougaj4 Says:

    Thanks for the links Simon. I’ve had this on my “must do when I have a few spare days” list for a few years now. In fact I’ve even got a copy of Visual C++.net (Version 2002) sitting on my desk next to me, waiting to be installed on my new (i.e. over 1 year old) computer.

    My question is, should I go with the latest version of C++ Express, or stick with the older full version?

  2. Ross Says:

    I would stick with the older fuller vision if you intend to drop in to native.

    I have written a few hello world xll with other c++ ide’s but it’s always been much easyier with MS VS

    I think that PED uses VS2002 for it’s xll example.

  3. Simon Says:

    Doug if you are just getting going use whatever your doco uses. PED is VS2003 I think.

    I’ll publish a demo xll project in the next week or so, I’m just not sure on redistributing the SDK so I’ll just note how to sort that.

  4. Al Gill Says:

    Does anybody have any detailed experience of deploying on lots of machines (which may or may not be all running exactly the same OS / versions of Office) in this context? I’ve had issues with requirements for particular versions of dlls to be present when writing with MS VS. Developing on old (Win 2K vintage) machines seems to avoid some of this.

  5. dougaj4 Says:

    Just tried installing my old VisualC++.net, got a message about compatibility issues (with Vista) and check on-line for a fix; ended up with a message saying it was a microsoft package, and contact microsoft for information about upgrades.

    Oh well; I’m trying the Express path now.

  6. Simon Says:

    I use VC6 as that rarely needs additional distributables. All the more recent VS require a whole bunch of stuff installed to get your apps working on client machines. MS have been unable to provide tools to create stand alone apps for the last 10 years.

  7. Ross Says:

    I think that VS2002? and VS2003 are not supported on Vista, VB6 is though! I rembere there beign quite a fuss at the time – I cant think why!!!

  8. sam Says:

    Simon,
    As I understand If you had Office XP Developer Edition – You could write XLLs free without the need for anything other than Office…

    Sam

  9. Simon Says:

    Sam
    I’m pretty sure the dev edition only did COM add-ins not xlls. Xlls must export C style functions, VB can’t do that.

  10. sam Says:

    Hi Simon
    You are right….but I tend to think of XLLs as DLLs ….which you could create in the Dev edition

  11. Ross Says:

    Sam,
    XLL are DLL’s, but they are design to work with Excel without having to do any other stuff that normal DLL would have to. Simon has some performance numbers on his codematics site.
    Excel opens itself up via a C API, which XLL hooks into so they can work directly with the Excel application, that’s why there so fast!

  12. Simon Says:

    And there are 2 types of dll
    real – native windows dlls that export functions you can call via VBA declares
    activex – don’t export functions, make objects available either by setting a ref (early binding) or using creatobject (late binding)
    xlls are th former
    VB/VBA dev edition addins are the latter.

  13. fran Says:

    Hi Simon,
    Have you ever managed to get hold of any decent xll documentation? I’m trying to use the xlcFormatNumber command in an xll and I just cannot get it to work. I searched MSDN as posted above but there is nothing there.

    The thing that I find most frustrating about xll development is the lack of documentation.

  14. Simon Says:

    Fran
    No the documentation is a ball ache, I just use Macrofun and Steve Daltons book. MSDN doesn’t help sadly.

    I always prototype OM stuff in XLM to get things working right then convert to xll syntax so:
    XLM:
    format num
    =FORMAT.NUMBER(“0.000;[red](0.000)”)
    =RETURN()

    XLL:
    e4Ret = Excel(xlcFormatNumber, 0, 1, TempStr(” 0.000;[red](0.000)”));

    That works (on the active selection) (from a command), what are you doing that won’t work?

  15. fran Says:

    I’m using xlSet to insert a grid of values which can be either numbers or text. I then want to apply some formatting to the number values. This may not necessarily be the same for all cells.

    I had assumed that I could do:-

    Excel4( xlcFormatNumber, 0, 2, (LPXLOPER)&xRef,(LPXLOPER)&xFormat );

    where xRef is the reference to the cell wherer I wanted to put the formatiing and xFormat is of type xltypeStr and contains a format something like “$#,##0.00″.

    Doing:-

    Excel4 (xlcSelect, 0, 1, &xRef);
    Excel4( xlcFormatNumber, 0, 1, &xFormat);

    works just nice! It hadn’t occured to me to select the cell first!

    Thanks very much for your help!

  16. Simon Says:

    Glad its sorted Fran

    Thats why I like to use XLM first just to get the arguments sorted, and see if there might be something useful but undocumented. Like an optional range param.

    I’m constantly amazed at how much C API stuff relies on selecting the relevant sheet/cell, having learnt not to do exactly that from VBA.

  17. Setting Excel Number Format via xlcFormatNumber in an xll - Programmers Goodies Says:

    [...] Thanks to Simon Murphy for the answer:- Smurf on Spreadsheets [...]

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


Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: