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:
Build a simple caching
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:
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.