Having a macro/function available in new spreadsheets

Hi everyone,

I wanted to define a function that would be available in all new
spreadsheets that I create from now on, so I defined a macro in the Personal
workbook (Personal.xlsb) and then manually pasted the code of function I
needed into the macro, such that the code of the macro looked something like
this:

Function functionName
...
End Function

After saving, hiding Personal.xlsb (from View|Hide) and restarting Excel, I
noticed that new spreadsheets did not know of this new function that I
created; the function was only available in Personal.xlsb itself, upon
unhiding it from the View menu.

I have to say that I am a rookie when it comes to macros&VBA, so I might be
making some pretty blatant confusions here between macros and functions. Can
anyone suggest how to make that function be universally available in all
spreadsheets (at least in those created from now on, if not also in
pre-exsiting ones)? Many thanks for any replies!

Hi,

Cal your function like this

=PERSONAL.XLSB!Function_Name()

Or have a look here

http://www.rondebruin.nl/personal.htm
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"wildetudor" wrote:

Hi everyone,

I wanted to define a function that would be available in all new
spreadsheets that I create from now on, so I defined a macro in the Personal
workbook (Personal.xlsb) and then manually pasted the code of function I
needed into the macro, such that the code of the macro looked something like
this:

Function functionName
...
End Function

After saving, hiding Personal.xlsb (from View|Hide) and restarting Excel, I
noticed that new spreadsheets did not know of this new function that I
created; the function was only available in Personal.xlsb itself, upon
unhiding it from the View menu.

I have to say that I am a rookie when it comes to macros&VBA, so I might be
making some pretty blatant confusions here between macros and functions. Can
anyone suggest how to make that function be universally available in all
spreadsheets (at least in those created from now on, if not also in
pre-exsiting ones)? Many thanks for any replies!