Develop Customized Data Imputation Models With Excel VBA

Step 1: Setting Up the Worksheet

First, organize your worksheet with a dataset that contains missing values (blanks). For simplicity, assume that the missing values are in column B. The goal of the imputation model will be to replace these missing values with estimates based on neighboring data, the mean, or another technique of your choice.

Here’s an example worksheet layout:

• Column A: Data (Values for imputation)
• Column B: Values to be imputed (some are missing)

Step 2: Open Visual Basic For Applications (VBA) Editor

• Press Alt + F11 to open the VBA editor.
• In the Project Explorer on the left, find your workbook. Right-click on VBAProject (YourWorkbookName) and select Insert > Module.
• This will create a new module where you can write your VBA code.

Step 3: Writing VBA Code

Now, let’s write the VBA code for the Data Imputation Model. We’ll assume that the imputation will be based on the mean of neighboring values.

Sub ImputeData()
    Dim lastRow As Long
    Dim i As Long
    Dim sum As Double
    Dim count As Long
    Dim imputedValue As Double
    Dim ws As Worksheet
    ' Reference to the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in Column A and B
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row in Column B to check for missing data
    For i = 2 To lastRow
        If IsEmpty(ws.Cells(i, 2)) Then
            ' Initialize sum and count for neighboring values
            sum = 0
            count = 0
            ' Check previous value
            If i > 2 And Not IsEmpty(ws.Cells(i - 1, 1)) Then
                sum = sum + ws.Cells(i - 1, 1).Value
                count = count + 1
            End If           
            ' Check next value
            If i < lastRow And Not IsEmpty(ws.Cells(i + 1, 1)) Then
                sum = sum + ws.Cells(i + 1, 1).Value
                count = count + 1
            End If
            ' If count is greater than 0, calculate the mean of the neighboring values
            If count > 0 Then
                imputedValue = sum / count
                ws.Cells(i, 2).Value = imputedValue
            Else
                ' If no valid neighboring data, leave the cell empty or set to a default value
                ws.Cells(i, 2).Value = "No Data"
            End If
        End If
    Next i
End Sub

Step 4: Explanation

Let’s break down the key parts of the code:

• Setting Up Variables:
  • ws: Refers to the worksheet where the data resides.
  • lastRow: This finds the last row in column A, ensuring the code works for any number of rows in your dataset.
  • sum and count: Used to accumulate the sum of neighboring values and count the number of valid neighboring cells.
• Main Logic:
  • The For i = 2 To lastRow loop goes through each row in column B starting from row 2 (assuming row 1 contains headers).
  • For each empty cell in column B, the code checks its neighboring cells (both above and below) in column A.
  • The sum of valid neighboring values is calculated and the count of valid neighbors is kept track of.
  • The imputed value is calculated by averaging the neighboring values.
• Imputation Process:
  • If there are valid neighboring values, their mean is computed, and the missing value is replaced by this mean.
  • If no valid neighbors are found (i.e., there’s no data around it), the code marks the cell as « No Data » or leaves it empty.

Step 5: Running the Code

To run the VBA code:

1. Close the VBA editor (press Alt + Q).
2. Go back to Excel and press Alt + F8 to open the Macro dialog.
3. Select ImputeData and click Run.

Step 6: Output

After running the code, the missing values in column B will be filled based on the mean of the neighboring values from column A. If no valid neighbors are found, the missing value will be marked as « No Data ».

Example:

After running the imputation, the table might look like this:

In this case, empty cells have been filled with imputed values based on the available neighboring values.

This model is customizable based on the imputation logic you want to apply (e.g., using the mean of all values in the column, using a regression model, etc.).