Excel

Dynamic Earned Value Management (EVM) Chart Using VBA



66% Off Microsoft Excel Value Bundle

This is a beginner level tutorial, but I will assume you know how to do basic things like open the “Developer” pane. You can learn more about that in Excel Visual Basic #1: Getting Started.

The Below Video has no Sound, it is intended to just show the application in action

Using the Excel EVM template I provide (Earned Value Management (EVM) Excel Template), the following tutorial shows how to add simple basic functionality that automatically takes the EVM calculations and places them in the chart contained in the template. This removes the need to manually enter in each value from the calculator.

Earned Value Management Excel Template

The template is already set up to do the EVM calculations using formulas. We are simply adding two buttons.

Requirements:

  1. Create two buttons
  2. One button, the “Save Values” button, will copy the EVM calculations and place them in the chart for saving and tracking
  3. One button, the “Clear Chart” button, will clean the chart up by clearing out all of the contents

1. Create the Buttons

For more information on button creation in Excel VBA, see earlier tutorials.

We are creating two buttons. Within the template provided (Earned Value Management (EVM) Excel Template) navigate to the “Developer” tab. If you do not have a “Developer” tab available see Excel Visual Basic #1: Getting Started to learn how to enable the “Developer” tab.

For each button, you will go to the “Insert” dropdown within the “Developer” tab and create an ActiveX button control.

Insert Under Developer Tab

ActiveX Button Control Object

For each button, click a spot on the Excel sheet to create it.

To modify the properties, ensure that a button is active and click on “Properties”

View Component Properties in Excel VBA

For the “Save Values” button, the properties should resemble the below:

Properties for Save Value ButtonFor the “Clear Chart” button, the properties should resemble the below:

Clear Chart button properties

When completed, you can arrange the buttons however you want by moving them around – assuming that the sheet remains in “Design Mode”.

Design Mode for Excel VBA development

Arrangement of buttons:

EVM VBA Excel Tutorial

2. Create the “Save Values” code

We will create functions to reference all of the cells we will need to make this happen. For the possible expansion of the number of rows in the table contained in the template, we will also create two functions that will grab the starting row of the chart and the maximum number of allowed rows in the chart.

Starting Row and Maximum Allowed Chart Rows

Private Function MAX_NUMBER_OF_ROWS() As Integer
   MAX_NUMBER_OF_ROWS = 20
End Function
Private Function CHART_ROW_START() As Integer
   CHART_ROW_START = 12
End Function

Single EVM Calculator Cells

Single EVM Values

'EVM Cells
Private Function PlannedValue() As Range
   Set PlannedValue = Range("H4")
End Function
Private Function EarnedValue() As Range
   Set EarnedValue = Range("H5")
End Function
Private Function ActualCost() As Range
   Set ActualCost = Range("D8")
End Function
Private Function CostVariance() As Range
   Set CostVariance = Range("H6")
End Function
Private Function CostPerformanceIndex() As Range
   Set CostPerformanceIndex = Range("H7")
End Function
Private Function ScheduleVariance() As Range
   Set ScheduleVariance = Range("H8")
End Function
Private Function SchedulePerformanceIndex() As Range
   Set SchedulePerformanceIndex = Range("K4")
End Function
Private Function EstimateAtCompletion() As Range
   Set EstimateAtCompletion = Range("K5")
End Function
Private Function EstimateToCompletion() As Range
   Set EstimateToCompletion = Range("K6")
End Function
Private Function VarianceAtCompletion() As Range
   Set VarianceAtCompletion = Range("K7")
End Function
Private Function ToCompletePerformanceIndex() As Range
   Set ToCompletePerformanceIndex = Range("K8")
End Function

EVM single values code

EVM Variable Table Cells

EVM Template Table Values

What is important here is the column. In part because of our inclusion of a variable maximum limit and a variable row counter, we only want to worry about the columns of each value. The actual row will be a variable that is passed while the script is running.

'EVM Table Ranges
Private Function Date_Table(ByVal row As Integer) As Range
   Set Date_Table = Range("A" + CStr(row))
