Data types 2

I’m surprised that I seem to be the only one who often knows the data type I expect. Maybe I should do less ‘poor mans ETL’ type work?

Would it be useful then if cells could have a fixed data type? one that didn’t get overwritten by pasting (unlike the rather flimsy data validation)? one that was at the cell level, not in the cell entry event (unlike the flimsy data validation*)

*What I mean here is is if you have a formula in a cell (with data validation) that results in a valid value, everything is fine. If you then change one of the precedents of that formula to give a invalid result, the data validation does not trigger. - I’m not saying thats bad or wrong, what I am saying is something that was more robust be useful?

Also what do you think of being able to give cells a unit (like miles, km, hours or something) that could then be used to check you are not doing something dumb. (I’ve lost count of the number of times I’ve added pounds, dollars, yen, and euros or whatever - luckily having the yen in there make that blunder obvious). I’ve heard this units suggestion a few times, I’m not sure how much use I would make of it, what about you?

cheers

Simon

Do you know the data type?

When working with cell values in code, do you generally know the data type you expect in the cell?

Or do you often need to assign the cell value to a variant or whatever and then look inside at the type before deciding what to do?

I reckon most of the time I know as I’m coding what type I expect, basically either a double or a string or an empty cell (and the occasional error 2042). Normally the type of processing is dependent on the data type, and usually I work out the target range before starting processing.

I think its quite rare I would not know, although there are times I might want to check maybe. What about you?

cheers

Simon

Microhoo

Thank goodness that nonsense is over. Perhaps now we can get back to focusing on a compelling desktop story.

(With Excel at the centre of course)

The bad news (poor Q1/Q3?? performance) got released and buried under the pile of Yahoo speculation. Job done.

I wonder what the diversion will be next quarter - buying Google? buying Red Hat? Windows 7 beta?

What do you reckon?

Did anyone seriously think buying Yahoo was a good thing? who for?

[EDIT]

I reckon Yahoos share price will plummet, MS could probably buy them for half the price in 6 months.

cheers

Simon

End User Development

We’ve had a few (sometimes lively) discussions about ‘our profession’, roles and responsibilities, IS/IT departments etc.

I thought I would just state my point of view and how that drives many of my posts in these sorts of areas. ‘Our profession’ sits somewhere around the end user developers, perhaps slightly more technical and slightly less business focused. What do you think?

I firmly believe that End User Development (EUD) is vital. I also think it should be encouraged and supported, and I know from research and personal experience that in many orgs it is not. Lack of user involvement is the number one reason for development failures.

I also believe that Excel/VBA is one of the (if not _THE_) most important end user development tools. Technically it may not be the best I accept, but culturally, in many of the numerate business disciplines it is the most familiar.

I totally accept that much EUD is not the best quality, however I would argue that if the customer is satisfied then its good enough. As the customer is the developer this only becomes an issue if systems get deployed beyond their original design brief. (assuming the user/dev is a responsible professional)

Much as I like VSTO and .net and like developing in them personally, I don’t really see them as EUD tools.

A refresh of the VBAIDE on the other hand would be a massive boost to EUD, as would integrating all that VSTO/VSTA/.net goodness into the basic Office setup and Excel and Access and maybe Word.

Those techs that I’m a bit lukewarm about are those that I see as having limited use to the EUD world which I see as so important. As IS/IT depts see Office as EUD and wouldn’t lower themselves to touch it, I wonder what will change to make these techs more popular - more responsibility to end users? or massive cultural change to IS/IT to find the love for Office dev?

Do you think EUD is important?

Do you think it is widely encouraged in the organisations you know?

How do you think these inbetweenie techs like VSTO, Excel services will get adopted?

Other thoughts?

cheers

Simon

Asus Eee 1.0 impressions

I promised a further review when I had had chance the sample the delights of actually using the Eee (701) as a portable, rather than on my desk. (where its connected to a decent screen, keyboard and mouse)

I reviewed some research papers on it the other night whilst out and about. That saved my a ton of time.

