bike or van?

which is best?

 bike?

or van?

that would depend what you want to do right?

which is best spreadsheet or database?
Err… that would depend what you want to do right?

I can tell you from personal experience – don’t go to collect your Chinese or Indian takeaway on a motor bike. And equally don’t try overtaking on narrow county lanes in a dagenham dustbin.

Same story with technology, for any element of a project, some products will support you, some will fight against you.
Need a quick gantt chart to keep the suits off your back – a spreadsheet is a great choice.
Need to reconcile the output from several different systems – consider a database.
Need to control the changes a user can make – maybe consider a database with a compiled front end.
Need to give the users the flexibility to develop reports and analysis you couldn’t know about – consider a spreadsheet (interface at least).

Time after time I see people doing the equivalent of moving house on a motor bike.

do you agree?

I think there is a delicate balance between using the second best tool for everything, and benefiting from the consistency. And picking the best tool for each job, and having to learn lots of different interfaces etc.

What about you? do you think it is possible (or reasonable?) to bend a spreadsheet to do anything, or do you actively select the right tool for the job?

cheers

Simon

Fantasy security

Got any pointless ‘security’ policies, procedures or features you want to get off your chest?
Here are some of my current ‘insecurity-masquerading-as-security’ things.

First a simple example from joinery.
Its standard to fit a lock to a hardwood external door about half way up. Some bright spark realised that many doors get kicked in so some people began fitting knee high locks as ‘additional security’. Sadly they got it completely wrong – the best place for an additional lock on an external hardwood door is 3/4 height. That way the door just flexes when kicked and absorbs much of the energy. Putting the lock at knee height transfers the force straight to the jamb often causing it to split. Overall knee height locks make it easier to force an entry not harder – fantasy security.

Next an office mangement example – clear desk policy.
If you need to keep paper off the desks at night for security reasons then you have security problems that are too big to be solved by simply locking paperwork in a drawer. If non trusted people have access to your offices then the paperwork is the least of your worries. For example inserting a hardware key logger on a keyboard cable is simple and unobtrusive. Clear desks are fantasy security.

A hardware example.
One UK financial institution had a significant confidential data loss via a USB pen drive. Now some spotty dweeb has decreed that all USB drives should be disabled to ‘improve data security’. Of course people can still email large files of sensitive data to external email addresses, or maybe upload via FTP to a web server. A better solution would be to work harder to minimise the sensitivity of the data people need, for example by substituting codes for names, addresses and card details. Now people who need access to these files are either sending them over an open internet connection, or storing them for longer than before – fantasy security.

And of course a couple of spreadsheet examples (Excel specific)
Workbook open passwords – these encrypt the actual spreadsheet, making it hard to impossible to recover from file corruption. Microsoft don’t see this as a security feature, they would prefer we rely on the rich permissions based security model of NTFS, and I would agree with them.
Worksheet protection – I’ve ranted about this before, but as it stops people from tracing precedents etc it prevents them from fully understanding the sheet contents. How does being unable to confirm logic improve information security? Spreadsheets are not secure (and I’m glad about that), worksheet protection does not make them more secure. But it may lead to misplaced trust. To me its total fantasy security.

I was going to go on about some flight (fantasy) security stuff, but I’ll save that for another day.

What security fantasies are bothering you at the moment?

cheers

Simon
 

Source Control

Charlie asks what people do for source control?

A very good question, and one I would like to know more about too.

Me, I don’t really bother with formal source control for Excel based projects, mainly because its a binary format. If I do an Excel/VBA job the VBA part will probably secondary to the worksheet stuff, and that doesn’t go well into any source control that I know of. I tend to keep multiple copies of the workbook so I can go back to any point. I have tried using SourceSafe with VBA, but it seems a bit pointless to control the code, but ignore the worksheet stuff.

I do use Visual SourceSafe for pure VB6 and .net work, I keep meaning to try Subversion, but havent yet.

What do you do?

Anyone got top tips for managing and versioning the spreadsheet content?

cheers

Simon

The old new thing

