More VB pain
23 Nov 2004 
very late at night 
Matt Winckler
It’s been a long day again today hammering away at VBA, and my opinion of the language has only been reinforced. Things I have learned today:
- A function called from an Excel spreadsheet cell cannot modify any other cells on the spreadsheet, including cell formatting. Furthermore, said function cannot modify its own cell’s formatting, either.
- The accepted (and indeed encouraged as “good coding”) practice to write code in an Change event handler without having those changes set off an indefinite loop of Change events is to suspend event handling for the entire application, run your code, and then reinstate application-wide events.
- Column/Row coordinates (i.e. [X,Y]) are backwards from the entire rest of the world, including other parts of Excel itself–VBA demands that such coordinates be specified in (Row,Column) pairs.
- There are virtually no useful events in Excel. This includes glaringly obvious things, like inserting or deleting columns/rows. Instead, the accepted practice is to write a function, put it in some unused column way off to the right, have it get called whenever the worksheet recalculates, and then check to see if the function is still in the same column it started in. If it’s not, that means a column has either been inserted or deleted. Furthermore, if you want to prevent such an insertion or deletion, you are instructed to write the function as above, and when you detect a column change, call Application.Undo. Application.Undo!!
- Visual Basic, in addition to using a single quote character to indicate comments in-code, has no capability for handling multi-line comments. Instead, it has buttons on the toolbar (”Comment Block” and “Uncomment Block”) to insert or remove quote characters at the beginning of each selected line in code.
sob