472,986 Members | 2,874 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,986 developers and data experts.

Macros or VBA?

14,534 Expert Mod 8TB
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
3 20519
125 100+
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
234 Expert 100+
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
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

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Andrew Dalke | last post by:
Here's a proposed Q&A for the FAQ based on a couple recent threads. Appropriate comments appreciated X.Y: Why doesn't Python have macros like in Lisp or Scheme? Before answering that, a...
by: mike420 | last post by:
I think everyone who used Python will agree that its syntax is the best thing going for it. It is very readable and easy for everyone to learn. But, Python does not a have very good macro...
by: Pete | last post by:
In Access 95/97 I used to be able to create pull down menus (File,Edit ...) from a macro. It seems there used to be some wizard for that. However in Access 2000 it seems you have to build your...
by: Newbie_sw2003 | last post by:
Where should I use them? I am giving you my understandings. Please correct me if I am wrong: MACRO: e.g.:#define ref-name 99 The code is substituted by the MACRO ref-name. So no overhead....
by: Alexander Ulyanov | last post by:
Hi all. Is it possible to pass the whole blocks of code (possibly including " and ,) as macro parameters? I want to do something like: MACRO(FOO, "Foo", "return "Foobar";", "foo();...
by: lasek | last post by:
Hi...in some posts i've read...something about using macro rather then function...but difference ??. Best regards....
by: Laurent Deniau | last post by:
I was playing a bit with the preprocessor of gcc (4.1.1). The following macros expand to: #define A(...) __VA_ARGS__ #define B(x,...) __VA_ARGS__ A() -nothing, *no warning* A(x) -x ...
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the...
by: Bill | last post by:
This database has no forms. I am viewing an Access table in datasheet view. I'd like to execute a macro to execute a function (using "runcode"). In the function, I'll reading data from the record...
by: =?Utf-8?B?TGV0emRvXzF0?= | last post by:
I'd like to create a Macro that will sort some raw data, apprx 20k lines, remove some lines based upon a condition in a certain column. Then copy this data into a new spreadsheet and sort the ...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.