Test harnesses in production

You wouldn’t believe how the cold dark winters evening simply fly by here (well at least I managed to get home  even though 4″ (100mm for the metricians) of snow fell this afty, the first November neige in Geneva since 1980 allegedly). And its still dumping it down – might have to throw a sickie tomorrow and go sledging with the kids.

Anyway the big argument today was test code.

Should you or should you not put your VBA test code into production?

Should you strip down your project to the absolute minimum clean prod only code?

Or should you leave in the code you used to test your production code? (assuming you are one of the 3 VBA devs worldwide who bothers to test of course)

My vote is to leave it in, and even though I was in the minority at school today that doesn’t mean I’m wrong, yet.

If your test code is crappy and distracting then yep take it out, no probs (in fact take out all the crappy distracting code), but why I think decent test harness code should be left in:

1. It helps show what the code is meant to do
2. It helps when you are trying to fix something later in production at a users’ desk.
3. It shows that someone did some testing sometime
4. If you make significant edits to your code you should retest, if that edit was removing what you thought was the test code how will you check you haven’t broken something? add a test harness???

What do you think? what am I missing that these clean code freaks can see? Remember I’m not for a minute suggesting leaving in a load of random junk scattered throughout a project. I’m thinking of separate modules or at least sections with a bunch of meaningful tests that exercise the main functionality of the system in a controlled way.

What do you do?

cheers

Simon

Be careful out there

Just in case you thought the most serious biscuit issue of our time was whether jaffa cakes are a biscuit or cake, look at this.

If their figures are to believed (and why should we?) nearly half of us have suffered a biscuit injury. Presumably excluding fat-knacker-itis.

I’ll fess up to scalding my tongue dunking rich teas, but of course its illegal to eat them dry, not to mention close to impossible. And the mess left if they drop into your Yorkshire tea is the slime whose name we speaketh not.

In fairness you have to be pretty sharp to get a look into the biscuit barrel at our house:

Whats your worst biscuit related injury you can talk about on a public blog?

(Extra points if caused by a bourbon)

Cheers

Simon

Excel VBA .net developer available

My current contract is drawing to a close leaving me available for new challenges. A casual scan of this blog and the codematic site should give an idea of the sort of work I do.

In summary: Advanced Excel development with VBA, C# and C++, Relational and OLAP databases

I normally work in and around financial services organisations, most recently commodities trading.

Happy to consider any location and remote work, currently I’m in Geneva. Contract or consulting. Available end Dec/start Jan 2011.

Drop me a line via here.

cheers

Simon

 

Performance

In my ADX review I mentioned that the XLL UDF performance was not as good as some other technologies. But I also mentioned that some of the other features probably more than made up for that for many people.

The reality is for Excel/Office development we have a wide range of choices, some Microsoft, some not. Some open source, some commercial.

There is not one single technology that is best at everything. Sorry if you want an easy ‘Excel is the answer, now what’s the question’ (Or VBA etc) type scenario.

I currently have live add-in projects using VB6, VBA, XLM, VSTO, ExcelDNA and XLL+, and raw C, and I guess I have VS2008 and VS2010 C# stuff going on too. And I hope to add ADX to that list soon. Within all of those I would say each one uses the right technology given the requirements. No VB.net – sorry, the improved interop in C# 2010 would put another nail in its coffin – if we could be bothered to dig it up.

The add-in tech choice is not easy, and is possible through something that a few manager types don’t really seem to understand – real world experience. Books and forums can help but its only when you try and deliver real solutions to real users with real needs that you see how the techs really work. For example I moved one of my projects off VSTO because I didn’t need the click-once goodness or the start up delay.

I’ll write more over time on the criteria I have applied when selecting the most appropriate technology, and how to spot when you are going down a dead end and how to swap. I’ll also write more detail on my xll performance tests because I have done a fairy extensive shootout.

Do I have a favourite add-in tech? No, not one, its not that easy. But if performance is critical then you have to use the C API not the automation interface. And if an attractive interface is important then you are going to need something a bit richer than XLM 4.0 dialogs. Then there is the whole pre-requisites excitement…

Do you have an overall favourite add-in tech? What other techs are you working with? Anyone doing any Java/Excel stuff?

cheers

Simon

Add-in Express Review

