Having spent roughly a week (my first) working in MS Excel I must admit that it is a nice application in a business setting. The cool thing about Excel is that:
- You can do nearly anything in it
I’ve seen quite a few impressive hacks over these days from people without programming skills and even heard reports elsewhere of people implementing bugtracking and similar applications in it. Personally I’ve gone from not knowing anything about it at all a few days ago to doing some quite hefty data mining today, obviously I’ve been able to profit on my prior knowledge in Visual Basic 6 and have written most stuff in VBA instead of writing formulas for it but still. This being the cool part there is a really crappy part as well:
- You can do nearly anything in it
… being this versatile is of course nice but it just doesnt scale. When a critical mass of data is reached, you end up with a workbook too big to handle, inordinate amounts of time invested in it and with no way out but a complete redesign somewhere else. The next logical step being a database this is a big problem since the solution consist of (i) Professional developers, (ii) Infrastructural changes and (iii) Time and money. I wonder how many businesses that hit this wall just because it’s so easy to keep going in Excel instead of taking a scalable route at an earlier stage when breathing-room is still available.
While on the subject of Excel, I found my first serious bug in it today. When recording a macro to create a chart with secondary axes and datarepresentation with both columns and lines you get this code (at least in the swedish version of Excel 2003) :
Charts.AddActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Name“ActiveChart.SetSourceDataSource:=Sheets(”Sheet1″).Range(”A1:A10,B1:B10″), PlotBy:=xlColumns
This will however yield an error when executing, the correct code (somewhat VB’ified using With) is:
Charts.AddWith ActiveChart .SetSourceDataSource:=Sheets("Sheet1").Range("A1:A10,B1:B10"), PlotBy:=xlColumns .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Name“End With
Being used to doing things the UNIX way I ended up hacking all macros by hand instead of clicking them up in the GUI, and reduced a 500 loc macro to a mere 80 loc in the process. If only they had implemented regular expressions in the formulas instead of the enormous quantities of bloatware features..