Microsoft Excel

Most any office has tons of MicrosoftExcel spreadsheets lying around. When they get to a certain size, and start thinking about more automation, a common initial task is to take these spreadsheets and convert them to a format that is more scalable, easier to index, etc., etc.

I used to think it was funny that offices do this, instead of just defining a database right away. Recently, though, I've come around. Defining a few DB tables works well if you have a very intimate knowledge of the problem domain right from the start; however, this is rarely the case. You could put this in a database table, but every time you want to change a column format or add a new column, you'll have to hire some expensive programmer to write the SQL for you. Maybe it's an acceptable trade-off to have a tool that lets your receptionist/office manager/whoever change the format, and accept worse performance. Maybe MicrosoftExcel is a good strategy, at least until the spreadsheets grows to 2000 rows.

There's probably a pattern out there that subsumes this, but if there isn't, I'd define a proto-pattern like:

: If the initial data set is small, and you don't know that much about what kind of data you're storing, use a highly flexible, easy-to-modify format to store it. When the data set gets bigger and/or your knowledge about the data starts to crystalize, then migrate it.

There's also similar discussion under PowerOfPlainText.

Excel's hidden talent is the ease in which it massages delimited text files. It's far more useful than a TextEditor.

I've been doing some proto-accounting in MicrosoftExcel, and again amazed at the ease of use. Even though I myself know how to program, nothing beats plunking down numbers in pretty rows and plugging in the right formulas to get me what I want. I know it's not exactly scalable to anything else, but right now that fact isn't bugging me ... I wonder: What would an ideal Excel-killer look like, to offer both the instantaneous feedback of the MS' product, but also be painless to throw that logic into a robust DB-driven app? -- francis

Recently I started using Excel more after several years where I concentrated on 'programming' more than 'spreadsheets'. I noticed that functions were a lot easier to use after three years pythoning around. I also learned (from working with a co-worker) that many excel users don't know how to do very basic things. The Excel Killer I'm envisioning would be able to identify a column or a row as a range and offer several common fuctions (@SUM and others) when the person goes to enter a formula beyond the end of a range. -- SeanOleary

I can here looking for the URL to "Improving the world�s most popular functional language", grateful for having found the document. But the URL wasn't here! Sooo, here it is: http://www.research.microsoft.com/~simonpj/papers/excel/excel.pdf user-defined functions in Excel"

You can view a spreadsheet as a program for a FunctionalProgrammingLanguage. You could think in terms of SpreadsheetAsProgram? ... spreadsheeting as programming ...

Interesting example of commodity software is Worksheet Server (http://www.jedox.com) which turns Excel into a design tool for PHP ( CategoryPhp ) web applications.

Excel's macro recorder isn't very good. It produces awful code, and a major problem with that is maintanace of the code going forward.

Where the recorder does score, is using it as a way of discovering how the object model in Excel works.

Here is an example.

Typically, the recorder will produce code like this.

    Range("D7").Select
    Selection.Font.Bold = True

OK, now you embed this in a function, and you call the function. There is a major problem. There is a side effect. The current selection has been changed. Now it becomes very difficult to compose these functions into bigger functional units.

However, the solution is easy. Just remove the select and selection parts.

    Range("D7").Font.Bold = True

Does exactly what was intended, with no side effects.

The only time I've really needed to use selection was for context sensitive double clicking. You can use the select when you want to use it for navigation.

CategoryFunctionalProgramming? CategoryMicrosoft MicrosoftOffice