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?
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?