Resolver

I got a beta version of Resolver recently and had a decent chance to see what it is/what it does. So here is a (basic) screenshot:

Here is some background from Jon Udel no point me re-typing that right? which got slashdotted here

It is a bit like a spreadsheet with a code editing window tacked below. Both Ross and I have tackled this issue with VBA in different ways in the past. Here is Ross’s code to unlock the VBAIDE Immediate window to float over the grid.

Here is my more limited scripting thingie.

The Resolver code is Python though not VBA. But if you can do VBA you’ll find it very easy to pick up the python basics. And its not just a code window tacked on the bottom. The whole calculation process is opened up for you to code into.

They have it split into

• Imports (similar to setting refs in VB/VBA)
• Creation phase (system only)
• pre-constant user code (put your own formulas in here)
• constants and formatting (can’t mess in here)
• Pre-formula user code (can change sheet values etc here)
• Formula code (no messing in here)
• post formula stuff (didn’t use this)

So you can code into several of those phases, but still not moff up the systems stuff. You can quickly set up simple UDFs and immediately call them from cells. Apparently you can also reference .net objects from cells too, I didn’t try that. The code editor is similar to VS2005, with code folding, highlighting changed lines etc.

The big thing that caught my eye is the import export feature – web enable a spreadsheet in minutes (or seconds). Import a .xls specify input cells and export as a python app, job done. neat.

The other thing I took a look at was data sheets. Resolver can pull data in from a wide variety of sources.

I’ve only scratched the surface so far, but its pretty impressive. I’m not sure how much extra time I am going to get to delve deeper, but I think this is something to keep an eye on.

With this and all the other cool apps I want to play with its going to be hard to find the time to pay the bills or develop my fee generating skills.

Cheers

Simon

Excel calc bug effect

Here is the impact of the Excel 2007 calculation bug on sos visitor numbers:

So some people found it not totally boring anyway.

Pre all the excitement it was running at around 300 per weekday (100 at weekends). It peaked around 900, then dropped to 700 and then 500. I’m interested to see what happens next week – hopefully some of the recent visitors will hang around and join in.

The most popular post was the one with the test code. I posted a few links to this around the place. Still waiting to hear if anyone found a live example of the fault. (No, would be my guess at this stage).

Cheers

Simon

Boring proof

Excel 2007 has a calculation bug, no one really knows the potential impact, and no one cares. Mainstream media stay away in droves. Spreadsheets are utterly critical to a great many financial institutions and transactions but nope, no one cares.

Apple update some bugs in their phone (which btw is not underpinning the whole financial world) and it’s instant front page news.

Maybe its the fact that the iphone fix breaks the unlocking hack people have used to get more competitive air-time contracts? Maybe the journos thoroughly analysed the Excel 2007 calc bug error and established it was not critical?

Or more likely spreadsheets are just (even) more boring than mobile phones.

I think the name for the updated patched iphones is an iBrick or iDoorStop.

Was anyone here using one?(past tense!)

cheers

Simon

Excel calculation bug take 2 – not many hurt

I’ve been thinking about this and reading up at various places, trying to decide how big of a deal this is. Hopefully many of you will know I am quite heavily into the whole ‘spreadsheet quality’ scene. So here is my (surprising?) view – feel free to disagree.

I think its pretty much irrelevant. Basically fear of having a problem is probably a bigger issue that actually having a problem. (Eg someone mentioned stopping a 2007 rollout that is part way through – thats not good!)

Reasons why its a bad thing:

• its an error!
• It is mainly presentation but can be made to propagate in calcs
• It gets exported in .csvs
• We need to feel we can trust Excel – this shakes that.
• single cell materiality is massive 65k – 100k is ~ 50% error

Mitigations:

• 2007 market penetration is small
• 2007 enterprise market penetration is V small.
• It only affects a very small range of numbers.
• In most cases it will not propagate – so you need to rely on one of the 2 erroneous numbers being in the face of a report, if they are intermediate calcs the error will probably not flow through to reports.
• Most users probably make bigger blunders than this somewhere in their analysis/reporting process. Published material error rates in commercial s/s are in the 30-90% have error range – but the earth hasn’t stopped spinning.
• No publicly reported cases in the wild (that I have seen – do you know different?)

Summary

Yep its not ideal, yep someone somewhere may have a significant problem, overall in the grand scheme of things this is unlikely to bring the sky crashing down. And yes of course MS need to get a patch out (soon would be good) and make lots of reassuring noises.
What do you reckon? too flippant? right sense of proportion?

Cheers

Simon

Excel 2007 efficient interface

Sorry I couldn’t help it. From a KB article about automation add-in failures:

[How to load an Automation Addin]

In Microsoft Office Excel 2003 or in Microsoft Excel 2002, follow these steps:
a. On the Tools menu, point to Add-Ins, and then click Automation.
b. In the list, click TestAddIn.Class1, and then click OK.
c. In the Add-Ins dialog box, click OK.

In Microsoft Office Excel 2007, follow these steps:
a. Click the Microsoft Office Button, and then click Excel Options.
b. Click the Add-Ins tab.
c. In the Manage list, click Excel Add-ins, and then click Go.
d. In the Add-Ins dialog box, click Automation.
e. In the list, click TestAddin.Class1, and then click OK.
f. Click OK.

6 steps in 2007 compared to 3 in 2003 – thats progress!

If they ‘improve’ that UI much more in the next version it will be 12 steps, Office 14 will need an RSI health warning as part of the EULA. And possibly need to ship with your choice of spare mouse or trackpad.

I hope their documentation folks get paid by the word!

And btw creating the automation add-in refers to VB6!

