Zahid Hasan
It is quite fascinating how easily we can find square root in Excel using VBA codes. Simply by running an Excel VBA code, we can find square roots of multiple numbers at once. In this article, we will discuss 3 simple, and easy examples to find square root in Excel VBA.
How to Find Square Root in Excel VBA: 3 Suitable Methods
Calculating the square root in Excel of a number manually becomes almost impossible for most numbers. Using the Excel VBA code provides a simple yet effective solution to the problem. In this portion of the article, we are going to learn 3 suitable examples to find the square root.
Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Using Sqr Function to Find Square Root in Excel VBA
In the first example, we will use the Sqr function of Excel VBA to find the square root. Let’s follow the steps mentioned below.
Step 01: Creating Module to Write VBA Code
- Firstly, go to the Developer tab from Ribbon.
- Following that, click on the Visual Basic option from the Code group.
As a result, the Microsoft Visual Basic window will open as shown in the following image.
Note: You can use the keyboard shortcut ALT + F11 to directly open the Microsoft Visual Basic window from your worksheet.
- Now, go to the Insert tab in the Microsoft Visual Basic window.
- Then, select the Module option from the drop-down.
Consequently, a blank Module will be available on your worksheet.
Step 02: Writing and Running the Code
- Firstly, write the following code in the Module.
Sub square_root_of_given_number()
Dim given_number As Integer
Dim square_root As Double
given_number = 64
square_root = Sqr(given_number)
MsgBox square_root
End Sub
<img class="aligncenter wp-image-222227 size-full" src="https://www.exceldemy.com/wp-content/uploads/2022/09/how-to-find-square-root-in-excel-vba-5.png" alt="Writing and Running the Code to find square root in excel vba" width="793" height="733" />Code Breakdown
- Firstly, we created a Sub Procedure named square_root_of_given_number().
- After that, we declared a variable named given_number As Integer and another variable named square_root As Double.
- Then, we have assigned the value of the variable given_number as 64.
- Following that, we used the SQR function to find the square_root of the given_number.
- Next, we introduced a MsgBox to display our output.
- Finally, we have ended the Sub Procedure.
- After writing the code, click on the Save icon as marked in the following picture.
- Following that, click on the Run option.
Note: You can also use the keyboard shortcut F5 to run the code.
Consequently, you will see the square root of our given number (64) is shown in the message box.
- To find the square root of the remaining numbers, change the value of the given_number argument and run the code.
As a result, you will find the square roots for the remaining numbers as shown in the following image.
2. Utilizing Sqr Function with User Input to Find Square Root
In this example, we will again use the Sqr function. But here we will take the number of which we need to find the square root as input from the user. Let’s use the steps discussed below.
Step 01: Creating Module to Write VBA Code
- By following the steps mentioned in Step 01 of the 1st method, we can get the following output.
Step 02: Writing and Running the Code
- Firstly, write the following code in the newly created Module.
Sub square_root_with_user_input()
Dim number As Double
Dim square_root As Double
number = InputBox("Enter a Number", "Value Enter")
If number < 0 Then
MsgBox "You must enter a Positive Number"
Exit Sub
End If
square_root = Sqr(number)
MsgBox square_root
End Sub
Code Breakdown
- Here, we have created a Sub Procedure named square_root_with_user_input().
- Following that, we declared a variable named number As Double and another variable named square_root As Double.
- Then, we used the InputBox statement to take input from the user and assign the value in the variable named input.
- After that, we used an IF statement to check if the number is less than 0.
- Next, we introduced another MsgBox that will show You must enter a Positive Number if the number is less than 0.
- If the number is less than 0, then it will exit the Sub Procedure.
- Then, we ended the IF statement.
- Afterward, we used the SQR function to find the square_root of the number.
- Next, we used another MsgBox to display the output.
- Finally, we ended the Sub Procedure.
- After writing the code, click on the Save icon from the Microsoft Visual Basic window.
- Following that, click on the Run option as marked in the following image.
As a result, a dialogue box will open named Value Enter.
- Now, enter a number as you like. In this case, we have entered the number 64.
- Then, click OK.
Consequently, you will see the square root of your provided number as marked in the image given below.
By following the same steps, we can find the square roots for the rest of the numbers.
3. Employing For Next Loop to Find Square Root in Excel VBA
We will use the For Next Loop to find the square root in Excel VBA. After learning this method, you will be able to find square roots of multiple numbers just by running a single Excel VBA code. Let’s follow the steps mentioned below to do this.
Step 01: Creating Module to Write VBA Code
- Follow the steps mentioned in Step 01 of the 1st method to get the following output.
Step 02: Writing and Running the Code
- Firstly, write the code given below in the newly created Module.
Sub square_root_using_For_Next_Loop()
Dim inputRng As Range
Dim offset As Integer
Dim output_cell As Range
Set inputRng = Range("B5:B9")
offset = 1
For Each output_cell In inputRng
output_cell.offset(0, offset).Value = Sqr(output_cell.Value)
Next output_cell
End Sub
Code Breakdown
- Firstly, we have created a Sub Procedure named square_root_using_For_Next_Loop().
- Following that, we declared a variable named inputRng As Range, a variable named offset As Integer, and another variable named output_cell As Range.
- After that, we used the Set statement to assign the values from the worksheet within the range B5:B9.
- Then, we assigned the value of the variable named offset as 1.
- Next, we initiated a For Next loop.
- In the loop, we used the SQR function to find the square root of the numbers from inputRng and assigned them in the range C5:C9 by using the Offset function.
- Then, we closed the loop.
- Lastly, we ended the Sub Procedure.
- Now, click on the Save icon as marked in the following image.
- Subsequently, press the keyboard shortcut ALT + F11. This will redirect you to your worksheet.
- After that, go to the Developer tab from Ribbon.
- Following that, select the Macros option as shown in the image below.
- Now, from the Macro dialogue box, choose the square_root_using_For_Next_Loop option.
- Finally, click on Run.
As a result, you will get the square roots of all numbers at once.
How to Find Nth Root of a Number Using Excel VBA
While working in Excel, we often need to find the root of numbers. It can be incredibly beneficial to us if we know how we can calculate the root of any number up to any degree. VBA is a user-friendly and dynamic coding language. In this article, we are going to learn how we can find the Nth root of a number using Excel VBA. Here, N can be any integer.
In the following dataset, we have some numbers. We aim to find the Nth root of a number by using Excel VBA.
Here, we will create a function using the VBA code. Using this function, we can find the root of a number up to any degree. Let’s follow the steps discussed below.
Step 01: Creating Module to Write VBA Code
- Follow the steps mentioned earlier in Step 01 of the 1st method to get the following output.
Step 02: Writing and Running the Code
- Firstly, write the following code in the Module that you created.
Option Explicit
Function nth_root(number As Double, root As Long) As Double
nth_root = number ^ (1 / root)
End Function
Code Breakdown
- At first, we initiated the Option Explicit statement.
- Following that, we declared the name of our function as nth_root.
- After that, we declared the function paraments along with their number formats.
- Then, we specified the mathematical expression for the function.
- Finally, we ended the Function statement.
- After writing the code, click on the Save icon in the Microsoft Visual Basic window.
- Now, enter the following formula in cell C5.
=nth_root(B5,2)Here, cell B5 represents the number of which we want to find the Nth root, and 2 indicates that we will find the square root of the number.
- Afterward, hit ENTER.
As a result, you will get the following output on your worksheet.
- Now, by using the AutoFill option of Excel, we can find the rest of the outputs as marked in the following image.
- Following that, use the formula given below in cell D5.
=nth_root(B5,3)- Then, press ENTER.
Subsequently, as shown in the image below, you will get the 3rd root (cube root) of 9.
- At this stage, use the AutoFill feature of Excel to get the remaining roots of the rest of the numbers.
- Next, enter the following formula in cell E5.
=nth_root(B5,4)- Then, hit ENTER.
Subsequently, you will get the following output on your screen.
- By using the AutoFill option of Excel, you can find the roots of the remaining numbers, as shown in the following image.
Read More: How to Do Cube Root in Excel
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Download Practice Workbook
Conclusion
That’s all about today’s session. I strongly believe that this article was able to guide you to find square root in Excel using VBA. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality.
