Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA References (XL XP)

    Hi collegues,
    I have created a nice module in XL to claculate and set the pagelenght of a mobile bon priinter.
    As we have a lot of Excel templates using this routines we were looking for a way to seperate teh code from the templates.
    Our solution at the moment was to create a XL Workbook containing the code and store it as an XLA in the %appdata%microsoftaddins path.
    No quite sure at the moment whether to load with Add-in Manger or load as Workbook dynamically.
    The problem now is that we need a VBA Reference to that workbook to acces the code there.
    As we want to distribute it to a number of laptops, this reference would have to be dynamic in the way that it adresses the %appdata%microsoftaddins path dynamically for the individual user.
    Is it better to create a DLL in VB (but never did that before)?
    Any ideas are highly appreciated.

    Horst

  2. #2
    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: VBA References (XL XP)

    If you create an addin, then the users just have to install the addin (tools - addin <browse> find the file and then select it). It should be loaded whenver they load excel. I don't think it requires a reference.

    Steve

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA References (XL XP)

    I have created an addIn and its visible, but unfortunately VBA needs a reference as well. As those are stored with the absolute path there is from my view no possiblity to make it variable, like a reference to a DLL, which can be registered.

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

    Re: VBA References (XL XP)

    I faced the same problem several years ago and also tried putting the code into an addin. I had the same problem with references. In addition, whenever the code in the addin was changed, the references in all workbooks that were created from the template broke and each workbook had to be manually rereferenced to the addin.

    The way that I finally solved the problem was to put all of the code into another workbook that I marked as hidden. My installation routine then installed this workbook into a directory in the user's "C:Program Files" directory and stored the path to in into the Windows Registry. I then put code into the template Open Event routine to get the path to the workbook from the registry and open it. The code was then executed by using the Run command in VBA routines in the template.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA References (XL XP)

    Tanks for the advice.
    I have now written a DLL which does the Job.
    Horst

Posting Permissions

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