VBA Code to Standardize a User Specified Range by Column

Hey guys,

I wanted to share some new code with you. The code below allows a user to specify a range of data and then the code will output the standardized values (mean=0 and standard deviation 1) for each of the columns. This can be a big time saver over Excel’s standardize function, which requires the user to input the mean and standard deviation and only standardizes one cell at a time. Also, this allows the user to specify specifically how they want the standard deviation calculated.

I hope that you guys enjoy the code. I welcome any feedback!

Thanks,
Adam

 

 

Sub standardize_range()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This code allows a user to select a group of data organized by columns and it will provide a standardized
'output with mean 0, variance 1
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim variables As Range
Dim change As Range
Set variables = Application.InputBox("Select the row of variable names:", Default:=Range("a1:c1").Address, Type:=8)
Set change = Application.InputBox("Select the row of cells containing the data to standardize:", Default:=Range("a2:c14").Address, Type:=8)
Dim Total, Total_sq, Average, Variance As Double




numRow = change.Rows.Count
numCol = change.Columns.Count
'Debug.Print "numrow=" & numrow & " numcol=" & numCol




'1. Calculate Average and Std for all Columns
'Note: This calculation for Variance and StdDev differs from Excel's slightly



For j = 1 To numCol
    
    ReDim col(numRow) As Double
    Total = 0
    Total_sq = 0
    
    For i = 1 To numRow
        col(i) = change.Cells(i, j)
        'Debug.Print col(i)
        Total = Total + col(i)
        Total_sq = Total_sq + col(i) ^ 2
    Next i
    
    Average = Total / numRow
    Variance = (Total_sq / numRow) - (Average) ^ 2
    Std = Variance ^ (1 / 2)
        'Debug.Print "Total="; Total & " Average="; Average & " Total_sq=" & Total_sq & " Variance=" & Variance _
        '& " Std=" & Std
    
    'Store values
    change.Cells(numRow + 1, j).Value = Average
    change.Cells(numRow + 2, j).Value = Variance
    change.Cells(numRow + 3, j).Value = Std
    
Next j

'2. Create Sheet to store standardized values
Application.DisplayAlerts = False
Sheets("Standardized_Values").Delete
Sheets.Add.Name = "Standardized_Values"

    'Write variable names
    For k = 1 To numCol
    Sheets("Standardized_Values").Cells(1, k).Value = variables.Cells(1, k) & "_std"
    Next k

'3. Compute standardized values
For n = 1 To numCol

  
  
    For m = 1 To numRow

        Sheets("Standardized_Values").Cells(m + 1, n).Value = (change.Cells(m, n).Value - change.Cells(numRow + 1, n).Value) / _
        change.Cells(numRow + 3, n).Value
    Next m
Next n

End Sub