Excel - Same Code, diferent files

[baixinho]

Hi,
i have a few excel files wich use the same exact macro.
what i´m trying to do is to make this macro independent from all excel files, so when i have the need to change it, i would just have to do it only once. Kind like importing a text file, but automaticaly...

can anybody help me?

thx

 

[RoyVidar]

Hi!

If you haven't used it before, I've found it not to be directly available in the project explorer in VBA-environment, but:

When recording a macro, you have the option to store it in some different locations (dependent of settings/privileges)

In my version the "Personal Macro Workbook" is available. (Think I've seen Global Macro Workbook etc...)

Select one of those, when registering some macro, afterwards a "VBA project" with that name should be available in the project explorer. Paste your macro/function/sub there, and it should be available to all workbooks. I'm not 100% sure, but I think you should declare it as public.

HTH Roy-Vidar

 

[baixinho]

That would work if i had lots of workbooks in a .xls file... which is not the case... i have in the same directory lots of .xls files, lets say 1.xls 2.xls and 3.xls. what i am trying to do, is to have something like one code.txt in which i would write the vba code i have in the files, so when they load, they would use this code from the text file. So, when i eventualy have the need to change it, i would only have to do it once...

 

[pcmedic411]

Don't use a text file, try saving this "code.txt" as a template(.xlt). Then in the VBProject of each workbook you are using the "code.txt" macros in, reference the template file.

Then you only have to update the code in the template, and are able to run the code in any workbook that has a reference set up.

Thanks,

Chuck

 

[baixinho]

i´ve never made a template file before... i´m having some trouble addind the references. can you please explain me how i make a template file?