Got a simple request from one of the folks in Operations; we’re sending out Excel spreadsheets for some quick data gathering, might we do a little basic input validation before they send in garbage that needs to be scrubbed? This person is very sharp, knows a decent bit about what is possible, and this is definitely not something that is worth a major project engagement; “throwaway technology”, a particular fave of mine.
His request was simple – just want to make sure folks enter data into one or two required columns. I’ve done plenty of Excel VBA, and had figured out a simple approach while we were talking (it’s all in the Before_Save() event, naturally), but I couldn’t really tell him how to do it – he’d never programmed VBA before. However, I do have some rather large projects coming this year, and this person’s group will be very important in making timely decisions, implementing change – so I figured that a little lagniappe here would pay big dividends down the road.
Of course, I knew there would be some I couldn’t get all of his requirements right away – I’ve done many similar things in the past, and could anticipate a number of requests down the road. So, a few minutes of Q&A, and I got a decent set of requirements for future flexibility that, if I do a little extra coding now, I could make much simpler in the future.
you may want to jump to the bottom of this post for the lessons learned … gets a bit tedious here …
“Teaching Event” Explodes Scope
Of course, I didn’t want to become the maintainer of another shadow system, so I need to keep this simple. And, I really think there is a lot of potential in quick-and-dirty Excel automation that would do great things for many companies – if more folks knew how to do it. So, I resolved to make the code as modular and self-documenting as I could; I will publish a generic version of the spreadsheet on my code page, so it might be useful Out There as well.
Then again, I have written before about the difficulties of documentation, and I fully appreciate the fact that knowledge capture, while always valuable exercise, adds a lot of overhead – time and complexity, and required concentration. So, I thought I could compound the complexity even more by journaling the programming exercise “real time”, to get some measurements on how much of an impact “good” tech documentation can add. So, I’m composing this blog entry “real time”, to capture a little data.
And, because I just can’t seem to leave complex enough alone, I’ll leave my Twitter client [current fave: TweetDeck] up, and do a little play by play for the Twitterverse as well. Not that I expect much feedback, it’s Saturday evening; had a nice steak dinner with the family, hopefully I’ll stay up through the end of the experiment.
Time Line – Saturday
8:55 pm – He gets a Bright Idea, and starts the blog entry.
9:15 pm – The KM preamble (above) is done, start opening windows. Before I get going, I’ll have four apps open: Excel and the Excel VBA editor, plus a Google Doc (this entry) and Tweetdeck.
I did grab a sample of the spreadsheet to be sent out, with the various columns, header rows already defined, so that’s a nice start.
9:18 pm – Fractal nature of KM – had an idea to #hash tag all the tweets together, so had to go retroactively tag first tweet. Maybe I can code soon …
9:20 pm – Proof of concept / flow was just a message box in the BeforeSave event. Now, I’m off stealing code from old stuff – processing row/column arrays with somewhat predictable locations and dimensions. I need to make what are basically simple loops 99.99% driven with variables, no hard coding. This is the fundamental way to deliver flexibility.
I also will assume future applications that will have multiple tabs with a different data input table in each tab – so will need to build a master loop that runs thru all the tabs.
9:25 pm – Coding finally starts, with a search thru old ssheets. 2-3 more windows opened up. I’m commenting the code while I’m writing it, so the coding time isn’t just raw coding
9:50pm – All stolen code, but built basic structure to process multiple sheets, handle errors at dropout at the end. I think folks might read this source code and get intimidated by VBA – hmmm, might not be helping things. Ah well, on we go …
10:00pm – Some actual new code, still mostly cribbed from other projects – but I’m aggressively genericizing. Also, first comment from twitterverse (@faseidl). Will have to Follow commenters later.
10:05pm – Gonna steal some ReDim syntax, rarely do that, always have to reuse
10:15pm – Been coding for 45 minutes since the last debug, never timed it like this before, kinda interesting
10:33pm – Code is flying, flexible error checking loops all built – writing the magic line of code “If blank then error” now. Probably should structure this bit of code to allow for different types of error checks (<, >, limits, etc.)
10:45pm – Aha, basic loop works, but I made a mistake in my assumption of how to control the thing. I need to specify a column that I will assume is always filled – when I see a blank there, I stop checking. I’ll have to write the “end of check” to be a warning “note: I am stopping here …“
11:00pm – Ok, it’s all done, tested, working just fine. Code was about 140 lines, not a lot. Will do final documentation and code clean up tomorrow morning – kinda tired right now.
Time Line – Sunday
9:45am – Ok, back to it – should be able to finish this up right quick, one would think. Some quick math on the time line above: Roughly 40 minutes (32%) of documentation, 85 minutes (68%) of coding. Not really quantifying how much longer the coding took because I was aggressively cloning (speedup), commenting (slowdown), and genericizing (slowdown).
The sheet works fine, but I do have some work left. Need to package it all up for the original requester, so he knows how to change things; also need to genericize the final thing, so I can publish it / share the knowledge. Again, I’m trying to capture the teaching moment opportunity.
First run through the code was to add comments / documentation so folks know how to extend it. The target audience ranges from technically savvy, but no VBA experience, to VBA hackers – I think it’s all in how I structure the code.
9:59am – Getting some additional coding in – trying to take out as many opportunities for typos and such as possible. Restructuring the code so I only have to code the name of the tab to be checked once. I’m actually doing a bunch of coding here, trying to make maintenance as easy as possible – I know these aren’t the most elegant methods, but I am growing conscious of how much time this is all taking. Tradeoffs, always tradeoffs.
10:18am – Code cleanup done – but in testing, noted something I forgot to add. Data checking loop ends with first blank in the “check column”, but if that’s a mistake, and there are data rows below, I should give them a chance to see that – so I’ll let them know what I think has just happened. A bit more detail than just a “success” message – again, this is a data quality check based on my experience with similar spreadsheets.
10:31am – Fine, the actual programming request is done. An email to the requester to deliver, but then I need to finish the Distribution part of KM. Note how I am short cutting the knowledge transfer part of this exercise for the requester – in my email, I told him to let me know when I might drop by, to walk him through the editing / changing process.
Lazy? No, actually quite practical. I’ll be walking him thru the process of making changes to VBA, and I’m not about to document that. Just show him how the basic sheet works, and give him hints on how he can read more , make simple changes if/when interested. I also need to make sure he understands this is not something that IT will “officially support” going forward – just a quick-and-dirty bit of macro coding for a friend.
10:37am – Now, I’m carving out the code, prepping a sample ssheet for sharing … to be posted on my code page. Note that I’m doing some “documenting” by generating sample data, including an error!
10:52am – Here’s is the part of KM that really drives tech folks nuts, methinks. It’s “prep for final distribution”, making everything digestible for a broad, unknown, unanticipated audience. Up until now, the total stands at 75 minutes (39%) documentation, 117 minutes (61%) coding – but from this point on, it’s 100% documentation. Remember, if a tree falls in the forest, no one hears the sound; documentation won’t help until the code is all checked in, text is cleaned up to be made readable, and everything is put where it can be indexed and found.
11:00am – Just starting the editing pass on this blog post – typos, prose formatting. When I’m documenting on the fly, I’m not trying to make it look and sound pretty, I’m trying to capture the ideas. However, must invest in the look/feel of final product, else folks won’t read it, understand it, or believe it.
12:00 Noon – I’ll stop the timer on the documentation here – this is a ton of work compared to the size of the original. Just starting the editing pass on this blog post – typos, prose formatting. When I’m documenting on the fly, I’m not trying to make it look and sound pretty, I’m trying to capture the ideas. However, must invest in the look/feel of final product, else folks won’t read it, understand it, or believe it.
Lessons Learned
- Twitter definitely adds overhead – can’t quantify it easily, and it was also difficult to keep remembering to post status updates there. Might be because it’s still a new tool, I’m just getting used to it, but it’s a different kind of overhead than the blog entry.
- There is a chunk of complexity added because I’m flipping between different windows. Two large monitors helps, but KM requires multi-tasking; if your teams can’t actively, effectively juggle four threads at once, you’ll never get good documentation out of them.
- Programming for speed? Hardcode, don’t go for flexibility. The coding time was easily double since I was anticipating reuse, etc.
- Only the one comment from the Twitterverse while the project was underway – not sure if that was time of day, target audience, or what. Twitter is still opportunistic, hit or miss communication – hence the interest (I think) in building up follow lists (ings and ers).
- Knowledge capture and sharing can be a relationship management and change management exercise as well. The ability to capture things in writing are important, but not everything
- Final time stats, rounded off: Coding 120 minutes (60%), Documentation 80 minutes (40%). I can speed up coding with reuse and practice, but I can also speed up documentation with practice! Don’t give up on documentation because it’s going to shave 40% from all of your effort estimates – unless you honestly track all of the lost time spent looking up definitions, requirements, previous art.
KM is not free, but I think the value is only seen retroactively; folks that have gotten burned with lost requirements, or forced to do rework because the framer’s intent was lost – they seem to be the folks skilled at and committed to KM.
This Post Has 0 Comments