Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: best storage location for macros

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,211
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •