Shortening the runtime of VBA macro

Question

I'm fairly new to this (started yesterday), so maybe someone has some suggestions. I have created a macro which updates a download sheet with new columns and formulas for monthly values, while leaving the yearly and static values unchanged. The code executes its intended function, but takes almost 30 minutes to cycle through loop through when Stock = 25

Sub New_monthly()

    Dim i As Integer

    Dim Measure As Integer

    Dim Count As Integer

    Dim Stock As Integer

    Dim Monthend As Long


    Stock = Application.WorksheetFunction.CountIf(Range("AP1:AP50"), ">0")

    Monthend = Range("AS1").Value


    For i = 0 To Stock - 1
        ' Copy old values and move them over one space

        Range("B54:B115").Offset(i * 115, 0).Select
        Selection.Insert Shift:=xlToRight

        ' Enter new values

        Cells(108 + i * 115, 2).Value = Monthend
        Cells(109 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(109 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(110 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(110 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(111 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(111 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(112 + i * 115, 2).Formula = "=BDH(" & Cells(112 + i * 115, 1).Address & "," & Cells(111 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(113 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(113 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(114 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(114 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(115 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(115 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"

        For Measure = 0 To 5
            Cells(54 + i * 115 + Measure * 9, 3).Value = Monthend
            For f = 0 To 7
                Cells(55 + i * 115 + Measure * 9 + f, 3).Formula = "=BDH(" & Cells(19 + i * 115 + f, 1).Address & "," & Cells(54 + i * 115 + Measure * 9, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
            Next f
            Cells(57 + i * 115 + Measure * 9, 3).Formula = "=AVERAGE(" & Cells(58 + i * 115 + Measure * 9, 3).Address & ":" & Cells(62 + i * 115 + Measure * 9, 3).Address & ")"
        Next Measure
    Next i

End Sub

Since you are starting, here are few tips... 1. `CountIf` is an unreliable way to find last row. You may want to see [This](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) 2. Avoid the use of `.Select`. You may want to see [This](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). 3. You can enter formulas in a range in one go if they are contiguous. You do not need to do that individually. — Siddharth Rout, Aug 03 '16 at 07:31
For Bloomberg the best approach is to call the function in Vba by passing array, store the return values in another array and just dump the resulting array on report sheet/ data sheet. Avoid writing a lot of formulas in cells. Specially BDH is async, down the line it will become pretty messy to manage. — cyboashu, Aug 03 '16 at 08:55

score 1 · Answer 1 · 2016-08-03T08:47:30.810

You should turn off ScreenUpdating and Calculations.

Sub New_monthly()

    Dim i As Integer
    Dim Measure As Integer
    Dim Count As Integer
    Dim Stock As Integer
    Dim Monthend As Long

    Stock = Application.WorksheetFunction.CountIf(Range("AP1:AP50"), ">0")

    Monthend = Range("AS1").Value

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    For i = 0 To Stock - 1
        ' Copy old values and move them over one space

        Range("B54:B115").Offset(i * 115, 0).Select
        Selection.Insert Shift:=xlToRight

        ' Enter new values

        Cells(108 + i * 115, 2).Value = Monthend
        Cells(109 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(109 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(110 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(110 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(111 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(111 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(112 + i * 115, 2).Formula = "=BDH(" & Cells(112 + i * 115, 1).Address & "," & Cells(111 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(113 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(113 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(114 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(114 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
        Cells(115 + i * 115, 2).Formula = "=BDH(" & Cells(100 + i * 115, 1).Address & "," & Cells(115 + i * 115, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"

        For Measure = 0 To 5

            Cells(54 + i * 115 + Measure * 9, 3).Value = Monthend
            For f = 0 To 7
                Cells(55 + i * 115 + Measure * 9 + f, 3).Formula = "=BDH(" & Cells(19 + i * 115 + f, 1).Address & "," & Cells(54 + i * 115 + Measure * 9, 1).Address & ",$AS$1,$AS$1,""DAYS=C"")"
            Next f
            Cells(57 + i * 115 + Measure * 9, 3).Formula = "=AVERAGE(" & Cells(58 + i * 115 + Measure * 9, 3).Address & ":" & Cells(62 + i * 115 + Measure * 9, 3).Address & ")"
        Next Measure
    Next i

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

psst, you forgot to turn them back on at the end of the code, your turning them off twice atm ;) — DragonSamu, Aug 03 '16 at 08:45
And to be sure you do not mess with other parts of code you should save the previous values of ScreenUpdating and Calculation and restore the saved values at the end (maybe for some reasons the default value for Calculation was xlCalculationSemiautomatic) — Vincent G, Aug 03 '16 at 09:22
That is actually what I was thinking about when I neglected to re-enable events and calculations. Stock is the only value taken from the worksheet and all the formulas are absolute; so I think we are good there. — , Aug 03 '16 at 10:05

score 1 · Answer 2 · answered Aug 03 '16 at 10:45

In addition to what everyone else has said, there is an article here which I found helpful:

https://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/

Essentially add this at the start of your code:

With Application.Excel
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

and this at the end:

With Application.Excel
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With

You can also use

ActiveSheet.DisplayPageBreaks = False 'at the beginning and True at the end

Personally though I have found that the biggest performance upgrade generally comes with deactivating the calculations.

score 0 · Accepted Answer · answered Aug 03 '16 at 07:26

0

Set below the Sub New_monthly() function the following line:

Application.ScreenUpdating = False

and in the end of you function set it on Application.ScreenUpdating = True.

In this link you will get some advices for improve your Performance:

http://datapigtechnologies.com/blog/index.php/ten-things-you-can-do-to-speed-up-your-excel-vba-code/

answered Aug 03 '16 at 07:26

ManInTheMiddle

128
1
2
13

Great, thanks a lot for your help! The function executes in seconds now! – Peteris Celms Aug 03 '16 at 08:41

Shortening the runtime of VBA macro

3 Answers3