May 29, 2009

Lost in the Land of Spreadsheet

I'm kind of quiet of late - please forgive me. I am deep in the bowels of a big spreadsheet, finishing up a long overdue revision / update for one of my clients.

This thing started innocently enough - they have a device which captures voltage disturbances and logs RMS voltages. I was asked to take a look at their data - text files that could be easily imported into Excel. For my own sanity and efficiency, I put together a simple Excel template to import the data (just to avoid having to start fresh each time). And then I just kept going until I had a full data viewer. The next thing I knew, it was up on their website for their customers to use.

It's all pretty complicated, as spreadsheets go:

* Macros driven by various buttons and checkboxes
* Multiple hidden pages, that are unhidden and rehidden as the spreadsheet processes commands and massages data
* A special function to import text files longer than the Excel maximum permitted number of rows (65,536 - in case you are curious)
* The ability to handle multiple generations of data (and be backword compatible with them all)
* Functions to zoom and scale in two axis, functions to turn individual data channels on and off, for graphing purposes.
* An Auto Close feature to purge data and repeated formulae from the spreadsheet upon close (to reduce file size) and repopulate same upon file Opening. The compacted file is about 5 Meg - without this crunching it balloons out to 25 or 30 Meg, easily.

Of course, this is not my core competency, just one of those odd bits of techno-geekery I happen to have a knack for. So I'm sure the resultant code is a bit sprawling and disorganized. Each time I go back in to make changes (every year or so) I end up relearning it, reorganizing it a little, and documenting it a bit better. But I'd probably do better the next time, just because I know a bit more about this sort of programming now and would set up some organization from the start.

I'm almost done. This particular revision involves adding current data channels, adding a KVA calculation, adding environmental channels. So lot's of added features and functions, spreding out, making room. Trying to keep a lot of channels of data organized and useful.

When I get deep into code like this, I start to think about it all the time - in the shower, on the yoga mat, in meditation, in bed, while driving. My analytical mind gets cranking and does not want to stop. It's pretty annoying, so I will be happy to have it done. Today, with any luck....

No comments: