Mikkel84
11/14/2019 - 7:26 PM
Macro for Sprint Analysis
Sub Distribute_User_Stories()
'
' Written by Sebastian Krug, AIM, Deloitte Consulting GmbH
' Macro for distribution of user stories with given mapping
'
Dim epics As Worksheet, epic_mapping As Worksheet, sprintSheet As Worksheet
Dim epicFoundInMapping As Boolean
Dim epicFoundInOriginalList As Boolean
Dim userStoryIsAssigned As Boolean
Dim epic_dict As New Scripting.Dictionary
Dim priceList As Object
Set priceList = CreateObject("System.Collections.ArrayList")
Set epics = ThisWorkbook.Sheets("EPICs")
Set epic_mapping = ThisWorkbook.Sheets("EPIC Refinement")
Set sprintSheet = ThisWorkbook.Sheets("Sprint 11") ' ==> ANPASSEN FÜR ANDEREN SPRINT!
' Add prices for T-Shirt sizes to priceList
priceList.Add epics.Cells(2, 6) 'S
priceList.Add epics.Cells(2, 8) 'M
priceList.Add epics.Cells(2, 9) 'L
priceList.Add epics.Cells(2, 10) 'XL
priceList.Add epics.Cells(2, 11) 'XXL/2XL
'Iterate through sprint epic IDs
For Counter_sprintSheet = 1 To sprintSheet.UsedRange.Rows.Count
epicFoundInMapping = False
epicFoundInOriginalList = False
userStoryIsAssigned = False
Set epic_link = sprintSheet.Cells(Counter_sprintSheet, 23)
Set Status = sprintSheet.Cells(Counter_sprintSheet, 7)
Set Provider = sprintSheet.Cells(Counter_sprintSheet, 13)
Set size = sprintSheet.Cells(Counter_sprintSheet, 15)
If (Status = "Resolved" Or Status = "Closed") And Provider = "DT-HuP" And epic_link <> "" Then
WriteLogEntry ("The T-Shirt Size of " + epic_link.Value + " is " + size.Value + " and the status is '" + Status.Value + "'")
' Iterate through epic mapping
For Counter_refinement = 1 To epic_mapping.UsedRange.Rows.Count
Set epic_new = epic_mapping.Cells(Counter_refinement, 2)
If epic_new = epic_link Then 'epic found is mapping
epicFoundInMapping = True
Set epic_original = epic_mapping.Cells(Counter_refinement, 1)
Set flag_Merge = epic_mapping.Cells(Counter_refinement, 3)
Set flag_Split = epic_mapping.Cells(Counter_refinement, 4)
WriteLogEntry ("Epic link " + epic_link.Value + " found in mapping and is mapped to " + epic_original.Value + ".")
If flag_Merge = "" And flag_Split = "" Then
' 1-to-1 mapping
WriteLogEntry ("1-to-1 conversion from " + epic_new.Value + " to " + epic_original.Value)
For Counter_epics = 1 To epics.UsedRange.Rows.Count
Set epic = epics.Cells(Counter_epics, 2)
If epic = epic_original Then
epicFoundInOriginalList = True
WriteLogEntry ("Old Epic from mapping (" + epic_original.Value + ") found in EPICs list.")
Set epic_volume = epics.Cells(Counter_epics, 5)
Set volume_available = epics.Cells(Counter_epics, 6)
If volume_available - getBlockedVolume(epic_dict, epic.Value, priceList) - getPrice(size.Value, priceList) >= 0 Then
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " - " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " (volume blocked by previous assignments) > " + Str(getPrice(size.Value, priceList)) + ". Adding User Story with T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Set epic_dict = addTShirtSize(epic_dict, epic.Value, size.Value)
userStoryIsAssigned = True
Else
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " minus blocked volume " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " is NOT SUFFICIENT to cover the price of the T-Shirt size of " + Str(getPrice(size.Value, priceList)) + ".")
WriteLogEntry ("Adding User Story with T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Set epic_dict = addTShirtSize(epic_dict, epic.Value, size.Value)
userStoryIsAssigned = True
End If
End If
Next
ElseIf flag_Split = "Split" Then
WriteLogEntry ("Split of " + epic_original.Value + " into " + epic_new.Value + " and others. Unique assignment possible ...")
For Counter_epics = 1 To epics.UsedRange.Rows.Count
Set epic = epics.Cells(Counter_epics, 2)
If epic = epic_original Then
epicFoundInOriginalList = True
WriteLogEntry ("Old Epic from mapping (" + epic_original.Value + ") found in EPICs list.")
Set epic_volume = epics.Cells(Counter_epics, 5)
Set volume_available = epics.Cells(Counter_epics, 6)
If volume_available - getBlockedVolume(epic_dict, epic.Value, priceList) - getPrice(size.Value, priceList) >= 0 Then
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " - " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " (volume blocked by previous assignments) > " + Str(getPrice(size.Value, priceList)) + ". Adding User Story with T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Set epic_dict = addTShirtSize(epic_dict, epic.Value, size.Value)
userStoryIsAssigned = True
Else
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " minus blocked volume " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " is NOT SUFFICIENT to cover the price of the T-Shirt size of " + Str(getPrice(size.Value, priceList)) + ".")
WriteLogEntry ("Adding User Story with T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Set epic_dict = addTShirtSize(epic_dict, epic.Value, size.Value)
userStoryIsAssigned = True
End If
End If
Next
ElseIf flag_Merge = "Merge" Then
WriteLogEntry ("Merge of " + epic_original.Value + " and other into " + epic_new.Value + ". No unique assigment possible ... ")
WriteLogEntry ("Start searching for possible epics for assigment ...")
For Counter_epics = 1 To epics.UsedRange.Rows.Count
Set epic = epics.Cells(Counter_epics, 2)
If epic = epic_original Then
epicFoundInOriginalList = True
WriteLogEntry ("Old Epic from mapping (" + epic_original.Value + ") found in EPICs list.")
If userStoryIsAssigned = False Then
Set epic_volume = epics.Cells(Counter_epics, 5)
Set volume_available = epics.Cells(Counter_epics, 6)
If volume_available - getBlockedVolume(epic_dict, epic.Value, priceList) - getPrice(size.Value, priceList) >= 0 Then
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " - " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " (volume blocked by previous assignments) > " + Str(getPrice(size.Value, priceList)) + ". Adding User Story with T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Set epic_dict = addTShirtSize(epic_dict, epic.Value, size.Value)
userStoryIsAssigned = True
Else
If epic_new = epic_mapping.Cells(Counter_refinement + 1, 2) Then
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " minus blocked volume " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " is NOT SUFFICIENT to cover the price of the T-Shirt size of " + Str(getPrice(size.Value, priceList)) + ". Cannot add T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Else
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " minus blocked volume " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " is NOT SUFFICIENT to cover the price of the T-Shirt size of " + Str(getPrice(size.Value, priceList)) + ".")
WriteLogEntry ("Adding User Story with T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Set epic_dict = addTShirtSize(epic_dict, epic.Value, size.Value)
userStoryIsAssigned = True
End If
End If
Else
WriteLogEntry ("Skipping user story because it has already been assigned to a previous epic.")
End If
End If
Next
Else
WriteLogEntry ("Mapping condition not fulfilled. please check.")
End If
' Else
' WriteLogEntry ("Epic link " + epic_link.Value + " not found in mapping.")
End If
Next
If epicFoundInMapping = False Then
WriteLogEntry ("Epic link " + epic_link.Value + " not found in mapping. Searching in original epic list ...")
For Counter_epics = 1 To epics.UsedRange.Rows.Count
Set epic = epics.Cells(Counter_epics, 2)
If epic = epic_link Then
epicFoundInOriginalList = True
WriteLogEntry ("Epic link " + epic_link.Value + " found in EPICs list.")
Set epic_volume = epics.Cells(Counter_epics, 5)
Set volume_available = epics.Cells(Counter_epics, 6)
If volume_available - getBlockedVolume(epic_dict, epic.Value, priceList) - getPrice(size.Value, priceList) >= 0 Then
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " - " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " (volume blocked by previous assignments) > " + Str(getPrice(size.Value, priceList)) + ". Adding User Story with T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
Set epic_dict = addTShirtSize(epic_dict, epic.Value, size.Value)
userStoryIsAssigned = True
Else
WriteLogEntry ("Available volume =" + Str(volume_available.Value) + " minus blocked volume " + Str(getBlockedVolume(epic_dict, epic.Value, priceList)) + " is NOT SUFFICIENT to cover the price of the T-Shirt size of " + Str(getPrice(size.Value, priceList)) + ". Cannot add T-Shirt size " + size.Value + " to epic " + epic.Value + ".")
End If
End If
Next
If epicFoundInOriginalList = False Then WriteLogEntry ("Epic link " + epic_link.Value + " also not found in original list. No assignment possible ...")
End If
If userStoryIsAssigned = Flase Then
WriteLogEntry ("User story " + epic_link + " could not be assigned to an epic.")
End If
WriteLogEntry ("--------------------------------------------------------------------------------")
End If
Next
WriteLogEntry ("############################################################## Results #######################################################################################")
Dim counterAssignedUserStories As Integer
counterAssignedUserStories = 0
Dim k As Variant
For Each k In epic_dict.Keys
WriteLogEntry (k + " --> S =" + Str(epic_dict(k)(0)) + ", M =" + Str(epic_dict(k)(1)) + ", L =" + Str(epic_dict(k)(2)) + ", XL =" + Str(epic_dict(k)(3)) + ", XXL =" + Str(epic_dict(k)(4)))
counterAssignedUserStories = counterAssignedUserStories + epic_dict(k)(0) + epic_dict(k)(1) + epic_dict(k)(2) + epic_dict(k)(3) + epic_dict(k)(4)
Next k
WriteLogEntry (Str(counterAssignedUserStories) + " has been assigned.")
WriteLogEntry ("##############################################################################################################################################################")
' Write results to cells
' iterate again through dict and write list values to cells
For Each k In epic_dict.Keys
For Counter_epics = 1 To epics.UsedRange.Rows.Count
Set epic = epics.Cells(Counter_epics, 2)
If epic = k Then
epics.range("BL" & Counter_epics).Value = epic_dict(k)(0) 'S ' ==> ANPASSEN FÜR ANDEREN SPRINT!
epics.range("BM" & Counter_epics).Value = epic_dict(k)(1) 'M ' ==> ANPASSEN FÜR ANDEREN SPRINT!
epics.range("BN" & Counter_epics).Value = epic_dict(k)(2) 'L ' ==> ANPASSEN FÜR ANDEREN SPRINT!
epics.range("BO" & Counter_epics).Value = epic_dict(k)(3) 'XL ' ==> ANPASSEN FÜR ANDEREN SPRINT!
epics.range("BP" & Counter_epics).Value = epic_dict(k)(4) 'XXL ' ==> ANPASSEN FÜR ANDEREN SPRINT!
End If
Next
counterAssignedUserStories = counterAssignedUserStories + epic_dict(k)(0) + epic_dict(k)(1) + epic_dict(k)(2) + epic_dict(k)(3) + epic_dict(k)(4)
Next k
MsgBox "User Stories have been distributed."
End Sub
Function getPrice(s As String, priceList As Object) As Double
Dim p As Double
If s = "S" Then
p = priceList(0)
ElseIf s = "M" Then
p = priceList(1)
ElseIf s = "L" Then
p = priceList(2)
ElseIf s = "XL" Then
p = priceList(3)
ElseIf s = "XXL" Or s = "2XL" Then
p = priceList(4)
Else
WriteLogEntry ("Cannot get price of T-Shirt Size. Size '" + s + "' is invalid.")
End If
getPrice = p
End Function
Function getBlockedVolume(d As Dictionary, epic As String, priceList As Object) As Double
Dim p As Double
p = 0
If d.Exists(epic) Then
For i = 0 To 4
p = p + CDbl(d(epic)(i)) * CDbl(priceList(i))
Next
End If
getBlockedVolume = p
End Function
Function addTShirtSize(dict As Dictionary, key As String, size As String) As Dictionary
Dim vArray As Variant
Dim index As Integer
ReDim vArray(0 To 4)
If dict.Exists(key) Then
If size = "S" Then
index = 0
ElseIf size = "M" Then
index = 1
ElseIf size = "L" Then
index = 2
ElseIf size = "XL" Then
index = 3
ElseIf size = "XXL" Or size = "2XL" Then
index = 4
Else
WriteLogEntry ("Cannot store T-Shirt Size. Size '" + size + "' is invalid.")
End If
vArray = dict(key)
vArray(index) = vArray(index) + 1
dict(key) = vArray
Else
If size = "S" Then
dict.Add key, Array(1, 0, 0, 0, 0)
ElseIf size = "M" Then
dict.Add key, Array(0, 1, 0, 0, 0)
ElseIf size = "L" Then
dict.Add key, Array(0, 0, 1, 0, 0)
ElseIf size = "XL" Then
dict.Add key, Array(0, 0, 0, 1, 0)
ElseIf size = "XXL" Or size = "2XL" Then
dict.Add key, Array(0, 0, 0, 0, 1)
Else
WriteLogEntry ("Cannot store T-Shirt Size. Size '" + size + "' is invalid.")
End If
End If
Set addTShirtSize = dict
End Function
'Function Get_Column_Index(columnNumber As Integer) As Integer
' Dim dividend As Integer
' Set dividend = columnNumber
' Dim columnName As String
' Dim modulo As Integer
' While dividend > 0
' modulo = (dividend - 1) Mod 26
' columnName = Convert.ToChar(65 + modulo).ToString() & columnName
' dividend = CInt((dividend - modulo) / 26)
' End While
' Console.WriteLine (columnName.ToString)
' result = columnName
'End Function
Sub WriteLogEntry(logMessage As String)
Dim pathLogFile As String
pathLogFile = "C:\Users\rueschmidt\Desktop\Log.txt"
Open pathLogFile For Append As #1
Write #1, DateTime.Now & " -- " & logMessage
Close #1
End Sub
Sub printSprintResults(d As Dictionary)
Dim k As Variant
For Each k In d.Keys
WriteLogEntry (k + " --> " + Str(d(k)(0)) + ", " + Str(d(k)(1)) + ", " + Str(d(k)(2)) + ", " + Str(d(k)(3)) + ", " + Str(d(k)(4)))
Next k
End Sub