Speed Challenge

[alexaronson]

Hello

I have a friendly challenge for the board.

I have some code I built that I want to see if anyone can improve the performance of. I never had any formal VBA education other than the trial and error method and learning from looking at different code on this board.

Here is the challenge. I have a macro called averageGenerator. It creates 9 columns of data for various moving averages and for different time ranges.

I have broken this macro up into two sections. Section 1 creates the raw data and control keys for the moving averages and periods to generate for. Section 2 is the code that needs to be enhanced for the judging process. Competitors may edit the variables that are used and the code in Section 2.

To declare the winner, I will run the code enhancements on my PC to provide a benchmark improvement from the submitted code. Competitors, please replace the name of my macro with your user ID from the board so I can keep track of who’s code is which.

This Competition expires Friday August 21st 11:30 AM Central Time (Memphis, TN time)
Good Luck!

Sub averageGenerator()
'This Macro allows a user on sheet1 to define the moving average periods they want to compute
'and for how many iterations.  For this exercise, a 100, 30, and 15 period moving average will be
'created 19,000 times each (8,000 + 6,000, +5,000).
Dim avg2Compute As Integer, lastRow As Integer, i As Integer
Dim j As Integer, iAvg As Integer, dataPoints As Integer, maPeriod As Integer, maRange As Variant
Dim startPoint As Integer, endPoint As Integer, n As Integer, t As Double
Dim vFirst As Integer, vLast As Integer
Application.ScreenUpdating = False
Sheets("Sheet1").Select
'This part of the code is going to create raw data to be used in the competition
Range("K2").Value = 8000
Range("K3").Value = 6000
Range("K4").Value = 500
Range("H2").Value = 100
Range("H3").Value = 30
Range("H4").Value = 15
Range("J2").Value = 3
Sheets("Sheet2").Select
vFirst = 1
vLast = 100
i = 1

For i = i To 8100
    Randomize
    Sheets("Sheet2").Cells(i, 8).Value = Int((vLast - vFirst + 1) * Rnd() + vFirst)
Next
Sheets("Sheet1").Select

'Speed Part of the competition if from this point on!  Good Luck!
t = Timer
avg2Compute = Range("J2")
j = 10
Sheets("Sheet2").Select
lastRow = Range("H" & Rows.Count).End(xlUp).Row

iAvg = 1
i = 1
n = 1
For n = n To 3 ' Counter for the number of period spans to comput.
 Sheets("Sheet1").Select
 dataPoints = Cells(n + 1, 8) ' defines the number of periods I want to evaluate
 For i = i To avg2Compute 'counter to define which average is being computed
    maPeriod = Cells(i + 1, 11) 'number of periods in the average
    Sheets("Sheet2").Select
    startPoint = lastRow - dataPoints - maPeriod + 2 'the first row in the average
    endPoint = startPoint + maPeriod - 1 'the last row in the average
    For iAvg = iAvg To dataPoints 'defines the iteration the average is currently on.
    maRange = Range("H" & startPoint, "H" & endPoint) ' defines the range of data to use in the average
    Cells(endPoint, j).Value = Application.average(maRange) 'enter the average value
    startPoint = startPoint + 1 ' redefines the new startPoint variable
    endPoint = endPoint + 1 'redefines the new endPointvariable
    Next
    Cells(1, j).Value = maPeriod 'Places the number of the moving average used
    Cells(2, j).Value = dataPoints 'Places the number of periods created by the moving average
    j = j + 1  ' moves j over so that a new average can be displayed
    iAvg = 1 'resets iAvg for the iteration
    Sheets("Sheet1").Select
Next
i = 1 'resets i for the iteration
Next
Application.ScreenUpdating = True
MsgBox Timer - t
End Sub

 

[Norie]

Does that code actually work for you?

It doesn't for me - it errors on this line with a Type mismatch.

Cells(endPoint, j).Value = Application.Average(maRange)

 

[alexaronson]

Norie,

I opened a new excel workbook, copied the code from the post, and pasted the code into a module, and hit run. The code worked without any issues for me. I am running Windows XP professional and Excel 2007.

By the way, you helped me with some coding issues a long time ago (about 3 years) and I have not forgotten.

 

[Norie]

Right, got the code to run and altered it a bit - seems to cut the time by half.

Sub averageGenerator()
'This Macro allows a user on sheet1 to define the moving average periods they want to compute
'and for how many iterations.  For this exercise, a 100, 30, and 15 period moving average will be
'created 19,000 times each (8,000 + 6,000, +5,000).
Dim avg2Compute As Long, lastRow As Long, i As Long
Dim j As Long, iAvg As Long, dataPoints As Long, maPeriod As Long, maRange As Variant
Dim startPoint As Long, endPoint As Long, n As Long, t As Double
Dim vFirst As Long, vLast As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    With Sheets("Sheet1")
        'This part of the code is going to create raw data to be used in the competition
        .Range("K2:K4").Value = Array(8000, 6000, 500)
        .Range("H2:H4").Value = Array(100, 30, 150)
        .Range("J2").Value = 3
    End With

    vFirst = 1
    vLast = 100
    i = 1
    For i = i To 8100
        Randomize
        Sheets("Sheet2").Cells(i, 8).Value = Int((vLast - vFirst + 1) * Rnd() + vFirst)
    Next
 
    'Speed Part of the competition if from this point on!  Good Luck!
    t = Timer
    avg2Compute = Sheets("Sheet1").Range("J2")
    j = 10
    lastRow = Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Row
    iAvg = 1
    i = 1
    n = 1
    For n = n To 3    ' Counter for the number of period spans to comput.
        dataPoints = Sheets("Sheet1").Cells(n + 1, 8)  ' defines the number of periods I want to evaluate
        For i = i To avg2Compute    'counter to define which average is being computed
            maPeriod = Sheets("Sheet1").Cells(i + 1, 11)    'number of periods in the average
            startPoint = lastRow - dataPoints - maPeriod + 2    'the first row in the average
            endPoint = startPoint + maPeriod - 1    'the last row in the average
            For iAvg = iAvg To dataPoints    'defines the iteration the average is currently on.
                Set maRange = Sheets("Sheet2").Range("H" & startPoint, "H" & endPoint)    ' defines the range of data to use in the average
                Sheets("Sheet2").Cells(endPoint, j).Value = Application.WorksheetFunction.Average(maRange)    'enter the average value
                startPoint = startPoint + 1    ' redefines the new startPoint variable
                endPoint = endPoint + 1    'redefines the new endPointvariable
            Next iAvg
            Sheets("Sheet2").Cells(1, j).Value = maPeriod    'Places the number of the moving average used
            Sheets("Sheet2").Cells(2, j).Value = dataPoints    'Places the number of periods created by the moving average
            j = j + 1  ' moves j over so that a new average can be displayed
            iAvg = 1    'resets iAvg for the iteration
        Next i
        i = 1    'resets i for the iteration
    Next n
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Debug.Print Timer - t
    
End Sub

Note the problem I mentioned in the first post was because when you use Average in VBA a range is expected.

With this you were creating an array of values not a reference to a range.

maRange = Range("H" & startPoint, "H" & endPoint)