best storage location for macros

chookers · May 6th, 2002, 04:34 PM

Howdy,

I've only ever had to use macros from my own computer, not have to send a doc out where the macros will have to work on someone else's. I had them saved in 'this workbook' (it's a template) but it looks like when the doc arrives to the other computer it's looking for the macros in the last document the template was used for (ie I think it's looking for the last "document 1" based on the template, which is of course on MY computer, not the recipients).

I have noticed this before actually but where is the proper place to save macros that need to "travel"??

Thanks for helping me understand this, my book only confused the issue!

Cheers!

PaddyD · May 6th, 2002, 05:46 PM

Not a macro expert, but my thoughts...

If you record a macro & store it in 'This workbook', the VBA editor shows it as being stored in the modules. You could cut the macro from there to store it as an excel object in 'This workbook'. Note: A macro recorded using the 'Record Macro' command will turn up as 'Macro1' in the macro list - if you do the cut paste, it turns up as 'ThisWorkbook.Macro1'.

I'm sure there are alternatives, but it might work.
Paddy

NateO · May 6th, 2002, 05:55 PM

Chookers, you'll want the macros stored where they ought to be. Worksheet events in worksheet modules, workbook events in the 'thisworkbook' module. Unless you call a procedure with a workbook name proceeding the procedure, and your sent workbook contains all of the applicable modules, it should work. Perhaps post the problematic 'thisworkbook' code.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-06 16:59 ]

chookers · May 6th, 2002, 07:55 PM

Thanks for that Nate et al, Nate, there are actually 7 different macros that go with 7 different buttons, am having the same trouble with all of them. If I look in VBA Project Explorer, you've got the 'Microsoft Excel Objects Folder', and the 'Modules' folder. Some of these codes open the toolbar, then close it when the workbook is opened, some cut and paste from one sheet to another, etc. At the moment all the codes are in the modules folder. Are you saying maybe to paste them all to 'This workbook' under 'Microsoft Excel Objects' instead? Does that work??? I thought they were meant to stay under modules?

Sorry- I'm a bit fuzzy on this for some reason (well, lack of experience basically). Thanks for helping to educate me!

Cheers, Lauren

NateO · May 6th, 2002, 09:12 PM

Laur,

No, don't store all your macros in 'thisworkbook.'

Anytime you store a 'macro' it is within a module, there are different types though....No module is better than another, they just serve different purposes (not to be profound).

So no, what I'm saying is keep these procedures in normal modules, unless they are workbook events (in which case, they would go in the 'thisworkbook' module), it doesn't sound like they are. You would know if the procedure (macro) is a workbook event, i.e., closing the workbook.....

It's hard for me to say why your macro won't transfer, don't have much info....

When you send someone a file (look in your sent folder), is the intended code in place? What is your procedure for delivering files to folks (yourself?)? Try and run the empty code and show the error line (hit debug when it goes bad).

Cheers, Nate

[ This Message was edited by: NateO on 2002-05-06 20:20 ]

chookers · May 6th, 2002, 10:32 PM

Nate, yes I've been emailing this out. I'm going to email this to myself and have a look at what's going on tonight. Will post again tomorrow to see if you or anyone can help -

As near as I can tell though, yes, I'm sending a template and when it's opened it seems to be giving an error message about can't find the macro in (for example) English1.xlt, which is basically document1 opened from the 'English' template, which I tested the macro in. I then closed it, sent the template off, but it's just sort of looking for it in the LAST document it was used in, not the current one.

Very bizarre. Will try to find out more tonight when I email myself.

Thanks very kindly, L

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 6th, 2002, 04:34 PM	#1
chookers Board Regular Join Date: Mar 2002 Posts: 115	Howdy, I've only ever had to use macros from my own computer, not have to send a doc out where the macros will have to work on someone else's. I had them saved in 'this workbook' (it's a template) but it looks like when the doc arrives to the other computer it's looking for the macros in the last document the template was used for (ie I think it's looking for the last "document 1" based on the template, which is of course on MY computer, not the recipients). I have noticed this before actually but where is the proper place to save macros that need to "travel"?? Thanks for helping me understand this, my book only confused the issue! Cheers!

May 6th, 2002, 05:46 PM	#2
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	Not a macro expert, but my thoughts... If you record a macro & store it in 'This workbook', the VBA editor shows it as being stored in the modules. You could cut the macro from there to store it as an excel object in 'This workbook'. Note: A macro recorded using the 'Record Macro' command will turn up as 'Macro1' in the macro list - if you do the cut paste, it turns up as 'ThisWorkbook.Macro1'. I'm sure there are alternatives, but it might work. Paddy

May 6th, 2002, 05:55 PM	#3
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Chookers, you'll want the macros stored where they ought to be. Worksheet events in worksheet modules, workbook events in the 'thisworkbook' module. Unless you call a procedure with a workbook name proceeding the procedure, and your sent workbook contains all of the applicable modules, it should work. Perhaps post the problematic 'thisworkbook' code. _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-05-06 16:59 ]

May 6th, 2002, 07:55 PM	#4
chookers Board Regular Join Date: Mar 2002 Posts: 115	Thanks for that Nate et al, Nate, there are actually 7 different macros that go with 7 different buttons, am having the same trouble with all of them. If I look in VBA Project Explorer, you've got the 'Microsoft Excel Objects Folder', and the 'Modules' folder. Some of these codes open the toolbar, then close it when the workbook is opened, some cut and paste from one sheet to another, etc. At the moment all the codes are in the modules folder. Are you saying maybe to paste them all to 'This workbook' under 'Microsoft Excel Objects' instead? Does that work??? I thought they were meant to stay under modules? Sorry- I'm a bit fuzzy on this for some reason (well, lack of experience basically). Thanks for helping to educate me! Cheers, Lauren

May 6th, 2002, 09:12 PM	#5
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Laur, No, don't store all your macros in 'thisworkbook.' Anytime you store a 'macro' it is within a module, there are different types though....No module is better than another, they just serve different purposes (not to be profound). So no, what I'm saying is keep these procedures in normal modules, unless they are workbook events (in which case, they would go in the 'thisworkbook' module), it doesn't sound like they are. You would know if the procedure (macro) is a workbook event, i.e., closing the workbook..... It's hard for me to say why your macro won't transfer, don't have much info.... When you send someone a file (look in your sent folder), is the intended code in place? What is your procedure for delivering files to folks (yourself?)? Try and run the empty code and show the error line (hit debug when it goes bad). Cheers, Nate [ This Message was edited by: NateO on 2002-05-06 20:20 ]

May 6th, 2002, 10:32 PM	#6
chookers Board Regular Join Date: Mar 2002 Posts: 115	Nate, yes I've been emailing this out. I'm going to email this to myself and have a look at what's going on tonight. Will post again tomorrow to see if you or anyone can help - As near as I can tell though, yes, I'm sending a template and when it's opened it seems to be giving an error message about can't find the macro in (for example) English1.xlt, which is basically document1 opened from the 'English' template, which I tested the macro in. I then closed it, sent the template off, but it's just sort of looking for it in the LAST document it was used in, not the current one. Very bizarre. Will try to find out more tonight when I email myself. Thanks very kindly, L