none
Looking to see if this VBA subroutine can be made more efficient RRS feed

  • Question

  • To All,

      I have put together a SUB that depending on a constant averages those numbers together then starts the process of exponential smoothing of the number until the last row of data.  As it does so, it checks to see if it is at the last row and then copies the last number to row 2 of the same column.

      It works fine, but seems a little slow for just 11 columns and 200 rows.  Any thoughts on how to increase the efficiency of the routine?

    Thanks

    Harry

    Private Sub EMACalc()
    'EMA Calculations
    Dim numRows As Integer
    Dim EMAWindow As Long
    Dim colCount As Integer
    Dim rowCount As Integer
    Dim col As Integer
       
       Application.Calculation = xlCalculationManual
       Application.ScreenUpdating = False
    
    numRows = Sheets("InvertData").UsedRange.SpecialCells(xlCellTypeLastCell).Row - 1
    colCount = Application.WorksheetFunction.CountIf([BlanksRange], "<>" & "") - 1
    EMAWindow = [EMAConst]
        For col = 1 To colCount
        Sheets("EMALng").Cells(EMAWindow + 1, col) = "=Average(InvertData!R[-" & EMAWindow - 1 & "]C[2]:R[4]C[2])"
            For rowCount = EMAWindow + 2 To numRows
            Sheets("EMALng").Cells(rowCount, col).FormulaR1C1 = "=InvertData!R[4]C[2]*(2/(EMAWindow + 1)) + R[-1]C * (1-(2/(EMAWindow+1)))"
            If rowCount = numRows Then
            Sheets("EMALng").Cells(2, col).Value = Sheets("EMALng").Cells(rowCount, col).Value
            Else
            End If
            Next rowCount
        Next col
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    End Sub
    

    Saturday, January 24, 2015 6:59 PM

Answers

  • To All,

      After some more research in looking to how to fill a dynamic area with formulas, I came across this blog post at https://fastexcel.wordpress.com/2011/06/13/writing-efficient-vba-udfs-part-3-avoiding-the-vbe-refresh-bug/.  This is the same person that writes and sells FASTExcel addin.  The use of FillDown cut the execution to just milliseconds.

    Harry

    • Marked as answer by Harry Stevens Thursday, February 5, 2015 1:02 PM
    Thursday, February 5, 2015 1:02 PM

All replies

  • Any thoughts on how to increase the efficiency of the routine?

    You write 2211 formulas into the cells and the formulas reads much more cells when they are calculated and as far as I understand your code you read many cells twice. So there is a chance to make that faster, but then you have not formulas in the cells at the end.

    A possible solution is to read all data from the cells into an array, do all calculations in memory and write the results in one step.

    Have a look at this article:
    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    Andreas.

    Sunday, January 25, 2015 9:32 AM
  • Hi Harry,

    >> It works fine, but seems a little slow for just 11 columns and 200 rows.

    I don’t quite understand the goal you were trying to achieve through the code. Based my understanding, accessing 2000+ cells and changing the formula cell by cell may make the macro running slow.

    Since I’m not very familiar with your business requirement, I’m not sure whether it is possible to put the logic on the formula instead of changing the formula dynamically through VBA.

    Hope it will help.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, February 5, 2015 3:22 AM
    Moderator
  • To All,

      After some more research in looking to how to fill a dynamic area with formulas, I came across this blog post at https://fastexcel.wordpress.com/2011/06/13/writing-efficient-vba-udfs-part-3-avoiding-the-vbe-refresh-bug/.  This is the same person that writes and sells FASTExcel addin.  The use of FillDown cut the execution to just milliseconds.

    Harry

    • Marked as answer by Harry Stevens Thursday, February 5, 2015 1:02 PM
    Thursday, February 5, 2015 1:02 PM