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.
The template is already set up to do the EVM calculations using formulas. We are simply adding two buttons.
Requirements:
- Create two buttons
- One button, the “Save Values” button, will copy the EVM calculations and place them in the chart for saving and tracking
- 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.
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”
For the “Save Values” button, the properties should resemble the below:
For the “Clear Chart” button, the properties should resemble the below:
When completed, you can arrange the buttons however you want by moving them around – assuming that the sheet remains in “Design Mode”.
Arrangement of buttons:
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
'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 Variable Table Cells
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
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
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
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
Share this:
Categories: Excel, Technology, Tutorials, VBA
Agile-Mercurial 
Hi. Good step by step post. I use VBA a little but now tend to use the spreadsheet as an input and juggle the numbers in PowerBi or some such other tool. Cheers. Stacey
LikeLiked by 1 person
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).
LikeLiked 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!
LikeLiked by 1 person