How to Use Log Function in Excel VBA (5 Suitable Examples)

The VBA LOG function is a built-in Math or Trig function in Excel. The LOG function is available in both Excel Worksheet and VBA however they differ by syntax. In this article, we discuss and demonstrate the VBA LOG function and its usage.

Overview-VBA LOG function


Download Excel Workbook


Excel VBA Log Function: Syntax and Arguments

🔄 Function Objective:

 To find the logarithm value of any given numeric expression greater than zero (0).

🔄 Syntax:

The syntax of the VBA Log function is

Log(number)

log syntax-VBA LOG

🔄 Arguments Explanation:

Argument Required/Optional Explanation
number Required any numeric expression greater than zero (0)

🔄 Return Parameter:

The VBA LOG function returns a double (i.e., decimal negative or positive value type) or integer type numeric value.

🔄 Applies To:

Microsoft Excel Version 2003, Excel 2011 for Mac and Onwards.


⧬ Note

VBA Log function works as a natural logarithm with a base e. So, the e-based logarithm returns values approximately 2.303 times of 10 based logarithm. If you want to calculate the n-based logarithm of a number (i.e.,x), we have to divide the natural logarithm of that number (i.e., logex) by the natural logarithm of the base (i.e., logen). As a result, the formula becomes,

Lognx= (logex / logen)


⧭ Opening and Inserting Module in Microsoft Visual Basic

Before proceeding to demonstrate any examples, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.

🔄 Opening Microsoft Visual Basic: There are mainly three ways to open Microsoft Visual Basic window,

🔼 Using Keyboard Shortcuts

 Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

 In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

developer option-VBA LOG

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).

View code


🔄 Inserting a Module in Microsoft Visual Basic: There are two ways to insert a Module in Microsoft Visual Basic window,

🔼 After opening the Microsoft Visual Basic window, Select a Worksheet > Right-Click on it > Select Insert (from the Context Menu) > then Choose Module.

module insertion

🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

Module insertion


5 Suitable Examples to Use Excel VBA Log Function

Example 1: Using VBA Log Function to Return a Logarithm Value

From the syntax, we know that the VBA LOG function requires only one argument in numeric form. In this example, we simply use the LOG function to return a logarithmic value of a given number (i.e., 1000). We provide the number in the function and take the resultant value in a message box. To achieve the logarithmic value in a message box execute the below sequences.

Paste the following macro code in a Worksheet Code of Microsoft Visual Basic window,

Sub Logarithm_Value()
MsgBox Log(1000)
End Sub

logarithm value-VBA LOG

in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – display the logarithm value (i.e., 1000) in a message box.

Press F5 to run the Macro and it brings out a message box depicting the function’s outcome.

logarithm value result

In the above screenshot, we show values provided by the Worksheet LOG function and VBA LOG function to differentiate them.

Read More: VBA EXP Function in Excel (5 Examples)


Example 2: Calculating N-Based Logarithm Value Using VBA Log

Now, we know that the VBA LOG function calculates logarithmic values using the e-based natural logarithm. If we have to calculate the logarithmic value with a different base, we can use the previously discussed (i,e., in the Note section) formula

Lognx= (logex / logen)

Write the following code in Microsoft Visual Basic window.

Function LogRND(N As Double, X As Double) As Double
LogRND = Log(X) / Log(N)
End Function
Sub Log_N_Based()
MsgBox LogRND(10, 1000)
End Sub

n-based value-VBA LOG 

From the above image, in the sections,

1 – begin the macro code by declaring a function name and variables.

2 – assign a formula to find any n-based logarithm values.

3 – give the performing macro code a name.

4 – the logarithm value shows up in a message box. Also assign the base(i.e.,10) and value (i.e., 1000).

Hit F5 to run the macro and it displays a message box with the resultant value in it as depicted in the following image.

n-based value result

You can see the resultant value of the Worksheet LOG function and the VBA LOG function is the same. It’s because in both cases we use a 10 based logarithm.

Read More: How to Use VBA IsNumeric Function (9 Examples)


Example 3: Finding Multiple Logarithm Values (Result in the Immediate Window)

