Thursday, April 4, 2013

How Do I Convert A Macro to VBA Code?

Although Microsoft has made great strides in improving the capabilities of macros in Access, most experienced developers agree that Visual Basic for Applications (VBA) code is still far superior to using macros in Access client apps. (Web apps, of course, cannot use VBA, so the following is only pertinent to applications that run in the Access Client.)

So suppose you have a completed macro that you want to convert to VBA? Fortunately, Access makes it easy.

Access 2010 and 2013

Open the Macro in Design View

 image

Click the File Tab then

    1. Click Save As
    2. Save Object As
    3. Save As

image

You will get a pop-up Dialog box.  Give the macro a meaningful name in the top box and in the AS box, choose Module. Click OK.   image

Click OK.  A second box will appear asking you if you want error trapping and comments.

image

Leave both checked and click Convert.

The macro will be converted and saved in a Module.

'------------------------------------------------------------
' MyMacro
'------------------------------------------------------------
Function MyMacro()
On Error GoTo MyMacro_Err

    DoCmd.TransferSpreadsheet acExport, 8, "table7", "table7.xls", True, ""

MyMacro_Exit:
    Exit Function

MyMacro_Err:
    MyMacroError$
    Resume MyMacro_Exit

End Function

'------------------------------------------------------------

In Access 2007

Open the macro in Design View, Click the Office Button (Pizza), Choose Save As, and you will immediately be given the Save As dialog.

A second box will appear asking you if you want error trapping and comments. Leave both checked and click Convert.

In Access 2003 or previous

In the Database Window, right-click on the macro you want to convert and choose Save As. Everything else works the same.

2 comments:

Vada said...

This is cool!

Dave said...

It seems to me that I have done this in the past, but in my current db, the "convert macro to code" option is disabled. There's probably a reason for this that's obvious to everyone except me, so please chip in!