# Logarithms in VBA: When a Log is not a Log

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

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.

Scroll to Top Complete... 50% Complete... 50% ## 10 SMARTER WAYS TO USE EXCEL FOR ENGINEERING

### Take your engineering to the next level with advanced Excel skills. ## EXCEL TRAINING FOR ENGINEERS

Learn advanced engineering techniques in Excel with this limited time free video training.

Click the button below to get started today.