Archive for December, 2008

Over the last few days I have been spending more time in MS Excel than can be considered good for my health! As I tweeted this morning, this has obviously hasn’t been without its frustrations!

I don’t often use Excel for more than basic stuff, but this time I am putting together a front end to a simple reporting tool in our internal Confluence wiki. More to come on that in a later post, but allow me to get this off my chest first!

First up, I wanted to do some string concatenation. Easy I thought, only to find out that the CONCATENATE() function does not allow a cell range to be used as a parameter!

Google to the rescue, where I came across this great little snippet of VB which does the job nicely:

'*****************************************
'Purpose: Concatenate all cells in a range
'Inputs: rRng - range to be concatenated
' sDelimiter - optional delimiter
' to insert between cell Texts
'Returns: concatenated string
'*****************************************
Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


Next I wanted to create some links between workbooks. Easy right!? Its easy enough to create the links, but I didn’t like the full filepaths it was using:

C:\Documents and Settings\User\My Documents....\

So I went to look for a way of creating relative links….

It turns out that in some areas, Excel hasn’t exactly kept up with the times. This article gives us some insight:

it just can’t be done in Excel. A *LONG* time ago Microsoft wrote the original Excel version 1 for 512K Macs. Those beasts had a nonhierarchical file system and mostly only one floppy drive and no harddrive, so there could only be one file open with a given base filename. And Microsoft decreed this was Good & Sufficient, and it hasn’t changed at all since. Yes, all OS’s under which the most recent 3 or 4 versions of Excel runs now have hierarchical file systems spanning multiple local and networked drives, but Microsoft seems not to care to expend the resources to bring this bit of Excel functionality out of the mid 1980′s.

So a dead-end there then!

The same article goes on to finish with an assessment of things….

Excel simply hasn’t evolved from its original implementation in this respect. It’s nice to imagine Microsoft fixing this, but it probably won’t happen until well after they provide built-in support for flashing text. Microsoft seems to prefer adding eyewash to adding serious features that have been in competing spreadsheets for over a decade.

Sad, but likely true. I had to chuckle, rather than cry.