If you're going to share this with others, don't put it into your personal.xls*
workbook. They may have their own version and you'll have to make them choose
what workbook to open (since excel can't have two files with the same name open
at the same time in the same instance).
Instead, you could create an addin -- either specific for this situation or
something that could contain all your UDFs and macros.
Name/save it as:
JensUtils.xlam (or .xla if you have to support earlier versions of excel)
Then you could create a UDF that uses data on a sheet in that addin. The addin
will be hidden from the user, so they won't see that sheet via the user interface.
But you could even hide that sheet within your addin to make it safer (or at
least make it feel safer) from prying eyes/fingers.
It's always better to pass the ranges that the UDF needs to use to the UDF via
the formula in the cell:
=vlookup(a1,[jensaddin.xla]tablesheetname!a:b,2,false)
(for instance)
Excel looks at those ranges in the function to determine when it should
reevaluate the formula. If you don't pass the ranges, then excel doesn't know
for sure when to recalc.
But I bet in your case, the table doesn't change very often. But if you find
that it does, you may want to either provide the range in the formula -- or give
the users a way to recalculate all their formulas before they trust anything!
=======
Saved from a previous post:
This makes updates a lot easier (well, somewhat easier). If a macro changes,
you fix your master copy of the addin and plop it back onto the common network
drive where the users find the addin.
(More later if you have to share individual files with each user (if they don't
have access to the common network share).)
But (there's always a but!)...
The users need a way to access those macros. The macros don't show up in the
tools|macro|macros dialog. So you need a nice way for them to run them.
For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm
Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)
In xl2007, those toolbars and menu modifications will show up under the addins.
And if you use xl2007:
If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm
And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003
and xl2007.
http://msmvps.com/blogs/xldynamic/archive/2010/03/27/deploy-me-simple.aspx
=========================
More about if you have to share those individual macro workbooks with users.
If your macro workbook (addin) contains any UDFs (userdefined functions) located
in cells in a worksheet, then if the users put that addin in any path that they
want, you're going to be hit with a lot of phone calls--especially when your
users share workbooks with each other.
All my functions that used any of the UDFs turn to NAME errors and I get asked
to point to a file to update links. What's going on???
Those UDFs will point to the drive/folder/filename that was used by the original
developer of the formula (not you as the developer of the addin.
Instead of saying put this addin on your local harddrive, tell them explicitly
to create a new folder on their C: drive. Call it ExcelAddins.
And file the addin in that folder as:
C:\exceladdins\JensUtils.xla
(or xlam)
Since everyone will be using the same location and same name, excel will not
have any trouble with links or NAME errors.
