Automate data normalization in Excel VBA

Objective:

Normalize the data in a range of numerical values (e.g., values in a column).

VBA Code:

Sub NormalizeData()
    Dim ws As Worksheet
    Dim Range As Range    Dim Cell As Range
    Dim MinValue As Double    Dim MaxValue As Double
    Dim Column As Integer    
    ' Define the worksheet (change "Sheet1" to the actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")    
    ' Define the range of data to normalize (e.g., column B from row 2 to 100)
    Set Range = ws.Range("B2:B100")    
    ' Find the minimum and maximum values in the range
    MinValue = Application.WorksheetFunction.Min(Range)
    MaxValue = Application.WorksheetFunction.Max(Range)    
    ' Normalize each value in the range
    For Each Cell In Range
        If MaxValue <> MinValue Then
            ' Normalization: (Value - Min) / (Max - Min)
            Cell.Value = (Cell.Value - MinValue) / (MaxValue - MinValue)
        Else
            ' If all values are the same, assign a constant value (e.g., 0)
            Cell.Value = 0
        End If
    Next Cell    
    MsgBox "Normalization Complete!"    
End Sub

Explanation of the Code:

1. Declaration of Variables:
   • ws: Refers to the worksheet containing the data to be normalized.
   • Range: Defines the range of data you want to normalize.
   • MinValue and MaxValue: Variables used to store the minimum and maximum values of the data range.
   • Cell: Represents each individual cell in the range that you will loop through.
2. Defining the Data Range:
   • Here, the data to normalize is in column B, from row 2 to row 100 (B2:B100). You can adjust this range based on your needs.
3. Calculating the Minimum and Maximum Values:
   • WorksheetFunction.Min and WorksheetFunction.Max are used to find the minimum and maximum values in the specified range.
4. Loop to Normalize Each Cell:
   • For each cell in the range, the normalization formula is applied:
     Normalized Value = (Current Value – Min) / (Max – Min)
   • If all values in the range are the same (i.e., if MaxValue = MinValue), a value of 0 is assigned (you can choose another value if needed).
5. Confirmation Message:
   • After the normalization is complete, a message is displayed to inform the user that the operation has finished.

Note:

• You can adjust the range of data to normalize by changing the range in the line Set Range = ws.Range("B2:B100").
• This method normalizes the data in the specified range, but you can adapt it to normalize multiple columns or extend the logic to different conditions.

This allows you to automatically normalize data in Excel with a single execution of VBA code.