How to do standard deviation on desired ranged of cells using Excel VBA?

Question

I am trying to do the standard deviation of ranged cells using this formula

Private Sub cmdStandardDeviation_Click() 
txtStandardDeviation = StDev(Range("A19:I19"))
End Sub

But finally i realized that this may result to further errors because im working on so many cells.

What i am trying to do is by using the userform from VB Excel, i want the user to select the desired ranged of cells and then the user calculate the standard deviation in a click of a button and the result will be posted to the input box of txtStandardDeviation. Instead of i'm coding it using the above syntax, because if new cells is added, the code that i'm writing is no longer valid.

I thought this was simple after a few trials, but still i can't manage to do this. Thank you so much for your help!

Answer 1

You could approach this from a few different angles.

• Using a selection
• Having the user input the range as text
• Getting the range dynamically either from a Named Range or VBA

Selection

If the user has access to the worksheet directly, a possible solution would be to have them select the cells they wish to calculate. Any functions could then be run on that range by using Selection.

Text input

Another way would be to have a way for the user to input text and parse that as a range. You could include various types of fields if that's something that would help the user. For example they could also input ranges to exclude from the calculation.

Named Range

You could use dynamic ranges without any user input at all if the user doesn't need control over specific cells. This would work well if you need to calculate all cells or a known group of cells. Here I have two formulas on a second sheet that calculate the height and width of the range with COUNTA.

VBA

Or you might go as far as to specify the range to be calculated entirely by using VBA. In this example I'm getting the all of the values with SpecialCells(xlCellTypeConstants).

---

Example Setup

Option Explicit

Private Function ParseRangeInput(textInput As String) As Range
On Error GoTo ErrHandler:
  Dim rangeOutput As Range
  Set rangeOutput = ActiveSheet.Range(textInput)
  Set ParseRangeInput = rangeOutput
Exit Function
ErrHandler:
  Debug.Print textInput & " Could not be converted to range."
  Set ParseRangeInput = Null
End Function

Private Function StDevWithSelection() As Double
On Error GoTo ErrHandler:
  StDevWithSelection = WorksheetFunction.StDev(Selection)
Exit Function
ErrHandler:
  Debug.Print "Couldn't get StDev with selection."
  StDevWithSelection = 0
End Function

Private Function StDevWithManualRange() As Double
On Error GoTo ErrHandler:
  Dim rangeTarget As Range
  Set rangeTarget = ParseRangeInput(TextManual.Text)
  StDevWithManualRange = WorksheetFunction.StDev(rangeTarget)
Exit Function
ErrHandler:
  Debug.Print "Couldn't get StDev with manual range."
  StDevWithManualRange = 0
End Function

Private Function StDevWithNamedRange() As Double
On Error GoTo ErrHandler:
  Dim rangeTarget As Range
  Set rangeTarget = ActiveSheet.Range("Numbers")
  StDevWithNamedRange = WorksheetFunction.StDev(rangeTarget)
Exit Function
ErrHandler:
  Debug.Print "Couldn't get StDev with named range."
  StDevWithNamedRange = 0
End Function

Private Function StDevWithVBARange() As Double
On Error GoTo ErrHandler:
  Dim rangeTarget As Range
  Set rangeTarget = ActiveSheet.Range("A:Z").Cells _
                               .SpecialCells(xlCellTypeConstants)
  StDevWithVBARange = WorksheetFunction.StDev(rangeTarget)
Exit Function
ErrHandler:
  Debug.Print "Couldn't get StDev with VBA range."
  StDevWithVBARange = 0
End Function

Private Sub ButtonGo_Click()
  If OptionSelection.Value = True Then
    TextResults.Text = StDevWithSelection
  ElseIf OptionManual.Value = True Then
    TextResults.Text = StDevWithManualRange
  ElseIf OptionNamed.Value = True Then
    TextResults.Text = StDevWithNamedRange
  ElseIf OptionVBA.Value = True Then
    TextResults.Text = StDevWithVBARange
  Else
    Debug.Print "No option selected."
  End If
End Sub