423,846 Members | 1,959 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Macros or VBA?

MMcCarthy
Expert Mod 10K+
P: 14,534
Although some users find Macros simple and easy to use, there are some major limitations to using them. Although you can use macros to perform tasks, there is limited control on when and how those tasks should be performed, and setting conditions can be difficult. There is however, another even stronger reason not to use macros and that is that when errors are thrown during a macro's execution it is difficult to trap the error and therefore very difficult to debug it.

Some simple VBA programming skills can replace all the functions available in a macro and, as you become more proficient there are many more tasks that can be performed which would just not be possible with macros. To itemise, the advantages of using VBA over macros are as follows:

VBA provides much more functionality than macros
Using VBA allows more complex navigation and conditions to be imposed on tasks you wish to perform. You can code functions that are just not possible with macros.

VBA allows you to trap and handle errors
All tasks can produce unexpected errors due to unforeseen circumstances. It is important to identify and trap these error exactly where and when they occur, to identify the problem and to handle the consequences. This is simple enough when using VBA.

VBA executes faster than macros
Although you may not notice the difference in speed when using simple macros, you will notice a big difference when using VBA in place of complex macros.

Using VBA makes your database more maintainable
Macros are not saved with the form they are designed to act on, but as separate objects, whereas your VBA code is saved with the form it belongs to. If you were trying to change the functionality on a form, it would not be obvious which macro you needed to change, whereas it is easy to find the appropriate VBA event(s) to change. VBA is also easier to read and follow.

Using VBA allows you to connect to other applications
This is a technique known as Automation. Using VBA allows you to work directly on applications like Word and Excel while still in Access. You can control these applications programmatically.

Using VBA allows you to use Public Variables
You can declare variables publicly either to a form or to a database by declaring them in a module. This means you can pass a value to those variables and have it retain that value for use in various of your objects (reports; forms; etc).

Some programmers still use macros for things like AutoExec which doesn't have a directly comparable facility in VBA. This macro command will execute once the database is opened. However, this can be replaced with code which is placed within the "Startup" form. This is the form that is set to open when the database is opened (See Tools / Startup...).

Access will allow you to convert existing macros to VBA.

In the macros tab select the macro you wish to convert. Then select File / Save As.... When the Save As... dialog box appears change the As from Macro to Module. In the next dialog box tick the Add Error handling and Include Macro comments. Access will open the Visual Basic Editor window and covert the macro to VBA code. When finished it will prompt with a message box, just click OK.

This is useful way to learn VBA when you are starting. You can see how Access VBA codes your macros.
Nov 26 '07 #1
Share this Article
Share on Google+
3 Comments


100+
P: 125
Don't understand the difference between VBA and Macros in Microsoft office according to this HOW-TO.

Until reading this i was thinking that macros (microsoft definition) was written in/with VBA.

http://en.wikipedia.org/wiki/Macro_(computer_science)#Application_macros_and_sc ripting
Sep 11 '08 #2

topher23
Expert 100+
P: 234
VBA code subroutines that are created by recording user input in Word, Excel, PowerPoint, etc. are referred to as "Macros" by Microsoft. This article is specific to Access, in which Macros are a completely different entity, totally apart from VBA, although they can be compiled to VBA code and manipulated. It would be nice if Microsoft would come up with different names, as the two different types of Macro share no tangible similarities IMHO, but it is what it is.
Nov 3 '09 #3

P: n/a
Aileen Hewat
Once I have converted my macros to VBA, what then? does the new module automatically link itself in to where the macro was supposed to run? Or do I have to do something? Do I need to delete the macros so the program doesn't get confused?
Nov 3 '10 #4