Embedded Macros Vs VBA

[PhilOfWalton]

Assuming someone is reasonably proficient at writing both Macros & VBA so that ease of learning is NOT relevant, and also appreciating that a number of things can only be done using VBA (API calls, Class Modules etc.) could the experts offer views on the advantage of each method for the more basic functions (Opening forms, searches etc)

Phil

[ADezii]

Here is an interesting Article written by a Bytes Moderator some years ago, hope it is helpful. Some of this Article is no longer applicable since the newer Versions of Access were released.
Macros or VBA?

[PhilOfWalton]

I think that when that article was written, the guy was referring to macros, rather than Embedded Macros.

I personally never use macros (Except AutoExec occasionally), but am trying to establish if they have any advantages, particularly when using non-Access back ends (SQL Server, MySQL, Silverlight etc)

Phil

[NeoPa]

Hi guys.

@ADezii.
I'm intrigued to hear some of the points in the article are no longer applicable. Do you have some examples for me to consider? I couldn't see anything affected by new versions when I skimmed through it again.

@Phil.
The author is Mary (McCarthy). A lovely Irish lady who no longer has much time to devote to Bytes.com, but whom I work with from time to time. Neither of us has been a member as long as ADezii here.

I'm curious to understand what you're referring to with the term 'Embedded Macros'. I couldn't follow that point I'm afraid.

As a general rule, even assuming full expertise in both macros and VBA, I would recommend the latter, as all but the simplest of projects will require some VBA due to the limited nature of using macros. Assuming then, the use of VBA, why would anyone complicate a project by having some of both. I've inherited projects of that type in the past and it was never fun to deal with. Once all the macros had been replaced by VBA it was much easier to work with.

I hope this is some help.

[NeoPa]

Bizarrely enough, I've just had Embedded Macros explained to me by Access MVP and guru Crystal Long. This is a new feature with ACCDBs which I've never come across because I don't believe using macros ever makes sense in an Access database. Embedded or otherwise (and Crystal informs me that the undocumented feature AccessApp.SaveAsText(ObjectType:=acMacro, ObjectName:=..., FileName:=...) saves Embedded Macros in an entirely new XML format.) I don't believe having macros in a project with VBA makes any sense at all. Everything a macro can do can equally, and generally better, be done in VBA. You can even call a VBA function procedure directly as an Event destination (by setting the property to =YourFunctionName() - optionally with parameters) rather than always having to direct it to the Event Procedure (by setting the property to [Event Procedure]).

Hopefully that answers your question more fully Phil.

[ADezii]

@NeoPa:
A couple of points that I was referring to are:

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.

No longer true with 'Embedded Macros'

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).

The use of TempVars has changed this since they retain their Value until either Access is closed, or they are explicitly removed from memory.

[PhilOfWalton]

There appears to be confusion between macros & Embedded Macros. AFIK, Embedded macros are saved with the form.

Please, Don't get me wrong, I have always used VBA, but Embedded Macros are, I believe, a comparatively recent development (Access 2007 and later). I hate to say this, but 10 years ago for me is recent...

So there must have been a reason for their addition, and I am trying to find out why.

Phil

[NeoPa]

@ADezii.
Indeed. Your first point I recently discovered.

TempVars are an addition and are also available to macros. That doesn't imply the statement in the article is no longer correct. VBA does allow use of Public Variables. That's a less important difference now, but still fundamentally true. However, I think I understand you better now anyway.

@Phil.
You seem to be implying Embedded Macros are separate from Macros generally. I see them as a subset thereof, albeit saved in a different place.

My earlier comment is based on the idea that where they're saved is fundamentally irrelevant and usage of any type of macro in a project is likely to make your life harder than simply using VBA.

I'm afraid I have no insight into why the Embedded Macro was added as a thing, when linking to named macros worked in fundamentally the same way. The only pertinent difference I can see is that they're not so easily (if even possibly) reusable. Nothing I know about them would lead me to recommend them to anyone who could do even the most basic work in VBA.

If I do hear anything that might throw any light on that decision I'll try to remember to feed it back to you.

PS. I asked it of the other Access MVPs (& alumni) so maybe you'll get an answer later.

[ADezii]

Just a couple of additional points regarding Embedded Macros.

1. Embedded Macros are stored in an Event Property and is part of the Object to which it belongs. That being said, each EM is totally independent from each other.
2. EMs are not visible in the Navigation Pane and are only accessible from the Object's Property Sheet.
3. Embedded Macros are Trusted and even run if your Security Settings are set to prevent the running of Code.
4. Using thee Macros allows you to distribute your Application as a 'Trusted' Application because Embedded Macros are automatically prevented from performing unsafe operations.

P.S. - Personally, I never use Embedded Macros, and there are only two standard Macros that I will ever use: AutoExec, and AutoKeys.

[NeoPa]

You've stolen my thunder a little there ADezii. Well done. Here are some extra considerations :

1. TheSmileyCoder:
   Another bonus of embedded macros is that they copy/paste along with the control. So if you have a generic "Close form" button, then you can copy paste it along with the logic behind it.

   I'm not sure that's such a big issue really as that could be done with standard named macros too of course.

2. TheSmileyCoder:
   I use a splash form that says "This project is not trusted, click the button blah blah". The form's onOpen event closes itself, and opens the login form. So if code IS trusted, the form is never shown.

   Quite clever really :-)
   
   That might be another to add to your list of exceptions ADezii.

[Nancy845]

Macros can also facilitate the users in creating graphs or charts. If the tasks of copying and pasting the data or making calculations based on the assigned formulas are done repetitively, macros is of obvious importance and great use to carry out such activities effectively.

[NeoPa]

Hi Nancy.

I'm not sure I can agree with that, not if I understand you correctly. When we talk about macros in Access we're not talking about VBA, as we might be if say we were talking about Excel. In Access macros are a different animal. There are extremely few situations where use of macros can't (and indeed they should) be replaced by VBA code. Very few experienced developers use more than the absolute basics of macros (AutoKeys etc). Anders has given a good example of an embedded macro that can be very useful if you don't have control of the environment the project may run within.