Bug-hunting spreadsheets

Posted on February 9th, 2010 by Sacha Chua

There’s a certain delight in working on obscure problems. In my case, I was trying to debug an old spreadsheet that statistically analyzed survey responses in order to match them to innovation archetypes. It started when I noticed that a few chart labels were incorrect. In the process of trying to figure that out and make handling multiple survey responses easier, I ended up deep in Visual Basic code, untangling algorithms that didn’t make complete sense to me.

The spreadsheet had been created three years ago, and it was hard to track down people associated with the project. Documentation was practically non-existent. Working with the source code, sprinkled comments, and formula auditing tools, I figured out what was going on.

So I knew what the code did, but I wasn’t sure it did what it was supposed to do. I asked two team members and a third consultant to refer me to someone who could explain the manual procedure and provide the missing information. If I could figure out how to do the analysis by hand, I could find the bugs and update the spreadsheet.

Even if we end up discarding the tool, I had fun following the logic through the code. There’s something about understanding a small piece of the puzzle well, and then expanding your understanding until you can hold the program in your mind.

More posts about: flow, geek, ibm, work // 1 Comment »

Short URL: http://sachachua.com/blog/p/7025

http://lepslair.com/tutorials/vb Sean

I don’t know why people don’t comment code more, it is better in the long run. It even helps the original developer to understand what is going on years later.

On This Day...

2012: Drupal 6: Adding color support to your theme — We spent a few hours trying to figure out how to use Color to make our custom Drupal 6 theme [...]
2011: Thinking about personal random moment studies — John Handy Bosma (Boz) proposed a personal productivity random moment study. His goals are: Find out how he’s spending [...]
2011: Things to write about: questions for your blogger’s block — People often tell me they’re worried about finding enough material for their blogs. The truth is, there’s so much you [...]
2010: Notes from WITI: The Shy Connector — 100 people and I chatted about networking for introverts in The Shy Connector, a webinar hosted by Women in Technology, [...]
2009: Planning meetings, get-togethers, and interviews with AgreeADate — One of the reasons why I love thinking of ways to help people rock even more is that I often [...]
2009: DrupalCampToronto organizing notes — We had the second organizers’ meeting for DrupalCampToronto 2009 today. I started a number of Google Documents for keeping track [...]
2008: Weekly review – Feb 9, 2007 — I haven’t written in a while, and my fingers are starting to itch. I’ve been preoccupied. Last Thursday I learned [...]
2007: Snuggle — Today was just way too intense. A morning of calling, an afternoon of walking, an evening of panicking… I’m exhausted. I’m going [...]
2007: What’s the scariest thing you’ve ever done? — When people think of scary things, they usually think of flashy stuff: sharks, heights, etc. The scariest things, however, aren’t those [...]
2007: Is this what a trapeze artist feels? — This is a fascinating feeling. I’m not sure if I’ve blogged it before. There’s fear, yes. I can feel the stress [...]
2007: Also, positive thinking means that… — … you put “Find checkbook” on your task list instead of “Look for checkbook”. ;) Little things matter. Random Emacs symbol: gnus-group-tool-bar [...]
2007: Found my checkbook… — … zippered into the lining of my carry-on suitcase, practically flush with the frame of the suitcase. That’s probably why I [...]
2007: I just have to ride out the panic… — Panic is natural. As long as I don’t act during it, I’m fine… I’ll work out a plan somehow. What’s the [...]
2007: Borrowed some time from my thesis… — … to scour Toronto for an apartment. I wrote down the details of more than thirty ads, called more than twenty [...]
2006: CookOrDie: Minestrone — Level up! Steve helped me prepare minestrone today, and I’m reasonably happy with it. =) See, I picked up the Good Food [...]
2004: More thoughts — Deleted: Lost secret key
2004: New referrers — 2004.02.01 http://www.blogcn.com/user5/robinh/main.asp 2004.02.03 http://www.javablogs.com/Welcome.jspa 2004.02.03 http://www.livejournal.com/users/quirky/friends 2004.02.03 http://www.freepgs.com/yecartes/wiki.pl 2004.02.06 http://www.geocities.com/thenonymous/home.html 2004.02.06 http://naesten.stumbleupon.com/ 2004.01.27 http://www.vanillaice.net ../../sitelogs/sacha.free.net.ph-access_log.1
2004: Chamberlain Fong — spectralfft@yahoo.com
2004: Wearable ring mouse — Jonas Meyer on wear-hard@haven.org: Does anyone have any experience with the Bosswave FinRing, out of Taiwan? It’s a little mouse that [...]
2004: The Joel Test: 12 Steps to Better Code — http://www.fogcreek.com By Joel Spolsky Wednesday, August 09, 2000 Have you ever heard of SEMA? It’s a fairly esoteric system for measuring how good a [...]
2004: New story by JM — http://www.mycgiserver.com/~butiki/stories/sleep.html
2004: University of Canterbury — http://www.canterbury.ac.nz/courses/postgrad/pedu.shtml Master of Science Education – M.Sc.Ed. The course of study for the degree comprises either (a) six courses and two research projects [...]

sacha chua living an awesome life