Archive for the ‘error’ Category

Linguistic gymnastics

Saturday, 11th June, 2011

As I’m sure many of you know I am a fully fledged European, living it up in a multi-country, multi-culture, multi-language, (limited understanding ;-)) kind of a way.

At my current role I am running the multi language version of Office in English – which is pretty good (apart from Outlook obviously, that does not respect language settings properly). I have Windows set to English interface too, and that works well too.

Google and its spooky spammy ad network seem completely unable to respect my language choice. I would never notice this as I usually run an ad-blocker but here at work we get the full pointless hit. I had no idea the internet was so infested. Even on my blog here I have seen ads, I didn’t think it was popular enough to warrant it.

But anyway I just wasted a couple of hours of my life trying to understand why some VBA wouldn’t work.

Its something I have done 13 1/3 times before with no problem, but here, today, no go. writing a formula from VBA to a cell – no bigee.

ActiveCell.Formula = “=CONCATENATE(….”

I built up my string carefully using an in cell version as a model. I copy pasted it into a cell no bother. And yet when I tried to run it, it errored. With the ‘oh so helpful’ ‘Application or object defined error’. It would have been easier and more honest for that to read ‘Error, tough shit’

So anyway, I wondered if it was an R1C1 thing, I have been burnt by that a few times, so I changed all my code to be R1C1 stylee, and tried to set ac.formulaR1C1, still no go.

I noticed it was a long formula, so I cut out a big chunk of stuff to get it in under 256 chars, still no joy.

Then I thought maybe I had too many arguments, so I changed my code to just do 3, still no joy. In the immediate pane (pain) I tried activecell.formula = “=A1”, worked fine no problem.

So then I asked another dev to help, then, as is often the case as soon as you ask someone else, it finally it hit me: with nl settings the argument separator is a semi colon ; whereas in en it is a comma ,. Edit replace ; with , and job done, my formula worked. VBA always talks to Excel with en culture. So my simple immediate window test formula worked fine, but anything with multi-arguments failed. there is a point there about making tests realistic I guess.

So there it is that’s what I did the other morning morning, I relearnt that I need to build formulas in VBA using commas not semi colons. Of course I could ‘just’ change the windows regional settings, but anyone who has done that will know what a can of worms that opens. for example VSTO add-ins wont install, CSVs wont import, and a million other things I have not been burnt by recently, no, regional settings is best left alone.

As a matter of interest I find it very difficult to get used to entering formulas via the UI with ;’s instead of ,’s too. nearly every time I get a slap for using a comma.

Have you had any multi lingual challenges recently?



Too much cheese

Wednesday, 30th March, 2011

Its deja vu-tastic round here at the moment, best cut back on the late night gorgonzola!

When C# was released it aimed right between VB and C++. VB was seen as lacking in a few areas, and C++was seen as too hard/dangerous/inefficient. C# slotted nicely in the middle.

Many VB devs made the move to C# and enjoyed the extra power and easy access the the .net framework.

Most C++ devs stayed away though, claiming it wasnt powerful enough. And lo over time all those things that made C++ hard have been added into C# so it now as complex as C++ ever was.

Imagine where we would be if, instead of creating a brand new language they had invested all the time money and effort into STL and Boost, C++ would be IT. whatever the question.

But at least they addressed the power issue in C#, and in 4.0 they have improved the Office interoperability.

And so to user interface.

‘No one modifies their Office interface so we have taken away the facility to do it’, oh hang on – that was Office 2007, in 2010 its back in – but very clumsy (could change).

Now, to me this adding back in is a big thing, because it shows a couple of vital things:

  1. Their user experience ‘data’ was misleading, and this is an acknowledgement of that (they wouldn’t add (back) a feature that ‘only 2% use’ would they?
  2. Microsoft can and do listen to their the community, and will make things right, even if ‘evidence’ does not back it up.

I think point 2 there is brilliant news. I just hope they can fully discredit the CIP ‘data’ and undo all the mistakes over reliance on it has led them to make.

But mainly I am pleased and re-assured they are listening – 2010 is shaping up to be an Excellers Excel.



Who nicked my data?

Friday, 25th March, 2011

1467 records went in

1453 came out



I had great fun recently tracking this down.

The system creates an Access database on the fly, adds a bunch of tables and queries, populates it all then pulls the final answer of the stack of queries back into Excel. I could have done it in one SQL statement, but it would have been the size of a book.

Anyway when running it from Access everything was fine, when triggered from Excel someone nicked (a few of) my records!

Back to Access, everything fine, Excel wrong, Access still ok…

Eventually, after much digging, I remembered a previous painful ADO encounter. One of my queries was using the ‘*’ wild card which is fine in Access, but of course in ADO it is ‘%’!

One of my colleague then suggested a neater solution than the brute force approach I was about to commit:

SELECT blah blah from blah where (x Like “*fut*”) OR (x like “%fut%”)

This will work with either driver without the need for separate queries (the route I was considering).

Have you been burnt by this different wild card issue?



European Spreadsheet Risk Group 2010

Friday, 23rd July, 2010

We had another excellent Eusprig Conference last week – congrats to the organisers.

Lots of interesting discussions both within the sessions and outside, and of course in the pub.

Some highlights:

Sumwise is a new spreadsheet-alike product that allows more structured models, runs in the browser locally or remotely. It looked really good, I can see a whole class of problems that it fixes very elegantly.

EASA presented on their tech for publishing spreadsheets to web servers for browser based usage scenarios. Having built a few of these Excel-runners myself (I still have the scars) I appreciate what’s involved. I liked the way it would work with any spreadsheet and is not as picky as Excel Services (2007 anyway, 2010 is more accommodating).

ClusterSeven were talking about new value they are discovering for clients by tracking cell changes over time. They are able to build up not just validation trends, but also business, pricing, economic etc trends. I suspect converting all that unstructured tat scattered across the average spreadsheet forest into mineable information is more valuable, and a better sales story than the hunt for ‘potential’ errors, or mischief.

Dean Buckner from the FSA described their current views on data risk, and it close relation spreadsheet risk/end user apps. I always enjoy the clarity with which Dean explains what the FSA care about and how those things should be addressed. For example sometimes just a written policy is fine, for other areas the FSA want clear practical evidence.

There was some interest in trying to create a generally agreed set of best practices, with caveats as required. I’m not sure if this is something Eusprig will officially endorse/sanction, but I think its something they must if they want to maintain credibility. You can’t spend 10 years saying ‘what about the spreadsheets?’, and then offer nothing to help.

I was disappointed to miss some of the academic papers which ran on a different track. I am not a fan of the Eusprig 2 track approach. I don’t think there are enough people interested in this area to divide further, and I think the current conf length (1.5 days)  could be extended by 3 hours to allow the academic stuff, perhaps on the Friday afternoon.

So instead of hearing the evidence of how names can impair less experienced users we had a half hour slot about why a certain modelling company use names extensively. This was a little long on hyperbole and a little short on fact/evidence for me. And it unfortunately failed to address all the real world scenarios that make many experienced commercial devs wary of names in the real world.

My favourite (repeatable) quote of the event was actually just after
Ralph Baxter the CEO of clusterseven was explaining some of their new features/use cases to me as we ascended some lift in the tube system. As we got the street level some bloke turned round and said….
Drum roll please…
“Ralph thats the best elevator pitch I have ever heard”
That bloke turned out to be Mel Glass from EASA, we all then spent the next hour discussing the harsh reality of corporate spreadsheet use. (And some of the opportunities around at the moment)

One of the people pushing for some generally approved spreadsheet techniques was Morten Siersted from F1F9. Of course we will never all agree about the minutiae (note the interminable named ranges debate). But it has to be better to have reviewed a well thought out approach and decide where you will adopt and where you won’t and the supporting reasons.
FAST is one of these well thought out approaches, and its free/open source, non commercial etc etc. And unlike some of the others, Fast stands on its own. there are no chargaeble tools required to implement or test it.

Its here.

I’m not sure where the best place is to discuss it, but I do think we should discuss it. I’ll maybe do a more in depth post in the next week and we can discuss it there, or if FAST put up a discussion blog post that would be even better.

I’m not sure which is the most contentious, climate change or spreadsheet modelling/developer standards?

We’ll see I guess.

Did you go to Eusprig? what did you think?



ps I managed to use the é and the è on my Swiss keyboard today.

Eusprig 2010

Tuesday, 22nd June, 2010

Pack yer party frocks its nearly time for Eusprigs annual conference.

In my opinion this is a must attend event for anyone serious about professional spreadsheeting. It should also be required for anyone contemplating spreadsheet management/migration/quality/control projects. Why make all your own mistakes when you can invest 2 days, a couple of hundred quid and learn from others, bypassing a heap of pain (and cost) for your own organisation/project?

There are two key aspects

  1. The technical conference content, including several schools of thought on best practice (not sure whether to bring my cornering kit, or my gum shield and boxing gloves) spreadsheet quality project post mortems, and lots of original research. And not just wishy washy stuff – proper, well designed experiments that really focus in on key issues. I am particularly looking forward to the slot demonstrating that names make spreadsheets harder to understand. I’ve had plenty of rows on that very topic.
  2. Social/networking – where else do you get the opportunity to buy drinks for big cheeses at the FSA, and big cheeses from the spreadsheet audit/management software vendors, and student bludgers? At least that’s how I remember it.

Full conference details (including dates: Thur/Fri 15th – 16th July, and venue: Greenwich Uni London, England, and agenda etc) are here.

I’m giving a keynote first thing Friday (which seems a bit harsh!).

Why not combine it with the Excel Dev Conf for the full spreadsheet-oholic effect?

Are you going to Eusprig??

If not why not? (serious question) – what would need to change for you to attend?



Comedy support

Wednesday, 16th June, 2010

I had a call from a user the other day about a report that had stopped working.

I had a look at the VBA, not bad, but damn hard to follow (suck a loads of stuff into a random set of arrays of various dimensions, shuffle it and miraculously drop the right values into a range elsewhere.) well normally the right values – now it wasn’t working right.

I adjusted the caller parameters to fit a minor change in the data, tested it and had no clue if it was working. (It’s that type code that gives you no clue what its doing just presents the answer (Paul Daniels stylee)). I sat with the user to understand what it was meant to do. Lots of buggering around and a few back and forths, until it finally dawned on me:

The last few rows were being cut off by the print area!

Change the print area, user happy, ticket closed. Only about a bazilion hours longer than it should have taken – d’oh!

I truly can’t remember the last time I printed out a spreadsheet, and thats my excuse and I’m sticking to it.

Do you print out much stuff?



Excel Developer conference London Monday July 12 2010

Wednesday, 9th June, 2010

Here are some more details for the forthcoming Excel Developer Conference on Monday July 12 2010.

Outline initial Agenda
A one day event focussing on real world development with Excel. The event targets Corporate Excel developers and individuals considering selling Excel add-ins, tools, and templates on a commercial basis. There is an excellent mix of the business of Excel tools and the technical aspects of getting the best from Excel. And probably most usefully it is an excellent (and rare) opportunity to network with fellow professionals operating in the same technologies.
The presenters are all commercial developers, most of whom have been selling Excel related products for years.
The format is relaxed and informal, the numbers are strictly limted to maintain a friendly atmosphere. (the expectation is for an intimate event of 20-30 people)

The business of Excel:
Back Office systems and activities to sell on-line
security, piracy, sales and marketing tools
(both of these are based on the real world experience of Charles Williams and his highly acclaimed Fast Excel optimisation toolset).

Technology of Excel:
How to choose the most appropriate implementation technology, both in a corporate environment and as a independent software vendor.
Creating fast User defined functions with .net and ExcelDNA and XLL+, and by hand.
Using Excel as a window to your corporate data
Designing Excel based systems for easy maintenance, support and modification
Extending Excel with VSTO and Add-in Express based add-ins
Review of Office 2010 and Visual Studio 2010 and the possible implications
Discussion of the future direction of Excel development, which technologies to back which to drop.
Panel based questions and answers – we guarantee to answer all pre-submitted questions at the event, and will follow up any on the days ones we can’t answer there and then.

Currently confirmed:
Charles Williams – Excel calculation master
Ross Mclean – international lute playing spreadsheet developer
Me – general spreadsheet botherer and Visual Studio tinkerer

A key part of these event is the opportunity to meet other people going through the same issues. We will be in the pub the night before and the night after to discuss all things Excel, corporate, commercial and technological, and possibly a little footy too.

The event will include unlimited tea and coffee but the midday meal is left open to delegates to arrange. Some of us will no doubt be going to a local pub.

In order to ensure that cost is not a barrier to attendence, the event is priced at only 150 GBP (+vat) for the day.

I will put up a booking and payment page on the codematic site in the next day or 2.

Any questions, comments or topic suggestions – please leave a comment below.



non error error

Wednesday, 2nd June, 2010

how come this = 0 and not #REF or something?


=SUM(G8,#REF!) = #REF! as expected

=SUMIF(E7:E12,#REF!,F7:F12) = 0 too.

All in 2007 (and 2010), I don’t have a 2003 to check just now, is it the same? (just the SUMIF obviously, you dinosaurs don’t have the luxury of SUMIFS ;-))

OpenOffice returns the #REF! I would expect.

Would you expect a formula to return an error if one of its required arguments is an error?

In fairness if there are #REF!s in the data it matches them and returns the total, so in a literal sense it ‘works’, but I’m not sure its what I would expect. What about you?



Excel DDE

Monday, 26th April, 2010

Just recently we have been having some fun with DDE at work.
For those of you young enough to not know what DDE is – well done.
DDE (Dynamic data Exchange) is a 1980’s/1990’s technology for enabling disparate systems to pass data between themselves. As with most Microsoft technology of that era they favoured ease, and getting the job done over security. Hence they now discourage its use.

Of course that doesn’t stop most of the market data providers using it.

Anyway, long story short, I wrote a server in VB6 so I could play with it in Excel and I wrote about it here.

I had a quick look around and it didn’t seem that obvious how to write one with C#, anyone got a link?

It seems that if you have a bad enough Excel crash you can hang the whole DDE messaging process, well somebody can, in our case we suspect IE.

Anyone else doing working with DDE?

Anyone actually developing a DDE server app?



Why I love and hate project management methodologies (long)

Friday, 26th March, 2010

I’m thinking of documentation focused, process map driven, meeting oriented, service level agreement based, gated review, buzzword encompassing, busy work, job-fer-life methodologies. Perhaps there are others but I havent seen them in the wild.

So why Hate?
Well, here is my ‘how to address a business need’ shopping list in preference order (fave at the top)

  1. Drop in an off the shelf system (nice in theory, rare to find a good match)
  2. Customised off the shelf system (the breeding ground for big methodologies)
  3. In house IT team developed ‘mainstream’ system (often relational database CRUD with .net/java/whatever business logic and presentation layers.)
  4. Professionally developed spreadsheet based system (pro means knows spreadsheets, knows databases, knows coding, knows software development. (funnily enough there aren’t many people who fit this bill))
  5. and last  but not least, an end user developed solution, probably in a spreadsheet (or friendly desktop database).

This list is based on my estimated cost/benefit over the lifetime.
In a great many cases a spreadsheet solution will be the quickest and cheapest, no doubt, but their lifetime is often short, and they can be a maintenance burden.

The purveyors of snake oil methodologies are mainly feeding off their big consulting firm background in implementing massively customised, big fee earning ERP systems in the job-fer-life timescales. End user developed spreadsheet proliferation is the direct response to the gross user neglect that is core part of these big systems. ‘Take the pain now, we have a brilliant system that will fix this’… in 2017! Isn’t SAP the one that no-one has actually finished implementing yet?

In a 100 dev year, assembly coded, man-on-the moon project, yeah, sure bring in a heavyweight methodology suitable for life critical systems. For a two week spreadsheet spring clean? give me a break. For modern business systems tools like .net and java etc I think the tools are powerful enough that if the initial design is good the tools can be used to adapt to the inevitable emergent requirements.

The idea that a burdensome methodology will improve business systems quality is utter nonesense. The handful of systems that finally makes it through might be of some sort of higher quality by some measure somewhere. But the shanty town of spreadsheet hell that sprouts up to cover those things that didn’t make it in or out really really isn’t an overall step forward.

These methodologies always stink of waterfall to me, and surely that is the most discredited swear word in business system development.

I think these plan, document, approve, analyse, document, approve, design, document, approve…. processes really increase the risk of building the wrong thing too late. To me they are just so much more risky than partnering with the business to jointly develop the required system with trust. And they back load all the risk as the customer gets zero value until close to the end when if the system meets their then current need all well and good. Compare and contrast with iterative development where the customer gets a rough partially working system that address their most critical needs first and then is gradually extended and expanded to meet their developing need. These methodoligies don’t consider this because they were all developed back in the 60’s when the waterfall was the bible and C was just a glint in K&R’s eyes. (remember the 60’s was 50 years ago!!)

So when the the IS department implements their SLA based, documentation heavy, change control hell what actually happens?
Crap spreadsheets spring up absolutely everywhere (thats the least favourite from above for the hard of reading (I didn’t put offshoring on there)). Like the most tenacious of tenacious weeds these instantly become a huge support burden. The support effort takes away from the replacement development effort and the whole thing spirals down out of control like a dying fly that got burnt on a (non eco, non carcegenic old skool filament) bulb. Buzzz..t.

So thats why I hate them – they directly cause a massive deteriation in organisational data quality and cause major paralysis, scores of missed business opportunities, and lots of frustrated business people.

And thats why I love them – those frustrated business people who are busy building castles in the sand with their ratty spreadsheets who have just had their confidence in their in house IS team shattered will be wanting someone to fix their spreadsheet hell at some point. Thats a job for us.

The other reason I love them is even more mercenry. Places I have worked that adopted these heavy methodoligies have a rapid loss of their more dynamic staff. So there are normally a few vacancies there too. And as a contractor you don’t normally have to adhere to their box ticking extravaganza.

So: hate they because they kill organisations love because they provide great work opportunities.

My biggest relief is that I didn’t cough up for that Prince2 course I considered years ago – I couldn’t work in a Prince2 environment so I’m better off with it not on my cv.

Any of you hiding behind an SLA? checking your traffic light (or kimball ball these days, fashion, eh?) progress KPI dashboard against the inevitable landscape printed gantt chart? (what size paper is yours?(dot matrix fanfold??))

How many of you have worked on short term systems whilst the customer waits for the ‘big fix’? (I have, loads)