VBA Square Root

In VBA, we have a built-in function called “SQR.” This function returns the square root for the given number provided in the input. The Square Root function requires only one argument for its function: Number.

The SQRT is a square root function in both Excel and VBA. The method to use this function is SQR(Number). One may use it to calculate the square root of a given number in Excel. However, the nomenclature is different. For example, one may write as SQRT compared to SQR in VBA.

Below is the syntax of the SQR function.

Number: For this argument, we need to supply the Number for which we are trying to find the square root. The Number could be a direct supply of a Number or Number assigned to the variable, or a Number with cell reference is valid.

This function is available with both worksheet and VBA function. But in a worksheet, it is available as SQRT.

Example #1

Now, we will try to write code to find the square root for the number 64.

But, first, start the VBA subroutine.

Code:

Sub Square_Root_Example() End Sub

Define two variables as Integer. One is to store the Number, and another is to show the result of the square root value.

Code:

Sub Square_Root_Example()  Dim ActualNumber As Integer  Dim SquareNumber As Integer End Sub

For the variable “ActualNumber,” assign the value of the number 64.

Code:

Sub Square_Root_Example()  Dim ActualNumber As Integer  Dim SquareNumber As Integer  ActualNumber = 64 End Sub

We will assign the square root value for another variable, enter the variable name, put an equal sign, and open the SQR function.

The only argument of the SQR function is “Number” since we have already assigned the number 64 to the variable “ActualNumber.” So, let us supply the same variable name in the SQR function.

Next, show the result in the message box. For example, the square root number assigned to the variable “SquareNumber” shows the same variable name in the message box.

Code:

Sub Square_Root_Example()  Dim ActualNumber As Integer  Dim SquareNumber As Integer  ActualNumber = 64  SquareNumber = Sqr(ActualNumber)  MsgBox SquareNumber End Sub

We have completed coding.

Run the code using excel shortcut key F5 and see what we get in the message box.

The Square Root of the number 64 is 8, 8 * 8 = 64.

Example #2

One more thing we need to remember while calculating sqrt in VBA is when the variable data type is either integer or long. Then, the result rounds off to the nearest integer or whole number value.

For example, if you are trying to find the square root for the number 70, there is no square root for this. But in VBA, it shows as 8 only because 8 is the nearest square root integer value.

Look at the below code.

Code:

Sub Square_Root_Example1()  Dim ActualNumber As Integer  Dim SquareNumber As Integer  ActualNumber = 70  SquareNumber = Sqr(ActualNumber)  MsgBox SquareNumber End Sub

The actual square root number result for 70 is 8.3666. But with VBA, it will round to the nearest integer value 8.

One thing we can do to rectify this error is we need to change the data type of the variable “SquareNumber” to “Double.”

Code:

Sub Square_Root_Example1()  Dim ActualNumber As Integer  Dim SquareNumber As Double  ActualNumber = 70  SquareNumber = Sqr(ActualNumber)  MsgBox SquareNumber End Sub

Run the code manually or through the F5 key to see the result.

As you can see, the result is accurate now, i.e., 8.366602. This is because of the data type assigned to the variable “SquareNumber.”

• In VBA, to find the square root of the number formula is SQR, and in the worksheet, it is SQRT.
• The Number we supply to the SQR function should be a positive number, or else we will get #NUM! Error.