Obviously, there are some simple tasks that can be performed happily by macros, but the example above should have highlighted one of their limitations. We could create navigation using macros, but we could not disable or enable them according to where we were in the records behind the form. That may not be a problem for some people, but if you want a slick interface that will win over your end-users, you'll probably want to enable and disable buttons . Our users will be sitting in front of this screen a lot, so we want to get it right.
The advantages that VBA has over macros can be summarized as follows :
VBA enables you to provide complex functionality.
You'll remember that when we tried to move back to the previous record from the first record we encountered an error and Access displayed an error message. What if we wanted to display our own error message instead? This type of intelligence isn't possible with macros.
You can trap (intercept) and handle errors using VBA.
Handling errors is impossible with macros but simple enough with VBA. Also, in some circumstances, you have to handle errors yourself. If you don't, your application could easily crash! We look in detail at error handling in Chapter 12.
VBA is faster to execute than macros.
VBA code is executed faster than macros. Although you may not notice the difference in a one-line macro, the difference in speed becomes more noticeable the longer and more complex the macro you are creating. Since speed is normally a critical factor in impressing end-users, we have another notch in favor of VBA.
Using VBA makes your database easier to maintain.
Macros are completely separate from the objects that call them. Although we created the navigation button macro from within the form, the macro is actually stored as a separate object in the database window. Click the Macros tab and you'll see it's there. In contrast, you can save VBA code with the form itself. This means that if you want to move the form into another database, the code automatically goes with it. With macros, you would have to find out for yourself which macros you needed to take as well.
Using VBA allows you to interact with other applications.
With VBA you are able to make full use of Automation. This facility allows you to access the functionality of applications like Excel and Word from within your Access application. It also allows you to control Access programmatically from applications like Excel and Word. More on this in Chapter 15.
Using VBA gives you more programmatic control.
Macros are good at performing set tasks where there's little need for flexibility. They can't pass variables from one macro to another in the form of parameters, are unable to ask for and receive input from the user , and they have extremely limited methods for controlling the sequence in which actions are performed.
VBA is easier to read.
Because you can only view one set of Action arguments at a time in the lower pane of the macro window, it is difficult to see the details of a macro. You have to select each action one after the other and look at its arguments in turn . In contrast, VBA is very easy to read with its color -coded text and Full Module View.
VBA is common to all Microsoft applications (well, almost!)
Finally, VBA is the language on which all Microsoft applications are now standardizing. VBA code written in Access is easily portable to Excel, Word, and any other applications that use VBA (we shall be showing you more about this in Chapter 15). In contrast, macros are highly specific to their native application.
By this stage, you may be wondering why you should ever bother to use macros if VBA has so much in its favor! Well, there are still a couple of things that you can't do in VBA that you need macros for, and we'll look at these below. They are:
Trapping certain keystrokes throughout the application
Carrying out a series of actions whenever a database is opened (this is done via the Autoexec macro)
But, apart from these, you'll find that with VBA you can do all that you could with macros and lots more besides.
In early versions of Access, you also had to use macros if you wanted to create custom menu bars or attach custom functionality to buttons on toolbars. However, from Access 97 onwards, both of these tasks are now achieved from the Customize dialog box available from Toolbars on the View menu.
Before we move on to the next chapter and completely discard macros in favor of VBA, let's just take a look at the two things mentioned above where we still need macros.
Something you may want to do to make your application more user-friendly is to assign frequently used actions to certain keystrokes. For example, you may want your application to print the current record when your users hit Ctrl+P .
We have already seen that on a specific form you can implement a hotkey by using an ampersand (&) in the caption for a control. That's what we did with the navigation button on the Company Details form. However, if you want to implement a global keyboard shortcut - one that is available throughout your application - you can do so by creating a special macro.
First create a new macro (click the down arrow next to the New Object button on the toolbar and select Macro from the drop-down menu). You will need to save the macro with the name Autokeys , as this is the name of the macro in which Access looks for keyboard shortcuts. To display the Macro Name column, click on the Macro Names button on the toolbar. This button toggles the column between visible and invisible. You can also do this by selecting Macro Names from the View menu:
Then you specify the keystroke that you wish to instigate the required action in the Macro Name column, and the action itself in the Action column. For example, the following macro will cause the currently selected records to be printed whenever Ctrl+P is pressed:
The lower pane of the macro window lists the arguments that you can pass to the PrintOut action to define exactly how it should operate . For example, we've specified Selection as the Print Range argument. This causes Access to only print out those records that were selected when the key combination Ctrl+P was pressed. If we had only wanted to print out the first two pages of the currently selected object, we could have chosen Pages as the Print Range argument and then typed 1 as the Page From argument and 2 as the Page To argument.
The caret sign (^) is used to indicate that the Ctrl key is held down at the same time as the P key. For more information on these key codes, search Microsoft Access Help using the phrase "Autokeys Key Combinations".
When you open up an existing database, the first thing that Access does is to set any options that have been specified in the Tools/Startup... dialog. After this, it checks to see if a macro called Autoexec is present. If it is, then Access executes it immediately. This handy feature allows you to carry out actions such as writing a record to a log file to indicate that your application has started up.
Users of versions of Access 2.x and earlier should note that many of the conventional uses of the Autoexec macro have now been replaced by the Startup... option on the Tools menu. If you're converting an application from a version 2.x or earlier, you may want to remove the functionality from the Autoexec macro and use the Startup... dialog instead.
If you want to perform an action whenever the database is opened, but want to get the benefits of using VBA rather than macros, then you should write a procedure in VBA and call the procedure from the Autoexec macro. You can do this by using the RunCode action in your Autoexec macro:
In this situation, when the database opens, the Autoexec macro is executed and this causes the MyCode() procedure - written in VBA - to be executed.
Be aware, however, that a user can prevent the Tools/Startup... options or the Autoexec macro from running by holding down the Shift key when the database is being opened.
You can prevent a user from bypassing the Tools/Startup... options and the Autoexec macro by setting the database's AllowBypassKey property to False . However, the AllowBypassKey property isn't available normally, and so can't be set in the usual way. We'll look at how to set this property from VBA later on.
Macros have their purposes then, but while undoubtedly useful for some things, they don't offer the power of VBA. We've just demonstrated in a few pages how and where you should apply macros. We'll now use the rest of the book describing how and where you can use VBA.
Just before we do that, however, there is one last useful trick that macros can give us: Access will allow us to convert macros into VBA code, and it can even go one step further by adding useful error trapping code if we ask it to. This can be a very useful technique for those new to VBA.
Your first sight of the Visual Basic Editor can be a bit daunting. Don't panic! We'll lead you through all the details as we continue through the book.
Load up the database file IceCream.mdb . In the Database window, select the Macros tab and then the macNextButton macro that we created earlier.
Select File and then Save As from the main menu.
When the Save As dialog appears select As Module and click OK :
Another dialog appears. Just accept the default settings of Add error handling and Include macro comments and press the Convert button:
Access will now open the Visual Basic editor and finish the conversion. When the conversion is complete, click OK .
Double-click on the new module that Access has created for us in the project viewer pane:
The Project Explorer will open the module for us, and we can view the code it created:
You might be able to recognize that the line that does all the work is the one that starts DoCmd.GoToRecord . Most of the rest is (necessary) padding and error handling code.
This technique is a useful learning tool and can help you to convert any existing macros you may have into working code that you can use 'as is' or simply copy into your own modules as you become more proficient with VBA.