VBA Tips, Tricks and Best Practices, Part Three of Four

This is Post Three in the VBA Tips, Tricks and Best Practices series. I'm posting some of my tips for maximizing productivity and efficiency in VBA.

Wrap your method calls

Let me clarify what I mean: Any function, method or property you need to call repeatedly should be encapsulated in its own function. And where possible, generalize functions so they can be reused.

I think I've taken this approach as far as it can go — most of my classes (i.e. forms) don't even contain any real code. They are just filled with method calls, where the real work is performed. I don't even use code like SaveButton.Enabled = False when I want to disable a Command Button. I call a custom function that disables any control, and pass a Control Object to it.

Public Function ToggleControl(ctl As Access.Control, Optional isEnabled As Variant)
' toggles Enabled property for whatever control passed to it
' isEnabled is declared as Variant so we can use IsMissing to detect if it was passed

  ' if boolean is missing, do a simple toggle, else use boolean
  If IsMissing(isEnabled) Then
    ctl.enabled = Not ctl.enabled
  Else
    ctl.enabled = isEnabled
  End If

End Function

This procedure is for Access. To use this in Excel, change As Access.Control to As MSForms.Control. You'll need a reference to Microsoft Forms Object Library (or just have a userform in your project, it'll be referenced automatically).

Now I can set the Enabled property of any control to True or False, or toggle it if I don't specify a parameter.

So if I want a control to be disabled:

Call ToggleControl(ctl,False)

If I want a control to be enabled:

Call ToggleControl(ctl,True)

And if I want a control to change from whatever it was to whatever it isn't (True to False, or vice versa):

Call ToggleControl(ctl)

You can see this approach in some of my code samples, where I use separate functions to return references to other programs such as Excel and Outlook. Other things like writing to text files, showing and hiding forms, database lookups, checking required fields, even setting control focus are all handled by external functions. I'm at the point where even showing message boxes and returning user selection (Yes, No, Cancel) will be handled by an encapsulated function.

Some people might find that to be overkill, but I find that it helps organize my thinking, and encourages better coding habits. You could just dump all your code behind a form or button and it would work just as well, but if you needed to reuse code, you'd either have to cut and paste it (creating duplicate code sections and code bloat) or write a custom function and have to completely refactor your application to use that function. I prefer to do that up front, so I don't have to rewrite whole sections. And if you need the function somewhere else, its easier to just call it than to duplicate the code.

Got any "Best of the best" VBA coding tips and tricks of your own to share?

Related Articles:

• March 19, 2010 -- Learn from the real Experts
• June 17, 2011 -- QuickSteps Week, Part 5
• February 3, 2010 -- Return Command bar info in a VBA function