Last week I highlighted how embarrassingly deathproof VBA has proven to be. Especially compared to its alleged big brother VB6 that died with barely a murmur back in the day. I say dead, there are still plenty of us still actively coding VB6 – there is nothing better for a whole raft of applications and scenarios – I mean dead as in off Microsofts support map. No reprieves, extensions or back pedalling for VB6.
These last few days I have be working in a technology that has been threatened with retirement lots of times, and continued as a core product feature regardless, the real Daddy of technology:
XLM!
why? because of the gaping holes in COM/VBA coverage of the Excel OM.
- Number of printed pages? – not with VBA (ignoring app.executeXL4 of course)
- Connect to Dlls when you don’t know the path at release time – not with VBA (not without some grotty virus-like code generation anyway
- Some protection/function registration combos – not with VBA
- Some mildly esoteric chart stuff – not with VBA (or 2007 probably!)
- And some other stuff I forgot
- And some stuff I don’t know about I’m sure
At one point I even fired up my old Excel 5 lapper because I needed to record some XLM to fix a syntax problem I was having.
They have been trying to kill off XLM since Excel 5 in 1995 and its still alive and kicking in 2010. So even if its gone in vNext that’s still 20 years of healthily cheating death. Fair play.
So yeah VBA is hard alright but XLM is harder.
I totally accept that some of those golden oldie languages like cobol and smalltalk and fortran, and even C are still ticking along, in fact isn’t fortran about to be relaunched into the limelight as F# for .net? (yep its in VS 2010 beta 1). But these non vendor specific languages are much more steady (lifecycle wise) I would say. Would you?
I’m really highlighting the longevity of VBA rather than any affection I might have for the language. (I have none, I would rather work in almost anything else (except XLM) – if it were as effective).
cheers
Simon
Smurf on Spreadsheets 
Tuesday, 20th October, 2009 at 9:16 pm |
Simon,
F# descends from ML via OCaml – *very* different from FORTRAN.
Speaking of programming languages, though, it seems like XLM is superior to VBA for writing certain kinds of UDFs, while VBA certainly wins for anything procedural like most macros. I’ve never used XLM much, though, because I’m a relative Excel newbie.
Do you know the history on why MS discourages XLM or where to find it? I guess it could be as simple as that they were adding VBA because macros make up more of the “end user programming” space than UDFs, and they didn’t want to confuse the end users…
I should also ask, can you recommend a good resource for learning more about XLM? There’s the help file which you can find online, but Google, Amazon, etc. are mainly convinced I’ve misspelled “XML”…
Tuesday, 20th October, 2009 at 10:30 pm |
Simon – Why do you say you’d rather work in anything else if they were as effective. Isn’t being effective pretty well the whole story as far as programming languages are concerned?
John – I know next to nothing about XLM as well, but I’m not sure what the advantages for writing UDFs are. I know calling built-in Excel functions from VBA is very slow, but are there any other areas where XLM is better than VBA for UDFs?
Wednesday, 21st October, 2009 at 12:10 am |
I’m thinking of the kind of UDFs that might replace a bunch of copied and pasted formulas, i.e. a “pure” function that can be composed only of calls to other functions and doesn’t require any procedural logic. You could write that in VBA, but it seems like it would be clearer and easier with XLM. (I say “seems like” because I don’t really know the syntax for building a macro sheet, though. My use of XLM has been confined to calling EVALUATE from the worksheet, and a little poking around with Application.ExecuteExcel4Macro to play with hidden names.) For that subset of UDFs, making a user-programmer learn about “Public Function”, “On Error”, “Dim”, “Application.WorksheetFunction”, strong typing, how to return values, etc. seems like overkill.
Wednesday, 21st October, 2009 at 12:23 am |
Hi John
Maybe I should have checked a bit more thoroughly, can we agree that F# is a cousin at least of fortran? I’m happy to believe its nearer OCaml, but at least I’ve heard of fortran.
XLM can be a bit clumsy, VBA is more forgiving and more feature rich, and stems from the days when everything was going to be VB. Anyone else remember client side scripting in VB script for IE and javascript for everyone else?
UDFs in Excel, (bearing in mind it is a functional language/system – you would think there were good ways to define functions – there aren’t) have always been badly implemented for ever. They were odd in XLM and they are odd in VBA, and in .net. In C/C++ the implementation is sensible, but the registration is a challenge.
For XLM its hard because google ‘helps’ by showing all the xml rubbish. download macrofun.hlp from MS, this is the old help file, about the only accessible resource, I use it everyday. Steve Daltons book is good, but is more aimed at xlls. Other than that there aren’t any printed resources around.
Doug for pragmatists I totally agree, but if you have ever worked with ‘professional’ developers you’ll notice they really don’t care much about delivering working code to the users – they care much more about refactoring, using the latest language features implementing a pattern they read about on MSDN, making code reusable even if there is no chance of it being reused, that sort of thing.
As to XLM v VBA, I use XLM to prototype my xlls, its generally a line by line translation into C. I’m not really recommending people drop everything and get into XLM, more don’t forget its there as an option. simple xlm udfs are way faster than vba, complex ones are slower, some OM accesses are a lot faster in xlm like print settings, and some things aren’t in the COM interface but are in XLM
Wednesday, 21st October, 2009 at 12:31 am |
John
spot on I agree with all of that.
The only trouble is in xlm udfs you have to use a function called =RESULT() to say what datatype you will return, and a func called =ARGUMENT() to name a type the function args. So it quickly gets confusing for a competent Excel user, just like vba.
eg here:
https://smurfonspreadsheets.wordpress.com/2008/10/15/crosscheck-xlm/
like I say UDFs have always been rubbish in Excel.
Wednesday, 21st October, 2009 at 1:19 am |
Simon,
Thanks for the example. I didn’t know about the RESULT and ARGUMENT stuff. Even so, I’d think your CrossCheck UDF would be more intelligible as XLM than as VBA to many users (if XLM were more well known, of course). And presumably many nested formulas of the INDEX( , MATCH( , , 0)) variety would be even more so.
If you don’t mind extending the tutorial a bit, would it be legal to have a line like:
=RETURN(IF(ABS(D8)<0.01, Arg1, ErrorMessage & " : " & D8))
in the XLM UDF you linked to? If so, then I definitely think it's a lot more "Excel-formula-centric".
I'm quite late to the Excel party, but I've become very interested in the intersection of spreadsheet modeling, end-user programming, and "conventional" software development. Obviously functions are fundamental to that intersection.
(About F#, I'd say if C or BASIC are FORTRAN's nephews, F# is like a fourth cousin three times removed…)
Wednesday, 21st October, 2009 at 5:11 am |
It was actually this post of yours:
https://smurfonspreadsheets.wordpress.com/2007/02/27/user-defined-functions/
and the comments there that I found while searching a while back that drew me in as one of your regular readers. It is surprising that Excel doesn’t provide a more seamless UDF mechanism.
Wednesday, 21st October, 2009 at 11:33 am |
Macrofun is the best, but there is also this http://www.microsoft.com/downloads/thankyou.aspx?familyId=c09bf7f7-d30e-4ce9-8930-5d03748ca5cd&displayLang=en which is useful as a simple reference.
Of course, the best resource is a good old fashioned manual, such as the M icrosoft Excel Function Reference. It’s the equivalent of macrofun, but of course you can hold it, and throw it at the monitor.I happen to have one that I rescued from a skip at a previous employemnt. My wife thought I was barking pulling stuff out of a bin, but she doesn’t say that now (actually, she does :-(). If anyone would like to offer me in excess of £1,000 for it, I might be tempted.
The post John referenced, https://smurfonspreadsheets.wordpress.com/2007/02/27/user-defined-functions/, was quite a detailed post, with many deep comments, and we end up with … very interesting, but I don’t agree with you … Where do these people crawl out of? I have one guy on my blog repeatedly posting a ‘cool, nice site’ comment under different names!
Wednesday, 21st October, 2009 at 1:12 pm |
Bob
its some kind of lamer spam, I normally catch them and delete them but those last two somehow got through.
Dunno if the linkless ones are them just scoping out where they can add their pointless junk.
I have a 97 dev kit book, I feel the same way about.
Wednesday, 21st October, 2009 at 5:08 pm |
Myself I’d like to see the information XLM functions (the ones that only return values and don’t try to change anything) such as GET.CELL become available for use directly in worksheet formulas. I realize that it may take more to update their values than automatic [minimal] recalc, but that situation already exists when using the CELL function.
As for sources, (dating myself) the Cobb Group published a monthly newsletter on advanced techniques in Excel in the late 1980s and early 1990s. There was a lot of XLM code in them. I have no idea whether they’re still available anywhere. My wife made me recycle mine a while ago. [Darn wives! Why can’t they let us keep out good stuff!]
Wednesday, 21st October, 2009 at 8:01 pm |
Although I wrote lots of macro code when I was teaching Lotus in the late 1980s, they were mainly for doing things like automating printing that I found pretty dull – by the time I developed my interest in financial modelling in the late 1990s I went straight for UDFs and by-passed XLMs
But, like Bob, I’m the proud owner of one Excel function reference for work as well, unlike Bob, a second one for home – and I’m keeping them both