Me and Aunt Marge

I'm hopeless. I'm an old, narrow-sighted, rigid UNIX-school developer. I will never be able to raise above the cultural differences between Windows and UNIX. Let's face it - I don't like Microsoft. After finally reading Joel Spolsky's article on Biculturalism I can appreciate the reasons behind the "Microsoft culture" but I guess I'll never be able to associate myself with it.

My wife has recently completed a course on VBA in Excel. A couple of days ago she suggested we sit together and try to implement something. Well, I thought it would be fun. If only I knew how wrong I was!

Now, what bothers me is that if Joel is right about this "Aunt Marge" philosophy, I wonder how it applies to VBA in Excel. Right, my wife is an accountant, not a programmer. But programming macros in VBA is as close to programming in a non-programmers' world as one could possibly think. Let's put it this way - I would be surprised to see this application in the Aunt Marge's toolbar (unless she has dragged it there by mistake).

Anyway, we opened Excel and started. Here's a spec of what we intended to do. In a certain column, find a cell that matches a certain template - this indicates a start of a section. A table is expected to be located two rows below this number. The table is concluded with a "total" line. Copy the initial number and the "total" to a new row in the second sheet. Repeat the search process until the end of the sheet, adding another row to the second sheet every time. Fairly simple.

Well, as a programmer having plenty of the first virtue, I assumed there must be a function that finds a cell matching a certain pattern. So I opened the Help and searched for 'find regular expression'. Great - here's a Find function! I'm starting to write the VB code, but when I hit the opening parenthesis I pay attention that the parameters in the tooltip do not exactly match, so to say, the parameters in Help. It took me a while to realize that there is more than one version of Find. No problem, I tried to locate another. Didn't work out. What does a frustrated programmer do these days? Google it! Surely, within a second I was able to find an article about the "correct" Find method.

Now, it seems that Microsoft developers have decided that since most of the programmers use Google anyway, API reference is no longer necessary. The internet's article hinted that in order to look for a regular expression, one should use the parameter "SearchFormat". Excel's help gives the following information about this parameter: SearchFormat - Optional - Variant. Description - "The search format". Duh! It's great they use a word breaker for automatically producing help files. Surely, no more light is shed on the mysterious "Variant" parameter in the Remarks section. And I'm not mentioning that the syntax of regular expressions is nowhere to be found. Oh, sorry, I forgot - Aunt Marge doesn't need those!

On the other hand, the mentioned Remarks section contains an absolutely remarkable (sorry for a wordplay) note that has shaken my concepts of good and evil. The values for the various optional parameters are saved each time you use this method. Which means that if you don't specify their values on the second invocation, the values from the first one are used! It seems that the designers of this particular piece have never heard of the Principle of Least Astonishment.

This is actually a very good proof of the Joel Spolsky theory. This behavior stems from pure GUI thinking. It's perfect for humans to have their previous choices remembered. For programmers it's disastrous because it makes the behavior unpredictable. I can bet there's a corresponding UI dialog for this Find function and it's simply a bad design to have this logic anywhere lower than the UI layer. But the Microsoft's culture is so "Aunt Marge" oriented that perhaps they simply don't have enough people who know how to write software for developers.

I'm ashamed to admit that I gave up trying to make this Find method work for me and resorted to some hand-made search with a While loop. Each one of us has his limits, sorry.

By the way, one of the times I desperately hit F1 brought me a third version of Find - something completely different! Which reminded me why back in 1999 I fell in love with Java. In Java there is only one type representing a sequence of characters - String. Young programmers who haven't seen the Win32-COM-MFC-ATL nightmare won't understand what I'm talking about. But those who remember words like char*, wchar_t*, TCHAR*, LPCTSTR, CString, CComStr, BSTR etc. would be nodding in agreement. Ah, yes, there was also an OleString. Wasn't it? Never mind. I'm sure every single one of those had its rightful raison d'être. But could someone please tell me what does it have to do with Aunt Marge?