Sometimes the subtle nuances of Excel functions can get a little tricky. I ran across one of those situations just the other day when I was working on a project that involved logarithms in VBA.

Log in Excel

To find the logarithm of a number in Excel, you use the LOG function, whose syntax looks like this:

LOG(number,[base])

Where:

• number is the number for which you want to return the logarithm, and
• base is the base of the logarithm. By default, this value is 10, unless you choose to specify it.

So if you want to calculate the following:

You would enter it into Excel like this:

=LOG(3,10)

or

=LOG(3)

And Excel would return a value of 0.477.

Log in VBA

But, let’s try using the Log function in a User Defined VBA Function. Just for demonstration I’ll create a simple function called MyLog with a single input.

And trying it out in the worksheet…

…gives us this result:

Although it appears that I used the same function, the result is definitely not the same. But why?

Well, it turns out that the “Log” function in VBA returns the natural logarithm of a number, rather than a common logarithm. In an Excel worksheet, the function to return a natural logarithm is LN(number).

How to Calculate a Common Logarithm in VBA

Although it’s a little messier, we can at least still reference the LOG worksheet function in a VBA subroutine or user defined function by preceding the Log function with Application.WorksheetFunction. This will cause the VBA code to reference the Application object’s built-in worksheet functions, of which LOG is one.

And now, of course, the User Defined Function returns the correct result:

Wrap Up

Honestly, this is something in Excel that disappoints me. We can work around it by using Application.WorksheetFunction, but the problem is that using LOG as a function in VBA DOES return a result, although its incorrect. It’s the kind of error that could easily go unnoticed. Of course, this is why it’s always important to try to cross-check your results with another data point or reference. And always ask, “Does this make sense?”, regardless of the software package you are using.