newest 64-bits access 365 vba works by far slower than 64-bits access 2010 don't understand. is any fix for that. (1 Viewer)

[olek_w1]

Dear All,

I have some project which uses lot's of Declare statements to windows api dll functions....also I'm using Microsoft scripting Dictionary object to store some data...
I'm suprised that very new laptop with newest (with all newest windows and office updtes installed) office 365 64-bits is doing vba code by far slower than
office 2010 64-bit on exactly the same hardware configuration. (i did a test on the same computer uninstalled office 365 and installed office 2010)

Please advise, have you meet also such an situation... is there any way to fix it.

 

[CJ_London]

Not aware of any specific issues - I’ve not noticed any significant difference myself between 2010 32bit and 365 64bit.

is you problem across the whole app or just related to the part that uses the ms scripting library?

@isladogs has multiple versions and may be able to throw some light on the subject

 

[olek_w1]

Hello,

Some update... I've noticed that once in "Trust Center" is chosen the last option "Enable all macros ( not recomended) then everything is working well and fast... but when it is deafault ( second - enable macros with notification) when if the i click "enable macros" - then the macros are working much slower....maybe this will be helpfull to someone

 

[CJ_London]

good to know

 

[The_Doc_Man]

This is an educated guess. In order to have macros "with notification" there has to be a test associated with each macro to determine if notification is required for the current call (i.e. each individual run-time activation of the macro). That means that you have to repeat the code for each macro activation each time - which means more instructions, more overhead. The setting "Enable All" implies another check that would bypass the "notification" check. This setting obviously does the least by just saying, "Oh, skip it." We don't know (because MS hides their code) just what is done at a low level for each macro activation but with the "Enable All" option, it obviously does the least.

This link suggests that the "with notification" option operates on a case-by-case basis as I described.

Enable or disable macros in Microsoft 365 files - Microsoft Support

Improve security, evaluate and mitigate the risks of running macros, and see how to enable or disable macros for Microsoft 365.

support.microsoft.com

So the question is, how often are you running macros? How many do you have? Also, do you run with warnings disabled? That can incur some hidden overhead as well.

 

[CJ_London]

So doc, since we are on the subject of macros would you say macros intrinsically run slower than compiled vba code since presumably it has to compile each time it is executed? Or do you think there is a macro equivalent of a query plan - that once executed a compiled version exists?

just curious on a sunny afternoon

 

[The_Doc_Man]

I don't think a pseudo-compiled version exists until / unless you exercise the option to convert a Macro to VBA. I think Macros are either fully interpreted or at most partially emulated, partially interpreted. At best, you have the macro action which probably is translated by a lookup to a dispatching index in the appropriate .DLL reference, then the arguments are laid one-by-one behind the action. I would GUESS that the arguments become similar to an argument array (which you find in some API calls). At least, that is how I might implement it, and I know that other .DLL files use a dispatcher entry-point index method.

 

[isladogs]

Hello,

Some update... I've noticed that once in "Trust Center" is chosen the last option "Enable all macros ( not recomended) then everything is working well and fast... but when it is deafault ( second - enable macros with notification) when if the i click "enable macros" - then the macros are working much slower....maybe this will be helpfull to someone

Sorry but I can't see any logic in these results.
The default option to enable macros with notification is the only option that causes the yellow security banner to appear.
Despite its name, it is actually blocking VBA code in Access .
Certain 'safe' macro actions will still run even if all macros are disabled (with or without notification)
See my article

Debunking Myths about the Autoexec Macro

This article debunks several widely believed fallacies about the autoexec macro used at startup

www.isladogs.co.uk

Once the security banner is clicked, the database should run exactly the same as the final option to enable all macros