Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    distributing modules (XL 97 sr2 on Win2000/NT4)

    I have lots and lots of spreadsheets with the same VBA module in them. I've tweaked the code in one sheet.

    Is there an easy way to update all the sheets with this new code?

    stuck

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distributing modules (XL 97 sr2 on Win2000/NT4)

    Probably not at this point. See <!post=This Post,118382>This Post<!/post> for a description of what you should do to make it easier next time you have to do this.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distributing modules (XL 97 sr2 on Win2000/NT4

    Perhaps I should have said also that these sheets are used by many different people, that is why I put the code in the sheets and not personal.xls.

    stuck

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: distributing modules (XL 97 sr2 on Win2000/NT4

    What about creating an add-in and storing it in one location that everyone has access to. They can all attach the addin and then there is only 1 file to update.

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: distributing modules (XL 97 sr2 on Win2000/NT4)

    If your code is in a general module, you can export the module from the VBA editor . Export it to a location that all usersworkbooks can access. Then open each workbook that needs updating, and from the VB editor select the obsolete module in th eobject browser, right click and choose to remove module. You can then import the module you exported by right clicking on the relevant project in the object browser and select Import file. Assuming your macro names have not changed, all should be ok.

    Andrew C

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distributing modules (XL 97 sr2 on Win2000/NT4

    OK, then there are two possibilities. Steve has already mentioned putting the code into an addin. I find that this approach is a bit troublesome when you update the code, at least on versions less than XL2000 (I haven't tried it on more recent versions, so I don't know if it is still a problem). The problem I found was that you could not just replace the .xla file on a system or server and have the new code work. You had to also remove the reference to the .xla, shut down Excel, start Excel, and then re-establish the reference to the addin on all systems that use it.

    My preferred solution was to put the code in a separate hidden workbook. Then have code in the open event of the workbook that needs the code that opens the hidden workbook. Now you can use the RUN command to run any of the macros in the separate workbook (which can be shared of a server), and you can update the code by just replacing the code workbook.
    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distributing modules (XL 97 sr2 on Win2000/NT4

    Thank you one and all,

    I'll try out both the add-in idea and the shared hidden workbook approach. I reckon either way is going to be more fun than opening each sheet and importing the new code.

    NB In this context the phrase 'more fun' is actually a technical term that really means "in blissful ignorance I'm going to regret ever starting on this but how else will I learn?"

    stuck

  8. #8
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: distributing modules (XL 97 sr2 on Win2000/NT4

    My initial exploration of putting the code into an xla has had mixed results.

    I started by transferring some user defined functions (UDFs) out of a working sheet and into a .xla held in a network folder. When I activated the .xla (via tools|add-ins and browsing to the network folder) XL asked if I wanted to copy the file to my local library folder, I declined.

    Having restarted XL the UDFs seemed to be available, e.g. they appeared in the function list available from the fx button, but if I tried to use them they returned a #NAME! error. They only burst into life once I switched to the VBE and added a reference to the .xla (via tools|references and checking the relevant box) to the worksheet in which I was trying to use the UDF.

    Is this the way it is supposed to work? I had expected that once I'd made the .xla available in XL, then the UDFs would be available to any worksheet without recourse to the VBE.

    stuck

Posting Permissions

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