mav5209
07-02-2014, 12:20 PM
Is there a way to like reverse engineer code in VBA? I want to write a series of numbers in a double skew shape. An example is below:
0
0.857143
1.714286
1.392857
1.071429
1.392857
1.714286
0.857143
0
If you put these into an excel document you will find that they add to 9. I am writing a program that has a time period in number of months and an amount of work to be done also in an amount of months. So for this example, there are 9 months with 9 months of work to be done throughout those nine months. I have already accomplished evenly distributing, rising, falling, a normal distribution curve and an early and late skew curve. This curve would look like if you combined the early and late skew curves. The problem I am having is finding a point in the data where it rises and falls correctly. Here's an example of the math behind Early Skew written in code:
Case 4 ' Early Skew Distribution
sSkewSlope1 = CalculateSkewSlope1(lSubPeriods, sTotalEffort)
sSkewSlope2 = CalculateSkewSlope2(lSubPeriods, sTotalEffort)
sQuarterSubPeriods = Int(lSubPeriods / 4)
For sIndex = 1 To lSubPeriods - 1
If sIndex < sQuarterSubPeriods Then sMonthlyEfforts(sIndex) = sIndex * sSkewSlope1 'Calls on steep slope data for data below 1/4 of the total monthly effort
If sIndex >= sQuarterSubPeriods Then sMonthlyEfforts(sIndex) = ((lSubPeriods - 1) - sIndex) * sSkewSlope2 'Calls on gentle slope data for data above 1/4 of the total monthly effort
Next sIndex
Which calls on:
Public Function CalculateSkewSlope1(lSubPeriods As Long, sTotalEffort As Single) As Single
Dim sIndex1 As Long, sIndex2 As Long, sArea As Single, sTotal1 As Long, sTotal2 As Long
sArea = sTotalEffort / 4
sTotal1 = 0
sTotal2 = 0
For sIndex1 = 1 To Int(lSubPeriods / 4)
sTotal1 = sTotal1 + sIndex1
Next sIndex1
For sIndex2 = 1 To (Int(lSubPeriods / 4) - 1)
sTotal2 = sTotal2 + sIndex2
Next sIndex2
If (lSubPeriods / 4) = Int(lSubPeriods / 4) Then CalculateSkewSlope1 = sArea / sTotal2
If (lSubPeriods / 4) <> Int(lSubPeriods / 4) Then CalculateSkewSlope1 = sArea / sTotal1
End Function
Public Function CalculateSkewSlope2(lSubPeriods As Long, sTotalEffort As Single) As Single
Dim sIndex As Long, sArea As Single, sTotal As Single
sArea = (sTotalEffort * 3) / 4
sTotal = 0
For sIndex = 1 To (((lSubPeriods - (lSubPeriods / 4))) - 1)
sTotal = sTotal + sIndex
Next sIndex
CalculateSkewSlope2 = sArea / sTotal
End Function
Any suggestions?
0
0.857143
1.714286
1.392857
1.071429
1.392857
1.714286
0.857143
0
If you put these into an excel document you will find that they add to 9. I am writing a program that has a time period in number of months and an amount of work to be done also in an amount of months. So for this example, there are 9 months with 9 months of work to be done throughout those nine months. I have already accomplished evenly distributing, rising, falling, a normal distribution curve and an early and late skew curve. This curve would look like if you combined the early and late skew curves. The problem I am having is finding a point in the data where it rises and falls correctly. Here's an example of the math behind Early Skew written in code:
Case 4 ' Early Skew Distribution
sSkewSlope1 = CalculateSkewSlope1(lSubPeriods, sTotalEffort)
sSkewSlope2 = CalculateSkewSlope2(lSubPeriods, sTotalEffort)
sQuarterSubPeriods = Int(lSubPeriods / 4)
For sIndex = 1 To lSubPeriods - 1
If sIndex < sQuarterSubPeriods Then sMonthlyEfforts(sIndex) = sIndex * sSkewSlope1 'Calls on steep slope data for data below 1/4 of the total monthly effort
If sIndex >= sQuarterSubPeriods Then sMonthlyEfforts(sIndex) = ((lSubPeriods - 1) - sIndex) * sSkewSlope2 'Calls on gentle slope data for data above 1/4 of the total monthly effort
Next sIndex
Which calls on:
Public Function CalculateSkewSlope1(lSubPeriods As Long, sTotalEffort As Single) As Single
Dim sIndex1 As Long, sIndex2 As Long, sArea As Single, sTotal1 As Long, sTotal2 As Long
sArea = sTotalEffort / 4
sTotal1 = 0
sTotal2 = 0
For sIndex1 = 1 To Int(lSubPeriods / 4)
sTotal1 = sTotal1 + sIndex1
Next sIndex1
For sIndex2 = 1 To (Int(lSubPeriods / 4) - 1)
sTotal2 = sTotal2 + sIndex2
Next sIndex2
If (lSubPeriods / 4) = Int(lSubPeriods / 4) Then CalculateSkewSlope1 = sArea / sTotal2
If (lSubPeriods / 4) <> Int(lSubPeriods / 4) Then CalculateSkewSlope1 = sArea / sTotal1
End Function
Public Function CalculateSkewSlope2(lSubPeriods As Long, sTotalEffort As Single) As Single
Dim sIndex As Long, sArea As Single, sTotal As Single
sArea = (sTotalEffort * 3) / 4
sTotal = 0
For sIndex = 1 To (((lSubPeriods - (lSubPeriods / 4))) - 1)
sTotal = sTotal + sIndex
Next sIndex
CalculateSkewSlope2 = sArea / sTotal
End Function
Any suggestions?