VBA Tips, Tricks and Best Practices, Part Two of Four
July 10, 2009 • JP • 5 Comments • Rate This Article
• Links to this article
This is Part Two of a short series on VBA best practices. I'm sharing my tips for getting the most out of VBA. This post is focused on two free VBA utilities that will maximize your coding time and make you more efficient.
Use Smart Indenter Add-In to keep your code neat
This tip I got from Bob Umlas (Excel MVP). I knew about this add-in, but seeing him use it made me a believer. I downloaded it and immediately saw the benefits. Smart Indenter is an add-in for VB6/VBA that indents your code for you. I am a ruthless indenter and always make sure my code is indented two spaces (not four, that's way too many). In fact, I even fix the indenting on code I find on the internet, to make sure it appears neatly along with the code I write.
But it can get tedious doing it manually, since the IDE doesn't do it for you (another reason I like .NET, by the way). I want neat, readable code, but want to spend more time writing and debugging it, not making it look nice. This add-in solves that problem. With two clicks you can indent all the procedures in your whole project. It follows my rules and indents everything on the same level two spaces, just the way I like it.
Download it now, as the add-in is several years old and websites go down or are taken offline. I've gone so far as to download and store a second uninstalled copy of the add-in, in case I need to reinstall Office at some point (or for a new computer). It's that good.
Fix mouse wheel scrolling issue in VBA IDE
Here's another utility-based tip. The mouse scroll wheel doesn't always work in the VBA IDE. I found that out the hard way in Access 2003. But Microsoft has a DLL to fix that. Visit Add support for the scroll wheel to the Microsoft Visual Basic for Applications 6 environment, which has detailed instructions for applying the fix to VBA. After doing so, you'll be able to use the scroll wheel on your mouse to navigate through code modules.
The great thing is that, like Smart Indenter, it works across all VBA IDEs. So you can now use your mouse's scroll wheel in Outlook, Access, Word and Excel to browse VBA code. And like Smart Indenter, I've saved a copy of the executable file in case I need to reapply the fix later.
[Update 7/14]: Ajay from databison.com posted a neat tutorial showing how to install the mouse wheel fix.
Got any "Best of the best" VBA coding tips and tricks of your own to share?
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: VBA Tips, Tricks and Best Practices, Part One of Four
Next Post: VBA Tips, Tricks and Best Practices, Part Three of Four
Jimmy,
The mouse wheel scrolling issue in the VBA IDE was near the top of list of annoyances for me when writing code. Thanks a ton for sharing this wonderful tip and making my day.
I hope you don't mind my "stealing" this one from you.
Ok here's what I'd do when working with the IDE:
Turn off the Auto Syntax check from the "Tools" -> "Options" menu. That takes care of the pesky alert messages when you need to copy and move code around. While at it, turn off the require variable declaration also (but then that is not a very good coding practice
)
Regards,
Ajay
Feel free to steal, it's hardly an original idea
I agree with the Auto Syntax Check, I turned that off after hearing about it at the Advanced Excel Conference. The code still turns red to alert you to the error (and still won't compile), but you don't get interrupted with a messagebox.
I don't recommend turning off Option Explicit, you're bound to make easy mistakes.
MZ Tools (free for VBA and VB^) has a ton of helpful additional features:
MZ-Tools 3.0 for VB6 & VBA
I've used a small utility called VBScroll that fixes the lack of scrolling in the VB Editor:
http://www.gasanov.net/VBScroll.asp
It's smaller than the MS DLL.
In 2007, an unrelated change made by Microsoft results in the scroll wheel working again. Not all unrelated changes have bad side effects.