How much of a performance impact does declaring variables / passing arguments have? (1 Viewer)

Having worked with VBA for a few months I've now set myself the task of cleaning up a lot of messy code I wrote a long time ago. All my subs and functions are in one module and I want to structure them more neatly. This means separating them out into groups, putting them in different modules and calling them individually when needed.

As I'm using VBA in Access one of the things I do is to assign the table names to string variables at the top of my code for table names/recordsets etc. As I don't believe you can assign variables at a global/public level I will have to pass them as arguments to subs/functions in other modules. Does this passing of arguments slow things down much? Previously I just assigned them once at the top but now they're getting passed on every loop.

Same goes for declaring private variables within the different modules. I believe best practice is not to declare all variables as public but that means some private variables will be assigned every time a sub/function is called. I presume this doesn't hit performance too much?

in nanoseconds, none.
all slowdown is file I/O.

the impact on speed is negligible.
in fact your variables are encapsulated on that sub/function only.
make sure to declare the parameters as intented.
if you want to modify the content of the variable, on function/sub declaration, use Byref.
if you don't want to modify them use Byval.
but there is little penalty of Byval, since it makes a second copy of the variable passed.

modules also occupy memory and stays there until you close the app.
gather all func/sub that you often use in one module so they are loaded at one time.
every call to func/sub on different modules add them (the entire module) to memory.

Hi. I'm with the others. Efficiency is more important than speed in this case, which is negligible anyway.

In fact, declaring a variable as public and using it throughout your application can improve performance

For example, if you always use CurrentDB.Execute "SQL statement here", Access has to interpret the CurrentDB part each time it is run

If you instead declare Public db As CurrentDB and then use db.Execute "SQL statement here" on each occasion, there will be a small but measurable reduction in the time required to run the process

OK, up front let's be clear. We cannot see the code built by Access to do the things you have asked about. There is no "show compiled code" option.

HOWEVER, if you had a true compiler that generated hard machine code, the cost of passing arguments through a call vs. using public (global) variables is on the order of a few instruction times.

Allocating the stack as part of the call pre-amble just changes the constant added to the stack pointer and causes no difference in stack allocation. No timing difference to the pre-amble.

That extra variable has to be loaded to the stack in some way, which will take three instructions at the worst, and maybe only two if you are lucky.

Returning from the call's post-amble just changes the constant subtracted from the stack pointer. Again, no difference in the post-amble.

Net result? Two or three instructions at most. On a machine running 3.2 to 3.8 GHz (depending on which chipset you use), one instruction would be roughly 0.3 nanoseconds, three instructions might make 1.1 or 1.2 nanoseconds.

VBA doesn't compile machine code and doesn't expose its code. Therefore I can't tell you the overhead of the call emulation, but the odds are good that we are still talking about less than 1 microsecond (1000 nsec) for that difference on the worst possible emulation you could imagine. So net result? If you called the routine a million times, it might cost as much as one second in total and I doubt it would really be that much.

The differences for the example I provided are indeed small as previously stated but I think slightly greater than the Doc suggests.
See http://www.mendipdatasystems.co.uk/speed-comparison-tests-2/4594428908

Colin, is your reference in post #7 the one you intended?

I interpreted the question as having to do with argument passage in a more traditional subroutine setting. I agree and understand why setting a variable once is faster than repeatedly setting it inside a loop. However, the question here - or at least the way I interpreted it - wasn't about complex objects as massive as a database object. It was more like strings or numbers, traditional variables. Did I miss something?

Although originally intended to compare CurrentDB and DBEngine(0)(0), that was the reference I intended.
The highlighted section below hopefully shows the point I was making