Recursive Functions in Excel?

  • Thread starter Thread starter F. Michael Miller
  • Start date Start date
F

F. Michael Miller

Can I do recursive functions in Excel?

I want a function to count the words in a cell [and I know I can do it with
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)], so I
tried the following:

Function CountWords(sSource As String) As Double

Dim sPhrase As String
Dim dWords As Double

dWords = 0
sPhrase = Trim(sSource)

If Len(sPhrase) = 0 Then
dWords = 0
Else
dWords = dWords + CountWords(Right(sPhrase, Len(sPhrase) - Find(" ",
sPhrase, 1)))
End If

CountWords = dWords

End Function

I get the following error when I try to compile:
Compile Error: Ambiguous name detected: CountWords.


Thanks!
 
Michael,

VBA certainly supports recursive functions. Are you sure you
don't have another procedure or a global variable name
CountWords?


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

Your recursion doesn't have a way out, and your code as written caused a
stack overflow. But you are being needlessly complex (unless you are
actually doing something different). Use this instead:

Function CountWords(sSource As String) As Double
CountWords = UBound(Split(Application.Trim(sSource), " ")) + 1
End Function

HTH,
Bernie
MS Excel MVP
 
F. Michael,

Here's a working recursive version of your code:

Function CountWords2(sSource As String) As Double
Dim sPhrase As String
Dim dWords As Double

dWords = 0
sPhrase = Application.Trim(sSource)

If InStr(1, sPhrase, " ") = 0 Then
dWords = 1
Else
dWords = 1 + CountWords2(Right(sPhrase, _
Len(sPhrase) - InStr(1, sPhrase, " ")))
End If
CountWords2 = dWords
End Function

HTH,
Bernie
MS Excel MVP
 
Thanks!

Bernie Deitrick said:
F. Michael,

Here's a working recursive version of your code:

Function CountWords2(sSource As String) As Double
Dim sPhrase As String
Dim dWords As Double

dWords = 0
sPhrase = Application.Trim(sSource)

If InStr(1, sPhrase, " ") = 0 Then
dWords = 1
Else
dWords = 1 + CountWords2(Right(sPhrase, _
Len(sPhrase) - InStr(1, sPhrase, " ")))
End If
CountWords2 = dWords
End Function

HTH,
Bernie
MS Excel MVP

F. Michael Miller said:
Can I do recursive functions in Excel?

I want a function to count the words in a cell [and I know I can do it with
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)], so I
tried the following:

Function CountWords(sSource As String) As Double

Dim sPhrase As String
Dim dWords As Double

dWords = 0
sPhrase = Trim(sSource)

If Len(sPhrase) = 0 Then
dWords = 0
Else
dWords = dWords + CountWords(Right(sPhrase, Len(sPhrase) - Find(" ",
sPhrase, 1)))
End If

CountWords = dWords

End Function

I get the following error when I try to compile:
Compile Error: Ambiguous name detected: CountWords.


Thanks!
Click to expand...
Click to expand...
 
...
...
Function CountWords(sSource As String) As Double
CountWords = UBound(Split(Application.Trim(sSource), " ")) + 1
End Function
Click to expand...
...

Picky:
Version dependent. Won't work in XL8/97 since that version doesn't have Split.

Still, it's a mystery why the OP isn't just using the formula shown in the
original posting. Even with as many function calls ash shown, it'll fun circles
around a udf in terms of recalc performance.
 
Version dependent. Won't work in XL8/97 since that version doesn't have
Split.

I know, but I just liked it - really, I should have used the Len() -
Len(substitute()) version, but..
Still, it's a mystery why the OP isn't just using the formula shown in the
original posting. Even with as many function calls ash shown, it'll fun circles
around a udf in terms of recalc performance.
Click to expand...

I think the OP was really just trying to learn how to do a recursive
function....

Bernie
 
Back
Top