Intro

Sespi Cola want to automate how their salespersons are performing.

What you will learn

• Primarily:
  • Excel automation
• Secondarily:
  • Using Excel macros/VBA
  • Working with variables

Client Name

Sespi Cola

Process Name

ExcelEmployees

Description

Sespi Cola wants your help to do Excel Automation for their employee data:

• Download the Excel book here
• Yearly Sales
  • Calculate the Yearly Sales (Q1Q2+Q3Q4) and write the result for each row in the Yearly Sales column
• Monthly Sales
  • Calculate the monthly sales for each of the employees in the Excel sheet (Employees) and write it to the Monthly Sales column
• Color the Monthly Sales cells based on the value of them:
  • If Monthly Sales < 10,000: Red
  • If Monthly Sales >= 10,000 and Monthly Sales <= 30,000: Yellow
  • If Monthly Sales > 30,000: Green

Hints

• Set variable, store you Excel Path here
• Launch Excel
• Close Excel in the end
• Set Active Excel Worksheet
• Get first free column/row from Excel worksheet
• Read from Excel worksheet
• Yearly Sales
  • For each
  • Convert text to number
    • Text to convert: %Employee['Q1Q2']%
    • Variables produced: Q1Q2
  • Convert text to number
    • Text to convert: %Employee['Q3Q4']%
    • Variables produced: Q3Q4
  • Create an index variable to get the right row
  • Write to Excel worksheet
    • %Q1Q2 + Q3Q4%
• Monthly Sales
  • Set variable
    • Variable: Monthlysales, Value: %(Q1Q2 + Q3Q4) / 12%
  • Write to Excel worksheet
    • %(Q1Q2 + Q3Q4) / 12%
• Color
  • We need to enable macros for our Excel Book
    • Save it as a .xlsm file
    • If your Developer menu tab in Excel is not enabled:
      • File - Options - Customize Ribbon and make sure Developer is enabled.
    • Developer - Visual Basic
      • Right Click and Insert/Module
    • Paste in the VBA code below
      • MonthlySales and RowNumber are (ingoing) arguments
  • Run Excel macro
    • ColorCode;%MonthlySales%;%RowNumber%

Sub ColorCode(MonthlySales As Double, RowNumber As Integer)
'Paint red if it's a bad performing employee
If MonthlySales < 10000 Then
    Range("F" & RowNumber).Interior.ColorIndex = 3
'Paint yellow if it's an average performing employee
ElseIf MonthlySales >= 10000 And MonthlySales <= 30000 Then
    Range("F" & RowNumber).Interior.ColorIndex = 6
'Paint green if it's a good performing employee
ElseIf MonthlySales > 30000 Then
    Range("F" & RowNumber).Interior.ColorIndex = 4
End If
End Sub

Video Guide