WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Square Root

VBA Square Root

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.

VBA SQR Syntax

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 functions as well as VBA function, but in a worksheet, it is available as SQRT.

VBA Square Root 1

Examples of Square Root in Excel VBA

You can download this VBA SQRT Function Excel Template here – VBA SQRT Function Excel Template

Example #1

Now we will try to write code to find the square root for the number 64.

First, start the VBA subroutine.

Code:

Sub Square_Root_Example()

End Sub

VBA Square root Example 1

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

VBA Square root Example 1-1

For the variable “ActualNumber,” assign the value of number 64.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Square_Root_Example()

  Dim ActualNumber As Integer
  Dim SquareNumber As Integer

  ActualNumber = 64

End Sub

VBA Square root Example 1-2

For another variable, we will assign the square root value, enter the variable name, put equal sing, and open the SQR function.

VBA Square root Example 1-3

The only argument of the SQR function is “Number” since we have already assigned the number 64 to the variable “ActualNumber” let’s supply the same variable name in the SQR function.

VBA Square root Example 1-4

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

Square root Example 1-5

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.

VBA SQRT Example 1-6.gif

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

Example 2

The actual square root number result for 70 is 8.3666, but with VBA, it will round to the nearest integer value 8.

Example 2-1

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

Example 2-2

Now run the code manually or through the F5 key and see the result.

Example 2 - Output

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

Recommended Articles

This has been a guide to VBA Square Root Function. Here we discuss how to calculate square root using the SQR function in excel VBA with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • Project Password in VBA
  • VBA Compare String
  • On Error Resume Next in VBA
  • VBA LBound
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA SQRT Function Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More