You may have noticed that I haven’t posted much in the last week. I was going to post about my week so you know what I’ve been up to and it would have went something like this:
I’m busier than crap at work. We use a certain accounting software to which I am not accustomed and for which I have little respect. Yet, I am using it to the best of my ability and it’s taxing my patience. On top of that, I contracted some form of influenza on Wednesday afternoon. I went home early from work and stayed home all the next day. On Friday, I felt considerably better so I trekked off to work. Two hours later, I was sweating like a whore in church and decided that I may have jumped the gun on my recovery. I spent the remainder of Friday in bed. I watched a movie called Napoleon Dynamite which was recommended to me by a friend. There’s an hour and a half of my life I’ll never get back. In the mean time, I have an Excel project due on Monday that hasn’t been getting the attention it needs. I spent a good deal of Saturday and Sunday on the project and got it done to a reasonable degree. That was in between frequent flu-induced naps and NCAA basketball tournament auction.
But I’m not going to make that post, because it’s too whiny. And that’s just not me. Instead, I’m going to post about MZ Tools. I have recently completed an Excel project that I assumed was far larger than any project that I’ve completed previously. I expected this project to be about 10,000 lines of code which would make it about three times larger than it’s largest predecessor.
Using MZ Tools, I determined that the project was 3,110 lines of code in 118 procedures. That’s an average of 26 lines of code per procedure. The lines per procedure seems quite reasonable to me. I assumed it would be nearly 100 lines per procedure. I thought, as I was writing some of the procedures, that they were getting unwieldy. Maybe 26 isn’t as good as I think. Certainly 3,000 wasn’t what I expected at all. How in heck did I only produce 3,000 lines of code? I’ve spent about 70, or so, hours on this project which means I produced 44 lines of code per hour. That may seem like a decent amount, but I have a confession. I copied a lot of the code.
For example, I used the error handling procedure from Professional Excel Development. The central error handling module is over 100 lines that it took me about five seconds to “produce”. It’s a very nice error handling system. I need to spend some more time under the hood, but I’ve learned quite a bit about it by screwing it up.
My last 3,000 line project was over at least a year and paid by my former employer. It was a nice piece of programming and I had the benefit of being one of the users as well as the developer. I could catch bugs early, fix them on the spot, and redistribute the app before anyone knew what was going on. It seemed like a much smaller project because it was developed over such a long time. As it turns out, it was roughly the same size as this recent project which seems like a gargantuan task.
But this post isn’t about virii or how bad I am at estimating project size. It’s about MZ Tools. A few years ago, I had a big problem with Excel. It would start up and immediately shut down. It started happening about 4:00 PM on a Friday afternoon which is not the time of the week that I’m most productive. With the help of my fellow MVPs, I used the delete-all-add-ins-and-add-them-back-one-at-a-time method to determine if an add-in was the problem. One was. It was MZ Tools. I deleted it and everything worked fine. I didn’t reinstall it. What a mistake that was. I have no idea what the cause of the problem was. I had MZ Tools installed on that machine for easy five years with nary a problem.
Recently, I’ve installed MZ Tools again. I wish I’d done it at the start of this most recent project. It would have saved me a lot of time. It’s what I used to count the code lines, but that’s not even close to its best feature. If you do any half way serious development, I suggest you give it a try.
In conclusion, thanks to John for the referral and if you have any stats for your Excel projects, please share them.
Yes, MZ-Tools is invaluable. As is Stephen’s Smart indenter.
What do I use most of MZTools:
– Insert Headers (module, procedure)
– Insert error handler (VERY useful!)
– Review source code (shows me what variables I have decalred which I don’t use)
– Convert public variable to property (I am too dumb to remember what exact syntax is needed for those property let/get/set statements, so I simply write “Public foo as Whatever” and let MZTOols figure out how to convert that to the two property statements)
– Combine and split lines to split/combine long lines of code
My largest paid project:
4827 code lines, 1842 comment lines in 16 (class)modules and 6 userforms, alltogether 226 procedures.
I spent about 110 hours on this project.
As an example another “project” (unpaid for): Name Manager
8,032 code lines, 2,679 comment lines in 86 procedures
Spent time: I haven’t the faintest idea. I guess a zillion hours .
About 80 % of them working around bugs, 19 % doing the UI and about 1 % the actual logic .
I don’t usually count code lines. So many of my projects have a huge amount of effort in the worksheet, constructing ranges that help the customized charts work flawlessly, or building elaborate templates. In one large recent project, there were a total of five lines of code (six counting Option Explicit) which served to remove the defined hidden names put there by an addin that someone had tried out then abandoned. Fortunately the names all had an identifying prefix. I couldn’t use the Name Manager, because the program was polluted after it left my hands, and I had to fix it over a Webex connection. I removed those lines of code when the task was done, so maybe I should say it’s zero lines.
But if we’re going to count code, my personal.xla file has 3468 lines, and this huge project that I’ve worked on and off for 18 months has 3059 (but also a slew of heavily customized templates).
I use MZ Tools a lot, particularly its flexible Find feature, which lists all occurrances of text in the procedure, module, project, or project group (your option), and its Procedure Callers feature, which lists every procedure which calls the current procedure. I use Review Source Code, and wish it had a Module or Procedure option, instead of it reviewing everything that’s open. Sometimes I use its Convert to Upper/Lower Case.
Other utilities: I use Stephen Bullen’s Smart Indenter very often, Rob Bovey’s Code Cleaner fairly frequently, and a dozen times a day I use my own Export Module to Word utility (which is nowhere near ready for prime time, sorry).
I downloaded MZ Tools as soon as I read this post. I was pretty excited (which, I guess, goes to show how exciting the rest of my CPA life is this time of year) when I found the analysis of variables that were not used. Since some (read: a lot) of my code has come from the NGs (and thanks to all those that contribute, BTW), cutting a pasting the code and getting is work is a breeze, but in modifying the code to do what I want I fail to get rid of unused variables. I’m sure this product has many other useful features, but when I went to close the VBE and Excel, I got the “Microsoft Excel has encountered a problem” message. This also happened with more than one workbook. I uninstalled MZ Tools and *poof* – no more problem.
So maybe I’m just the lucky one…
I use Stephen’s Smart Indenter the most, then Rob Bovey’s code cleaner and MZTools.
Largest paid project (Version 5 of a rather complex Forecasting system with back-end database) is currently 25859 lines of code and 7908 comments for a total of 33767 lines, 429 procedures and 706 controls. The version of this I am currently working on (adding long-range forecasting) will take 120 hours to complete (I hope ).
For comparison my commercial product is 15945 lines of code excluding comments, and I have been careful not to track how much time it has taken to develop so far…
I use MZTools for VBA, classic VB and for VB.NET 2003/2005, it’s an excellent productive toolset.
Code cleaning and indention is done with my own utilities. The largest project I’ve worked with was fulltime in 6 months and the NDA still prevent me from telling more about it.
Kind regards,
Dennis
I like MZ-Tools as well. In addition to the features mentioned, I use the Sort Procedures and the one that erases the Immediate Window. My biggest VBA project is about 1,800 lines of code and 700 lines of comments. The first time I used Review Source Code, I was pleasantly surprised to see only 2 unused variables.
Hope you are all better now Dick. Too bad about Napoleon Dynamite – kind of like Rushmore without good acting, directing, or characters.
OK, largest project (and still growing) is 5672 codelines in 186 procedures (for an average 30 lines per procedure – but it’s a highly skewed distribution) with 2843 comment lines (which is a high comment to code ratio relative to above stories). Time spent is far too long!
Whenever stopping to debug something on someone else’s machine in the office, when they don’t have MZ-Tools installed, I get a little…ahh…grumbly.
cheers,
Christopher
My VBA stuff tends to top out around 10,000 LOC. I try to avoid comments. Procedure size is generally no more than 40 lines, with good clear names not many comments are needed.
Biggest Excel project was 3 dev years (felt like more!).
I use MZ Tools, code cleaner, FastExcel and PUP, and I use notepad++ (sourceforge) to pretty up any vba I have to print.
Worksheetwise the biggest was 170 Megs with 970k total formulas 6,000 unique. I didn’t write it I just fixed it when it (inevitably) broke.
cheers
Simon
One of our recent projects: 16647 code lines in 752 procedures for 22 lines per procedure. This number also strikes me as low. There must be a lot of tiny procedures attached to form controls pulling the average down.
Largets project I had was around a whopping 19000! lines of Excel VBA Code within around 45 modules(this is without the Access VBA code, VB code and database which amounded to around 8000 lines as well). It took 2,5 years total to develop this. with 3,5 years of modifications/support afterwards. AFAIK it is stil in use. I used mainly self developed developer tools, but also Code Cleaner and FMS Total Visual CodeTools(http://www.fmsinc.com/products/CodeTools/index.html)which also does code scrambling BTW.
Sweet LOARD! 170 megs!!?!?!?!
I dont think i have the will to write 25859 lines of VBA, I think my bigest project is around 3000 lines.
MZ tools, code intenter (and VBE tools) and Code cleaner are all used regularly. I use the close all window button alot in MZ tools too.
Hi all,
Well I am not a professional developper more an advanced user but I use MZTools for more than 5 years and never been disapointed. It helps me mainly to check the unused variables, count lines and create headers + clearing the execution (not sure of the translation) window.
I never wrote more than 5000 lines of code in a maximum of 10 modules (what about forms ;-)).
I use VBE Tools, code cleaner, Pretty Code Print (an old version which was free) for clean printings of my code.
Just a quick tip for printing code:
I use one of the copies of vbHTMLMaker listed here (I don’t know what the current version is):
http://www.puremis.net/excel/downloads.shtml
which creates formatted html of selected code. Mainly for pasting code into my site, but it also works to get coloured printing.
I use VBCodePrint, VBACodePrint and NetCodePrint for all kind of code-documentation as well as code published on my sites.
Kind regards,
Dennis
After reading these comments, I downloaded MZTools and also had issues with it as described by others above. But instead of uninstalling it, I used the Add-In manager to unload when not using it and all the ‘issues’ disappeared. This way I don’t have to keep installing and uninstalling and yet get the full benefits of MZTools.
Looks like I’m winning:
44,452 lines (41,710 without comments) on 1,382 procedures for 30.2 lines per procedure. The project is still going… and this is without counting the VB6 or VB.Net ASP pages that the program uses, just the main XLA of the program.
I use MZ tools as well. One thing that is always a problem is getting lost in the process; both in coding and spreadsheet design (range names, named formulas, etc). What process do you use to keep track: is there a custom program (Source Safe) or have you built a custom word/excel/access widget?
doco
MZ Tools is a regular for me as well. I wish that there were updates!
Best functions in MZ tools for me are “Procedure Callers”, “Find”, and “Sort Procedures”
I also really like the “Find Name in Range” function from Excel Utilities and the form “Nudge” controls from VBE Tools.
So far, my dictator app with satellite add-ins & template has about 20,000 LOC at about 20 lines procedure. 3 years and counting on this one, most paid by clients (yes! more than one).
Hi all,
For those of you having crashes unloading Excel when MZ-Tools is loaded, please read the FAQs, specially this one:
I have problems unloading the add-in or the Visual Basic IDE
http://www.mztools.com/v3/faq.htm#Unloading
Which surely will fix the problem. Otherwise, contact me. I do support the tools with monthly bug fixes for years despite being freeware…
Best regards,
Carlos J. Quintero
MZ-Tools: Productivity add-ins for Visual Studio.
You can code, design, locate code and document your apps much faster:
http://www.mztools.com