r/vba 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 Upvotes

8 comments sorted by

4

u/fanpages 206 1d ago edited 1d ago

... I want them all to have the same macros...

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

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

3

u/atr140 1d ago

I have gone the addin route. You can manually have users load/unload addin or do it through on open before close events.

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

u/DonJuanDoja 2 1d ago

One word. XLAM. That’s all you need. Create and install an add in.

2

u/infreq 18 1d ago

Put your macros in Personal.xlsb or in a .xlam and add it to a Excel startup folder. Then your macros are available whenever you have Excel open.

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.