Mike Schaeffer's Blog

I've spent a fair amount of time lately working with code that generates Comma Seperated Value files for loading into Excel. You'd think the format would be trivial, but not quite. One additional subtlety, one not covered in that 'specification', is Excel's inconsistent handling of end of line markers. As it turns out, if Excel loads a CSV file that contains a quoted, multi-line value, it expects a different line feed convention within the quoted value than the usual CR/LF. A CR embedded in a quoted field renders as a box, rather than as part of a newline. To suppress the box, CSV files for Excel need to be written with a LF-only convention within quoted values. Even then, Excel will not automatically expand rows containing a multi-line value. That has to be done manually.

Internally, Excel seems to follow the same LF-only convention that this issue with CSV files seems to imply. Taking the CODE(...) of each character in a manually entered multi-line cell value, shows only one charater, a LF, at each line break. My guess is that the quotes in a CSV file just act as a signal to turn off all special character handling, not just handling that signals new rows and cells. Either way, it's more than a little irritating that Excel compatible CSV files with multi-line values have to have two seperate end of line conventions.

Recently, I was asked for advice on how to learn Excel macro programming. I'm not the expert, but I read sites and books written by folks who are. Here are some useful links.

Websites:

• An Introduction to User Defined Functions
• Chip Pearson's Website
• John Walkenbach's Website
• The Daily Dose of Excel, an Excel community blog
• Microsoft's Excel 12 Blog

Books:

• Professional Excel Development : The Definitive Guide to Developing Applications Using Microsoft(R) Excel and VBA(R)
• Excel 2003 Power Programming with VBA.
• Excel Add-in Development in C/C++

The last book is particularly interesting: it focuses on the C/C++ API to Microsoft Excel. Since Excel 5, when Microsoft introduced VBA and the Excel COM object model, this programming mechanism seems to have fallen out of vogue, but it is still being maintained, and represents a way to write really fast and secure Excel add-ins.

Thirty days hath September,
the rest I can't remember.
The calendar hangs on the wall;
Why bother me with this at all?

— http://leapyearday.com/30Days.htm

Here's an Excel one liner that computes the number of days in a particular month. Cell A2 contains the year of the month you're looking for, Cell B2 contains the months' ordinal (1=January, 2=February, etc.):

=DAY(DATE(A2,B2+1,1)-1)

This is mainly useful to illustrate what can be done with Excel's internal representation of dates. Dates and times in Windows versions of Excel are normally stored as the number of days from January 1st, 1900. You can see this by entering a date in a cell, and then reformatting the cell to display as a number rather than a date. For example, this reveals April 1st, 2004 to be represented internally as the number 38078. This is because there are 38,078 days between January 1st, 1900 and April 1st, 2004.

The formula above relies on this in its computation of the number of days in a month. The sub-expression DATE(A2,B2+1,1) computes the date number for the first day of the month immediately following the month we're interested in. We then subtract one from that number, which gives us the date number for the last day of the month that we are interested in. The call to DAY then returns the number of the day within the month, which happens to be the number of days in the month.

At my job, we use Excel extensively to keep track of software testing progress. One typical use is to maintain a list of features to be tested, along with their current pass/fail statuses and an attempt at a rough subdivision into functional areas. Excel's AutoFilter then makes it easy to ask questions like "show me all failed tests relating to function block scheduling."

This works really well as long as "function block scheduling" is one of the categories into which you've subdivided your features list. If it's not, you have to get a little creative to filter your list. One approach to this problem I've found useful is filtering based on columns populated with a formula similar to this:

=IF(ISERROR(SEARCH($K$5,K6)),"No","Yes")

If column K contains feature descriptions, this formula returns "Yes" is the description matches the search string in K5 and "No", otherwise. Filtering based on this formula makes it possible to display every list item whose description matches a word. If there is more than one column to search, you can use string concatenation to aggregate the columns together:

=IF(ISERROR(SEARCH($K$5,K6&L6&M6)),"No","Yes")

So, why the name apropos? Follow this link.