understanding add-ins or references

[steve]

Someone gave me the suggestion that I use an add-in or a
personal.xls to, I am guessing, reference the modules and
forms that i have created in one workbook so that I can
use them in another workbook. I have been reading the
help files in vb for excel and am having a lot of trouble
understanding the logic behind add-ins. Can someone give
me a crash course in add-ins or a way that I can reference
a module I have created in one workbook to work with
another. Or even better to have my module simply
reference a text file with all the code.

*The idea is to write and edit the code once for several
workbooks.*

[Dave Peterson]

First, addins are pretty much the same as a regular workbook. But it's a
special workbook in the sense that it's been saved as an addin ("save as type"
in the saveAs dialog).

One of the nice things about addins is you can refer to the UDF's (user defined
functions) in your formulas just like it was a built in function.

You like =sum(a1:a10), then you can do this with an addin that's loaded:
=mysum(a1:a10)

You don't need to prefix your function with the workbook name:
=mywkbk.xls!mysum(a1:a10)

The second thing about addins is that the macros don't appear in the
Tools|Addins dialog. That's might be a bad thing, too. You have to have some
way for the user to run the macros.

You could add options to an existing toolbar. John Walkenbach has a neat
workbook that can be saved into an addin at:
http://j-walk.com/ss/excel/tips/tip53.htm. He also has a nice and simple help
system: http://j-walk.com/ss/excel/tips/tip51.htm.

Or you can create a temporary toolbar that gets created when you open the addin
and goes away when the addin is closed.

Here's one I keep as a template:

In a general module:

Option Explicit
Sub create_menubar()

Dim i As Long

Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant

Call remove_menubar

mac_names = Array("mac1", _
"mac2", _
"mac3")

cap_names = Array("caption 1", _
"caption 2", _
"caption 3")

tip_text = Array("tip 1", _
"tip 2", _
"tip 3")

With Application.CommandBars.Add
.Name = "Test99"
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = tip_text(i)
End With
Next i
End With
End Sub

Sub remove_menubar()
On Error Resume Next
Application.CommandBars("Test99").Delete
On Error GoTo 0
End Sub

Under Thisworkbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call remove_menubar
End Sub

Private Sub Workbook_Open()
Call create_menubar
End Sub

====

From my personal.xla file, I use a variation of John's MenuMaker. For an addin
that has only a few macros (less than 10), I'll use the additional toolbar.

====

And the last thing (that I can remember) that is different, addins are hidden
from the user. (Workbooks can be hidden, too (Window|Hide)).

====
If I want my macros always available, I'll put my addin in my XLStart folder.
(personal.xla is there.) But if it's something that I only need for a specific
purpose, I'll just open it like any other workbook (File|Open).

--

Dave Peterson
ec3...@msn.com