When I call this public function from a worksheet cell (e.g., =Nspaces(7)),
the result is #NAME?

Pulic Function Nspaces(NumSp As Integer)
Dim Nspaces1 As String
Nspaces1 = ""
While Len(Nspaces) < NumSp
Nspaces1 = Nspaces1 & " "
Wend
Nspaces = Nspaces1
End Function

Why?

I created this function in a separate module in Personal.xls.

Thank you.

John

You have Pulic Function!

try...

Public Function

--

Regards,
Nigel
(E-Mail Removed)



"JWirt" <(E-Mail Removed)> wrote in message
news:5FFE5EC1-746D-470E-83C4-(E-Mail Removed)...
> When I call this public function from a worksheet cell (e.g.,
> =Nspaces(7)),
> the result is #NAME?
>
> Pulic Function Nspaces(NumSp As Integer)
> Dim Nspaces1 As String
> Nspaces1 = ""
> While Len(Nspaces) < NumSp
> Nspaces1 = Nspaces1 & " "
> Wend
> Nspaces = Nspaces1
> End Function
>
> Why?
>
> I created this function in a separate module in Personal.xls.
>
> Thank you.
>
> John
>
>
>

UDF's need to be either in the workbook from which they are called or in an
addin. So your function won't work if in your Personal.xls (except of course
if called from personal.xls).

In passing, I'd change the "As Integer" to "As Long".

Regards,
Peter T

"JWirt" <(E-Mail Removed)> wrote in message
news:5FFE5EC1-746D-470E-83C4-(E-Mail Removed)...
> When I call this public function from a worksheet cell (e.g.,
> =Nspaces(7)),
> the result is #NAME?
>
> Pulic Function Nspaces(NumSp As Integer)
> Dim Nspaces1 As String
> Nspaces1 = ""
> While Len(Nspaces) < NumSp
> Nspaces1 = Nspaces1 & " "
> Wend
> Nspaces = Nspaces1
> End Function
>
> Why?
>
> I created this function in a separate module in Personal.xls.
>
> Thank you.
>
> John
>
>
>

Hi,

You have to put your function in a module. BTW you can simplify your
function as follow:

Function Nspaces(NumSp As Integer)
Nspaces = space(Numsp)
End Function

Cheers,






"JWirt" wrote:

> When I call this public function from a worksheet cell (e.g., =Nspaces(7)),
> the result is #NAME?
>
> Pulic Function Nspaces(NumSp As Integer)
> Dim Nspaces1 As String
> Nspaces1 = ""
> While Len(Nspaces) < NumSp
> Nspaces1 = Nspaces1 & " "
> Wend
> Nspaces = Nspaces1
> End Function
>
> Why?
>
> I created this function in a separate module in Personal.xls.
>
> Thank you.
>
> John
>
>
>

try:
=personal.xls!nspaces(7)
(and fix the public typo)

or use:
=rept(" ",7)



JWirt wrote:
>
> When I call this public function from a worksheet cell (e.g., =Nspaces(7)),
> the result is #NAME?
>
> Pulic Function Nspaces(NumSp As Integer)
> Dim Nspaces1 As String
> Nspaces1 = ""
> While Len(Nspaces) < NumSp
> Nspaces1 = Nspaces1 & " "
> Wend
> Nspaces = Nspaces1
> End Function
>
> Why?
>
> I created this function in a separate module in Personal.xls.
>
> Thank you.
>
> John

--

Dave Peterson

"Peter T" <peter_t@discussions> wrote in message
> UDF's need to be either in the workbook from which they are called or in
> an addin. So your function won't work if in your Personal.xls

-er, unless of course you fully qualify the function in cell formulas along
the lines as suggested by Dave

Regards,
Peter T