http://bytes.com/topic/access/insights/741520-macros-vba
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.
Visual Basic for Applications (VBA)
VBA enables building user defined functions, automating processes and accessing
Windows API and other low-level functionality through
dynamic-link libraries (DLLs). It was also built into Office applications apart from version 2008 for
Apple's Mac OS X, other Microsoft applications such as
Microsoft MapPoint and
Microsoft Visio; as well as being at least partially implemented in some other applications such as
AutoCAD,
WordPerfect and
ArcGIS.
It supersedes and expands on the abilities of earlier application-specific
macro programming languages such as
Word's WordBasic. It can be used to control many aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes. VBA can also be used to create import and export filters for various file formats, such as
OpenDocument (ODF). As its name suggests, VBA is closely related to
Visual Basic and uses the Visual Basic Runtime, but can normally only run code within a host application rather than as a
standalone application.
It can, however, be used to control one application from another via
OLE Automation. For example, it is used automatically to create a
Word report from
Excel data, in turn automatically collected by Excel from polled observation sensors. The VBA IDE is reached from within an Office document by pressing the key sequence Alt+F11.
VBA is functionally rich and flexible but it does have some important limitations, such as restricted support for
function pointers which are used as
callback functions in the Windows API. It has the ability to use (but not create) (
ActiveX/COM) DLLs, and later versions add support for class modules.
http://en.wikipedia.org/wiki/Visual_Basic_for_Applications
