Converting a Macro to VBA
You may ask why would on bother converting a Macro to VBA. As you would be well aware, you can develop a functional database in Microsoft® Access by using Macros. Utilising Macros alone does have its limitations. Macros are great for simple commands and can utilise simple logic such as ‘if’, but when more advanced scenarios and greater functionality is required then the Visual Basic® for Applications development system is necessary. Some examples of these extra functionalities include:
- The ability to loop through a record set (table or query, one record at a time);
- Create and modify objects at runtime, i.e. queries, tables, databases, etc;
- Add data to your table, once you have checked everything is OK (i.e. do not save the record unless the user chooses to do so);
- Error handling;
- Write code which can be stored for later use;
- VBA has tools that assist you in debugging (working out what you have done wrong);
- 1000 Functions (code that specifically does something) has already been written and stored within Access;
- Communicating with other applications.
Just to name a few.
The later versions of Microsoft Access create Macros by default when you do things such as add a button to open a new form, run a report or to save or delete a record. Most of the time this is adequate, but what happens if you want to do more? You have checked out all the Macro options and what you wish to achieve is not there? You may then consider using VBA code. The issue you now have is that you want to turn the Macro to VBA code can you can expand on the functionality. How to do that?
How to convert a Macro to VBA
The following instructions are for Microsoft Access 2016, but should be similar for other versions.
1. Make sure your form is in design view and that you have something that has a Macro: for example a 'button'.
2. Click on the ‘Design’ tab
3. Towards the left-hand side of the ribbon you will see ‘Convert Form’s Macros to Visual Basic. Click on this option
4. Your choose to add error handling and macro comments > Convert
5. A message box will come up to tell you the conversion has finished.
To view the code, select the button. If the properties window is not open: left click on the button and select properties (bottom of list).
In the properties window, select the 'Event' tab. Here you will see the words [Event Procedure] next to the On Click event. Click on the button with the three dots '...' next to this [Event Procedure].
The code will be displayed. It will look similar to the below.
Do not worry too much about what the code means I will be breaking it down in detail in future lessons.
'------------------------------------------------------------
' Command15_Click
'
'------------------------------------------------------------
Private Sub Command15_Click()
On Error GoTo Command15_Click_Err
On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
Command15_Click_Exit:
Exit Sub
Command15_Click_Err:
MsgBox Error$
Resume Command15_Click_Exit