CODEWITHSUNDEEP
excelintworksheet-functionlogarithmvba
Alexander Bell
Alexander Bell

Reputation: 7918

Inconsistency between Excel Worksheet and VBA INT(LOG()) Functions

In a standard Excel VBA code module I’ve created custom formula MyLog10() to get 10-base Logarithm:

Public Function MyLog10(ByVal dbl_Input As Double) As Double
    MyLog10 = Log(dbl_Input) / Log(10)
End Function

Tested for the value of 1000 and got the accurate result of 3. Surprisingly, when applied the Int() VBA Function to the result of MyLog10(), it came out as 2. The same was observed with Fix() VBA function (see the following test Sub):

Sub TestIntMyLog10()
    Debug.Print MyLog10(1000) 'Result 3 (accurate)
    Debug.Print Int(MyLog10(1000)) 'Result 2  (inaccurate)
    Debug.Print Fix(MyLog10(1000)) 'Result 2  (inaccurate)
End Sub

Even more surprising, my custom Function MyLog10(1000) entered in Excel Worksheet as =MyLog10(1000) and =INT(MyLog10(1000)) in both cases returns the correct result of 3.

Any explanation for this VBA/Worksheet inconsistency (probably bug)?

Also, by adding a very small number (1E-12) to the output of custom VBA MyLog10() produced the correct result in both cases (with/without additional Int()):

Debug.Print Int (Log10(1000)+1E-12) 'Result 3 (accurate)

Upvotes: 0

Views: 1579

Answers (2)

Alexander Bell
Alexander Bell

Reputation: 7918

After trying multiple type conversions, the solution was found: it is using a Decimal type as custom Log Function MyLog10Decimal return type. Following is the code snippet containing a universal VBA solution and test Sub TestIntMyLog10():

' SOLUTION: using output type decimal : Int() and Fix() return correct value
Public Function MyLog10Decimal(ByVal dbl_Input As Double) As Variant
    MyLog10Decimal = CDec(Log(dbl_Input) / Log(10))
End Function

'Problem: using output type double: Int() and Fix() may return incorrect
Public Function MyLog10(ByVal dbl_Input As Double) As Double
     MyLog10 = Log(dbl_Input) / Log(10)
End Function

Sub TestIntMyLog10()
   ' ** test sample: input number 1000
   'using Log output type double: Int() and Fix() produces incorrect results
    Debug.Print MyLog10(1000) 'Result 3 (accurate)
    Debug.Print Int(MyLog10(1000)) 'Result 2  (inaccurate)
    Debug.Print Fix(MyLog10(1000)) 'Result 2  (inaccurate)

    'using Log output type decimal: Int() and Fix() produces correct results
    Debug.Print Int(MyLog10Decimal(1000)) 'Result 3  (accurate)
    Debug.Print Int(MyLog10Decimal(1000)) 'Result 3  (accurate)
    Debug.Print Fix(MyLog10Decimal(1000)) 'Result 3  (accurate)

   ' ** test sample: input number 0.001
   'using Log output type double: Fix() produces incorrect results
    Debug.Print MyLog10(0.001) 'Result -3 (accurate)
    Debug.Print Int(MyLog10(0.001)) 'Result -3  (accurate)
    Debug.Print Fix(MyLog10(0.001)) 'Result -2  (inaccurate)

    'using Log output type decimal: Int() and Fix() produces correct results
    Debug.Print Int(MyLog10Decimal(0.001)) 'Result -3  (accurate)
    Debug.Print Int(MyLog10Decimal(0.001)) 'Result -3  (accurate)
    Debug.Print Fix(MyLog10Decimal(0.001)) 'Result -3  (accurate)
End Sub

A bit of explanation. VBA does not support Decimal type as Function parameter/output, so it's declared a Variant with CDec conversion applied to it. So far, it was tested on multiple inputs (>1 and <1) and returns correct results. Also, Excel Worksheet contains a built-in function LOG10(), but VBA does not. Thus, this VBA solution may be used in other Office app without need for Excel Object Library reference.

Upvotes: 2

Kyle Tegt
Kyle Tegt

Reputation: 103

You can just use =LOG10 in excel to get the same effect without having to make your own function (I have Excel 2013, I'm not sure if older versions have that formula).

And if you need that formula in VBA, you can just use Application.WorksheetFunction.Log10(number or name of variable)

I know that this doesn't really answer your question, but its a reliable work-around for it

Upvotes: 1

Related Questions