Recursion with user defined functions
 

My problem, is that Excel (I am using excel 2003) seems
to reenter the user function, but never completes the
first call and gets confused. (I get very confuded).

I would appreciate any assistence.


I want the user to select from a list box, and for the
response to feed a few cells.

eg. List Box =
"7 table Mitchell",
"8 table Mitchell",
"6 table Howell"
etc.

I have formulas on the two target cells as follows :-
Cell NoTables = UserFunction("NoTables") ' 7, 8, 6
Cell NoBoards = UserFunction("NoBoards") ' 24, 32

I have the following user function :-

Public Function UserFunction (cString As String) As
Integer

Dim iNoBoards As Integer
Dim iNoTables As Integer

Dim cMovementName As String
cMovementName = Range("MovementName").Cells(1,
1).Value

Dim rFound As Range
Set rFound = Range("MovementNames").Find
(cMovementName, LookIn:=xlValues)
If Not rFound Is Nothing Then
' We found the value.
iNoBoards = rFound.Cells(0, 1).Value
iNoTables = rFound.Cells(0, 2).Value
Else
' Value not found.
iNoBoards = 24
iNoTables = 8
End If
Set rFound = Nothing

If (cString = "No Boards") Then UserFunction =
iNoBoards
If (cString = "No Tables") Then UserFunction =
iNoTables

End Function

Recursion with user defined functions
 

There doesn't seem to be anything recursive about your function.

Perhaps the combination of functions you have in your worksheet are causing
the problem.

--
Regards,
Tom Ogilvy



"bigJim" wrote in message
...
My problem, is that Excel (I am using excel 2003) seems
to reenter the user function, but never completes the
first call and gets confused. (I get very confuded).

I would appreciate any assistence.


I want the user to select from a list box, and for the
response to feed a few cells.

eg. List Box =
"7 table Mitchell",
"8 table Mitchell",
"6 table Howell"
etc.

I have formulas on the two target cells as follows :-
Cell NoTables = UserFunction("NoTables") ' 7, 8, 6
Cell NoBoards = UserFunction("NoBoards") ' 24, 32

I have the following user function :-

Public Function UserFunction (cString As String) As
Integer

Dim iNoBoards As Integer
Dim iNoTables As Integer

Dim cMovementName As String
cMovementName = Range("MovementName").Cells(1,
1).Value

Dim rFound As Range
Set rFound = Range("MovementNames").Find
(cMovementName, LookIn:=xlValues)
If Not rFound Is Nothing Then
' We found the value.
iNoBoards = rFound.Cells(0, 1).Value
iNoTables = rFound.Cells(0, 2).Value
Else
' Value not found.
iNoBoards = 24
iNoTables = 8
End If
Set rFound = Nothing

If (cString = "No Boards") Then UserFunction =
iNoBoards
If (cString = "No Tables") Then UserFunction =
iNoTables

End Function