Excel VBA Log Function: Syntax, Examples, and Base

VBA & macros · Updated November 21, 2026

In Excel VBA, Log(number) returns the natural logarithm of a positive number—the logarithm with base e. For a different base, divide two natural logs: Log(number) / Log(base). For example, Log(10) is about 2.302585, while the base-10 logarithm of 10 is 1.

What VBA’s Log Function Calculates

A logarithm answers the question: what power must a base be raised to in order to produce a number? The natural logarithm uses the mathematical constant e, approximately 2.71828. Since e raised to the power 1 equals e, Log(e) is 1; since any positive number raised to the power 0 equals 1, Log(1) is 0.

VBA’s Log function calculates this natural logarithm. It is a built-in VBA mathematical function, so you can call it in a procedure or a custom function without enabling an add-in or declaring a reference. Its argument must be greater than zero. The result is a floating-point number, so it may have decimal places even when the input is an integer.

The word “log” can refer to logarithms with different bases in different contexts. In VBA, the built-in Log always means natural logarithm; it does not accept a second argument for the base. That difference matters when comparing VBA code with an Excel worksheet formula or a textbook calculation.

Syntax and Input Rules

The syntax is Log(number). Supply one numeric expression whose value is greater than zero. For example, Log(1) returns 0, and Log(10) returns approximately 2.30258509299405. The number can be a literal, a variable, or the result of another calculation.

  • Positive input: permitted, including values between 0 and 1. For instance, the natural logarithm of 0.5 is negative.
  • Input of 1: returns 0.
  • Input of 0 or a negative number: invalid for a real-valued logarithm and causes a VBA run-time error.
  • Base: not an argument to VBA’s built-in function. Use the change-of-base calculation when you need another base.

VBA evaluates expressions passed to Log, so a variable or calculation is fine as long as it produces a positive numeric value. If a value comes from a worksheet cell or an external source, check it before calling the function; blanks, text, errors, or unexpected values can cause other type or argument problems.

Examples in a VBA Procedure

This small procedure calculates the natural logarithm of 10 and writes the result to the Visual Basic Editor’s Immediate window. It does not change worksheet cells.

Option Explicit

Public Sub PrintNaturalLogExample()
    Dim x As Double
    Dim result As Double

    ' Log returns the natural logarithm.
    x = 10#
    result = Log(x)
    Debug.Print result
End Sub

The displayed result is approximately 2.30258509299405. In the Visual Basic Editor on Windows, open the Immediate window with Ctrl+G. To run the procedure, place the cursor inside it and press F5, or use Run > Run Sub/UserForm. In the Mac Visual Basic Editor, use View > Immediate Window; keyboard shortcuts can differ with the keyboard and Excel settings. Before running a macro, save a copy of the workbook: macro actions generally cannot be reversed with Ctrl+Z.

For a base-10 logarithm, use the change-of-base rule. For example, the calculation Log(100) / Log(10) returns 2, because 10 squared is 100. The same approach works for other valid bases.

Calculating a Logarithm in Another Base

For a positive number x and a valid base b, the change-of-base rule is log base b of x equals the natural log of x divided by the natural log of b. In VBA, write this as Log(x) / Log(b). Both the number and the base must be positive, and the base cannot equal 1. A base of 1 cannot distinguish powers because every power of 1 is still 1, so its logarithm is undefined.

For example, with x = 100 and b = 10, the result is 2. With x = 8 and b = 2, the result is 3, because 2 raised to the third power equals 8. The answer may be a fraction: the base-10 logarithm of 2 is about 0.30103.

If you will reuse the calculation, a custom VBA function makes the intention clearer. Put this code in a standard module, such as one created through Insert > Module in the Visual Basic Editor:

Option Explicit

Public Function LogBase(ByVal number As Double, ByVal base As Double) As Double
    ' Reject values outside the real-logarithm domain.
    If number <= 0# Then
        Err.Raise vbObjectError + 1000, "LogBase", "The number must be positive."
    End If

    If base <= 0# Or base = 1# Then
        Err.Raise vbObjectError + 1001, "LogBase", "The base must be positive and not 1."
    End If

    LogBase = Log(number) / Log(base)
End Function

This function deliberately raises a descriptive error when the inputs are outside the valid domain, rather than silently returning a misleading value. It expects numeric arguments that can be converted to Double; a nonnumeric cell value can trigger a type mismatch before the function’s checks run. Save the workbook as a macro-enabled Excel workbook (.xlsm) if you need to retain VBA code.

