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.
Excel VBA LOG Function: Syntax and Arguments
🔄 Function Objective:
To find the logarithm value of any given numeric expression greater than zero (0).
The syntax of the VBA Log function is
🔄 Arguments 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.
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,
x / log
⧭ 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 the Microsoft Visual Basic window,
🔼 Using Keyboard Shortcuts
Press ALT+F11 altogether to open the Microsoft Visual Basic window.
🔼 Using Developer Tab
In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.
🔼 Using Worksheet Tab
Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).
🔄 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.
🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.
1. Using VBA LOG Function to Return a Logarithmic 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
➤ 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.
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)
2. Calculating N-Based Logarithmic Value Using VBA LOG Function
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 the 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
➤ 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.
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.
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
➤ 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.
➤ You can compare the VBA LOG function’s resultant values to the Worksheet LOG function’s outcomes depicted in the below picture.
- How to Use the VBA Environ Function (4 Examples)
- Use VBA IsEmpty Function (5 Relevant Examples)
- How to Use the VBA Weekday Function (2 Suitable Examples)
- Use VBA Mod Operator (9 Examples)
- How to Use VBA Case Statement (13 Examples)
4. Calculating Multiple Logarithmic Values from Cell Reference
In this example, we use cell references to insert a value into 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.
➤ 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
➤ 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.
Related Content: How to Use VBA Val Function in Excel (7 Examples)
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.
➤ 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
➤ 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.
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
🔄 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).
Download Practice Workbook
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.