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; "
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
, and had figured out a simple approach while we were talking (it's all
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
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
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
, so it might be useful Out There as well.
Then again, I have written before about the
, and I fully appreciate the fact that knowledge capture, while always
valuable exercise, adds a lot of
- 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:
] 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
Time Line - Saturday
He gets a Bright Idea, and starts the blog entry.
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.
Fractal nature of KM - had an idea to #hash tag
all the tweets
together, so had to go retroactively tag
. Maybe I can code soon ...
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.
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
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 ...
Some actual new code, still mostly cribbed from other projects - but I'm
aggressively genericizing. Also, first comment from twitterverse (
). Will have to Follow commenters later.
Gonna steal some ReDim syntax, rarely do that, always have to reuse
Been coding for 45 minutes since the last debug, never timed it like
this before, kinda interesting
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.)
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 ...
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
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.
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
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
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.
Now, I'm carving out the code, prepping a sample ssheet for sharing ...
to be posted on my
. Note that I'm doing some "documenting" by generating sample data,
including an error!
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.
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.
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.
- 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 opportunisitc, hit or miss
communication - hence the interst (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.