Question...

[mav5209]

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]

Yeah, use code tags and post Excel questions in the Excel forum.