Results 1 to 9 of 9

Thread: Excel Add-Ins

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Add-Ins

    Hi all,

    I was just wondering, what exactly happens when you save a workbook as an Add-In?

    Do modules in the add-in become available to other workbooks?

    In copying a workbook from one machine to another, would you also need to move the Add-In should you decide to use it on that machine? (I would think so)

    In a complex workbook, might it be preferable to save a workbook containing relevant code changes as an Add-In as opposed to significantly altering the original workbook?

    Is it possible to automatically declare an Add-In upon workbook activation from within VBA?

    I have saved a blank workbook as an Add-In, and I can see the "Add-In" project from the VBE. I have experimented with opening and referencing other workbooks from one workbook, via seemingly complicated windows and workbook manipulation. Is, or could it be, that an "Add-In" actually loads a workbook when the Add-In is selected, creating an easier method to access the "Add-In" workbook?

    Seems to be a lot of uses for an Add-In, just don't quite think I understand all the possibilities...

    Thanks,

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

    Re: Excel Add-Ins

    What happens is that the workbook is saved as an add-in <g>. The workbook itself stays loaded in XL. Once you close the workbook and open the Add-in, you'll have access to all functions and subs in the add-in.

    An add-in has some advantages:

    - it is invisible in the normal XL environment
    (Since XL 97, one can view the code in it though.)
    - It can be installed using the Tools/Addins menu.

    Like any other workbook, modules inside an add-in are available to other workbooks. The way they are accessible will depend on the nature of the modules, subs and functions (e.g. are they declared private or not).

    An add-in is completely self contained, that means, if you want to have the functionality of the add-in on another machine, just copy the add-in itself and install it (tools/add-ins) on the other machine. You don't need the workbook that is the source of the add-in.

    Editing has to be done on the workbook itself. Once ready, one creates the add-in, *but should keep the workbook!!* Otherwise, the sheets of the add-in (which are still there and are accessible through VBA code) cannot be changed by the normal user interface of XL. Also saving changes becomes more diffcult.

    An add-in just loads itself (and possibly other workbooks, when there is code enclosed that does that), it can be thought of (but is different from!) a hidden workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel Add-Ins

    I have just created an add-in containing one public Sub and one public Function.

    When I load the add-in from another workbook, I can use the function on a worksheet, but I cannot reference the public sub - it is not in the list of available macros, and if I try to call it from a sub in the new file, it does not compile!!

    What have I missed?

    Jeremy

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

    Re: Excel Add-Ins

    I think what you have to do is create a reference from the calling workbook to the add-in (Tools, References in the VBE).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Add-Ins

    Thanks for the "scoop" on that Jan!

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Add-Ins

    Jan (or whomever),

    When referring to an add-in, how do you refer to a sheet within the add-in workbook? I can see the add-in worksheets in the VBE, but cannot view the sheet object. How would you refer to the add-in sheet object? Same way as any other worksheet in a normal workbook?

    Also, is there any add-in where I can view the code? I believe that all of the add-ins available on my machine are password protected. I would like to see a real world application of this while also being able to view the code behind it and see how the references are made between the two workbooks.

    Thanks,

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

    Re: Excel Add-Ins

    One refers to worksheets within an add-in just like any other workbook:
    Workbooks("NameOfAddIn").Worksheets("WorksheetName ")

    I attached a zip file with two workbooks: an add-in and a workbook that uses a function from that add-in and has a sub that shows the content of a cell in the add-in
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Add-Ins

    Simple example, but very enlightening! I was unaware that you could define a function (whether in an add-in or not) and refer to that function from within a cell.

    I noticed in the code of each sheet in the add-in you had "Option Explicit". Is this necessary with an add-in, or coding habit?

    Many thanks,

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

    Re: Excel Add-Ins

    Such functions are called User Defined Functions.

    They even become available in the Function wizard (in the category "User Defined" (surprise, surprise <bg>)

    Using Option Explicit forces you to declare (Dim) all variables. It is one of the best ways to catch typo's in your code, which otherwise would compile with no problem at all. Very hard to detect bugs like that when you haven't got Option Explicit Set. HIGHLY RECOMMENDED!!!. You can set the VBE to default to that option in Tools, Options.
    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
  •