Results 1 to 9 of 9
  1. #1
    Leslie Earle
    Guest

    Multiple Excel Addins

    I'm in the process of creating a system using Addins, where a common XLA would detect, on a Workbook-Open event, which subordinate XLA should be loaded and run, based on values in the first few cells of the spreadsheet. This part works okay, but in order to save space, I tried to put the commonly used subroutines into COMMON.XLA. They are all declared as Public, but the subordinate XLA routines cannot 'see' them. As it is now, I have to make copies of the common routines into modules in each XLA.

    Sometimes the routines stop with duplicate references, but I don't know how to determine what the problem is. I should point out that the Module Names in the XLAs are default - Module 1, Module 2, etc.

    Can anyone shed some light on how to deal with it?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Excel Addins

    The first rule is to give your routines unique names so that they don't conflict with routines in other modules, which includes the modules in add-ins. Don't expect add-ins to be able to see each other. Each of them should be self-contained. If you need routines from your Common.XLA in the other add-ins, copy them in to the other XLAs and declare them as private in those modules where they're needed.
    Charlotte

  3. #3
    Leslie Earle
    Guest

    Re: Multiple Excel Addins

    Thanks for the reply.

    I'm not sure I understand the difference between what I did and what you suggested. I think the gist of it is to put all the code in one workbook, and call it as needed.

    Why a workbook vs an addin? I have a situation where I have multiple client accounts on a unix box. All of these clients either want to have a report downloaded to Excel and formatted manually by a live user, or to have a bunch or reports formatted and e-mailed to various recipients.

    Since the processing of the spreadsheets vary so much, the idea was to keep the code separate (in addins) except for common routines. That way several people could work on( write) different addins, but the PC's executing those routines would not have to have an addin loaded explicitly except COMMON.XLA . None of my routines have exceeded 100K yet, but that's beside what I'm trying to accomplish.

    Another idea that came up, was to move the common routines/functions into a VB project and create a DLL which could be referenced.

    The bottom line was to create a 'system' of programming which would dynamically load and unload itself depending on the users' immediate requirement, including putting up menus or automatically formatting reports, and would not need the PC administrators worry about which addin to load on which PC.

    Would it make more sense to wrap this whole system into a VB program? I really don't want to take the users too far away from regular Excel practices.

  4. #4
    Leslie Earle
    Guest

    Re: Multiple Excel Addins

    I've been following the unique name rule for the subroutines, but does this apply to the module names as well? I guess I expected the addins TO SEE EACH OTHER, because of their connection to Excel.

    One point that came up in my browsing, was that the modules or addins needed to be part a the same PROJECT to be aware of routines in other modules. Could that explain why a routines in one addins cannot refer to a routine in another addin, without expliciting declaring the addin's name?

    Thanks,
    Leslie

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Excel Addins

    Module names have to be unique within the same project. And yes, not having the modules in the same project is at least one reason they can't see each other.
    Charlotte

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

    Re: Multiple Excel Addins

    I have found that using addins to accomplish what you are describing is an extremely poor way to get it done. Addins are a pretty good way to implement functions to be used from spreadsheets, but I don't think they are good for functions an subs to be used from VBA code in other files. You have described just one of many problems I had trying to use them this way.

    I have found what I think is a better way of doing this. In my case, what I was doing was implementing a system that would generate workbooks from a template. The generated workbooks would use a lot of VBA code to implement the system. If I put that code in the template, then it would be duplicated in every generated workbook, and if any changes ever needed to be made to the code, I would be faced with having to update the code in thousands of workbooks that had been created from the template. What I ended up doing was to put the code in a separate workbook. The the workbooks that need to use this code use the Workbook Open or Workbook New events to open the workbook with the code and make it xlVeryHidden sl that the user never sees it. The Workboob Close event closes this workbook. Then anytime I need to use the code I juse use something like this:

    <pre> Cancel = Run(sPgmBook & "!AI_Workbook_BeforePrint", Cancel)
    </pre>


    Where sPgmBook is a global string variable containing the path to the code workbook.

    This works great, and I can now change the code without having to update all of the generated workbooks. The only code left in my template is the code to find and open the code workbook (from path pointers in the registry) and Run statements like the above.
    Legare Coleman

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

    Re: Multiple Excel Addins

    I do not know for sure how addins work internally. However, from the project I was working on, I am guessing that code in one workbook that calls code in an addin gets bound to the addin when the workbook code is compiled. What I found was that if I had to change the code in the addin, then the code in the workbook using it would fail until that workbook was opened, the addin was removed, the addin was reinstalled, and the code was recompiled. Since I was building a template to that would generate literally hundreds of thousands of files that used the addin, I was looking at the possibility of doing the above process manually on hundreds of thousands of workbooks that used the addin every time something in the addin had to be changed. The method I showed using a workbook and the Run command does not have this problem. I can now change the code that would have been in the addin and distribute the workbook withe the code change, and all of those workbooks out there use the new code with no changes.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Excel Addins

    Have you thought setting a reference to your "add-in" project instead of installing it as an add-in. That should allow you to use all the public methods, although it won't give you access to forms. I haven't tried it, but it should be possible.
    Charlotte

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

    Re: Multiple Excel Addins

    I had problems both ways.
    Legare Coleman

Posting Permissions

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