Simple recursive function in VBA with Excel not returning expected result

Question

I have a simple recursive function to write in VBA that does the following : It must count the number of times we must take the log of a parameter 'x' to find log(x) < 1

Examples :

logcount(5) : log(5) = 0,6... so the function should return 1
logcount(89) : log(89) = 1,9... and log(log(89)) = 0,28... so the function should return 2
logcount(0,4) should return 1 etc...

So I wrote it and it doesn't work as expected ! It always adds +1 to the result ! It looks like the last 'Else' block is always interpreted. Any help will be really appreciated

Function logcount(x As Double) As Integer
  If x <= 0 Then
    MsgBox "You must enter a positive value"
    Exit Function
  ElseIf Log(x) < 1 Then
    logcount = 1
  Else
    logcount = 1 + logcount(Log(x))
  End If
End Function

what is logcount(0,4) Your function only takes one parameter. Also as written logcount(1) and logcount(2) both return 1 — Conrad Frix
– Conrad Frix, Commented Jan 30, 2012 at 22:22
@ConradFrix 0,4 is 0.4 written in a non-Englis-US locale (which is used through the question), where the decimal point is ,. — GSerg
– GSerg, Commented Jan 30, 2012 at 22:26
Sorry, in france we put a comma as a decimal separator, you should read '0.4' — Yass T
– Yass T, Commented Jan 30, 2012 at 22:27
@YassT no my apologies. I actually know better and I still got caught by my Anglo/US centric view of the decimal mark — Conrad Frix
– Conrad Frix, Commented Jan 30, 2012 at 22:31

Jean-François Corbett · Accepted Answer · 2014-09-26 07:17:37Z

5

Log in VBA is the natural logarithm.

Apparently you meant a base-10 logarithm:

Log10 = Log(X) / Log(10#)

edited Sep 26, 2014 at 7:17

Jean-François Corbett

38.7k30 gold badges146 silver badges192 bronze badges

answered Jan 30, 2012 at 22:18

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yass T Over a year ago

OMG ! In fact I was checking my results with using the log in the windows calc ! So with this calc, we have the base-10 log, right ? My program was correct if I want the natural log ?

MisterCary · Accepted Answer · 2025-03-09 20:10:31Z

With Microsoft 365 in March 2025, Excel allows recursion using its LAMBDA function. You can create a custom User Defined Function (UDF) by saving a recursive LAMBDA function to the name manager in the Formula tab.

Save this to the name manager as LOGCOUNT:

=LAMBDA(n,[c], IF(n < 0, "You must enter a positive value!", IF(n < 1, c, LOGCOUNT(LOG(n), c + 1))))

Once saved, you can call the function just like any other function:

Direct Input
=LOGCOUNT(89)

Cell Reference 
=LOGCOUNT(A4)

You can also use the LET function to test a UDF LAMBDA without saving it in the name manager. But you have to add an extra parameter to the UDF, then use the extra parameter to replace the function name. This is because of a limitation in LAMBDA calculus:

=LET(LCOUNT, LAMBDA(ME,n,[c], IF(n<0, "You must enter a positive value!", IF(n<1, c, ME(ME, LOG(n), c+1)))), LCOUNT(LCOUNT, A4))

Collectives™ on Stack Overflow

Simple recursive function in VBA with Excel not returning expected result

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related