Converting macros to VBA

There will, inevitably, come a time when you get sick of macros - they're slow, unwieldy and clutter up your database. Who needs them (with the exception of AutoExec and AutoKeys, of course)? Most annoying of all though, macros just don't give you the flexibility you might want - what if you want to add custom error-handling, or perform some action beyond the dazzling array that the RunCommand macro action gives you?

It's time, then, to convert your crappy old macros to shiny VBA. Fortunately, this conversion couldn't be easier. Simply highlight the macro in question in the database window, then click Tools, Macro, Convert Macros to Visual Basic. You'll be prompted on-screen to confirm whether you want to add comments and error-handling. I'd recommend always saying yes to these questions; after all, you can always delete the comments later if they're not appropriate, and the default error-handling saves you a bit of typing when you come to write your own.

There are a couple of things to note though. Each macro you convert will be put in its own module - if you want to simplify things later on, you'll have to manually cut and paste the created VBA code into the module of you choice, then delete the surplus module. These created modules have names of the form “Converted Macro - Your macro name here”.

It's also worth remembering that the VBA this conversion process creates might not always be as slick as you might like. Consider the simplest of examples: a simple macro called mcrOpenMain which has one action, OpenForm, to open frmMain. Converting this in the manner described above produces the following VBA:

'------------------------------------------------------------
' mcrOpenMain
'
'------------------------------------------------------------
Function mcrOpenMain()
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain", acNormal, "", "", , acNormal

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Error$
    Resume mcrOpenMain_Exit

End Function

Now this isn't too horrendous, and certainly isn't as bad as the VBA that some of the form design wizards (notably the command button one) write. But it does add all the optional command parameters in and does ugly error handling - I like to see the error number too, for a start. My advice then is this: once you're happy that the VBA does what it's supposed to do, delete/edit any surplus comment lines, strip out any unnecessary command parameters and improve the error-handling loop... something like this:

Function mcrOpenMain()
' Converted from mcrOpenMain on 15-Sep-2005 by A. Developer
' Called from menu mnuMain
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain"

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume mcrOpenMain_Exit

End Function

Much nicer, wouldn't you say? Depending on how fussy you are, you might also want to consider renaming the created function so that it doesn't start with “mcr” - but if you do, don't forget to check and change any other references to your nice new function, otherwise they won't run.