Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Share code (2003)

    How can I share code among a group of users? Do I need to copy it to each users' personal.xls? Can I somehow make a shared personal.xls and store it on a network drive? I actually tried this, but I didn't do it right (or it isn't even really possible).

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Share code (2003)

    Hi Melanie

    Howabout putting all your code or snippets in a workbook module and saving it as an add-in (password protected if you want <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15>) and then put it on the network and all the users can navigate to it and link via their add-in manager.

    I ran an Excel programming course and all my source code was saved like this and my students just downloaded it and placed it in their personal network drive
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Share code (2003)

    Sounds good, but I am a code neophite, so how do I do this? Can I somehow export personal.xls or do I have to copy/paste? This user has quite a lot in personal.xls that he wants to share.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Share code (2003)

    Hi Melanie

    Can I just check we are using the same terminology here. When you say code, are you talking VB code in modules and on the worksheets? If this is the case then all you have to do is:

    1) File | Save As
    2) In Save As dialoge select Excel Add-in
    3) Select location you want the add-in (XLA)...let's say on a shared drive of sorts

    If users want to access/link to the XLA file then they do the following:

    1) Tools | Add ins...
    2) Navigate to shared folder
    3) Select file
    4) Click OK

    All the functions and subs are available to the file from this point


    If on the other hand you just have a set of codes you could get the users to add a personal.xls to:

    Cocuments and Settings[user_name]Application DataMicrosoftExcelXLSTART
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Share code (2003)

    You might want to read this article of mine, it fully addresses your question (and any others you might have).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Share code (2003)

    Well, I read your article, and I thought, "Cool!" But, I'm doing something wrong. I tested this on my computer. I opened my personal.xls, which contains 6 modules, and saved the workbook as an add-in on one of our network drives. I opened a blank workbook on another PC and went to Tools, Addins, and browsed to the xla file on the network.

    Then, I went to tools, macros and cannot find any of the macros.

    However, if I press Alt+F11 I can see the modules.

    So, what am I doing wrong? I want to be able to use the macros without having to Click Alt+F11. I apologize for being so dense here. This whole code thing has me a bit baffled. I am learning, but I have a long way to go. I really appreciate your patience with me.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Share code (2003)

    You won't see a macro from the add-in listed in Tools | Macro | Macros, but you can still run it by typing its name in the Macro Name box.
    Similarly, when you create a custom toolbar button and assign a macro to it, you won't see macros from the add-in in the list, but you can type their name.
    However, it's better to create a custom toolbar and store it in the add-in (and destroy it when the add-in is closed), as described in Jan Karel Pieterse's article. That way, the user won't have to know the names of the macros.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Share code (2003)

    You'll have to create some way to access them directly, e.g. a toolbar or a menu. Or remember the macro names and type them every time indeed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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