VBA: How to Calculate Standard Deviation of Range

You can use the following basic syntax to calculate the standard deviation of values in a range using VBA:

Sub StDevRange()
    Range("D2") = WorksheetFunction.StDev(Range("B2:B11"))
End Sub

This particular example calculates the standard deviation of values in the range B2:B11 and assigns the result to cell D2.

If you would instead like to display the standard deviation of values in a message box, you can use the following syntax:

Sub StDevRange()
    'Create variable to store standard deviation of values
    Dim stdev As Single
    
    'Calculate standard deviation of values in range
    stdev = WorksheetFunction.StDev(Range("B2:B11"))
    
    'Display the result
    MsgBox "Standard Deviation of Values in Range: " & stdev 
End Sub

The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:

Example 1: Calculate Standard Deviation of Range Using VBA and Display Results in Cell

Suppose we would like to calculate the standard deviation of values in the points column and output the results in a specific cell.

We can create the following macro to do so:

Sub StDevRange()
    Range("D2") = WorksheetFunction.StDev(Range("B2:B11"))
End Sub

When we run this macro, we receive the following output:

Notice that cell D2 contains a value of 11.93734.

This tells us that the standard deviation of values in the points column is 245.

Example 2: Calculate Standard Deviation of Range Using VBA and Display Results in Message Box

Suppose we would instead like to calculate the standard deviation of values in the points column and output the results in a message box.

We can create the following macro to do so:

Sub StDevRange()
    'Create variable to store standard deviation of values
    Dim stdev As Single
    
    'Calculate standard deviation of values in range
    stdev = WorksheetFunction.StDev(Range("B2:B11"))
    
    'Display the result
    MsgBox "Standard Deviation of Values in Range: " & stdev 
End Sub

When we run this macro, we receive the following output:

The message box tells us that the standard deviation of values in the range B2:B11 is 11.937.

Note that in this example we calculated the standard deviation of values in the range B2:B11.

However, if you’d like to instead calculate the standard deviation of values in an entire column you could type B:B instead.

This will calculate the sum of values for every cell in column B.

Note: You can find the complete documentation for the VBA StDev method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Calculate Average Value of Range
VBA: How to Count Number of Rows in Range
VBA: How to Write SUMIF and SUMIFS Functions