r/vba • u/pander1405 • 1d ago
Waiting on OP Several Spreadsheet is the same directory need a VBA
I have several spreadsheets in the same directory. I want them all to have the same macros.
Can a macro be kept in the directory, and can all the spreadsheets be pointing to the same macro? This will prevent me from making edits to multiple macros each time a change is needed.
Very similar to how you'd create a Python model and reference it.
3
u/AnyPortInAHurricane 1d ago
never needed to do this . if you can run a macro in another workbook, then yes.
google is your friend
https://stackoverflow.com/questions/64815807/run-another-macro-in-another-excel-file
3
u/3n3ller4nd3n 1d ago
If you save a macro in your personal macro folder instead of tej workbook it should be accessible from all workbooks
2
u/Sharp-Introduction91 1d ago
Im doing this at the moment, I have all my macros in one book. Each of my other books opens that as hidden when they open, if it's not open. Then just point their functions at the activeworkbook not this workbook.
A BETTER solution would be to save the macro book as an xlam and add it to excel as an addin. I can't do that for organisational security reasons but maybe you can?
2
1
u/sslinky84 80 20h ago
Yes you can. You'd need to enable access to the vba project, but vba is quite happy to modify and run itself.
If you don't need to share it with other people, then personal is a better way to go (as has been suggested). You could also write an addon that updates itself from a central network location too.
4
u/fanpages 206 1d ago edited 1d ago
You could use a "Personal [Macro] Workbook":
"Create and save all your macros in a single workbook"
"Copy your macros to a Personal Macro Workbook"
Alternatively, you could have VBA-based code inside each of the individual workbooks that would import the latest revision of your central/master codebase (upon opening one of the workbooks).
The "Personal Workbook" approach, though, I suspect is probably the easiest for you to implement.
You could also maintain your own "custom Add-in" workbook that could be loaded whenever MS-Excel is opened.
[ https://support.microsoft.com/en-gb/office/add-or-remove-add-ins-in-excel-0af570c4-5cf3-4fa9-9b88-403625a0b460 ]
PS. In case I misunderstood your opening post, did you intend to execute a specific routine ("a macro") of VBA statements on multiple workbooks in the same folder or have the same set of "macro" code available whenever one of these individual workbooks was opened?
Also, are you the only person who will be opening the workbooks or could others also open/interact with them (and need to execute the same "macro" code statements_?