What is Excel VBA Square Root (SQR) Function?
The square root of a number can be determined using the VBA Square Root function. The non-negative square root of a numerical value can be found using this mathematical function. If the input number is not a perfect square, the result is a numerical value with a decimal component. Let us look at an example. Here, we have a subroutine named SquareRootExample.
First, two double-type variables are declared: number and result. The ‘number’ variable has 9 assigned to it. The square root of the number is then calculated using the VBA Square Root function, and the result is saved in the ‘result’ variable. Lastly, the result is shown using a message box (MsgBox) to display the square root of the selected number along with a message as “The square root of 9 is 3.”
Table of Contents
Key Takeaways
- The VBA Square Root function is used to calculate the square root of a number. It operates on numeric values and returns a result of data type Double.
- The VBA Square Root negative number function cannot compute the square root of negative numbers directly, so it is imperative to include conditional checks to ensure the input is non-negative when working with potentially negative input.
- While VBA Square Root is the primary function for square root calculations, alternative methods like the “^” operator or the Power function can also be used to achieve the same result.
Syntax
The syntax for the VBA Square Root function is as follows:
Sqr(number)
- number (required): The numeric value for which you want to find the square root.
The required number argument is a “Double” or any valid “numeric expression” greater than or equal to zero.
Note: The square roots of negative numbers produce complex or imaginary values, which are not supported by the VBA Square Root function, thus it can only compute the square roots of non-negative numbers.
How to Use Excel VBA Square Root (SQR) Function?
To use the VBA Square Root function, follow these steps:
Step 1: Open Excel and press ALT + F11 to open the VBA editor.
Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
Step 3: Declare variables to hold the outcome and your numerical values. To declare variables with the proper data types, use the Dim statement.
For example:
Dim number As Double
Dim result As Double
Step 4: Assign a numeric value for which you want to calculate the square root to the number variable. For example:
number = 36
Step 5: Use the Sqr function to calculate the square root and store the result in a variable. For example:
result = Sqr(number)
Step 6: You can display the result using various methods, such as a message box (MsgBox) or writing it to a cell in your Excel worksheet. Example:
MsgBox “The square root of ” & number & ” is ” & result
Step 7: Save your macro and run the VBA code to see the output.
Examples
Example #1
In this example, we’ll learn how to use the VBA Square Root function to compute and display the square root of 90.
Step 1: In the new module, we first define a subroutine (Sub) named CalculateSquareRoot.
Step 2: Here, the two variables, number and result, are declared as Double VBA data types. Double is used because it can hold both integer and decimal values.
Step 3: We assign the value 90 to the ‘number’ variable. You can replace 90 with any numeric value you want to calculate the square root.
Step 4: The Sqr function is used to calculate the square root of the number variable, and the result is stored in the result variable.
Step 5: Finally, a message box in VBA is displayed with a message that includes the original number and its square root. The & operator is used to concatenate (combine) strings and variables.
Step 6: When you execute this macro, it will display a message box stating, “The square root of 90 is 9.48683298050514.”
Note: Because there is no explicit rounding function or result formatting in the VBA code, the code does not display a rounded number in the message box.
To display the square root rounded to two decimal places, you can modify the message box line like this:
MsgBox “The square root of ” & number & ” is ” & Format(result, “0.00”)
With this modification, the square root result will be shown in the message box with two decimal places.
Here is the full code:
Sub CalculateSquareRoot()
Dim number As Double
Dim result As Double
number = 90
result = Sqr(number)
MsgBox “The square root of ” & number & ” is ” & result
End Sub
Example #2 – Handling Negative Numbers
In this example, we will see how to handle negative numbers by using the VBA Square Root negative number function to calculate and display the square root of -16 if it is non-negative, or to display an error message if it is negative.
Step 1: Similar to the first example, we first start by defining a new subroutine named “CalculateSquareRootWithNegative.”
Step 2: Now, two variables, number, and result, are declared as Double data types.
Step 3: We assign the value -16 to the ‘number’ variable. This time, we will use a negative number to demonstrate handling negative input.
Step 4: An If-Else statement in VBA is used to check if the number variable is greater than or equal to 0. This condition checks whether the input is non-negative.
Step 5: If the condition is met (i.e., the number is non-negative), the Sqr function is used to calculate the square root of the number variable, and the result is stored in the result variable.
Step 6: If the condition in the If statement is not met (i.e., the number is negative), a message box is displayed indicating that the square root cannot be calculated for a negative number.
Step 7: Now, execute the macro. This code handles negative numbers by producing an error message that appears in a message box that reads, “Cannot calculate the square root of a negative number,” when it runs.
Here is the complete code:
Sub CalculateSquareRootWithNegative()
Dim number As Double
Dim result As Double
number = -16
If number >= 0 Then
result = Sqr(number)
MsgBox “The square root of ” & number & ” is ” & result
Else
MsgBox “Cannot calculate the square root of a negative number.”
End If
End Sub
Important Things To Note
- The Double data type that the VBA Square Root function returns indicates that it can handle both integer and decimal values.
- The function will return a “Run-time error ‘5’: Invalid procedure call or argument” if the square root of a negative number is attempted to be calculated and is not handled correctly.
- To calculate the square root of a negative number, you can use the “Abs” function to get the absolute value of the number before applying the VBA Square Root function.
- Make sure that the numerical value entered into the VBA Square Root function is correct. A “Run-time error ’13’: Type mismatch” will appear if you pass a non-numeric value or an excel cell reference containing non-numeric data.
- The VBA Square Root function returns a numeric result. You must specifically apply formatting or rounding functions to the result in your code if you need to display the result as a rounded value or with a specified number of decimal places.
Frequently Asked Questions (FAQs)
Yes, besides the VBA Square Root function, you can use the “^” operator or the “Power” function to calculate the square root of a number in VBA.
For example: You can use result = number ^ 0.5 or result = WorksheetFunction.Power(number, 0.5) to achieve the same result.
When computing the square root, errors can happen. These can be handled by using error handling strategies like “On Error Resume Next” and “On Error GoTo”, especially when working with negative numbers. To prevent runtime errors, make sure you validate the input before using the VBA Square Root function.
A “Run-time error ’13’: Type mismatch” will appear if you try to calculate the square root of a non-numeric value or a cell reference holding non-numeric data. Before using the VBA Square Root function, make sure your input is a valid numeric value to prevent this error.
The VBA Square root calculations may not work if you encounter the following issues:
• Trying to compute a negative number’s square root without properly handling the negative input.
• Entering data that is not numeric into the VBA Square Root function, results in a type mismatch error.
• Writing VBA code with improper variable names or syntax.
• Inadequate error management that results in unanticipated runtime problems.
Recommended Articles
This has been a guide to VBA Square Root. Here we learn how to use Excel VBA Square Root (SQR) Function using syntax along with examples. You can learn more from the following articles –
Leave a Reply