Sorry for not posting much this week, one of the reasons is trying to finish reading my new book – The Old New Thing by Raymond Chen.

http://blogs.msdn.com/oldnewthing/

Its a really interesting read, although a bit of C/C++ knowledge helps with some of the real techy stuff.

Raymond is a backwards compatibility fan, and in his book he discusses all manner of things the Windows team and others have had to do to keep things working.

His philosophy is simple – if peoples stuff doesn’t work on the latest Microsoft release they wont upgrade. Joel has a great article about the inner tensions at MS between the ‘MSDN’ crowd who love new stuff and aren’t so bothered about backwards compatibility, and the Raymond Chen school of thought, here:

http://www.joelonsoftware.com/articles/APIWar.html

A couple of points instantly spring to mind:

1. It feels a bit like MS have lost some of that backwards compatibility love with some of the 2007 software
2. It must be well hard dragging around all that compatibility baggage of the last 10-20 years.
3. Security changes everything

Taking 1. first, to move forward you have to break, or at least dent, backwards compatibility from time to time. Most reasonable people will live with that if they can see the benefit (to themselves especially). (I am still not clear what the plus’s of the Ribbon are for me, compared with the huge negatives of shuffling and hiding all the commands I need, and stealing too much of my screen, and not being usefully flexible….)

One of the problems with introducing breaking changes to products is it destabilises the market for add-on services (and other stuff) as people transition. Some providers will dive in there and be in a great position to help the early adopters, some will hang back to see how the market reacts and what the potential business value is. I am in this second group. And as far as I can see Office 2007 work is so far in the future for my clients and therefore for me, its genuinely unlikely to happen. Office 14 is probably more likely I reckon.

I think the big take home point is that the next Office update organisations do will be quite painful, but if they can then lookforward to 10 years of low pain changes then that begins to make sense. Updates, migrations and changes are just part of the normal day to day activity in IT, the key point is to not get backed into a corner that makes this more unpleasant than it needs to be.

 I deliberately havent mentioned ‘productivity improvements’ claimed for all software updates. I have never seen people suddenly going home on time because they got a new os or office suite.

On the second point, past success can really come back to haunt in the long term. When you think about it, MS are really doing an impressive job overall. But I can also see how a younger organisation with a smaller legacy could easily outmanoeuvre them in some areas. I am watching Apple in particular on this point.

On the security front, I think in 5 years time we will look back on the freedoms we have now, and barely be able to believe what we get away with. It will be like seatbelts and kids car seats. Not using these safety devices is almost unheard of now, and you can almost touch the danger if you go 100 yards with out belting up. Yet I remember happily not wearing a seatbelt before it was made compulsory – and I wasn’t scared of instant death.

Although we all know that VSTO is a mega PITA to deploy, and VISTA can be painful security wise, I think that is the pain we will have to get used to. I think the days of happy-go lucky emailing VBA solutions around are severly numbered. Its already almost impossible to email exes around (easily fixed by changing the extension – which tells us something too!), .vbs and .js and .scr also rarely make it through. I really do think our highly effective, business focused VBA solutions will be a target at some point soon. Do you?

I think we are entering an era of increased security, and that will come at a cost of speed and ease of delivery of our solutions. The precise cost, and the speed of uptake I don’t know, but I’m sure there will be plenty of customers for fast, cheap, effective VBA based stuff for many years.

I always thought part of Microsofts success was that they focused on giving customers functionality, where the *nix folks seemed to focus more on security. Its starting to feel like MS are having to change focus.

Anyway back to the book, I highly recommend it as great peripheral reading. Anyone else read it?

Cheers

Simon

Analytic Services

It looks like Oracle/Hyperion has decided on a new name for Essbase. And it is …

Essbase!

Last month they were busy trying to change the name from the well known Essbase to the incredibly-close-to-the-competion ‘Analytic Services’. As that is only 3 letters different from Microsofts Analysis Services, I think they have avoided a lot of confusion by going back to Essbase.

cheers

Simon

another spreadsheet related event

The Open Office.org conference is in Barcelona 19 – 21 September. Thats a slight potential overlap with the UK Excel conference.