Its very portable, not quite small enough to fit in my coat pocket, but easy enough to carry. And with the low price its not a massive risk to leave around, or get knocked. Plus the fact its not my main dev machine eases the worry

For reading PDFs and writing in OpenOffice Write it works very well. Calc works fine too, although with big spreadsheets you notice the cramped screen.

It had a flat battery so I charged it for an hour and a half, that was good for about 3/4 hour of what I would consider to be very light use. It claimed to be half full but I think they are meant to do about 3 hours. Anyway about an hour is enough on your lap I reckon before the RSI really kicks in. I’m pleased I was mainly paging up/down, I think using the trackpad for mousing around would have been hard work.

The screen although small is very clear, I was reading at about 80% zoom before I upped it to 120% or something to save my eyes.

It can project much better resolutions than it can display, but that means you only see a corner of the screen image on the machine, whereas the audience sees the whole thing. If you want to get interactive that becomes tricky.

In terms of the included Linux OS and app stack - this all works well and I find its fine for pretty much everything except developing for Windows/Office. The odd crappy web site doesn’t display right, but the main ones do. I havent invested the time I wanted to learning more about Linux so I’m still only scratching the surface. But that is the great improvement of modern Linux - you don’t need to be a ubergeek to boot the machine, its as simple as any other OS.

Performance is fine for most ‘normal’ stuff, apps can take a while to get going, and I wouldn’t call it snappy, some javascript sites really drag. I’m not sure I would want to try and run some of my spreadsheet monsters on it.

The newly announced 900 has a bigger screen (9″ instead of 7″) which excellent, its a bit bigger physically which is bearable, but has a daft power brick instead of the mobile phone style power lead of the 701, and its a 100 quid more. Dunno if I’ll get one, when they finally arrive. As I mainly use this one connected to an external screen the 900 doesn’t seem to offer much. We’ll see.

Anyone else got one, or similar?

cheers

Simon

Is Excel Services a trap?

Excel is the tool of the business user, often to implement systems that the IS department has refused to do (either by saying no, or quoting ridiculous costs/timescales).

We have discussed here many times this cultural conflict, and how this is a factor in Excel popularity/usefulness.

So Excel services could almost be considered a bit of a trojan horse. IS (begrudgingly, of course) sets it up on a server for the business to use. Business starts to move many of their horrible spreadsheets to the new server. Suddenly IS have control of many new critical business processes. What will they do then? Lock them down? Lock people out ‘for their own good’? ‘rationalise’ them? Insist on migrating them to .net? webify them for no reason except to bolster IS developers CVs? break them? make them unavailable at key times? De-prioritise them? Set up a proper test/production split? Look after them as well as, or better than, their originator?

Who knows, but the point is many business spreadsheets represent failure of IS to deliver an effective alternative. Is Excel services that alternative? Or should we be holding out for a full N-tier/big iron solution?

Do you see Excel services as a short term bridging type technology to help IS and the business work more closely together? With most of those spreadsheets eventually migrated to SQL/.net type architecture?

Or is it a ‘for the foreseeable future’ technology?

I reckon it makes a good halfway house and if it can encourage collaboration between IS and the business that can only be good. I would expect many critical systems to move to a more mainstream techs within a year or 2 of appearing in Excel services. That will generally be a good thing I think, what about you?

cheers

Simon

Microsoft Q1 results out

EL reg has a brief summary here.

The general view is that Vista has not helped the financials, and there is still a question mark over Windows XPs June retirement.

Of course I’m not bothered about that I want to know how the Business division that owns Office has done.

Revenue down 9% from Q1 last year apparently. There are suggestions that that may be due to an artificially high Q1 last year due to Office 2007 coupons or something - although I don’t remember that being discussed last year. (’fastest selling version ever’ is how I remember it)

I wonder what component, common across several of the key Office products could have discouraged 9% of potential customers?

cheres

Simon

PS Of course I have no idea of the real causes of the results, but why let that little fact get in the way?

I guess Office 2007 could be booming and Navision could have bombed, but something big must have slumped to drag the whole division down 9%.

Anyone got links to decent analysis?

State of the profession 2

