accessing user defined functions in all workbooks

[flipflop]

went into personal.xls vb editor, inserted new module, typed:

public function f(x) as double
f=x+1
end function

f(x) works fine in personal.xls - not in any other workbook. saved
personal.xls, closed excel, re-opened, etc. etc. no joy. how hard is
it meant to be?

 

[Mike Fogleman]

Save Personal.xls in the Xlstart folder. Excel will open any file that is
placed there automatically. The macros within are then available to any
workbook you have open. Search for the Xlstart folder with Windows Explorer.
Where it is depends upon which OS you have.
Mike

 

[Tom Ogilvy]

Put the function in a separate workbook and make it an addin. Then load the
addin through tools=>Addins.

Otherwise, make sure personal.xls is open and use

=Personal.xls!f(21)

 

[flipflop]

Save Personal.xls in the Xlstart folder. Excel will open any file that is
placed there automatically. The macros within are then available to any
workbook you have open.

Mike, thanks for your help.

Personal.xls is already in the xlstart directory. Everything in
personal.xls defined as a "Sub" works ok - it is available in any
workbook. Anything defined in personal.xls as "Function" is not
available.

I also defined the function in a new workbook "a.xls" and saved that
in xlstart. It loaded a.xls when starting Excel but the function was
only available in a.xls, not any other workbook.

Perhaps I am defining the function wrongly. To create the function I
do exactly this:

start Excel
unhide personal.xls
alt f11 to get into vb editor
within "modules" folder under "VBAProject (PERSONAL.XLS), insert new
module
into that module type:
public function f(x) as double
f=x+1
end function
quit excel and save personal.xls when prompted

 

[flipflop]

Put the function in a separate workbook and make it an addin. Then load the
addin through tools=>Addins.

Tom, thanks very much. That worked perfectly.