Dawn of realisation

For pretty much as long as I can remember I have wondered why people in organisations persist in doing things in Excel and VBA that would be sooo much better in almost any other technology. We have discussed many of the reasons in the past but I got hit by a new one the other day:

The corporate process for requesting the right tools for the job is so long winded, painful and uncertain that no one bothers. Its quicker, easier and less pain to build an Excel monster instead.

To me this is a disaster, a dereliction of duty by IT, and just an all round FAIL.

IT should be proactively monitoring the markets and trialling interesting looking software and services with a view to proposing them to the business. Not sitting back, blocking all the interesting websites and file shares waiting to obstruct any other attempts to get the job done. (Hardly the work of a Strategic Business Partner is it?)

I have been through these processes a few times, some stuff like getting your Office install upgraded to Pro to get Access is normally a few clicks, an approval email and a cross charge. Getting approval to deploy some VSTO components and some C++ dlls at one place was a bit harder, including a half hour interview on the benefits and risks. I totally don’t mind needing to justify this stuff, but in many cases IT should already have proactively tested and approved it, imo. (or in the case of C++ understand they already have it all over)

I was looking at these processes for not so standard stuff at a few places that publish the detail, and sure enough it can be 6-8 weeks to get approval for an install. That’s ok if its bill and bobs virus ridden utility, but if its a part of what should be core infrastructure then you’ve already missed the boat.

So if you are in IT here are some tips

Developers need developer tools, they can and will develop without proper ones, but it wont be pretty – and it will be your fault for not providing the right tools.

Users need intelligent flexible access to complex data. If you don’t have the right tools, (Think OLAP/BI), then it is your fault when they do it badly in Excel.

just sayin…

What is the most long winded, laborious, misguided IT process you have had to go through? was it for software approval?

cheers

Simon

Happy Schtroumpf day

Today

 

Code execution has been interrupted

by whom?

not me!

I had this issue a couple of weeks ago, but for some reason (possibly an update) it seems to have stopped.

As my code was running, for no apparent reason this dialog kept popping up. It seemed especially prelavent when writing data to cells. In the end I had to wrap all my writes with

   ‘Application.EnableCancelKey = xlDisabled
   wsLogging.Cells(m_outrow, col).Value = wsLogging.Cells(m_outrow, col).Value & ” | ” & message
   ‘Application.EnableCancelKey = xlInterrupt

Anyone else bothered by this? I was going to try cleaning the code, but now it seems to have gone away. Or possibly it was someone elses code, and/or some interference from another addin? Whatever, I can’t seem to repro the behavior anymore.

If it makes any difference I am using a multilingual Office (2007 –  12.0.6550.5004).

so I am curious, did anyone else see this problem? did you work out what caused it? did you find a better workaround? has it fixed itself?

cheers

simon

ODTUG

I wish I could justify going to this.

In fact if it was in Europe I would probably go even though I can’t justify it.

Its been a while since I worked with Essbase, infact I think it was before Oracle bought Hyperion. But I think I might have an Analysis Services project coming up, so I’m feeling all OLAPy.

I am currently working with Oracle everyday, but that is not quite enough to justify the trip.

Maybe I should try and blag a speaker slot for next year (they have some Excel content this year)?

Any of you going?

got any other events in your gunsights?

cheers

simon

 

Eusprig 2011

This years spreadsheet risk and quality extravaganza is almost upon us. 

It is exactly just less than a month away in mid July.

You can book here.

I am not presenting this year, as I thought I would let someone else have a turn speaking (and of course I missed the submission deadline).

In fact I probably wont be attending as I’m not sure where I will be working/holidaying then.

I would be expecting a good talk from Patrick as we worked together this year on a few spreadsheet related projects. Indeed he came face to face with the source of several of my formula horrors from previous years!

oh looks like he is not presenting this year, but on the bright side there is some more original research on the power (or not) of range names, amongst other interesting papers.

Here is the (current) draft outline schedule.

Are you going?

Cheers

simon

Getting the data right

I was chatting with a fellow dev recently.

He said for him the most important first step (in design/development rather then analysis) was to get the data right.

I started off disagreeing and suggesting the most important first step was proving the concept will even work. But then in our discussion and thinking about it after I decided that I agree with him. To an extent anyway.

My new most important first step is to be clear how you will be able to check the data is correct. Because it may work in concept, but if you can’t prove your data is correct your system cannot be proven correct, and therefore not a right lot of use. I use prove and correct here in the traditional business consulting sense rather than legal. (correct = near enough to blame the old system, prove = no one will find out before you are out of there, invoice paid.) ;-)

We were talking about a standard, load data to sever db-run some queries-extract to Excel reports, kind of a system.

What would your most important first step be? (assuming you have some idea on what the users want/need of course)

cheers

Simon

Linguistic gymnastics

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?

cheers

Simon

New Excel Performance blog

Charles Williams from Decision Models, makers of FastExcel, has started blogging on Excel and UDF performance issues.

Charles has spoken at many of our Excel user and developer conferences, writes for MSDN, runs the de facto standard Excel performance site and sells the de facto standard Excel performance testing toolset, so he doesnt’t need any introduction from me…

The blog is here.

cheers

Simon

pure joy

I loooove this!

I might even apply for the job (it might be the only chance to get tickets…)

cheers

simon