PDA

View Full Version : Question...



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?

Bob Phillips
07-02-2014, 12:51 PM
Yeah, use code tags and post Excel questions in the Excel forum.