VBA Square Root

Excel VBA Square Root (SQR) Function

In VBA we have a built-in function called “SQR” to calculate the square root of a given number. Square Root function requires only one argument for its function i.e. Number.

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 function 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 sub-procedure.

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 SQR function.

The only argument of SQR function is “Number” since we have already assigned the number 64 to the variable “ActualNumber” let’s supply the same variable name in 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 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 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 SQR function in excel vba with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

• Convert String Values to Date
• Project Password in VBA
• VBA Compare String
• VBA Web Scraping
• On Error Resume Next in VBA
• LBound in VBA Examples
• VBA Randomize examples
• VBA ReDim Array Examples