The Excel Markos speed up Guide


If Excel macros to run slowly, because they must meet ever more complex requirements, can you use the following tricks, to enable a faster runtime:

VBA tips

Build a simple caching

See post VBA Excel key-value store cache with dictionary

Simple profiling to find bottlenecks in the application

The Term of certain calculations You can determine, by placing one time measurements in the VBA code on suitable place and spend on the debug console can be. This can be parsed, which parts of the application behave slowly.

Disable events during the Scriptlaufzeit

Are thus thrown no events such as:

Private Sub Worksheet_Change(ByVal Target As Range)

to prevent the event throwing and prevent unnecessary Excel events:

Application.EnableEvents = False
...
'your macro
...
Application.EnableEvents = True

Disable updating the view

It takes much performance in claim, Sometimes the Excel table view for the user to refresh each, If changes to the front end have been made by the Marko. That you can for the macro's runtime disable and enable again at the end of, Once again, everything works normal.

Application.ScreenUpdating= False
...
'your macro
...
Application.ScreenUpdating= True

Do not use Debug.print

The output of information at run time to the direct area in the macro editor is very convenient to get to an overview, What happens in the program at run time. However, this requires performance and a Refreshen of the view and should at a minimum (1 time minute) be limited to long-running scripts, to gain performance.

So better Auskommen animals spending used by much:

'debug.print("SPAM")

avoid unnecessary loops

A for Loop can be mostly dropping out or skipped calculations:

 For index As Integer = 1 To 100000
    ' If index is between 5 and 7, continue
    ' with the next iteration.
    If index >= 5 And index <= 8 Then
        Continue For
    End If

    ' Display the index.
    Debug.Write(index.ToString & " ")

    ' If index is 10, exit the loop.
    If index = 10 Then
        Exit For
    End If
Next

Also GoTo can be used for this, If it is positive for performance reasons, and the code is logical and easy to understand.

    Sub gotoStatementDemo()
        Dim number As Integer = 1
        Dim sampleString As String
        ' Evaluate number and branch to appropriate label.
        If number = 1 Then GoTo Line1 Else GoTo Line2
Line1:
        sampleString = "Number equals 1"
        GoTo LastLine
Line2:
        ' The following statement never gets executed because number = 1.
        sampleString = "Number equals 2"
LastLine:
        ' Write "Number equals 1" in the Debug window.
        Debug.WriteLine(sampleString)
    End Sub

This way, unnecessary operations can be skip. It, however, belongs to the bad programming practice and should be used carefully.

Hardware tuning: MacBook Pro vs. ThinkPad

ThinkPad X 220: 2,5GHz Intel Core i5, 8GB RAM, SSD, Windows 8

MacBook Pro: 2,5GHz Intel Core i5, 16GB RAM, SSD, OS X 10.9

Result: The MacBook Pro was to 94% faster compared to the ThinkPad, with Windows PCs, the operating system age plays an important role. My Windows system has become with the slower time much.

A Investment in good hardware worth, for Mac users the MacBook Pro and under Windows Lenovo Yoga slow macros is worth.