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:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]