Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - Square Root in VBA

This VBA tutorial shows you how to take the square root of a number in VBA using the Sqr function. The VBA Sqr function accepts one argument - a positive number - and will return the square root of that number as a Double data type.

Let’s take a look at an example.

Example - Square Root in VBA

VBA Sqr Function

Sub VBA_Square_Root()
Dim d1 As Double
Dim d2 As Double

d1 = 144
d2 = Sqr(d1) 'Returns 12
Debug.Print d2
End Sub

Did you find this helpful?

Please support wellsr.com by downloading an add-in below. Your support helps me continue to post VBA tutorials like this one.


Mouse to Macro

Record your mouse clicks and cursor movements and convert them directly into VBA macros so you can automate your mouse.



Import hundreds of macro examples from wellsr.com without ever leaving Excel and organize your personal macro library with this free add-in.


CF Shapes

Build stunning dashboards by dynamically controlling your shapes with this add-in that enables conditional formatting for shapes.

Tutorial - Square Root in VBA

How to find the square root in VBA

The VBA Sqr function returns the square root of a number. The Sqr function accepts one argument: a positive number. Here’s the syntax for the Sqr function:


In the example above, the “Number” argument is 144 so the value of d2 is 12 since 12x12=144.

Common Errors with the Sqr function

Like I said earlier, the Sqr function accepts a positive number. Any number multiplied by itself returns a positive number so, ignoring imaginary numbers, you can’t take the square root of a negative number.

Because of that, the VBA Sqr function will produce a “Run-time error ‘5’ Invalid procedure call or argument” error when attempting to take a square root of a negative number.

Invalid Procedure Call Error

Similarly, you will get a “Run-time error ‘13’ Type Mismatch” error when you try to take the square root of something that isn’t a number.

Type Mismatch Error

With that said, the Sqr function isn’t dumb. If you try to take the square root of a string that represents a number, it will properly process the string as a number and return the square root. Here’s an example to demonstrate what I mean:

Sub VBA_Square_Root_String()
Dim val1 As String
Dim val2 As String

val1 = "16"
val2 = Sqr(val1) 'Returns "4"
Debug.Print val2
End Sub

The result of applying the square root function in this example is a string with a value of “4.” If the variable val2 was instead declared as a double, the VBA square root function would be smart enough to process the string “16” as a number and return a double with a numeric value of 4.

Application Ideas

You’ll want to use the Sqr function anytime you need to calculate the square root in VBA.

It’s wise to use the Sgn function to test for a positive number or the Abs function to return the absolute value of your number before passing it as an argument to the VBA Sqr function.

Closing Thoughts

If you liked this VBA tutorial, I hope you’ll shop my Excel Add-ins store to show your support for wellsr.com. Without revenue from great readers like you, it’s difficult to grow the site and reach more users.

Please take a moment to share this article with your friends on Facebook, Twitter, and Google+.

About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.