[if you count the steps its more like 9 v 6 but that wouldn’t be so funny]

cheers

Simon

Excel 2007 rubbish

A couple of people using that search term landed here over the past few days. What could it mean?

Excel 2007 is rubbish?

Excel 2007 calculation/presentation logic is a little bit rubbishy in certain ranges?

Analysing rubbish with Excel 2007?

Producing rubbish with Excel 2007?

Analysing your 2007 rubbish with Excel?

I remember years ago when the UK government introduced a landfill tax. I did some spreadsheets to analyse the cost impact on a clients business. Maybe thats what these folks are looking for? Maybe there is a new stealth tax coming?

Any other ideas?

Cheers

Simon

If…

Rob noticed something in the VBA code I posted the other day to look for that Excel calculation error (or presentation error depending on your view). I always put an Else clause even if its empty. Style? Quality? Habit?

I’ll stick my neck out a little here and say I think which ever works best for you is probably the ‘best’ for you. I personally prefer my way and can justify it (to myself anyway!), but I wouldn’t impose it on another dev. I’ve worked with lots of other peoples code and generally its not that hard to switch, as long as the main things are ok (option explicit, reasonable procedure length, sensible names etc).

Anyway heres my ‘If’ style and why:

If TheNormalCase then
' 'do the normal processing
Else
' 'the non-normal or error proc, or just a comment if no valid else process
End If


I always put the normal/usual/most common case first, all the exception stuff then drops to the lower part of the proc. I only do 3 or 4 levels of nesting max, beyond that I break out into a new procedure. I always put an else (I type the 3 lines together then arrow back up to fill out the logic) and I always consider what to do in the non normal case. If there is nothing to do I just put a comment of why there is no else code. Why bother? Well in Code Complete Steve McConnell discusses some research where missing else clauses was a major cause of bugs. Its his recommendation to put the normal case first too. (I try to do that in IF formulas too).

I rarely use goto but I do if I think it makes the code clearer, or faster if thats critical (and of course error handling).
I also rarely use the one line if statement like

If bOk then doSummat
If not blah than exit sub/function
Simply because there is so often an else to catch, or some tidying up to do.

I’ll post more about other elements of coding style, but I think a lot of it is just that -Style.

Whats your If style? and what are your thought processes around it?

Cheers

Simon

Googlefights

I was reminded of this site recently.

Here is the fight I did

The result is that x is more popular than the ribbon ui (follow the link to see what x is!).

ie: People would rather have x than the ribbon!

I’d say thats pretty conclusive!

whats your favourite googlefight?

cheers

Simon

Hurdles and floodgates

I’ve been avoiding installing .net framework 2 for ages just in case it interferes with client apps I support. But a couple of weeks ago I took the plunge on a spare machine so I could try out Resolver. Now I am over that installation hurdle (nothing obvious broken yet) its really opened the floodgates to try other things that are dependent on it.

Now that I have framework 2.0 installed

1. there is a newer version of the framework available
2. there is a ton of dependent software I want to look at.

Here are a couple of high priority things I want to look at:

• excelDNA – an xll framework to allow writing performant worksheet functions in C#.
• Palo OLAP – a pretty straightforward looking OLAP server. I’ve just watched the vids I can see plenty of uses of this

There were some other things but I seem to have forgotten for now, I’ll have to trip over them again. (Probably around server/shared spreadsheets).

I also need to post about Resolver as that is an interesting product.

Am I the last to update to framework 2.0?

cheers

Simon

Funny old few days

Over the weekend I posted about spreadsheets being boring, and we generally agreed that was the case. And we agreed they weren’t likely to get big headlines in the general press.

On Monday I post a dig at the ribbon, comparing its contribution v the re-written multithreaded calculation engine.

On Tuesday the blog has its biggest day ever (3x previous max visitors) all looking for further info on the recently discovered calculation bug in the new multithreaded calculation engine in 2007.

And the mainstream media do indeed ignore the issue, in fact even the mainstream IT media seem to ignore it.

So the boringness is proven I suggest, all that remains is to decide which is the best/worst Excel 2007 feature:

• Is it the broken UI or
• Is it the broken calculation engine?

It pains me to say it but I think the ribbon did a great job. By keeping so many orgs from migrating to 2007 the impact of the calc bug has probably been much reduced. Thanks User Experience team!

I havent heard of any real world losses (or gains ;-) ) or problems caused by this bug yet. Have you?

Obviously having a spreadsheet that doesn’t appear to calculate accurately is sub-optimal for sure, but where are the real life stories of broken analysis and reporting it has caused? I guess as litigation is removed as an option by agreeing to the EULA most orgs will just bury any problems, like they do with security breaches and user errors.

What would be nice is if someone who used the code I posted got back to say they tried it on x live workbooks and it found y instances of the problem, or didn’t.

And finally a sweepstake on when/how this will get fixed:

1. within days – hot fix
2. within weeks – hot fix
3. as part of SP1
4. as part of SP2
5. Blame the users?
6. full scale denial?
7. Deprecate calculation (Jon P)?
8. Deprecate arithmetic (Jon P)?
9. Other?

I am going to say within a month, separate from SP1. I am guessing this is high priority at Excel central so 2 days to isolate the prob, 1 day to fix it, a few days to prepare a binary patch, a week or so the test the fix in every language on every os etc etc. Date? Hot fix – 18th October.

Although Jons suggestion to deprecate arithmetic would probably work for the many that do the adding on a calculator and just type the numbers into the s/s to print it nice. If they claimed it as a security fix many people might put up with the ‘reduced functionality in certain scenarios’.

What do you reckon?

cheers

Simon