Macros vs. VBA?

Djblois

Registered User.
Local time
Today, 09:15
Joined
Jan 26, 2009
Messages
598
I think I read somewhere that whenever possible I should use Macros over VBA because they are faster to run. Is this true? plus what are some other benefits to running Macros over VBA?
 
Personally I take the opposite view. I never use maros, and never had. VBA is far more versatile and manipulative.
 
I totally agree with DCrake, the only macro I use on a regular basis is AutoExec to run my code.
 
I know that already - I currently only use VBA. However, I know you can use both together. I have heard that Macros run quicker and was wondering if I should use them in the cases that I do not need anything more powerful (VBA)
 
Can you give an example of what they can supposedly do quicker? I have not seen any performance difference between calling code for say Docmd.OpenQuery in VBA or the openquery command in a macro.
 
Last edited:
The reason why certain macros run faster than vba is because the methodology was authenticated and validated by Access upon creation. Running the same procedure via vba in an uncompiled setup means that Access has to check the syntax before it performs the code.
 
so might it make sense to use Macros in Certain cases then? Of course when more complicated procedures are needed then I could still use VBA.
 
If the code you want to run is not reliant on variables or user input then go for macros.
 
The reason why certain macros run faster than vba is because the methodology was authenticated and validated by Access upon creation. Running the same procedure via vba in an uncompiled setup means that Access has to check the syntax before it performs the code.


If you compile the code, wouldn't there be no difference though (or at least, not for that reason)?

Like hyou I mainly use VBA, but as I'm self trained that's because it's what I know rather than because it is better.
 
Well, code may get uncompiled if you are using *DB files sot to ensure it's always compiled, distribute *DE files.

That said, I can kind of see how macros would be *slightly* faster than VBA since the underlying code would be running at much low level and would be reading only the inputs from macros. But the low-level code still has to read the macro input at every step, no different from VBA compiler interpreting the p-code to execute it. So if there's any difference either way, it'll be too slight to be even noticeable.

I would not waste my time mucking with nanosecond differences. I've had impatient users but they were never found wanting a nanosecond. :)
 
I totally agree with DCrake. (Sorry about misspelling the name, it must be my "double vision" :))
If maintaining someone elses code, might leave it in if it is not broke.

Just got word, after they started of course, that IT plans to update everyone from XP running Office 2007 and Access to Windows 7 and Office 2010.

Would the decision for Macros Vs VBA change for this?
The application I have upgraded with about 75% new data tables and forms (all VBA) don't concern me as much as the 25% that still uses Macros and very little VBA.
 
Last edited:
I'm pretty sure (but I have no actual proof!) that macros should continue to work as before in 2010.

If nothing else, at least, I do think macros are pretty nice in 2010. It's sort closer to how you'd write VBA and has some error handling capability so there's that going for 2010.
 
With regard to: -
>>I think I read somewhere that whenever possible I should use Macros over VBA because they are faster to run.<<

We could look at it this way…
In order to test for speed we need to time both.
We would need to test a Macro against the exact VBA equivalent functionality.
In order to test both we would need to write both.
We can test the speed of a VBA function using an API call.
How do we test the speed of a Macro; we write VBA to call the API call?
Or, do we convert the Macro to VBA in order to time it?

Often on the web we see statements like “this is faster than that” but has it really been tested correctly? Generally not. Very often we are looking at a last resort statement by someone. It’s a last resort because whoever feels like they are loosing an argument (discussion) will often resort to “but mine is more efficient” or “mine is faster”.

Macros are seldom mentioned, normally it’s SQL verses VBA, but the same thing applies.
In order to test the difference both need to be written and tested.

Today the machine we are using is rather complex and that makes it difficult to time any process. People generally do not know how to run timing test code, be it Macros, VBA or SQL. Timing tests will vary from run to run. During long timing tests other things may happen, incoming email, virus scan, network traffic, screen update time, blah, blah, blah.

How many people will, as much as is possible, isolate the test from the testing environment?

On this matter only three things are certain: -
1. Timing code is a complex matter and it takes time to run those tests.
2. People who make the claim that “this is faster than that” have generally not timed it.
3. Don’t believe everything (anything) we read, just test it.

The thing we get from running the tests is that we learn why some particular instance is faster.


Chris.

.
 
I would be surprised if macros are faster since one of the suggestions made by the Performance Analyser in Access is to convert macros to VBA.

About the only advantage of macros is they can run without causing trust problems. But as soon as the developer needs one piece of VBA that advantage is obliterated.

Since VBA can do everything available in macros plus (insert any large number) times more, there seems little point to bothering with macros at all. It also makes sense to me to keep all the code in one place.

Macros are probably simpler to learn (though I have never used them) but with experience, VBA code can be churned out quite fluently with very little effort.

Moreover, well written code is very portable between databases because it can accept arguments so is not tied to object names. This allows high powered functionality to be dropped into a database very easily.
 
i don't know if its changed, but it used to be that the only things you could do with macros, that you couldn't do with code were

a) autoexec macro and
b) autorun macros - which provide useful hot-key facilities - basically macros activated by a single key-click combination
 
In 2007, Autoexec can be emulated by using the form listed in:
Access Options/Current Database/Application/Display Form

Any code that needs to run automatically can be run from the Form_Load procedure.

The Key events can easily simulate hot key facilities.
 
FWIW, the same thing Galaxiom mentions was available in prior versions so not just 2007-onward. Just in a different place (Tools -> Startup Options). I actually prefer this over AutoExec anyway because when it's in a Form_Load procedure, then it get the benefit of a *DE file preventing it being edited/tampered with.
 

Users who are viewing this thread

Back
Top Bottom