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.
Are you struggling to the find the right solutions to your engineering problems in Excel?
In Engineering with Excel, you'll learn Excel for advanced engineering calculations through a step-by-step system that helps engineers solve difficult problems quickly and accurately.