End Function
Private Function PlannedValue_Table(ByVal row As Integer) As Range
   Set PlannedValue_Table = Range("B" + CStr(row))
End Function
Private Function EarnedValue_Table(ByVal row As Integer) As Range
   Set EarnedValue_Table = Range("C" + CStr(row))
End Function
Private Function ActualCost_Table(ByVal row As Integer) As Range
   Set ActualCost_Table = Range("D" + CStr(row))
End Function
Private Function CostVariance_Table(ByVal row As Integer) As Range
   Set CostVariance_Table = Range("E" + CStr(row))
End Function
Private Function CostPerformanceIndex_Table(ByVal row As Integer) As Range
   Set CostPerformanceIndex_Table = Range("F" + CStr(row))
End Function
Private Function ScheduleVariance_Table(ByVal row As Integer) As Range
   Set ScheduleVariance_Table = Range("G" + CStr(row))
End Function
Private Function SchedulePerformanceIndex_Table(ByVal row As Integer) As Range
   Set SchedulePerformanceIndex_Table = Range("H" + CStr(row))
End Function
Private Function EstimateAtCompletion_Table(ByVal row As Integer) As Range
   Set EstimateAtCompletion_Table = Range("I" + CStr(row))
End Function
Private Function EstimateToCompletion_Table(ByVal row As Integer) As Range
   Set EstimateToCompletion_Table = Range("J" + CStr(row))
End Function
Private Function VarianceAtCompletion_Table(ByVal row As Integer) As Range
   Set VarianceAtCompletion_Table = Range("K" + CStr(row))
End Function
Private Function ToCompletePerformanceIndex_Table(ByVal row As Integer) As Range
   Set ToCompletePerformanceIndex_Table = Range("L" + CStr(row))
End Function

EVM Code

Create the “Save Values” Functionality

Pushing the “Save Values” button puts the code above together. It should take the values from the EVM Single Values and place them into the table. Every push should start a new row.

Private Sub SaveValues_Button_Click()
    Dim blankCounter, rowCounter As Integer
    blankCounter = CHART_ROW_START
    rowCounter = 0
    
    While rowCounter < MAX_NUMBER_OF_ROWS
        Do While blankCounter < CHART_ROW_START + MAX_NUMBER_OF_ROWS
            If Date_Table(blankCounter) = "" Then
               Date_Table(blankCounter).Value = Date
               PlannedValue_Table(blankCounter).Value = PlannedValue().Value
               EarnedValue_Table(blankCounter).Value = EarnedValue().Value
               ActualCost_Table(blankCounter).Value = ActualCost().Value
               CostVariance_Table(blankCounter).Value = CostVariance().Value
               CostPerformanceIndex_Table(blankCounter).Value = CostPerformanceIndex().Value
               ScheduleVariance_Table(blankCounter).Value = ScheduleVariance().Value
               SchedulePerformanceIndex_Table(blankCounter).Value = SchedulePerformanceIndex().Value
               EstimateAtCompletion_Table(blankCounter).Value = EstimateAtCompletion().Value
               EstimateToCompletion_Table(blankCounter).Value = EstimateToCompletion().Value
               VarianceAtCompletion_Table(blankCounter).Value = VarianceAtCompletion().Value
               ToCompletePerformanceIndex_Table(blankCounter).Value = ToCompletePerformanceIndex().Value
               
               rowCounter = MAX_NUMBER_OF_ROWS
               Exit Do
            End If
            blankCounter = blankCounter + 1
        Loop
        rowCounter = rowCounter + 1
    Wend
End Sub

EVMSaveCode

3. Create the “Clear Chart” Code

This is just simply removing the contents from the table by putting in a blank value.

