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 function as well as VBA function but in a worksheet, it is available as SQRT.
Examples of Square Root in Excel VBA
Now we will try to write code to find the square root for the number 64.
First, start the vba subroutine.
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 number 64.
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.
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
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.
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”.
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.
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 –