Excel Add-Ins

Topic

New Reply

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,

Reply | Quote

Replies

What happens is that the workbook is saved as an add-in . 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.

Reply | Quote

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

Reply | Quote

I think what you have to do is create a reference from the calling workbook to the add-in (Tools, References in the VBE).

Reply | Quote

Thanks for the “scoop” on that Jan!

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

Such functions are called User Defined Functions.

They even become available in the Function wizard (in the category “User Defined” (surprise, surprise )

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.

Reply | Quote