How to Calculate Standard Deviations in Excel Visual Basic

by Contributor

Excel is the spreadsheet program included in the Microsoft Office Suite of productivity software. A spreadsheet program manages data through a series of tables. Each data value is placed within an individual cell within the table. Values can be manipulated through a set of built-in functions. Standard deviation is average deviation from the mean of the values of a set. Simply put, it identifies the precision of a set of values. Standard deviation can be calculated using the built-in function STDEV in Excel, or Visual Basic for Applications (VBA).

Built-in Standard Deviation Function

1

Open Excel and the data set where you want to find the standard deviation.

2

Type"=STDEV(" in the cell where you want to display the standard deviation. Do not press "Enter."

3

Holding down shift, select the range of cells containing the data points. If the cells are not continuous, hold down Ctrl while selecting the non-continuous cells. This should place a series of cells names in the formula bar after "=STDEV(".

4

Type ")" in the formula bar and press "Enter." The standard deviation will be calculated using the unbiased method.

Using Built-in Functions in VBA

1

This same built-in function can be used in a VBA script to calculate standard deviation. Open VBA and click on the end of the module where you are going to calculate the standard deviation.

2

Type "Function StDev( Rng As Range)" and then press "Enter." This should also create a new line "End Function."

3

On the line between "Function ..." and "End Function," type "StDev = Application.WorksheetFunction.StDev( Rng)" and then press "Enter."

4

To calculate the standard deviation in VBA, simply use the function StDev().

Tip

  • check This same method can be used to access any of the built-in functions of Excel within VBA.

Items you will need

Photo Credits

  • photo_camera BananaStock/BananaStock/Getty Images