How to add reference for Personal.xls?

[T Kirtley]

I have an assortment of general purpose functions in my Personal.xls vba
module, and I want to use them in worksheets without having to use a file
reference to the Personal.xls sheet.

For instance, I want a cell containing: "=CountVisible(RangeName)" to
execute the CountVisible() function in my Personal.xls vba module. As it is
now, I have to precede the function with a file reference such as:
"=Personal.xls!CountVisible(RangeName).

Is it possible to do this without creating an .xla for the functions? If I
do use an add-in won't that complicate making changes to the code?

I understand that another option is to create a reference to the vba module
that contains the functions, but where would the reference go? I don't think
I can add a reference in the Personal.xls module to itself, and my default
workbooks do not have modules. I feel like I am missing something.

Thanks in advance to anyone who can put me off in the right direction.

TK

 

[Chip Pearson]

No, you need to make the file an add-in if you don't want to use
the workbook name in the formula calls. Creating an add-in
doesn't require changes to the code. Simply do a Save As to save
as an Add-In. Adding a reference won't change anything.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[T Kirtley]

Well, if it is as simple as that an add-in is certainly is the way to go. I
was presuming that an add-in would involve some additional maintenance of
some sort.

Also, if I move my functions (and procedures) to an add-in is there any
reason to keep a Personal macro workbook anymore? It seems that would be
redundant. I'll just use the add-in for any general function or procedure
code I want to have at hand, and there's no need to keep track of multiple
modules.

Cheers! and thanks for the help.

TK

 

[Bob H]

My personal preference is to keep custom formulas in add-in files for the
reason you mentioned but keep procedures(macros) in the personal.xls file
becuase macros in an add-in file are not visible in the macro list from the
Macros Dialog Box or when attaching them to Custom Buttons for Menus
(although you CAN type the macro name even though it isn't visible to select
from the list).
After creating th eadd-in you need to attach it by selecting Tools/Addins
and then browising to your xla file.
Also, when using an add-in (.xla) file be sure to SAVE the file while in the
VBE whenever making changes. Excel won't promt you to save if make changes to
xla files the way it does with normal xls files.

 

[T Kirtley]

Good point about the macros not being visible, but that might actually be a
good thing in my case since I map most of the macros I use to key
combinations, and I wouldn't want some of the macros to be run by mistake.
Also, an excellent point about being sure to save changes in the vbe!

One other curiosity though, I noticed that an addin can actually be used
without attaching it if the xla file is saved to the XLSTART folder. This
makes the add-in code available without having the add-in visible in the
Add-Ins dialog box. I'm guessing that might come in handy in some situations,
but I'm not sure 'bout that.

Thanks,

TK

 

[Dave Peterson]

I save my personal.xl* as personal.xla.

I save it in my XLStart folder.

I use a variation of John Walkenbach's MenuMaker routine to expose the
subroutines to the user, er, me!

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm

If you like toolbars (I don't if there are lots of macros), here's how I do it:
http://groups.google.co.uk/[email protected]