http://marketing.openoffice.org/ooocon2007/programme.html

The call for papers hasn’t closed yet, but I’m interested to see what the agenda includes.

cheers

Simon

Spreadsheet dates (UK specific)

Eusprig:

11 – 13 July Greenwich http://www.eusprig.org/

UK Excel User Conference:

17-19 September Westminster http://www.exceluserconference.com/

Both highly recommended for anyone with an interest in spreadsheets. I plan to be at both.

See you there?

(anyone got any other (relevant) dates/events to mention?)

cheers

Simon

Are spreadsheets the language of business?

I would say that spreadsheets (and Excel in particular) are the language of business. There are the way business users communicate ideas and results to each other. Would you agree?

They have other uses for sure but I think the thing that makes them so prelevant is their use for communicating.

I often hear people saying, let’s see it in a spreadsheet because it is easy to follow/prove, or because that makes the analysis visible. I’ll post later about that, but for now would you agree that spreadsheets are a(/the?) language of business?

cheers

Simon

Advanced Training

I’ve done a couple of training courses recently (as a delegate)(I try and do 2-4 weeks a year). One trainer in particular struck me as superb. He was an ex full time developer, but still actively involved in banging out the code as open source.

On the most recent course some of the delegates were recounting their ‘advanced’ Excel training course. The trainer had limited real world experience, and often turned to other delegates to answer questions the trainer could not.

Now its a fact of life that when doing advanced training there is a high chance that some of the delegates will know more than the trainer in some areas. This is great if it can be leveraged to help other delegates, but it can damage trainer credibility if not managed effectively.

But anyway thats not the point of this post. I am wondering if anyone has ever heard of good quality, worthwhile, advanced Excel training?I think most of us here are self taught to a large extent, but I’m wondering if there are any good advanced Excel trainers out there? and if not why not? (note I do a bit of training, but dont push it as a service)

I suppose I should define good: able to communicate effective working practices so that delegates understand and can use the course content.

Training is a funny business, especially developer training, there are so few actual developers, or recent developers providing training. Why is that? Training seems to go hand in hand with authoring (and speaking), but rarely developing. Is it a personality type thing?

I enjoy training and developing pretty much equally, but financially, training in Excel/VBA/Access, and even .net type stuff just does not make sense compared to development.

I wonder if this apparent lack of high quality training has an impact on the numbers of people going forward to do advanced training?

Or is it all happening in-house as on-the-job training apprentice craftsman style? (I hope so, that is a great way to learn).

What’s your experience of the situation where you are?

Cheers

Simon

Geographical proximity

Marcus suggested this topic, which is a subject close to my heart:

–Marcus

Another topic that may be of interest is geographical proximity of work.

Unlike some other development environments – most Excel solutions are reasonably self contained or at least not intrinsically linked to other source systems.
Given this there’s a lot more freedom to do work remotely. For example, the furthest client I’ve done work with is 1,400 kms away in Coffs Harbour.

Does anyone see outsourcing a threat to their work?

Is this other people’s experience doing MSO development? For example, do you do
any work for London clients from home in Carlisle?

–/End Marcus

The biggest reason I came back to Excel/Access/VBA from VB6/C#/.net was exactly this. Deployment, deployment, deployment. Whilst those other technologies have their strengths, the ability to email a single file solution means that Excel/VBA/Access is a viable remote working option. I’ve done SQL Server and Oracle stuff too, and the overhead to work off-site is high (having to replicate the db in my environment). Maybe the Citrix/Terminal Services type apps will make other technologies viable for more potential clients eventually.

I do work for people far and wide, I have current clients in the US that I have never met. I remain unconvinced that I could do the same thing with .net or even COM (as a viable business).

In fairness much of my work is still on site, but I think that is a cultural/social thing rather than technical, and I know I can always do most development remotely.

Outsourcing a threat, no I don’t feel that, simply because the blend of business knowledge and technical skills common in Excel development is not easily replaceable. I think in more purely technical roles it is more of a risk.

What about you?

cheers

Simon