The European Spreadsheet Risk Interest Group will be having their annual spreadsheet risk/quality conference in July in Greenwich. (Eusprig). ‘In pursuit of Spreadsheet Excellence’ is this years title.

Greenwich is a mere stones throw from the City.

How many City institutions will be represented there?

A handful, a dozen at the most.

Why? they certainly know about the conference and the valuable information and networking opportunities.

They certainly use spreadsheets, they have even started to own up to using them, and many are even accepting they are a strategic resource.

The reason the don’t come (according to people who absolutely know) is they are worried about being seen to admit they might have spreadsheet quality concerns!

I guess that means the perceived value of attending is less than the loss of face of being there! What a shame.

I’m sure these same orgs happily attend security conferences without feeling they are dropping their pants in public.

Thanks for the responses to the previous post, I’m pleased to see some folks are not seeing such a bleak picture. SBs role especially sounds like exactly what smart orgs should be doing to get the best return on investment.

Cheers

Simon

State of the profession

Dick M asks if we (Office developers) are a dying breed. At least I think he’s asking - he could be telling us our profession is wilting away. Whatever, I agree. Completely.

Office developer, Excel developer, business developer, these mean pretty much the same thing to me, its a person nearer the business than a traditional IS department dev. They probably use a range of tools/platforms, they mainly target desktop rather than server, Excel usually features as at least part of the interface (or calc engine), source data may be from a server database, but working data is likely to be in jet. Glue code may be VB6 or .net but most likely VBA. The best differentiators (from mainstream devs) are around business acumen rather than technical tool choice.

Anyway Dick mentions a few items, here are my thoughts:

Value for work - Bad and getting worse, its getting so only City institutions understand the value of Excel, which is bizarre considering their normal sheep like tendencies. ( I suppose they are flocking round each other).

Respect - Bad and getting worse, for years I was embarrassed to admit I did a lot of work in Excel, as ‘real devs don’t touch Excel - hobbyists only’. Then as Eusprig gathered momentum I got a bit more confident, but I think that quality/error movement has stalled a little now. So back to claiming to be a mainstream dev for me I reckon.

Continuity - Bad and getting worse, I used to coach client staff in what I was doing in Excel/Access/VBA etc. These days they turn their noses up, happy to pick up some SQL or C# but no Excel/VBA thank you. I just don’t see many people rushing into our technologies.

Conclusion - yeah we’re proper fooked. Unless MS are going to wake up to their most valuable asset - MS Office on an MS Windows desktop. By wake up I mean the hand in pocket wake up, not the cheap soundbite one thats been (pointlessly) running (well walking) for as long as I can remember.

I don’t think us devs will be the losers we’ll just move to techs where the opportunities are better. The real losers will be MS as customers move to other platforms that have an apparently better cost/benefit story, but only because they are unaware of much of the benefit of the MS Office platform. [Ignoring for the time being the cost/no benefit fubar known as the 2007 UI shuffle]

The other losers of course will be businesses far and wide that have to wait for their IS departments to implement their grossly over engineered, over priced, over due, big iron monsters, just in time to realise the business requirement has moved on.

The winners are of course the IS departments who gain control, power and loads of low pressure work. If you know you will release straight into retirement (rather than production) there are lots of unpleasant things you can forgo, like testing, and documentation for example.

Is this how you see things?

or are you seeing different trends?

How do you think things will pan out in 3-5 years?

Do you have an escape plan?

Please comment here or on Dicks post

cheers

Simon

Access queries

Marcus recently reminded me of one of my Access maintenance headaches.

Do you write all your queries as queries and store them visible in the query window?

Or do you keep them in your VBA so they are easy to manipulate?

Or do you mix it up to give the maintainer a hard time?

Just wondering as I’ve been caught out a few times tracing the logic of the visible components in other peoples dbs, only to find they are quietly modified in code (from multiple places).

I often use Excel as the UI and build queries in cells, but I do try and consider the maintainer (just in case its me ;-)).

The last jet project I did I added all the SQL as querydefs, that made debugging much easier as I could open the .mdb and tinker with the queries via the Access UI.

What do you tend to do?

cheers

Simon