VBA Square Root

Excel VBA Square Root (SQR) Function

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

SQRT is a square root function in both excel and VBA. The method to use this function is as follows SQR(number) and used to calculate the square root of a given number in excel; however, the nomenclature is different, and it is written 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 number or number assigned to the variable, or a number with cell reference is valid.

This function is available with both worksheet functions as well as VBA function, but in a worksheet, it is available as SQRT.

Examples of Square Root in Excel VBA

Example #1

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

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 number 64.

Code:

Sub Square_Root_Example()

  Dim ActualNumber As Integer
  Dim SquareNumber As Integer

  ActualNumber = 64

End Sub

For another variable, we will assign the square root value, enter the variable name, put equal sing, 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” let’s supply the same variable name in the SQR function.

Next, show the result in the message box. Now the square root number is assigned to the variable “SquareNumber” shows the same variable name to 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

Ok, that’s all we are done with coding.

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

Square Root of the number 64 is 8 i.e. 8 * 8 = 64

Example #2

One more thing we need to keep in mind while calculating sqrt in VBA is when the variable data type is either integer or long, the result is rounded 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

Now run the code manually or through the F5 key and see the result.

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

Things to Remember

• 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.

Recommended Articles

This has been a guide to VBA Square Root Function. Here we discuss how to calculate square root using the SQR function in excel VBA with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

• Project Password in VBA
• VBA Compare String
• On Error Resume Next in VBA
• VBA LBound