VBA Square Root

Excel VBA Square Root Function

In this article, we will see an outline on Excel VBA Square Root. Every time working on Excel, you must have used the SQRT function that gives the square root of any whole number as a result. Is it possible to capture the square root of a number under VBA? Well, the answer is a sure Yes! You can get the square root of any positive number through VBA using the VBA SQR function. This function is a built-in VBA function and can be used on any positive integer to get the square root of it. Well, this function is very similar to the one which we use as a spreadsheet function SQRT and also take the same single argument “Number” which should be positive. We can’t get the square roots of Imaginary numbers either and entering negative values is of no use because there is no possible way with which a negative square value of a number can be captured.

Syntax:

Syntax for VBA square root function is pretty simple and is look similar to the one of spreadsheet SQRT function.

Where Number is a sole argument that needs to be provided and should be strictly positive. Since we can’t have a negative square values of any number, chances of getting a negative square root value is impossible. Also, no scope for finding out the square roots of the complex numbers. This argument can be provided directly as a number or a variable assigned to the number or a spreadsheet cell reference where the number is stored. Also, the point to be noted here is, system by default considers the argument you provide as a double to give you the square root on any number irrespective of the fact that it is a whole square or not (Ex. 87, is not the whole square, but will still get the square root value since the number type is double. You can still define a number as an integer and then use it as an argument to SQR function. It will round to the closest square root value if the number provided is not a whole square.

How to Use Square Root Function in Excel VBA?

Below are the different examples to use the Square Root function in Excel VBA.

VBA Square Root – Example #1

Follow the steps below to see how exactly the VBA SQR function works.

Step 1: Click on the Developer tab and then click the Visual Basic (ALT +F11) icon.

Step 2: Open Module from the Insert menu tab as shown below.

Step 3: Define a new sub-procedure that can hold the macro under the newly inserted module.

Code:

Sub sqrt_Example1()

End Sub

Step 4: Use the assignment operator to assign the value of the VBA square root function to a variable named “sqr_root” so that we can print the result and see if it works fine.

Code:

Sub sqrt_Example1()

sqr_root = Sqr(121)

End Sub

Step 5: Use the MsgBox function so that we can print the result as a message box that will pop-up as soon as we run the code.

Code:

Sub sqrt_Example1()

sqr_root = Sqr(121)

MsgBox "Square Root of Given Number is: " & sqr_root

End Sub

Step 6: Run this code by hitting F5 or Run button placed at the uppermost panel.

As soon as you run this code, you should see an output as shown below.

VBA Square Root – Example #2

Let’s dive deeper into the VBA SQR function and try to find out some different cases.

Step 1: Define sub-procedure that can hold your macros.

Code:

Sub sqrt_Example2()

End Sub

Step 2: We need to define two new variables as integer. One for storing the value which we want a square root for. Another variable would be for storing the result of the square root after we apply the SQR function.

Code:

Sub sqrt_Example2()

Dim square_num As Integer
Dim square_root As Integer

End Sub

Step 3: Assign value as 87 to the square_num variable using the assignment operator (‘=’). This is the number for which we wanted to capture the square root value.

Code:

Sub sqrt_Example2()

Dim square_num As Integer
Dim square_root As Integer
square_num = 87

End Sub

Step 4: Use the SQR function and assignment operator to assign the value of the square root to the  square_root variable. Use square_num as an argument under the SQR function (the number for which we wanted the square root value).

Code:

Sub sqrt_Example2()

Dim square_num As Integer
Dim square_root As Integer
square_num = 87
square_root = Sqr(square_num)

End Sub

Step 5: Use MsgBox Property to show the value of the square root as a message that pops up as soon as you run the code.

Code:

Sub sqrt_Example2()

Dim square_num As Integer
Dim square_root As Integer
square_num = 87
square_root = Sqr(square_num)
MsgBox "Square Root for Given Number is: " & square_root

End Sub

Step 6: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE.

Step 7: See the output in a message box as shown below:

However, this is not the actual square root value for number 87. This is the square root value for number 81. Why in the world VBA is giving wrong output for SQR function?

Well, there is nothing wrong with VBA. If something is wrong, it is ideal with our code.

Since we have defined the variables as integers, we are restricting the VBA to give the square roots of only those numbers which are whole square numbers (Ex. 4, 9, 16, 25, etc.). If any number is not a whole square, the VBA SQR function instead of throwing an error, it gives a square root value for a number that is close to the current number. Here, 87 has 81 as the closest whole square number. Therefore, you are getting the output as 9.

Step 8: Let’s see what happens when we tweak the code by defining the square_root variable as Double instead of Integer.

Code:

Sub sqrt_Example2()

Dim square_num As Integer
Dim square_root As Double
square_num = 87
square_root = Sqr(square_num)
MsgBox "Square Root for Given Number is: " & square_root

End Sub

Step 9: You will see that the system now can capture the actual square root value for the number 87.

Things to Remember

• If you provide a negative number as an argument under the VBA SQR function, it will throw a Run-time error ‘5’. Since it is not possible to have a number with negative square values.
• If you have a negative number, make sure you are converting it to a positive entity with the help of the ABS function. You can also check if the number is positive or not with the help of the SGN function under VBA.
• If you put zero as an argument under the VBA SQR function, you will get the square root value as zero.

Recommended Articles

This is a guide to VBA Square Root. Here we discuss how to use Square Root Function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA IF Statements
2. VBA Sort
3. VBA While Loop
4. VBA Counter