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:
- 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:
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:
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.