I have many macros in a module in my PERSONAL.XLS that I can use in any
sheet.

I have created a function thus:

Public Function getnum(ip As String) As Integer
getnum = Val(ip)
End Function

and placed it in the same module, but if I try to use it in a sheet I get a
#NAME? error.

If I create a module in the specific workbook and put the function in it
then it works fine.

So how do I make a function in my PERSONAL.XLS file available to all sheets?

Paul,

To call functions in another workbook, you have to use the
workbook name in the function call. E.g.,

=Personal.xls!GetNum(A1)

If you put functions in an add-in (an XLA file), you don't need
to use the workbook name in the call.

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



"Paul Lautman" wrote in message
...
I have many macros in a module in my PERSONAL.XLS that I can
use in any
sheet.

I have created a function thus:

Public Function getnum(ip As String) As Integer
getnum = Val(ip)
End Function

and placed it in the same module, but if I try to use it in a
sheet I get a
#NAME? error.

If I create a module in the specific workbook and put the
function in it
then it works fine.

So how do I make a function in my PERSONAL.XLS file available
to all sheets?

You have to qualify with the filename, like so

=PERSONAL.XLS!foo()

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul Lautman" wrote in message
...
I have many macros in a module in my PERSONAL.XLS that I can use in any
sheet.

I have created a function thus:

Public Function getnum(ip As String) As Integer
getnum = Val(ip)
End Function

and placed it in the same module, but if I try to use it in a sheet I get
a
#NAME? error.

If I create a module in the specific workbook and put the function in it
then it works fine.

So how do I make a function in my PERSONAL.XLS file available to all
sheets?

Thanks Chip (and Bob).
I knew I could do it in an XLA, but I couldn't figure why it didn't work
from my PERSONAL.XLS file.

Thanks again

"Chip Pearson" wrote in message
...
Paul,

To call functions in another workbook, you have to use the
workbook name in the function call. E.g.,

=Personal.xls!GetNum(A1)

If you put functions in an add-in (an XLA file), you don't need
to use the workbook name in the call.

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



"Paul Lautman" wrote in message
...
I have many macros in a module in my PERSONAL.XLS that I can
use in any
sheet.

I have created a function thus:

Public Function getnum(ip As String) As Integer
getnum = Val(ip)
End Function

and placed it in the same module, but if I try to use it in a
sheet I get a
#NAME? error.

If I create a module in the specific workbook and put the
function in it
then it works fine.

So how do I make a function in my PERSONAL.XLS file available
to all sheets?