Private Sub Clear_Button_Click()
    Dim blankCounter As Integer
    blankCounter = CHART_ROW_START
    
    Do While blankCounter < CHART_ROW_START + MAX_NUMBER_OF_ROWS
       
        Date_Table(blankCounter).Value = ""
        PlannedValue_Table(blankCounter).Value = ""
        EarnedValue_Table(blankCounter).Value = ""
        ActualCost_Table(blankCounter).Value = ""
        CostVariance_Table(blankCounter).Value = ""
        CostPerformanceIndex_Table(blankCounter).Value = ""
        ScheduleVariance_Table(blankCounter).Value = ""
        SchedulePerformanceIndex_Table(blankCounter).Value = ""
        EstimateAtCompletion_Table(blankCounter).Value = ""
        EstimateToCompletion_Table(blankCounter).Value = ""
        VarianceAtCompletion_Table(blankCounter).Value = ""
        ToCompletePerformanceIndex_Table(blankCounter).Value = ""
           
        blankCounter = blankCounter + 1
    Loop
End Sub

Clear EVM Table

The Entire Application

Private Function MAX_NUMBER_OF_ROWS() As Integer
   MAX_NUMBER_OF_ROWS = 20
End Function
Private Function CHART_ROW_START() As Integer
   CHART_ROW_START = 12
End Function


'EVM Cells
Private Function PlannedValue() As Range
   Set PlannedValue = Range("H4")
End Function
Private Function EarnedValue() As Range
   Set EarnedValue = Range("H5")
End Function
Private Function ActualCost() As Range
   Set ActualCost = Range("D8")
End Function
Private Function CostVariance() As Range
   Set CostVariance = Range("H6")
End Function
Private Function CostPerformanceIndex() As Range
   Set CostPerformanceIndex = Range("H7")
End Function
Private Function ScheduleVariance() As Range
   Set ScheduleVariance = Range("H8")
End Function
Private Function SchedulePerformanceIndex() As Range
   Set SchedulePerformanceIndex = Range("K4")
End Function
Private Function EstimateAtCompletion() As Range
   Set EstimateAtCompletion = Range("K5")
End Function
Private Function EstimateToCompletion() As Range
   Set EstimateToCompletion = Range("K6")
End Function
Private Function VarianceAtCompletion() As Range
   Set VarianceAtCompletion = Range("K7")
End Function
Private Function ToCompletePerformanceIndex() As Range
   Set ToCompletePerformanceIndex = Range("K8")
End Function


'EVM Table Ranges
Private Function Date_Table(ByVal row As Integer) As Range
   Set Date_Table = Range("A" + CStr(row))
End Function
Private Function PlannedValue_Table(ByVal row As Integer) As Range
   Set PlannedValue_Table = Range("B" + CStr(row))
End Function
Private Function EarnedValue_Table(ByVal row As Integer) As Range
   Set EarnedValue_Table = Range("C" + CStr(row))
End Function
Private Function ActualCost_Table(ByVal row As Integer) As Range
   Set ActualCost_Table = Range("D" + CStr(row))
End Function
Private Function CostVariance_Table(ByVal row As Integer) As Range
   Set CostVariance_Table = Range("E" + CStr(row))
End Function
Private Function CostPerformanceIndex_Table(ByVal row As Integer) As Range
   Set CostPerformanceIndex_Table = Range("F" + CStr(row))
End Function
Private Function ScheduleVariance_Table(ByVal row As Integer) As Range
   Set ScheduleVariance_Table = Range("G" + CStr(row))
End Function
Private Function SchedulePerformanceIndex_Table(ByVal row As Integer) As Range
   Set SchedulePerformanceIndex_Table = Range("H" + CStr(row))
End Function
Private Function EstimateAtCompletion_Table(ByVal row As Integer) As Range
   Set EstimateAtCompletion_Table = Range("I" + CStr(row))
End Function
Private Function EstimateToCompletion_Table(ByVal row As Integer) As Range
   Set EstimateToCompletion_Table = Range("J" + CStr(row))
End Function
Private Function VarianceAtCompletion_Table(ByVal row As Integer) As Range
   Set VarianceAtCompletion_Table = Range("K" + CStr(row))
