Excel VBA Square Root (SQR) Function

The SQRT is a square root functionSQRT Is A Square Root FunctionThe Square Root function is an arithmetic function built into Excel that is used to determine the square root of a given number. To use this function, type the term =SQRT and hit the tab key, which will bring up the SQRT function. Moreover, this function accepts a single argument.read more 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 referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more is valid.

This function is available with both worksheet and VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more. But in a worksheet, it is available as SQRT.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Square Root (wallstreetmojo.com)

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.

But, first, start the VBA subroutineVBA SubroutineSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more.

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.

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.

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.

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 keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more 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.

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

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

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.

This article has been a guide to VBA Square Root Function. Here, we discuss calculating square roots using the SQRT function in Excel VBA with practical examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –

  • Project Password in VBAVBA Compare StringOn Error Resume Next in VBAVBA LBound