Audit request

[Playthisontheradio]

Hi All!
As a learning exercise, I got the code below to do what I want - grab a set data and have the macro setup my statistical analysis formulas. This was very challenging for me as a novice.

I wanted to ask the forum for any color & commentary as to how & why this works, best practices, how to improve, and relevant resources. Would really appreciate any feedback.

This is in the ThisWorkbook module/object under Microsoft Excel Objects.

Sub mystatcalc()
'Declare name of range to be set by user
Dim myData As Range
'Declare name of string type to be used in statistical calculation formulas placed into cells (stdev."S", for example)
Dim dType As String

'Get range from user to set myData
Set myData = Application.InputBox("Range:", Title:="Select data", Type:=8)
'Get "s" or "p" for statistical formulas to be placed into cells on workbook
dType = Application.InputBox("Population or sample:", Title:="Indicate data as sample or population", Type:=2)

'Put formulas into cells below myData as defined by user
Cells(myData.Rows.Count + myData.Row + 1, myData.Column).Formula = "=Average(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 1, myData.Column + 1).Value = "Average"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column).Formula = "=stdev." & dType & "(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column + 1).Value = "Stdev"

End Sub

Thanks!

 

[Hui]

How it works
The code is reasonably commented and self descriptive
But lets step through it

'Get range from user to set myData
Set myData = Application.InputBox("Range:", Title:="Select data", Type:=8)

This asks for a InputBox and you select a Range (Type=8)
The code assigns the range to a Variable myData

'Get "s" or "p" for statistical formulas to be placed into cells on workbook
dType = Application.InputBox("Population or sample:", Title:="Indicate data as sample or population", Type:=2)

This gets another InputBox and asks for a P or S for Population or Sample

'Put formulas into cells below myData as defined by user
Cells(myData.Rows.Count + myData.Row + 1, myData.Column).Formula = "=Average(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 1, myData.Column + 1).Value = "Average"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column).Formula = "=stdev." & dType & "(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column + 1).Value = "Stdev"

These 4 rows are doing calculations and placing them into the appropriate answer cells

Cells(myData.Rows.Count + myData.Row + 1, myData.Column)
is a cell which is Row Number: myData.Rows.Count (The number of Data Rows selected) + MyData.Row (The top row of the data) + 1 = The second row after you data
Column Number: The first column of your data (myData.Column)

.Formula = "=Average(" & myData.Address & ")"
Means put into the cell a Formula which is =Average(My data address)

 

[Playthisontheradio]

Thanks for taking a look! I'm curious to know how a pro would accomplish the same steps: be able to grab a range of data and have the VBA spit out calculations of interest below the range. Another improvement I'd like to make is to limit the second input box to only allow "p" or "s," which are the only relevant variables to the formulas that the code inserts into cells.

 

[Hui]

Sub mystatcalc()
'Declare name of range to be set by user
Dim myData As Range
'Declare name of string type to be used in statistical calculation formulas placed into cells (stdev."S", for example)
Dim dType As String

'Get range from user to set myData
Set myData = Application.InputBox("Range:", Title:="Select data", Type:=8)

10:
'Get "s" or "p" for statistical formulas to be placed into cells on workbook
dType = Application.InputBox("Population or sample:", Title:="Indicate data as sample or population", Type:=2)

If UCase(dType) = "P" Or UCase(dType) = "S" Then GoTo 20
GoTo 10

20:
'Put formulas into cells below myData as defined by user
Cells(myData.Rows.Count + myData.Row + 1, myData.Column).Formula = "=Average(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 1, myData.Column + 1).Value = "Average"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column).Formula = "=stdev." & dType & "(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column + 1).Value = "Stdev"

End Sub

 

[Playthisontheradio]

Sub mystatcalc()
'Declare name of range to be set by user
Dim myData As Range
'Declare name of string type to be used in statistical calculation formulas placed into cells (stdev."S", for example)
Dim dType As String

'Get range from user to set myData
Set myData = Application.InputBox("Range:", Title:="Select data", Type:=8)

10:
'Get "s" or "p" for statistical formulas to be placed into cells on workbook
dType = Application.InputBox("Population or sample:", Title:="Indicate data as sample or population", Type:=2)

If UCase(dType) = "P" Or UCase(dType) = "S" Then GoTo 20
GoTo 10

20:
'Put formulas into cells below myData as defined by user
Cells(myData.Rows.Count + myData.Row + 1, myData.Column).Formula = "=Average(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 1, myData.Column + 1).Value = "Average"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column).Formula = "=stdev." & dType & "(" & myData.Address & ")"
Cells(myData.Rows.Count + myData.Row + 2, myData.Column + 1).Value = "Stdev"

End Sub

Thanks again Hui!