Campaign for XLM

I have been doing a load of XLM work the last few days. Its brilliant!

So many of us have made the leap to VBA (and it is a leap from spreadsheet formula type stuff) that XLM is completely overlooked now.

Thats a shame because for real User Defined Functions, once you get over the =ARGUMENT/=RESULT hurdle XLM is way way more accessible to your average Excel user. And its a boatload faster than VBA too.

I do remember when I first looked at XLM many years ago and it made my head explode. A decent book would probably have fixed that though. Now when I use it it seems more intuitive than VBA, and much much less verbose. Ages ago I posted about having User Defined Functions sheets, I reckon XLM macro sheets answer most of my requests.

I have always avoided VBA UDFS because they kill performance when calculation is set to auto (and I’m not a manual calc fan). XLM UDFS on the other hand are fast. I have had thousands in a workbook on auto calc – you just can’t do that with VBA.

The only real downside is lack of folks with XLM skills, I’d feel bad about locking a client into something that so few people could maintain.

Anyone else still using XLM? Any one interested in a post about XLM UDFs? Got any simple VBA UDFs you want converting to XLM? (leave VBA as a comment)

cheers

Simon

Advertisements

21 Responses to “Campaign for XLM”

  1. alastair Says:

    the help says I can’t use these in 2003 onwards – how do you get over that?

  2. Simon Says:

    Alistair
    Thats not something I’ve noticed.
    You have to right click the tab to insert a macro sheet and users get a scary enable/don’t open message but they work fine for me.
    Unless you mean the registry setting sys admins can set to make them harder to load?
    Whereabouts did you see that in the help?

  3. jonpeltier Says:

    At first I thought you said “XML”, and I suspected you’d gone round the bend. Then I reread the title.

    MS has been stating that “eventually” XLM will not be available in future versions of Excel. Insights?

  4. gobansaor Says:

    I too saw X M L, “What, a campaign for XML, has the man totally lost it!”. But yes, I would love to see some practical examples of XLM use as I’ve never been exposed to it myself.

    Tom

  5. Harlan Grove Says:

    I used to have a library of XLM functions back in XL4 days, FWIW.

    One thing XLM wouldn’t provide is access to other applications’ object models. The udfs I use make heavy use of WSH regular expression and dictionary objects. I wouldn’t want to implement either in XLM.

  6. Simon Says:

    Jon
    I’d heard that, I have no insight though. But I’m not sure how easy it would be to rip out, all the xll stuff is related to xlm, and they only just updated that.

    I’ve always been a bit underwhelmed by XML so don’t expect campaigns for that.

    Harlan if that stuff is available in win32 dlls XLM might be a good choice to expose it to Excel. I agree there are significant limits on its applicability though.

  7. Biggus Dickus Says:

    I loved XLM macros …… they made a lot more sense as a “Macro Language” to me than VBA. Sure VBA offered a LOT more bells & whistles, but XLM did the job and in ways that appealed to spreadsheeters like me.

    I do not believe though that we should be using XLM macros anymore. I would think it would be irresponsible to take people’s money for it when there is almost no one out there who could support it if I’m not around (a concern I also have with XL VBA frankly)..

    Dick

  8. Charles Says:

    How about doing an XLM version of this (VBA optimised) UDF?

    Public Function AverageTolE(theRange As Range, dTol As Double)
    Dim vArr As Variant
    Dim v As Variant
    Dim d As Double
    Dim r As Double
    Dim lCount As Long
    On Error GoTo FuncFail
    vArr = theRange.Value2
    On Error GoTo skip
    For Each v In vArr
    d = CDbl(v)
    If Abs(d) > dTol Then
    r = r + d
    lCount = lCount + 1
    End If
    skip:
    Next v
    AverageTolE = r / lCount
    Exit Function
    FuncFail:
    AverageTolE = CVErr(xlErrNA)
    End Function

  9. Rob Bruce Says:

    Charles, as an aside, is looping using For Each on a Variant array quicker than traditional looping by finding the bounds and then iterating using counter variables?

  10. Marcus Says:

    Actually the question I had was on the use of Value2 rather than Value?
    What advantages does this present?

  11. Rob Bruce Says:

    Marcus, .Value2 was touched on here: https://smurfonspreadsheets.wordpress.com/2008/04/02/data-types/

  12. Charles Says:

    @Bruce,

    Yes for each is faster, but its not usually a significant factor in the overall times.

    @Marcus,

    .Value2 makes no difference when handling strings or Booleans

    When handling numbers there are several advantages:
    – its 15-20% faster than .value and often the time to get the data into a variant array is the largest time component.
    – you don’t get the truncation of decimal places for cells formatted as currency that is caused by .Value implicitly converting to Currency data-type.
    – it does not convert Dates to the VBA epoch: this may be an advantage or disadvantage.

  13. Harlan Grove Says:

    Charles, why a udf for this vs an array formula:

    =AVERAGE(IF(ISNUMBER(1/(ABS(-TheRange)>dTol)),–TheRange))

    Would array formuas, even those needing a half dozen built-in function calls, run faster than any single udf?

    This formula would return #VALUE! rather than #N/A on overflow, but that’s about the only difference with your udf I can see.

  14. Charles Says:

    Harlan,

    This particular UDF is from one of a series of experiments comparing different ways of writing UDFs with equivalent formulae (and different technologies for UDFs), which is why it would be interesting to play with a well-written XLM version.

    It turns out that reasonably optimised VBA UDFs are often a lot faster than equivalent array formulae, but VBA UDF speed is very sensitive to the way the UDF is written (the slowest averagetol VBA UDF is about 100 times slower than the fastest), and if you are using many instances of a UDF you also need to avoid the VBE refresh slowdown bug.

    AVERAGETOLE is about 2.5 to 3.5 times faster than your particular array formulae (depends on the number of rows) when calculation is initiated from VBA, but is about twice as slow as the equivalent SUMIF()/COUNTIF() formula.

  15. Harlan Grove Says:

    There’s no equivalent SUMIF/COUNTIF. Your VBA statement,

    d = CDbl(v)

    converts numeric text into numbers. SUMIF won’t. If your range should contain only numbers and empty cells, wouldn’t a bushier

    If VarType(v) = vbDouble Then
    d = v
    If Abs(d) > dTol Then
    :

    be faster than using an error trap around

    d = CDbl(v)

    ?

  16. Harlan Grove Says:

    FTHOI, maybe this XLM udf.

    A1: =RESULT(3)
    A2:
    A3:
    A4:
    A5: =ARGUMENT(“TheRange”,64)
    A6: =ARGUMENT(“dTol”,1)
    A7: =SET.VALUE(s,0)
    A8: =SET.VALUE(n,0)
    A9: =FOR(“ri”,1,ROWS(TheRange),1)
    A10: = FOR(“ci”,1,COLUMNS(TheRange),1)
    A11: = SET.VALUE(v,INDEX(TheRange,ri,ci))
    A12: = IF(ISNUMBER(v))
    A13: = IF(ABS(v)>dTol)
    A14: = SET.VALUE(s,s+v)
    A15: = SET.VALUE(n,n+1)
    A16: = END.IF()
    A17: = END.IF()
    A18: = NEXT()
    A19: =NEXT()
    A20: =RETURN(IF(n>0,s/n,”no numbers in the range”))

    where A1 is the named FUNCTION foo, A2 is the local named range v, A3 the local named range s, and A4 the local named range n.

  17. Charles Says:

    Thanks Harlan,
    Seems to be very slow on large ranges …
    I tried to use FOR.CELL to see if that was faster but could not get it to work for some reason.

  18. Simon Says:

    Great challenge Charles!
    The argument needs to be type 8 not 64 for for.cell to work.

    This one whups your vba for sparse data sets, but is a disaster in large dense ones:

    | XLM AvToTol2
    Result type | =RESULT(19)
    |
    arg1(range) | =ARGUMENT(“theRange”,8)
    arg2 (num) | =ARGUMENT(“dTolerance”,1)
    |
    initialise sum | =SET.VALUE(G10,0)
    initialise count | =SET.VALUE(G11,0)
    |
    accumulate sum | 123
    accumulate count | 10
    |
    | =FOR.CELL(“x”,theRange,TRUE)
    |
    get the sum | =IF(ISNUMBER(x),IF(ABS(x)>dTolerance,x,0),0)
    get the count | =IF(ABS(G15)>dTolerance,1,0)
    |
    wrap out errors | =IF(ISERROR(G15),0,G15)
    wrap out errors | =IF(ISERROR(G16),0,G16)
    |
    set the val | =SET.VALUE(G10,G10+G18)
    set the count | =SET.VALUE(G11,G11+G19)
    |
    | =NEXT()
    |
    | =RETURN(G10/G11)
    |

    Use text to cols to split the comments

    This one is better than the first for dense sets but nowhere near your VBA:

    XLM AvToTol3
    =RESULT(19)

    =ARGUMENT(“theRange”,8)
    =ARGUMENT(“dTolerance”,1)

    =SUM(IF(ISNUMBER(theRange)*ABS(theRange)>dTolerance,theRange,0))
    =SUM(IF(ISNUMBER(theRange)*ABS(theRange)>dTolerance,1,0))

    =RETURN(J7/J8)

    (those sums should be array entered, otherwise it runs fast but wrong)

    I’m sure there is a better way, I’ll have a think. Suggestions welcome.

    This bears out my experience that looping in XLM is sloooow.

  19. Harlan Grove Says:

    Are you really sure

    =SUM(IF(ISNUMBER(theRange)*ABS(theRange)>dTolerance,theRange,0))
    =SUM(IF(ISNUMBER(theRange)*ABS(theRange)>dTolerance,1,0))

    would be faster than

    =AVERAGE(IF(ISNUMBER(theRange)*ABS(theRange)>dTolerance,theRange,0))

    ?

    Also, is some entry in theRange is neither a number nor numeric text, I believe ISNUMBER(theRange)*ABS(theRange)>dTolerance won’t produce the results you intend.

  20. Charles Williams Says:

    The AvToTol2 xlm function calculates faster (3600 millisecs compared to 5500 millisecs) if you hide the macro sheet. But its still a lot slower than the VBA (58 millisecs).

    I have not yet tested using the array formula or SUMPRODUCT in the XLM function, but presumably its faster than AvToTol2 but still not as fast as the VBA udf.

    BTW I am sure I remember someone from MSoft publicly saying that XLM was going away in XL14 or 15?

  21. Simon Says:

    Harlan
    You are probably right, my head was spinning at that point so I gave up.
    I wrote an xll which is about twice as fast as the VBA (which I was a bit disappointed with). Credit to Charles VBA coding, and/or some blunder in my xll code.

    An empty XLM loop ran slower than the VBA so its maybe best to avoid XLM if you need to loop

    I don’t think Ms are talking publicly about 14 or 15 yet (apart from scotching death of VBA rumours), but I have heard those death of XLM rumours (un-scotched so far!)

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: