Contextures is having a contest. There are some great prizes, so go enter. If I win the VSTO book, I’ll assume it’s a divine sign and actually learn something .NET.
Recently, I changed a program that I substantially completed a year ago. There was a lot of Excel and Access integration and I used class modules to obfuscate the Access interactions. That is, instead of dealing with Connections and Recordsets in the main code, I dealt with custom class modules. Beyond just that, it was significant to me because I forced myself to use classes to an extent I never had. I employed the “heavy class/light sub” method of coding. The vast, vast, vast majority of the code is in class modules. The subs in standard modules exist only to call properties and methods of classes. That might be a bit of an exaggeration, but I really went to extremes that I hadn’t before. The benefit came with the recent changes I made to the code. The code is so clean and readable and self-documenting, that it took me no time at all to get my bearings. I was able to find where the changes needed to be made, change a property, add a new property and I was done. I know I pimp class modules too much, but this project got me over a class module hump when I was doing it. Now that I go back and look at it a year later, I’m more convinced that HCLS is the methodology for me.
I’m training a new accountant. She’s pretty proficient in Excel, which is nice. As we work through processes together, I notice keyboard shortcuts that I use and don’t even think about. The one that stick out the most is Cntl+- to delete a row or column. If I want to delete a row, I use Shift+Space followed by Cntl+-. I haven’t been using that one long, but I took to it pretty quickly. Watching someone else reminds me that there are different ways to do things (and that my way is truly the best :) )
In October 2006, I bought a tablet PC and it didn’t work out too well. Gosh, was that really almost three years ago? Since then, I’ve struggled with buying another one. Not because of the experience I had, mind you, only because it is such an obviously extravagant purchase that I couldn’t justify it. I finally broke down and bought Ed Bott’s old Asus machine. Aside from the fact that I can’t get iTunes moved from another machine to this one, it’s been a lot of fun playing with the tablet. I’m genuinely shocked at how well the handwriting recognition does. We’ve come a long way from the Newton. I used to own a Palm and I got pretty proficient with the block lettering. For those of you who’ve used block lettering, you’ll appreciate that I keep writing a ‘7’ expecting a capital T. In fact, I’ve trained the table to recognize that quirk and to only recognize a seven with I put a horizontal line through the middle. I haven’t put Office on it yet and I’m not sure that I will. Maybe OpenOffice.
Dick wrote: “The code is so clean and readable and self-documenting, that it took me no time at all to get my bearings. I was able to find where the changes needed to be made, change a property, add a new property and I was done.”
I’ve been saying and doing this — well, at a more general level — for years. Most of my programs I can modify in an incredibly short time, years after last touching the code — and, by and large, you will find *no* documentation of the code.
The key is writing modularized code be it in class modules or elsewhere. For the most part, when I modify a procedure I can *guarantee* that the change will have no effect on any other code except as expected — in the case of a function through the returned value and for both subs and functions through explicitly declared ByRef variables. In general, and with few unavoidable exceptions, my code has *no* module level or otherwise global variables.
Modularization actually introduces a problem in writing class modules where properties are often retained in module level variables. This requires greater discipline in not accessing global variables willy-nilly.
One problem I think is that there’s not enough info available for Excel VBA programmers to appreciate using class modules as a standard technique for all of their programming projects. The vast majority of VBA books are procedural oriented, with a discussion of classes confined to a chapter. Most VBA code snippets are procedural oriented. Pro Excel VBA, which is 100% object-oriented, came as a surprise to me. My main annoyance with VBA classes is the lack of inheritance.
I really am so jealous of everyone who has time to write solid code for any project they are working on. I find that the only way I have time to do anything in my job is where I can run the formula (time spent on a tasks * number of expected times I’ll have to do the task > time spend on some quick-and-dirty code) and come up positive. If I replace the quick-and-dirty with clean, well documented, modular code the formula always comes up negative. Then months later when the * of expected times to do the task has multiplied by 3, I still don’t have the time to update the code to something proper. It’s very depressing, all of it, in that all of my code is just barely functional garbage that does the job but would be embarrassing to have to explain to anyone else.
I guess the moral of the story is to get a job where you have time to do it properly! That said, I have some old code that was very class module oriented that I haven’t touched in ages from a prior job and I can still figure out what it does just by reading the code. I totally agree with you, Dick, that that’s the way to go. That whole tradeoff really ties into the whole VBA vs. VSTO debate, where the fact is that most people who use VBA don’t go to VSTO just because the time investment for the simplest task isn’t worth the gain in most cases. I’d love to whip up some VB.net or C# code for my job, but my job isn’t that of a coder…it’s that of a financial analyst who has bosses that can appreciate a 100x time savings but don’t give a rat’s ass about the mechanics behind those time savings.
Are there any documentation/books on achieving better HCLS?
Zach, I don’t think this is a question of time, but of structure. If you know where you want to go and how to get there, then ‘quick-and-dirty’ may very well be encapsuled procedures and object oriented programming. My first approach on almost all VBA tasks is to see if a collection of objects can do the job pretty much themselves, if so it’s pretty quick to write, resulting in something like
for each x in subrcribers
if x.time2ask then call x.emailme
next
I agree with you on this; VSTO is never quick-and-dirty, it’s just too much hassle involved on programming, on testing and on deployment. I believe VSTO is made for something else, Developer delivers Project to Customer for Money.
Of course I may be wrong, weirder things happens constantly. Anyone write quick’n’d VSTO code for their own immediate use?
What does HCLS stand for?
Thanks!
Tushar: I resisted custom classes for years. Your preaching, in no small measure, was one of the things that got me over the hump.
Nick: Yeah, but for me it was all Ruby on Rails documentation. For every task, I just asked myself if I could it in a class. For properties, it was a no brainer. For methods, there were times I put it in a class and decided it wasn’t right, then took it out and put it in a sub. In my most recent situation, I had to determine if my CList object was the first one, that is, that there was not an earlier CList. I had to find the earliest CList so I created a property of the CLists collection class to loop through and get the first CList’s date. That one was easy to decide to put it in a class. I could have went a step further and made a CList.IsFirst property, but it seemed to contrived and I just compared CList.ListDate to CLists.EarliestDate in the sub. One way to go about it is to try to jam everything in a class and you’ll get a feel for what belongs and what doesn’t.
Charlie: Heavy class/light sub. It means to put the business logic/heavy lifting code in a class and not a sub.
I keep coming back to your blog because I always pick up something. Here amongst the weighty topic of class modules, I come across the gem of Shift-Space and Ctrl-Space. I’ve been using Excel for years, do some VBA programming (yes, even class modules), and yet I did not know about Shift-Space, etc. I love keyboard shortcuts!!!