Dave said:
Can someone explain to me the distinction between macros and VBA in
Access? I'm coming from the Excel world, where these terms are used
interchangeably. Access people seem to think of them as distinct
concepts.
Thanks for any insight.
Dave
They are different in Excel as well as Access.
They share some of the same capabilities and in general Macros are
easier to learn and use, but are more limited in what they can do. VBA is
a programming language and can do much more complex operations and generally
will run faster and offers much more control over the process.
From the Access help files.
Macros are a set of actions that you can create to help you to automate
common tasks. By using groups of macros, you can perform several tasks at
once.
In Microsoft Access, you can accomplish many tasks by using macros or
through the user interface. In many other database programs, the same tasks
require programming. Whether to use a macro or Microsoft Visual Basic for
Applications often depends on what you want to do.
Macros are an easy way to take care of simple details such as opening and
closing forms and running reports. You can quickly and easily tie together
the database objects you've created because there's little syntax to
remember; the arguments for each action are displayed in the Macro window.
In addition to the ease of use macros provide, you must use macros to:
Make global key assignments.
Carry out an action or a series of actions when a database first opens.
However, you can use the Startup dialog box to cause certain things to occur
when a database opens, such as opening a form.
More information on when to use VBA in the help files.
From Excel help files:
If you perform a task repeatedly in Microsoft Excel, you can automate the
task with a macro. A macro is a series of commands and functions that are
stored in a Microsoft Visual Basic module and can be run whenever you need
to perform the task.
For example, if you often enter long text strings in cells, you can create a
macro to format those cells so that the text wraps.
Recording macros When you record a macro, Excel stores information about
each step you take as you perform a series of commands. You then run the
macro to repeat, or "play back," the commands. If you make a mistake when
you record the macro, corrections you make are also recorded. Visual Basic
stores each macro in a new module attached to a workbook.
Making a macro easy to run You can run a macro by choosing it from a list
in the Macro dialog box. To make a macro run whenever you click a particular
button or press a particular key combination, you can assign the macro to a
toolbar button, a keyboard shortcut, or a graphic object on a worksheet.
Managing your macros After you record a macro, you can view the macro code
with the Visual Basic Editor to correct errors or change what the macro
does. For example, if you wanted the text-wrapping macro to also make the
text bold, you could record another macro to make a cell bold and then copy
the instructions from that macro to the text-wrapping macro.
If you perform a task repeatedly in Microsoft Excel, you can automate the
task with a macro. A macro is a series of commands and functions that are
stored in a Microsoft Visual Basic module and can be run whenever you need
to perform the task.
For example, if you often enter long text strings in cells, you can create a
macro to format those cells so that the text wraps.
Recording macros When you record a macro, Excel stores information about
each step you take as you perform a series of commands. You then run the
macro to repeat, or "play back," the commands. If you make a mistake when
you record the macro, corrections you make are also recorded. Visual Basic
stores each macro in a new module attached to a workbook.
Making a macro easy to run You can run a macro by choosing it from a list
in the Macro dialog box. To make a macro run whenever you click a particular
button or press a particular key combination, you can assign the macro to a
toolbar button, a keyboard shortcut, or a graphic object on a worksheet.
Managing your macros After you record a macro, you can view the macro code
with the Visual Basic Editor to correct errors or change what the macro
does. For example, if you wanted the text-wrapping macro to also make the
text bold, you could record another macro to make a cell bold and then copy
the instructions from that macro to the text-wrapping macro.
The Visual Basic Editor is a program designed to make writing and editing
macro code easy for beginners, and provides plenty of online Help. You don't
have to learn how to program or use the Visual Basic language to make simple
changes to your macros. With the Visual Basic Editor, you can edit macros,
copy macros from one module to another, copy macros between different
workbooks, rename the modules that store the macros, or rename the macros.
Macro security Excel provides safeguards against viruses that can be
transmitted by macros. If you share macros with others, you can certify them
with a digital signature so that other users can verify that they are from a
trustworthy source. Whenever you open a workbook that contains macros, you
can verify their source before you enable them.
.. With the Visual Basic Editor, you can edit macros, copy macros from one
module to another, copy macros between different workbooks, rename the
modules that store the macros, or rename the macros.
Macro security Excel provides safeguards against viruses that can be
transmitted by macros. If you share macros with others, you can certify them
with a digital signature so that other users can verify that they are from a
trustworthy source. Whenever you open a workbook that contains macros, you
can verify their source before you enable them.
The above reference to VBA is best understood by noting: "The Visual
Basic Editor is a program designed to make writing and editing macro code
easy for beginners, and provides plenty of online Help. You don't have to
learn how to program or use the Visual Basic language to make simple changes
to your macros."
Which means you do need to to learn how to program or use Visual Basic
language to get full use of it and to see all the more powerful features.
