When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

Mike: I'm not quite sure what you are trying to do. "TheList" isn't a
function; that's why you are getting the error. If, when you type "TheList"
into a cell, you would like "hello" to appear in the cell to right, you would
use a vlookup based on a list that you provide.
A B
1 TheList hello
2 mylist bye
3 yourlist tomorrow
4
5 TheList

In B5 you would type =vlookup(A5,A1:B3,2,FALSE)

"mikebres" wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

Hello mikebres,

Did you put your code inside a VBA module? Excel looks for UDF's in VB
Modules. This makes the code globally available.

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=49227

Oops, okay I used the wrong name in my example. I had recreated it for this
post :-/ The simplfied function should be
Function TheList()
TheTest = "Hello"
End Function

My actual function trying to do something a little more complicated.
However, what did you mean it's not a function? What would I need to do to
make that simplified test function work? Here is the actual function I'm
trying to create.

Function MakeList(rng As Range, Optional delimiter As String)
Dim TheList As String
Dim Temp As Variant, cElements As Long, j As Long
Dim Cell As Range

If IsMissing(delimiter) Then delimiter = ","

cElements = 1
For Each Cell In Range(rng)

If Not IsNull(Cell.Value) Then

If cElements = 1 Then
TheList = Cell.Value
Else
TheList = delimiter & Cell.Value
End If

cElements = cElements + 1

End If

Next

MakeList = TheList

End Function



"JR" wrote:

Mike: I'm not quite sure what you are trying to do. "TheList" isn't a
function; that's why you are getting the error. If, when you type "TheList"
into a cell, you would like "hello" to appear in the cell to right, you would
use a vlookup based on a list that you provide.
A B
1 TheList hello
2 mylist bye
3 yourlist tomorrow
4
5 TheList

In B5 you would type =vlookup(A5,A1:B3,2,FALSE)

"mikebres" wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

Mike,
=TheTest rather than =TheList?

"mikebres" wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

Mike,

Try this:

Function MakeList(rng As Range, Optional delimiter As String) As String
Dim TheList As String
Dim Temp As Variant, cElements As Long, j As Long
Dim Cell As Range

If IsMissing(delimiter) Then delimiter = ","
TheList = ""
cElements = 1
For Each Cell In rng

If Not IsNull(Cell.Value) Then

If cElements = 1 Then
TheList = Cell.Value
Else
TheList = TheList & delimiter & Cell.Value
End If

cElements = cElements + 1

End If

Next

MakeList = TheList

End Function


"mikebres" wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

Mike,

Another update ....

Function MakeList(rng As Range, Optional delimiter As Variant) As String

Delimiter (optional parameter) must be declared as Variant

"Toppers" wrote:



Mike,

Try this:

Function MakeList(rng As Range, Optional delimiter As String) As String
Dim TheList As String
Dim Temp As Variant, cElements As Long, j As Long
Dim Cell As Range

If IsMissing(delimiter) Then delimiter = ","
TheList = ""
cElements = 1
For Each Cell In rng

If Not IsNull(Cell.Value) Then

If cElements = 1 Then
TheList = Cell.Value
Else
TheList = TheList & delimiter & Cell.Value
End If

cElements = cElements + 1

End If

Next

MakeList = TheList

End Function


"mikebres" wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

Good point I should have done that anyway. However, it still returns the
#NAME? error.

"Toppers" wrote:



Mike,

Try this:

Function MakeList(rng As Range, Optional delimiter As String) As String
Dim TheList As String
Dim Temp As Variant, cElements As Long, j As Long
Dim Cell As Range

If IsMissing(delimiter) Then delimiter = ","
TheList = ""
cElements = 1
For Each Cell In rng

If Not IsNull(Cell.Value) Then

If cElements = 1 Then
TheList = Cell.Value
Else
TheList = TheList & delimiter & Cell.Value
End If

cElements = cElements + 1

End If

Next

MakeList = TheList

End Function


"mikebres" wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

Mike,
Works OK for me;

e.g in cell B1 I put =MakeList(A1:A4) which combined cells A1 to A4



"mikebres" wrote:

Good point I should have done that anyway. However, it still returns the
#NAME? error.

"Toppers" wrote:



Mike,

Try this:

Function MakeList(rng As Range, Optional delimiter As String) As String
Dim TheList As String
Dim Temp As Variant, cElements As Long, j As Long
Dim Cell As Range

If IsMissing(delimiter) Then delimiter = ","
TheList = ""
cElements = 1
For Each Cell In rng

If Not IsNull(Cell.Value) Then

If cElements = 1 Then
TheList = Cell.Value
Else
TheList = TheList & delimiter & Cell.Value
End If

cElements = cElements + 1

End If

Next

MakeList = TheList

End Function


"mikebres" wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

On Fri, 9 Dec 2005 11:10:03 -0800, "mikebres"
wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

1. You may not be putting the UDF in the right module.

Right click on the applicable VBA Project in the Project Explorer
Window.
Then select Insert/Module from the right click menu, and enter your
code into that module.

2. If you have Name'd the module and the Name is the same as the function, or
if there is a name in an open project that is the same as your function, you
may need to full qualify your function name.


--ron

Okay, in that case, I put the function in a module in Personal.XLS. Does
that make a difference?

"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 11:10:03 -0800, "mikebres"
wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

1. You may not be putting the UDF in the right module.

Right click on the applicable VBA Project in the Project Explorer
Window.
Then select Insert/Module from the right click menu, and enter your
code into that module.

2. If you have Name'd the module and the Name is the same as the function, or
if there is a name in an open project that is the same as your function, you
may need to full qualify your function name.


--ron

How do I full qualify the function name?

"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 11:10:03 -0800, "mikebres"
wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

1. You may not be putting the UDF in the right module.

Right click on the applicable VBA Project in the Project Explorer
Window.
Then select Insert/Module from the right click menu, and enter your
code into that module.

2. If you have Name'd the module and the Name is the same as the function, or
if there is a name in an open project that is the same as your function, you
may need to full qualify your function name.


--ron

=personal.xls!thetest()



mikebres wrote:

Okay, in that case, I put the function in a module in Personal.XLS. Does
that make a difference?

"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 11:10:03 -0800, "mikebres"
wrote:

When I create my own function and place it in a cell I get a #NAME? error.
To test this further I created the simple function below. I entered
=TheList() in a cell. I still got the error. Any ideas why, or better yet
how do I fix it? Thanks - Mike

Function TheTest()
TheTest = "Hello"
End Function

1. You may not be putting the UDF in the right module.

Right click on the applicable VBA Project in the Project Explorer
Window.
Then select Insert/Module from the right click menu, and enter your
code into that module.

2. If you have Name'd the module and the Name is the same as the function, or
if there is a name in an open project that is the same as your function, you
may need to full qualify your function name.


--ron


--

Dave Peterson

On Fri, 9 Dec 2005 12:40:02 -0800, "mikebres"
wrote:

How do I full qualify the function name?

Something like:

=Personal!UrFunction()


--ron

Wonderful! It works! Thank you all.

"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 12:40:02 -0800, "mikebres"
wrote:

How do I full qualify the function name?

Something like:

=Personal!UrFunction()


--ron

Mike

One reason to store UDF's in an Add-in which you load through ToolsAdd-ins.

You don't have to qualify the Function name

=urfunction() will suffice.


Gord Dibben Excel MVP

On Fri, 9 Dec 2005 14:10:02 -0800, "mikebres"
wrote:

Wonderful! It works! Thank you all.

"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 12:40:02 -0800, "mikebres"
wrote:

How do I full qualify the function name?

Something like:

=Personal!UrFunction()


--ron