How to perform standard deviation over desired range of cells using Excel VBA?
I am trying to perform the standard deviation of the cells of a range using this formula
Private Sub cmdStandardDeviation_Click()
txtStandardDeviation = StDev(Range("A19:I19"))
End Sub
But finally I realized that this could lead to further errors because I am working on so many cells.
What I am trying to do is use a custom form from VB Excel, I want the user to select the desired cells of the range, and then the user calculates the standard deviation with one click of a button and the result will be sent to the txtStandardDeviation input field. Rather than code it using the syntax above, because if new cells are added, the code I am writing is no longer valid.
I thought it was just after a few tests, but still I cannot do it. Thank you so much for your help!
source to share
You can approach this from several different angles.
- Using selection
- User enters range as text
- Getting a range dynamically from either Named Range or VBA
Choice
If the user has access to the worksheet directly, perhaps the solution would be to select the cells they want to calculate. You can then use any function within that range using Selection
.
Text input
Another way is to allow the user to enter text and parse it as a range. You can include different types of fields if that's what helps the user. For example, they can also enter ranges to exclude from the calculation.
Named range
You can use dynamic ranges without any user input whatsoever, if the user doesn't need control over specific cells. This will 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 a range using COUNTA
.
VBA
Or you can go as far as to specify a range to be calculated entirely with VBA. In this example, I am getting all the values using SpecialCells(xlCellTypeConstants)
.
Configuration example
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
source to share