In amongst all my other busy-ness I have been reviewing a few Excel developer tools, or in some cases Office developer tools.

Addin Express is definitely more of a general Office development tool than Excel specific, but it does create xlls which was my main motivation for the tool reviews.

Addin Express (which I will call ADX from now on) has been recommended to me by several of my Office developer buddies, so I was keen to try it out. For anyone who was at the UK Excel developer conf in London in July you might remember that I demoed creating an RTD function using ADX and it is very simple.

As I said my main initial interest was xlls, and so thats where I’ll start.

ADX has a novel drag and drop approach to xlls, but before we get to that, when you create an xll project you get to choose which language from VB, C#, C++, J# (remember J++??), or Delphi. And on the first dialog is where you get the first hint that an ADX xll is not like an XLL+ xll for example, it will need a proper install (setup project).

Next you get a further hint that this xll isn’t just a native dll with some Excel specific exported functions:

There is some serious COM/.net plumbing magic going on in the background. The big point to note here and one of the biggest appeals of ADX (IMO) to an add-in developer is the ‘version-neutral interop assemblies’ choice. That gets you the ADX IA’s which allow your .net code to work with any sensible version of Office. I’ll talk more about this later because, we would expect an xll to work with every version of Excel since 97 anyway.

Click finish and you drop into code view,

double click on the XLLModule.cs in the solution explorer, and drop pane appears where you can drag UDFs

For a hand coder like me its all a bit novel, but for a more normal, visual developer I think it would be far more second nature.

When you open the toolbox (there’s a clue – I never use it, as I never do UI development).

Here you will see a region titled ‘define your UDFs in this section’, which to be fair sounds like a pretty good place to define a function. Inside is a sample function you can uncomment or just copy the principles from. Its pretty easy, none of the C++ data type pain, use standard .net data types and ADX will do all the marshalling for you.

Its a static class so you can either define all your functions as static now or wait for the compiler to tell you to later .

public static double addSome(double d1, double d2)

{

return d1 + d2;

}

That’s C#, without wanting to start a fight (much ;-)), I think VB.net is a lost cause. Nothing wrong with the technology, I just think its future is somewhat less certain than C# or C++.

Anyway from an ADX pov we now need to get this function visible in Excel correctly, and for that they use some drag and drop GUI loveliness. Double click or right click the XLLModule.cs to view its designer surface, then right click that and ‘add an Excel function category’.

You now have a button like thing whose properties you can set as if it were a button, mine is called clever functions. This is the function category you will see if you click the Fx in Excel, so its worth doing something notable.

To this category you then assign your functions, this is a good hierarchical approach to building out the functionality of a non trivial add-in as you may want to categorise your functions into usage groups to make it easier for user to find and use your functionality.

When you right click and select Edit functions you get to fill in this dialog:

You add you description then assign the functionName (from those functions you already coded in the XLLModule class) that you are talking about. If you have a help file then you can connect the function to it here. Hidden and Volatile are best left as false.

You can repeat this process until you have all the functions you want to expose to Excel in the left hand pane of the dialog above. The next step is to explain the detail of the parameter of all these functions. The order you do it is not important, but you right click on a function in the left hand pane and choose Add Function parameter.

You have now defined a function that takes 2 doubles and returns a double. The current implementation in the old C# just adds them but we can change that later without going through all this interface stuff.

When you are happy with your functions and want to try them in Excel you need to build and register the add-in. Build, Fair enough create some binary thingymabob, register? Well, these ADX xlls, as I mentioned before are not ‘pure’ xlls, they need some registry magic to work correctly.

Click Build>>Register ADX Project to set up the registry side of things, from now on you just need to rebuild as normal.

To debug you will need to set Excel as the start up program

When Excel starts your xll is loaded automatically, no security warnings, no searching in \debug to load it manually, and all the functions are ready and available.

(Is your add-in list that clear??)

