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
Thursday, 14th August, 2008 at 2:40 am |
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?
Thursday, 14th August, 2008 at 11:44 am |
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.
Friday, 15th August, 2008 at 1:52 am |
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.
Friday, 15th August, 2008 at 11:11 am |
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.
Saturday, 16th August, 2008 at 3:23 am |
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.
Saturday, 16th August, 2008 at 8:26 am |
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.
Monday, 18th August, 2008 at 9:59 am |
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!!!
Monday, 18th August, 2008 at 3:23 pm |
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
Monday, 18th August, 2008 at 3:39 pm |
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.
Monday, 18th August, 2008 at 3:59 pm |
Hi Simon
You are right….but I tend to think of XLLs as DLLs ….which you could create in the Dev edition
Wednesday, 20th August, 2008 at 9:50 am |
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!
Wednesday, 20th August, 2008 at 11:53 am |
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.
Tuesday, 16th September, 2008 at 12:06 pm |
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.
Tuesday, 16th September, 2008 at 1:40 pm |
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?
Tuesday, 16th September, 2008 at 2:15 pm |
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!
Tuesday, 16th September, 2008 at 2:24 pm |
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.