Automate decision-making processes in Excel using VBA

Automating decision-making processes in Excel using VBA (Visual Basic for Applications) can be a powerful way to streamline repetitive tasks. This type of automation can include processes for selecting, analyzing, and executing actions based on predefined criteria. Below is a detailed example of VBA code that can automate a decision-making process.

Scenario

Let’s imagine a scenario where we have a list of projects, and for each project, there are several criteria such as cost, risk, and impact. The final decision of whether to « accept » or « reject » the project will be based on these criteria. If the cost is below a certain value, the risk is low, and the impact is high, the project will be accepted. Otherwise, it will be rejected.

Steps:

1. Create a worksheet with the data.
2. Write the VBA code to automate the decision.
3. Display a message or notification of the decision.

1. Create the Worksheet

Imagine a worksheet called Projects with the following columns:

In this example:

• Column A: Project name
• Column B: Cost in €
• Column C: Risk level (Low, Medium, High)
• Column D: Impact level (Low, Medium, High)
• Column E: Final decision (Accept or Reject), which will be automated using VBA.

2. VBA Code to Automate the Decision-Making Process

Here is the VBA code that analyzes each project based on the cost, risk, and impact criteria, and then makes the corresponding decision.

VBA Code

Sub AutomateDecision()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cost As Double
    Dim risk As String
    Dim impact As String
    Dim decision As String   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Projects")   
    ' Find the last row of data (based on column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Loop through each project
    For i = 2 To lastRow ' Start from row 2 to skip the headers
        cost = ws.Cells(i, 2).Value ' Project cost
        risk = ws.Cells(i, 3).Value ' Project risk
        impact = ws.Cells(i, 4).Value ' Project impact       
        ' Decision-making logic
        If cost < 120000 And risk = "Low" And impact = "High" Then
            decision = "Accept"
        ElseIf cost >= 120000 And risk = "High" Then
            decision = "Reject"
        ElseIf cost < 80000 And impact = "Medium" Then
            decision = "Reject"
        Else
            decision = "Accept"
        End If       
        ' Assign the decision to column E
        ws.Cells(i, 5).Value = decision
    Next i  
    ' Message box when the process is completed
    MsgBox "The decision process has been completed.", vbInformation, "Automation Completed"
End Sub

3. Explanation of the Code

Variable Declarations

Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cost As Double
Dim risk As String
Dim impact As String
Dim decision As String

• ws: Reference to the worksheet containing the data.
• lastRow: The last row used in the worksheet to loop through all the projects.
• i: Loop index for iterating through the rows.
• cost, risk, impact: Variables used to store the values of each project’s criteria.
• decision: Variable to store the final decision for each project.

Find the Last Row

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

• This line finds the last used row in column A (assuming that each project has a name in this column).

Loop Through Projects

For i = 2 To lastRow

• We start at row 2 to skip the headers and loop through each project.

Read Criteria Values

cost = ws.Cells(i, 2).Value
risk = ws.Cells(i, 3).Value
impact = ws.Cells(i, 4).Value

• These lines retrieve the values of cost, risk, and impact for each project.

Decision-Making Logic

If cost < 120000 And risk = "Low" And impact = "High" Then
    decision = "Accept"
ElseIf cost >= 120000 And risk = "High" Then
    decision = "Reject"
ElseIf cost < 80000 And impact = "Medium" Then
    decision = "Reject"
Else
    decision = "Accept"
End If

• This section contains the decision-making logic based on the criteria. For example:
  • If the cost is less than 120,000 €, the risk is low, and the impact is high, the project is accepted.
  • If the cost is above or equal to 120,000 € and the risk is high, the project is rejected.
  • Additional conditions can be added for more flexibility.

Assign the Decision to the Worksheet

ws.Cells(i, 5).Value = decision

• This line places the decision in column E (the decision column) for each project.

Completion Message

MsgBox "The decision process has been completed.", vbInformation, "Automation Completed"

• A message box pops up to inform the user that the automation process has finished.

4. Running the Code

Open Excel and press Alt + F11 to open the VBA editor.

In the editor, go to Insert > Module to create a new module.

Copy and paste the VBA code above into the module.

Close the VBA editor and return to Excel.

Press Alt + F8, select the AutomateDecision macro, and click Run.

Conclusion

This VBA code automates the decision-making process based on predefined criteria in an Excel sheet. You can customize the code to fit other decision-making scenarios depending on your project’s requirements. The code can be expanded with additional conditions or more complex logic as needed