Actually when you look in the add-ins list in 2007 (assuming you are (un?) lucky enough to working in ‘Excel Vista’, you will see the add-in is actually a dll, although ADX exposes it as if it were an xll/xla(m) one. IE its in Tools>Add-ins rather than COM add-ins.

So the key question for xll johnnies

Performance?

In my very limited testing I reckon that an ADX xll is about 3 times faster than VBA, which is pretty reasonable. It was close to 5 times slower than my test raw C xll which isn’t so brilliant.

I will add reviews of the other feature as I get chance to investigate. I can’t leave though without a little moan about the ‘getting a copy of Addin express’ process. I downloaded the trial, with the plan of installing it and reviewing it at the airport. I was somewhat fed up to find that you can’t install it without a key that you need to download. Without a wifi connection that meant I had to buy a mountain bike magazine to fill the hour wait instead. I’m not sure what chance I’ll get to look at other features as I wrote version one of this review several months ago, and I think my trial will have expired by now.

Summary/conclusions

An ADX xll is a bit more effort to deploy than a native code one, and the performance is not as impressive. But it does offer easy access to the full COM OM, unlike native code xlls targeting the C API only.

The choice is yours though really, ADX is easy to work with, version independent, targets all add-in technologies and all Office products, shows a significant performance improvement over VBA, and is reasonably priced. And offers improved security of VBA, which seems to be a fairly common question I get asked at the moment.

I think its a very valuable tool that every serious Office developer should have in their toolbox. You can download the docs here, they don’t really do a trial version, but you could try asking if you are serious.

I know lots of you are already using Add-in express, what are your views/experiences?

cheers

Simon

The Volatility Virus

I mentioned in a recent post about some hassles we had with RTD. Here is a bit of background.

In a daily reporting environment it’s pretty common to use =NOW()-1 or better still =TODAY()-1, or even better still (in 2007 anyway) =WORKDAY(TODAY(),-1) to get yesterdays date to run your close of business reporting.
All of these are volatile, and in the grand scheme of things that’s probably not a massive issue. They are volatile because they have no way of checking if their inputs have changed, mainly because they don’t have any. So they have no choice but to calculate each time Excel does.

Unfortunately volatility is like a virus, a bad one, oh I don’t know, like say that flu bug that sir cough-a-lot at the desk next door has. Every function that depends on a cell that contains a volatile function becomes sneezy volatile.

Here is what I did to test that hypothesis:
I created a xll udf, that takes one parameter that it ignores completely, that logs each time its called.
I pointed it a static cell, change an unrelated part of the worksheet and the function is not called. I take this as evidence that my udf is not naturally volatile.
I then entered =NOW() in an unrelated cell, my function is not called
I then enter = NOW() in that precedent cell, now any change anywhere in that excel session causes =NOW() to update, which causes my udf to log another call. My udf argument is declared as an integer (J) so the bit of =NOW() that makes it into the function never changes, but unfortunately Excel is not checking that level of detail and calls my udf anyway.

I also checked about 20 levels of indirection and got the same result – the whole stack becomes volatile. It might only infect udfs, I don’t know, I’m not sure how I could reliably test native functions, any ideas?

None of this is a catastophe until your function return causes a recalc – exactly what RTD does.

XLL PLus to the rescue again.
So we have this common use case where people use a volatile argument to an rtd function. The RTD return will trigger a recalc which will update the volatile function (potentially with the exact same value). That cell update will trigger a recalc of all dependent cells which will trigger an RTD call…
So one solution is to avoid calling RTD if the inputs haven’t materially changed. And this is exactly what you can do with a couple of clicks in XLL+. You just click the ‘cache results’ checkbox and first time through your RTD version will call, next time XLL+ will take the value from the cache without calling RTD, no return, no recalc – problem fixed.

I’m sure there are plenty of other ways around this, but fundamentally I think in Excel 2007 they will all depends on the principle of avoiding that second RTD call by checking nothing important has changed since the last time that function was called with those parameters. Or mandating that users don’t use volatile functions as input parameters (good luck with that…).

Interestingly we started to go that route, and came up with a LastValidAsOfDate(ticker) function. And guess what? we realised we needed to make it volatile to update overnight…

I wonder if a more finely grained volatility is needed? something like the throttle that RTD already has but maybe update every hour or something? But then would you have different levels of volatility, how would you tell Excel the update frequency? Perhaps Excel could always check the first argument of volatile functions as use it like a timer?

I don’t know the answer but as it is in Excel 2007 the RTD implementation is pretty fragile, I think. Unless I’m missing something?

Anyway another big thumbs up for XLL+, the more I use it the more useful I find it.

Have you experienced this issue? Any suggestions? A better way to get some asynchronousicity into Excel udfs?

cheers

Simon