In earlier examples, we calculate logarithmic values for a single given number. We can calculate multiple logarithmic values of multiple given numbers. And we can display them in the message box or immediate window. For this example, we display multiple logarithmic values in the immediate window below the code window.

Use the below code in any Microsoft Visual Basic’s Sheet code window.

Option Explicit
Sub Multiple_Values()
Dim Value1 As Double
Dim Value2 As Double
Dim Value3 As Double
Value1 = Log(1)
Value2 = Log(0.05)
Value3 = Log(100)
Debug.Print Value1
Debug.Print Value2
Debug.Print Value3
End Sub

multiple values-VBA LOG

The code’s sections,

1 – initiate the macro procedure declaring the Sub name.

2 – declare the variables Value1, Value2, and Value 3 as Double. As we mentioned earlier, the return values will be in any numeric value (i.e., negative or positive decimal or integer), we declare the variables as double.

3 – assign the values to variables. Where Value1 = Log(1), Value2 = Log(0.05), and Value3 = Log(100). You can use any numeric value except zero (0) and negative ().

4 – print the resultant logarithm value in the Immediate window below the Sheet code window.

Use F5 to run the macro and it brings out in the immediate window as shown in the following image.

multiple values in Immediate window

You can compare the VBA LOG function’s resultant values to the Worksheet LOG function’s outcomes depicted in the below picture.

multiple value result

Read More: How to Use VBA Randomize Function in Excel (5 Examples)


Similar Readings:


Example 4: Calculating Multiple Logarithmic Values from Cell Reference

In this example, we use cell references to insert a value to the formula and to display the resultant values in certain cells.

Before that, we calculate the logarithmic values of three given values (i.e., 1,0.05, and 500) using Worksheet’s LOG function. And keep a column to insert the resultant values using the VBA LOG function.

cell reference

Type the following code in a Sheet’s code of Microsoft Visual Basic window.  

Option Explicit
Sub VBA_LOG_Function_Cell_Reference()
Range("C3").Value = Log(Range("B3"))
Range("C4").Value = Log(Range("B4"))
Range("C5").Value = Log(Range("B5"))
End Sub

Macro code

From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – assign the values to the formula (i.e., =Log()) with cell reference (i.e., B3 or B4) as an argument. And display the resultant value in certain cells (i.e., C3 or C4). You can use any cell as a cell reference.

Press F5 to run the Macro and it makes the logarithm values appear in referenced cells as shown in the following picture.

cell reference result

Related Content: How to Use VBA Val Function in Excel (7 Examples)


 Example 5: Calculating Logarithmic Values from Range

We show how to fetch, and display given values and resultant values in a certain cell in the previous example (i.e., Example 4). In this example, we’ll take that example a step further. We use a range (i.e., B3:B5) to insert given values and the VBA OFFSET function to display logarithmic values in cells.

Prior to the VBA LOG function’s execution, we calculate the logarithmic values of the given value using Worksheet’s LOG function.

logarithm values from range

Paste the following code in a Sheet’s Code window.

Sub Logarithm_Value_Range()
Dim i As Range
For Each i In Range("B3:B5")
i.Offset(0, 1) = Log(i)
Next i
End Sub

Macro code-VBA LOG

The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – declaring the variable as Range.

3 – assign range value (i.e., B3:B4) for each iteration.

4 – create a loop and display the value in cell position at the end of each iteration.

Push the F5 key to run the Macro and it displays the logarithmic values in destined cells.

logarithm values from range result-VBA LOG

Related Content: How to Use VBA Round Function in Excel (6 Quick Uses)


Things to Keep in Mind

🔄 The syntax of the Excel Worksheet LOG function is LOG (number, [base]) and the VBA LOG function is Log(number).

🔄 Excel Worksheet’s LOG function calculates logarithm values 10-based by default and the VBA LOG function works as natural logarithm (i.e., e-based).


Conclusion

In this article, we discuss and demonstrate the uses of the VBA Log function. We try to clear the basics and cover typical uses of this function. I hope the above-described uses of the VBA Log function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. See you in my next article.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo