SOLVED

Change workbook name in multiple macros

%3CLINGO-SUB%20id%3D%22lingo-sub-2397271%22%20slang%3D%22en-US%22%3EChange%20workbook%20name%20in%20multiple%20macros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397271%22%20slang%3D%22en-US%22%3E%3CP%3Ewhen%20I%20create%20a%20new%20workbook%20from%20an%20existing%2C%20in%20which%20there%20are%20multiple%20macros%20that%20refer%20to%20the%20original%20workbook%2C%20Can%20a%20macro%20be%20written%20that%20will%20change%20the%20workbook%20name%20in%20all%20of%20the%20macros%20so%20that%20they%20will%20work%20with%20the%20new%2C%20without%20having%20to%20change%20each%20one%20manually%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2397271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2397482%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20workbook%20name%20in%20multiple%20macros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397482%22%20slang%3D%22en-US%22%3E1.%20You%20should%20look%20into%20how%20to%20share%20code%20as%20an%20add-in.%20That%20way%2C%20there's%20no%20proliferation%20of%20your%20code.%20If%20you%20want%20to%20fix%20a%20bug%20or%20enhance%20a%20code%2C%20you%20only%20have%20to%20worry%20about%20the%20code%20in%20the%20add-in.%20If%2C%20on%20the%20other%20hand%2C%20you%20are%20sharing%20code%20in%20a%20XLSM%2C%20there's%20no%20way%20you%20can%20find%20all%20the%20workbooks%20that%20contain%20your%20code%2C%20let%20alone%20fix%20all%20of%20them.%3CBR%20%2F%3E2.%20If%20you%20must%20stay%20with%20the%20current%20architecture%2C%20look%20at%20VBA%20objects%20ThisWorkbook%20and%20ActiveWorkbook.%20When%20you%20use%20those%20constructs%20your%20code%20will%20be%20independent%20of%20the%20name%20of%20the%20workbook%20containing%20it%20as%20well%20as%20independent%20of%20the%20workbook%20the%20user%20is%20currently%20working%20with.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2398008%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20workbook%20name%20in%20multiple%20macros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2398008%22%20slang%3D%22en-US%22%3Ethanks%20for%20the%20quick%20answer%20for%20above%20question.%3CBR%20%2F%3Ethis%20may%20be%20over%20my%20head%20as%20I%20am%20not%20to%20great%20with%20VBA%20but%20s-l-o-w-l-y%20learning%20the%20basics.%20Is%20there%20a%20tutorial%20that%20you%20could%20recommend%20for%20VBA%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Contributor

when I create a new workbook from an existing, in which there are multiple macros that refer to the original workbook, Can a macro be written that will change the workbook name in all of the macros so that they will work with the new, without having to change each one manually?

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution
1. You should look into how to share code as an add-in. That way, there's no proliferation of your code. If you want to fix a bug or enhance a code, you only have to worry about the code in the add-in. If, on the other hand, you are sharing code in a XLSM, there's no way you can find all the workbooks that contain your code, let alone fix all of them.
2. If you must stay with the current architecture, look at VBA objects ThisWorkbook and ActiveWorkbook. When you use those constructs your code will be independent of the name of the workbook containing it as well as independent of the workbook the user is currently working with.
thanks for the quick answer for above question.
this may be over my head as I am not to great with VBA but s-l-o-w-l-y learning the basics. Is there a tutorial that you could recommend for VBA?