Using VBA Log from a Worksheet

A public custom function in a standard module can be called from a worksheet cell, provided macros are enabled and the workbook containing the function is available. With the number in A2 and the base in B2, enter =LogBase(A2,B2). If A2 contains 100 and B2 contains 10, the result is 2. If the inputs are invalid, the custom function raises an error rather than returning a normal numeric answer.

For simple calculations, Excel’s built-in worksheet functions may be easier than writing VBA. With a positive number in A2, the worksheet formula =LN(A2) returns its natural logarithm. With 100 in A2, it returns approximately 4.605170186. With the number in A2 and the desired base in B2, =LOG(A2,B2) returns the logarithm using B2 as the base; for A2 equal to 100 and B2 equal to 10, the result is 2. These are worksheet formulas, not VBA expressions.

That distinction is a common source of confusion: worksheet LOG accepts a number and an optional base, while VBA Log accepts just one argument and always uses base e. Excel’s worksheet LN is the natural-log function, so it is the closest worksheet counterpart to VBA’s Log.

Handling Invalid Values and Common Problems

A real logarithm is defined only for positive inputs. Calling VBA Log with zero or a negative number causes a run-time error (typically error 5, Invalid procedure call or argument). This is not the same as getting a valid logarithm with a negative result: inputs between 0 and 1 have negative natural logarithms, but inputs at or below 0 are outside the real-number domain.

If a calculation depends on worksheet data, validate the data before calling Log. For example, a cell that appears blank may be empty, may contain text, or may contain a formula returning an empty string. Those cases are not interchangeable with a positive number. In a larger macro, decide how to handle invalid rows—skip them, report them, or stop with a useful message—rather than allowing one unexpected value to interrupt processing without context.

  • If you see an invalid-argument error, inspect whether the computed input is zero or negative.
  • If you see a type mismatch, check whether the value is numeric and compatible with the declared variable type.
  • If the result is unexpectedly negative, check whether the input is between 0 and 1; that result can be correct.
  • If you expected a base-10 answer, use the change-of-base calculation or the worksheet LOG function with a base.

Precision and Interpreting Results

Logarithm results are generally approximate decimal values, not neat integers. For example, the mathematical result for Log(10) is not 2.3 exactly; 2.3 is only a rounded display. VBA stores the result using floating-point arithmetic, so tiny rounding differences can appear in calculations. Avoid testing a calculated logarithm for exact equality with a decimal you typed. If a comparison is necessary, compare the difference against a small tolerance appropriate to the calculation.

Formatting affects what a worksheet displays, not the underlying VBA result. A cell formatted to show two decimal places might display 2.30 even though the stored value has more digits. Likewise, rounding a result for presentation is different from changing the precision of the calculation itself. Keep full precision for later computations unless your requirements specifically call for rounding at an intermediate step.

Choosing Between VBA and Worksheet Functions

Use VBA’s Log when a macro needs a natural logarithm as part of a sequence of calculations, or when you are writing a reusable VBA function. Use worksheet LN for a natural logarithm directly in a cell, and worksheet LOG when you want a selectable base without creating VBA code.

Worksheet functions are often more transparent in a model because readers can see the calculation in the cell and the workbook does not need a macro for that operation. VBA is useful when the logarithm belongs inside automation or a larger procedure. A worksheet custom function is not a macro that edits cells: Excel recalculates it from its arguments, but it should not be designed to change other cells or the workbook as a side effect.

Frequently Asked Questions

Does VBA Log use base 10?
No. VBA’s built-in Log uses base e, the natural logarithm. For base 10, calculate Log(number) / Log(10), or use the worksheet LOG function with 10 as its base.

What does Log(1) return in VBA?
It returns 0. This is because e raised to the power 0 equals 1, and the logarithm asks for the exponent.

Can VBA Log handle zero or negative numbers?
No. Real logarithms are defined only for positive numbers. Passing zero or a negative number to VBA’s function causes a run-time error, so check inputs before calling it.

What is the difference between VBA Log and Excel LOG?
VBA Log takes one argument and returns a natural logarithm. The worksheet function LOG can take a second argument for the base; worksheet LN calculates a natural logarithm.

How do I calculate log base 2 in VBA?
Use Log(number) / Log(2), with a positive number. For example, the calculation for 8 returns 3 because 2 cubed equals 8.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.