While macros are perfectly acceptable and even recommended in certain situations, there are some key advantages to using VBA instead of a macro. The following is a list of some of the advantages you'll enjoy using VBA instead of a macro.
□ Error Handling: If a macro encounters an error, it just stops. For example, a macro created to open a form will fail if the form can't be found. While Access will provide a fairly detailed error message informing you the macro failed, you can't add any error handling. If you're writing a macro to add a menu item to the menu bar, how can you be sure the code only runs once? If the code were to run twice, you could end up with two instances of the same menu listed on your menu bar. Using VBA code for this task is more appropriate as you can test to see if the menu has already been added. If not, the code runs; if so, the code does not run or displays a graceful error message. In some circumstances, running your macro could even crash the entire Access application.
□ Speed: A one-action macro will probably execute faster than the equivalent VBA code. However, running a complex macro with 10 or 12 actions usually takes significantly longer than the equivalent code. VBA code within Access is fast. If you're designing an end-user application, you definitely need to be concerned with speed. If your users see the hourglass for even more than 5 or 6 seconds, their perception will be that your application is slow.
□ Functionality: With 55 macro actions, how could you ever miss functionality? We're being facetious, of course—if all Access programming were limited to 55 actions, there wouldn't be very many applications written in Access. How would you ever display a custom error message, play a sound other than the default "beep" or open an HTML file in response to a button click? VBA provides so much more functionality than Access macros. Some of the key functionality is the ability to interact with other applications. Using VBA, you can open Word or Excel files, send e-mail from Outlook, open an Internet Explorer browser and navigate to a particular Web site, or open almost any file stored on your computer or a network drive. External application access isn't limited to Microsoft products either. You can add a reference to any number of applications through the References dialog box in Access VBA. Once you've added a reference, you can control other applications such as Adobe Acrobat, VMWare, or Yahoo Messenger. You can also take advantage of many Web services such as MapPoint and CarPoint.
□ Control: With VBA, you can exercise almost complete control over your code. Instead of working with macros where you are must let the chosen macro actions perform the work, you can control each step of the process in VBA. Macros can't ask for a variety of variables to input into an equation. They can't dynamically create an ADO connection based on user input. They also can't run a different set of actions for each user of your application. VBA can accomplish all of these tasks with ease.
□ Interaction with other applications: When using VBA within Access you're not limited to merely programming Microsoft Access. You can add references to other object libraries such as Word, Excel, Outlook, and even non-Microsoft programs including accounting packages, drafting programs, and even graphics programs.
This chapter covered the basics of VBA within Access, creating some basic macros within Access, and finally, why you might want to use VBA instead of a macro within Access. If you're going to use VBA however, you'll need to understand the various components of VBA programming. Chapter 3 covers what's new in Access 2003. Chapter 4 covers properties, methods, and events as well as goes into detail about the interactions between the three. If you're an experienced VBA programmer, you can skip the next chapter. However, if you're a self-taught programmer (and there are many out there) or just want a refresher course on the basics, take a look at Chapter 4, "VBA Basics."
Was this article helpful?