This is an old link ... let me redirect you ...

(... or click here if the page does not automatically redirect)

KM Overcomplicates: Heisenberg Impact on a VBA Quickie

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 throuught 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
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.