End Function
Private Function ToCompletePerformanceIndex_Table(ByVal row As Integer) As Range
   Set ToCompletePerformanceIndex_Table = Range("L" + CStr(row))
End Function


Private Sub SaveValues_Button_Click()
    Dim blankCounter, rowCounter As Integer
    blankCounter = CHART_ROW_START
    rowCounter = 0
    
    While rowCounter < MAX_NUMBER_OF_ROWS
        Do While blankCounter < CHART_ROW_START + MAX_NUMBER_OF_ROWS
            If Date_Table(blankCounter) = "" Then
               Date_Table(blankCounter).Value = Date
               PlannedValue_Table(blankCounter).Value = PlannedValue().Value
               EarnedValue_Table(blankCounter).Value = EarnedValue().Value
               ActualCost_Table(blankCounter).Value = ActualCost().Value
               CostVariance_Table(blankCounter).Value = CostVariance().Value
               CostPerformanceIndex_Table(blankCounter).Value = CostPerformanceIndex().Value
               ScheduleVariance_Table(blankCounter).Value = ScheduleVariance().Value
               SchedulePerformanceIndex_Table(blankCounter).Value = SchedulePerformanceIndex().Value
               EstimateAtCompletion_Table(blankCounter).Value = EstimateAtCompletion().Value
               EstimateToCompletion_Table(blankCounter).Value = EstimateToCompletion().Value
               VarianceAtCompletion_Table(blankCounter).Value = VarianceAtCompletion().Value
               ToCompletePerformanceIndex_Table(blankCounter).Value = ToCompletePerformanceIndex().Value
               
               rowCounter = MAX_NUMBER_OF_ROWS
               Exit Do
            End If
            blankCounter = blankCounter + 1
        Loop
        rowCounter = rowCounter + 1
    Wend
End Sub

Private Sub Clear_Button_Click()
    Dim blankCounter As Integer
    blankCounter = CHART_ROW_START
    
    Do While blankCounter < CHART_ROW_START + MAX_NUMBER_OF_ROWS
       
        Date_Table(blankCounter).Value = ""
        PlannedValue_Table(blankCounter).Value = ""
        EarnedValue_Table(blankCounter).Value = ""
        ActualCost_Table(blankCounter).Value = ""
        CostVariance_Table(blankCounter).Value = ""
        CostPerformanceIndex_Table(blankCounter).Value = ""
        ScheduleVariance_Table(blankCounter).Value = ""
        SchedulePerformanceIndex_Table(blankCounter).Value = ""
        EstimateAtCompletion_Table(blankCounter).Value = ""
        EstimateToCompletion_Table(blankCounter).Value = ""
        VarianceAtCompletion_Table(blankCounter).Value = ""
        ToCompletePerformanceIndex_Table(blankCounter).Value = ""
           
        blankCounter = blankCounter + 1
    Loop
End Sub
Advertisements

Categories: Excel, Technology, Tutorials, VBA

Tagged as: , , ,

3 replies »

    • PowerBI is nice for displaying data, I used to have to link it to SharePoint and set up PowerBI dashboards for a PMO I was working for. We had it set to update several times a day and it would bring in any SharePoint list changes. That became the project tracking tool for the PMO.

      Excel is a tool that has the ability to go well outside of its intended uses. You could forgo PowerBI if you wanted to (although more work is sometimes involved). It is a tool to go to when you don’t have the right tool to do the job. Robotic Process Automation – Excel can do it, Database – it isn’t pretty but Excel can do it. Full front-end application with data entry validation – I built a few. I have a love-hate relationship with Excel because of that. I know there are better ways to do things, but Excel gets picked because it is cheap (not always easier).

      Liked by 1 person

      • Yes excel has become rather ubiquitous and I agree is more powerful than its intended purpose. Hard to get rid of from a company when there are literally hundreds of thousands of sheets. The biggest problem is support of the sheet and how do you know that it’s a source of truth and indeed accurate? That’s what I find but then again same with tableau and power bi. Yes power good for KPI and whipping up quickly. cheers!

        Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.