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

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

Excel consulting firms

Where are they?
Where are the multi person Excel, or other spreadsheet, consulting/development firms?
If this technology is as important and useful as we think it is, and its hardly new (mature I think is the correct terminology (one step before legacy)), then why aren’t there more/any 10/20/100/200 person specialist firms?
Sharepoint has them, why not us?
(Is it because that is a ‘professional’ tech and Excel isn’t?;-))
Its like Excel dev is locked in the artisan phase and never going to move towards a more ‘engineered’ approach.
I’m not overly worried from a quality POV as I think if you get the right artisan you will get quality far beyond some tick list based ‘way’.
Its credibility that worries me, I think a few formalised big consulting firm standards would give the whole market a bit more clout. Even if they did conflict massively.
So where are they, and why aren’t there more?
cheers
Simon

First thing

Whats the very first thing you look at/for when you open a workbook from someone else? (that you trust not to be malicious)

And whats the first thing you look at/for when looking at someone elses code?

For spreadsheets its neatness/layout of whatever opens
too neat = gold plating/time wasting
too scruffy = slack/ little care

(Of course the ‘correct’ level of neatness is the sort of stuff I produce ;-)). I’m thinking use of formatting, use of number formatting, use of space etc.

I’d like to pretend thats its some kind of rational measure (file properties maybe?), but really its just a judgement on the initial visual appeal.

For (VB/VBA) code it is Option Explicit - missing = cowboy everytime. If its a snippet/function then I look to see if parameters variables and return are typed (as in Dim … as …., rather than dim x, y, z)

What about you?
one only for each category.
And is it relative to your own work? or do you think its an absolute measure?
cheers
Simon

I think they mean us

Guerilla IT

How to love your superusers!

The article is a bit more related to sys admin type stuff, and thinly veiled sales pitches, rather than deep and dirty Office dev. But I like the principle - instead of IS battling against the power users, support them and encourage them - same effort, much better outcome for everyone.

Of course, back in the real world there is the small matter of something called FEAR…

I have worked at the odd place that worked this way and supported and encouraged those users pushing the limits. But most places are some way off I reckon.

Anyone else worked somewhere where IS encouraged them, or are we all brow beaten into submission daily?

cheers

Simon

Consistency

important or overrated?

I think I am gradually getting more consistent in more things as time goes on and I find ways of doing stuff that I think are the best in the circumstances.

But every project is different and in many I will do a similar operation in different ways. Sometimes because I am searching for a ‘best’ way. Often because I forgot I already solved this in a previous project (even with the nagging deja vu). Sometimes I know I solved it before but can’t find the right project.

I do have tons of library code that finds its way into nearly all my projects, for managing Excel, managing Essbase, menus, common constants, ADO, logging etc.

I am thinking things like working with certain types of list, managing application state etc. Stuff that is not totally general but is in 1 in 3 projects or something. And I mean consistent across projects, not within one.

When I work with other peoples work if I find the same thing solved is many different ways in the same project I would probably start to worry. But if I got 2 projects and one used the app.rows approach and the other used the used range intersection approach it wouldn’t bother me as being inconsistent, would it bother you?

I think some consistency is handy enough, but overall I think it could be a little overrated, especially if we are constantly learning.

What do you think?

how consistent do you think you are (beyond library code stuff)?

cheers

Simon

Old dog new tricks

I have had a few set backs recently.

The Ribbon ruined my main work environment (that would be Excel for those that are new here), and Subaru released a new pig ugly Impreza that is even uglier than the hideous bug eye version they got ridiculed for a few years ago.

The deal was when I gave up my Ducati to be a responsible parent I could get a scooby doo instead. I did, and its ace, but its approaching time to change it and I can’t think of anything in the world that would make me buy that new bush pig (offers to the usual address though! ;-)).

That lead me to wonder if it was just a case of being and old dog not capable of new tricks. An accusation that I think was levelled at me when I first brought up how crap the ribbon is.

I was reassured then to read this Following broad condemnation of the conservative five-door hatchback shape which shows at least I am not alone among the scooby faithful. Rather than devalue the current model, I think the new one turns it into a classic, a bit like Excel 2003.

And back to computing, since Excel 2007 was released, I have changed browser to Firefox, changed email to Thunderbird, I now use Linux as my main web stuff os (on my magnificent Asus Eee). I’ve also moved to VS2005 (for C++) on my dev machine, reviewed Resolver including playing with Python and tried a bit of Ruby. And I have started poking around VS2008 have also been working with MS on Office 14. And of course I started this blog.

Outside computing, I’m training to be a football coach (thats proper football, involving foot and ball, not any kind of hand throw misnomer) and I have my eye on some other new stuff too.

In short I don’t think my dislike of the ribbon can be explained by some fear of change, or some inability to pick up new things. I think the only rational explanation is that is doesn’t support either the way I work or the work that I do. Other user interfaces do.

What new stuff have you been looking at this last 12m?

cheers

simon

Software developers are patronising

I just switched on my pc and went and brewed up. It turned itself off - thanks.

I read some software developer guidelines years ago that the default action of any dialog should be to leave the computer as is. Great! that saves idiots from accidentally destroying their computer. But it also means the default action is to ignore the users request. That seems pretty dumb/arrogant to me.

The Apple approach (as I understand it) is to let the user do whatever they want, but make it easy to undo. That seems to make more sense than to make it hard for them ‘for their own good’.

I did try turning off the ‘are you sure you want to delete that file’ warning, as I know I can get it back from the re-cycle bin, but I like the comfort of clicking Yes (without thinking) to the warning so I put it back.

I find that I turn off many of the Excel warnings, as they are covered by undo anyway. Things like ‘are you sure you want to overwrite those cells’ when pasting is turned off (along with all similar warnings) as I know I can either undo or revert to a saved version if things go badly wrong.

Do you agree that some of this stuff is patronising?

Do you turn off the warnings (Excel and elsewhere)?

What do you do with the systems you build for others?

cheers

Simon

Getting a range

How do you get a range in code?

If its a marker range (a single cell where I start my code from) I tend to name it at worksheet level then access it via

sheet1.[StartPoint].row/.column/.value etc

If I am looping through cells I use

.cells(r,c).value/.formula etc

I almost never use range(”A” & r) as it has a (part) fixed text cell reference that often ends in tears if someone modifies the sheet.

Bob P was outraged that I used [StartPoint] instead of Range(”StartPoint”), he now thinks I am a total cowboy. What do you think? if you don’t like [], why not? I was going to test the performance but its completely irrelevant as I only access these once.

I wish worksheet level defined names popped up in intellisense as a worksheet property, like public code thats added to a sheet class. This [] approach seems like a reasonable compromise to me. Obviously I’m not going to be wasting time wrapping a cell ref in a property (or variable) just to make intellisense do what I want. (Or should I?)

Whats your preference?

cheers

Simon

Browser wars

Did the browser wars kill VB?

I’m only asking as years ago (say 1999) it was fine to build intranets using VB script on the client, because you could assume they would be using IE.

I don’t hear so much of that now, it used to be ‘VB everywhere’, now it seems to be Javascript most places and C# where possible.

I’m wondering if the reduction in love for the VB family coincided with IE haemorrhaging market share to Firefox? (Ff marketshare is almost 30% in Europe, and gaining, fast).

Maybe VB fell out of favour a bit before?

Or maybe you don’t think it has?