| VBA-Programmcode optimieren | Home |
Improving the Performance of Your Code
| Zusammenfassung Auf dieser Seite finden Sie Empfehlungen, Tipps und Codebeispiele, wie man VBA-Programme in Bezug auf Ausführungsgeschwindigkeit optimieren kann. Die meisten Excel-Programmierer kennen und verwenden die weit bekannten Optimierungsmöglichkeiten wie das Ausschalten der Bildschirmaktualisierung (Application.ScreenUpdating = False) oder das vorübergehende Umstellen des Berechnungsmodus auf "Manuell" (Application.Calculation = xlCalculationManual). Auch andere Massnahmen zur Geschwindigkeitssteigerung wie beispielsweise das Vermeiden des Datentyps "Variant" für Variablen sind bestens bekannt. Doch weit weniger bekannt ist, dass es weitere Optimierungsmöglichkeiten gibt, die tatsächlich oft eine beträchtliche Steigerung der Ausführungsgeschwindigkeit bewirken. |
Leserprofil Erfahrene Excel-Anwender mit Kenntnissen über Makro-Programmierung und VBA-Programmierer mit guten Fachkenntnissen über das Excel-Objektmodell. |
| Inhalt dieser Seite Hier ein paar Beispiele, was Sie auf dieser Seite nachlesen können: - Wussten Sie, dass in bestimmten Situationen unqualifizierter VBA-Code viel schneller als qualifizierter Code ist? - Ist Ihnen bekannt, dass die Verminderung der Anzahl Interface Requests nicht nur bei Automation Clients sondern auch bei gewöhnlichen Makros bzw. VBA-Programmen eine erhebliche Reduktion der Ausführungszeit bewirkt? - Kennen Sie den Bug der VBA-Anweisung "On Error Resume Next", der in Office 2000 ein VBA-Programm stark verlangsamt? - Haben Sie schon mal etwas über den VBA Component Manager gehört, der u.a. für das Setzen des "Freeze Bit" von Objekten zuständig ist und aufgrund des technischen Designs von Microsoft Excel vor allem bei grösseren Arbeitsmappen eine ganze Menge Zeit für die Verwaltung der Objektreferenzen benötigt? - Können Sie sich vorstellen, dass ein Makro bei geschlossenem VBA-Editor in bestimmten Fällen sehr viel schneller ausgeführt wird; manchmal sogar bis zu 75 % schneller? |
Dokument-Information Autor: Philipp von Wartburg philipp.von.wartburg@bluewin.ch Dokument: |
Inhalt
Die vier Grundsatzfragen
Wie kann optimiert
werden?
Was kann optimiert
werden?
Wo kann optimiert werden?
Wann sollte nicht mehr
optimiert werden?
Ausführungsgeschwindigkeit
Allgemeine
Ausführungsgeschwindigkeit
Ausführungsgeschwindigkeit
erhöhen
Ausführungsgeschwindigkeit
messen
Möglichkeiten
der Code-Optimierung
Benutzerdefinierte
Funktionen optimieren
Ich persönlich vertrete die Meinung, dass VBA-Programmcode grundsätzlich optimiert werden sollte. Das heisst, nicht nur Code von grossen oder zeitkritischen Anwendungen sondern auch bei kleinen Programmen oder Makros, deren Ausführungsdauer nicht relevant ist. Auf Geschwindigkeit optimierter Programmcode führt nämlich nicht nur zu einem schnelleren Programm. Positive Nebeneffekte wie beispielsweise höhere Stabilität und bessere Lesbarkeit sind sehr häufig die Folge.
Optimization could be thought of as both a science and an art. The science is the techniques of optimization; the art is determining where and when optimizations should be applied. By definition, optimization is "the process of producing more efficient (smaller and/or faster) programs through selection and design of data structures, algorithms, and instruction sequences". It is a common misconception that optimization is process that takes place at the end of the development cycle. To create a truly optimized application, you must be optimizing it while you are developing it. You choose your algorithms carefully, weighing speed against size and other constraints; you form hypotheses about what parts of your application will be fast or slow, large or compact; and you test those hypotheses as you go.
The first step in the process of optimization is determining your goal. You can optimize your program for many different characteristics:
- Real speed (how fast your application actually calculates or performs other operations)
- Display speed (how fast your application paints the screen) - Perceived speed (how fast your application appears to run; this is often related to display speed but not always to real speed) - Size in memory - Size of graphics (this directly affects size in memory, but often has additional ramifications when working in Microsoft Windows)Rarely, however, can you optimize for multiple characteristics. Typically, an approach that optimizes size compromises on speed; likewise, an application that is optimized for speed is often larger than its slower counterpart. For this reason, recommended optimization techniques in one area may directly contradict suggestions in another. It’s important to note that optimization is not always completely beneficial. Sometimes the changes you make to speed up or trim down your application result in code that is harder to maintain or debug. Some optimization techniques contradict structured coding practice, which may cause problems when you try to expand your application in the future or incorporate it into other programs. In designing an optimization strategy for your application there are three things to consider: knowing what to optimize, knowing where to optimize, and knowing when to stop.
Bevor auf die konkreten Optimierungsmöglichkeiten eingegangen wird, sollten Sie sich mit den Grundsätzen des Optimierens vertraut machen. Diese helfen Ihnen, sich nicht in Details zu verlieren, in Sackgassen zu geraten und am falschen Ort zu optimieren.
Die Grundsätze lassen sich in Form von vier Fragen gut
erklären:
- Wie kann optimiert werden?
- Was kann optimiert werden?
- Wo kann optimiert werden?
- Wann sollte nicht mehr optimiert werden?
- Anderes Vorgehen wählen
- Reihenfolge der Anweisungen ändern
- Ähnliche bzw. verwandte Anweisungen verwenden
- Anweisungen anders einsetzen
- Aktionen weglassen
- Aktionen nur periodisch ausführen
- Ausführungsumgebung anpassen
Wann sollte nicht mehr optimiert werden?
Sometimes things aren't worth optimizing. For example, writing an elaborate but fast sorting procedure is pointless if you're only sorting a dozen items. It’s possible to sort things by adding them to a sorted list box and then reading them back out in order. With large numbers of items this is horribly inefficient, but if there aren't a lot of items it is just as quick as any other method, and the code is admirably simple (if a bit obscure). There are other cases where optimization is wasted effort. If your application is ultimately bound by the speed of your disk or network, there is little you can do in your code to speed things up. Instead you need to think about ways to make these delays less problematic for your users: progress bars to tell them your code isn't simply hung, caching data so they see the delays less often, yielding so that they can use other programs while they wait, and so on.
Allgemeine Ausführungsgeschwindigkeit
Sollte Ihnen einmal die Frage gestellt werden, ob ein Makro in den gängigen Excel-Versionen (Excel 97, 2000, 2002/XP, 2003) unterschiedlich schnell ausgeführt wird, so können Sie ruhig mit "Nein" antworten.
Ich habe dein Makro bei mir in Excel 97 optimiert und die Ausführungsgeschwindigkeit um den Faktor 30 verbessern können. Dass das Makro in Excel 2000 sehr viel schneller abläuft, kann (bzw. muss) andere Gründe haben. So kann beispielsweise die verwendete Windows-Version einen Einfluss haben. Durch das "DoEvents" wird die Steuerung an das Betriebssystem übergeben, damit es andere anstehende Ereignisse verarbeiten kann. Die Versionen Windows 95, 98 und ME verhalten sich dabei anders als Windows NT, 2000 und XP. Windows 95, 98 und ME benutzen ein koorperatives Multitasking in Gegensatz zu den anderen Windows-Versionen, die ein präemptives Multitasking verwenden. Wenn also das Makro in Win95/98/ME ausgeführt wird und auf dem PC viele bzw. ereignisintensive Prozesse laufen, so ist mit einer spürbaren Performance-Einbusse zu rechnen - egal ob Excel 97 oder 2000 verwendet wird. Aber auch andere Faktoren beeinflussen die Geschwindigkeit, wie z.B. der Grafikkarten-Treiber und sogar der Druckertreiber. Natürlich spielt auch die CPU des Rechners eine Rolle; und auch Excel-Optionen wie z.B. eingeblendete Seitenumbrüche drücken die Ausführungsgeschwindigkeit erheblich.
Ausführungsgeschwindigkeit erhöhen
Neben den Optimierungsmöglichkeiten für Programmcode gibt es noch eine weitere Möglichkeit, die Geschwindigkeit der Programmausführung zu steigern.
VBA-Editor geschlossen lassen
Beschreibung folgt...
Ausführungsgeschwindigkeit messen
Text folgt...
Möglichkeiten der Code-Optimierung
1. Bildschirm-Aktualisierung ausschalten
Application.ScreenUpdating = False
Application.ScreenUpdating = True
2. Automatischer Berechnen-Modus ausschalten
Berechnen-Modus vorübergehend auf "Manuell" stellen.
Application.Calculation = xlCalculationManual (-4135)
Application.Calculation = xlCalculationAutomatic (-4105)
3. Ereignis-Verarbeitung ausschalten
Application.EnableEvents = False
Application.EnableEvents = True
4. Anzeige der Seitenwechsel ausschalten
Worksheets(x).DisplayPageBreaks = False
Worksheets(x).DisplayPageBreaks = True
5. Variablen mit benötigtem Datentyp deklarieren
Deklarieren Sie alle Variablen und Konstanten mit dem erforderlichen Datentyp
6. Datentyp Variant vermeiden
Vermeiden Sie den Datentyp Variant
7. Variablen von For-Next-Schleifen mit benötigtem Datentypen deklarieren
Long ca. 15 % - 20 % schneller als Integer 20:24
Long ca. 300 % schneller als Variant 11:35
Double ca. 180 % schneller als Currency 204:372
Integer ca. 5400 % schneller als Variant 876:16
Rangliste der Ausführungsgeschwindigkeit
1. Long
2. Integer
3. Byte
4. Single
5. Double
6. Currency
8. Korrekter Operator für Ganzzahl-Divisionen verwenden
Integer Math (\) verwenden
D.h. Division mit \ ist schneller als mit /.
Ca. 30 % schneller
9. Konstanten statt Variablen einsetzen
Konstanten anstelle Variablen verwenden
10. Übergabe der Steuerung an das Betriebssystem vermeiden
Vermeiden Sie wenn immer möglich die Anweisung DoEvents. Mit DoEvents wird die Steuerung an das Betriebssystem übergeben, damit es andere anstehende Ereignisse verarbeiten kann. Je nach verwendeter Windows-Version kann dieser Vorgang unterschiedlich lange dauern. Windows 95, Windows 98 und Windows ME verhalten sich anders als Windows NT 4.0, Windows 2000 und Windows XP. Windows 95, 98 und ME benutzen ein sogenanntes koorperatives Multitasking in Gegensatz zu den anderen Windows-Versionen, die ein präemptives Multitasking verwenden. Wenn ein VBA-Programm unter Win95/98/ME ausgeführt wird und auf dem PC gerade viele bzw. ereignisintensive Prozesse laufen, so kann dies durchaus zu einer spürbaren Performance-Einbusse führen - egal ob das VBA-Programm in Excel 97 oder Excel 2000 läuft.
11. Lange Objektreferenzen verwenden
Dim appExcel As Application
Dim wkbBook As Workbook
Dim wksSheet As Worksheet
Dim rngRange As Range
Dim fntFont As Font
Set appExcel = Excel.Application
Set wkbBook = appExcel.Workbooks(1)
Set wksSheet = wkbBook.Worksheets(1)
Set rngRange = wksSheet.Range("A1")
Set fntFont = rngRange.Font
Debug.Print fntFont.Name
- besser -
Dim fntFont As Font
Set fntFont = Application.Workbooks(1).Worksheets(1).Range("A1").Font
Debug.Print fntFont.Name
12. Datentyp Object bei Objektvariablen vermeiden
Dim wksSystem As Object
- besser -
Dim wksSystem As Worksheet
Prüfen: IsObject, IsArray, TypeName
13. Automation mit Early Binding durchführen
Early Binding vs. Late Binding
14. Index statt Schlüssel in Objektauflistungen verwenden
Worksheets("Januar")
- besser -
Worksheets(1)
15. Referenzieren statt Aktivieren und Selektieren
Workbooks("Mappe.xls").Activate
Worksheets("Monat").Select
Range("B2").Select
ActiveCell.Value = 9
- besser -
Workbooks("Mappe.xls").Worksheets("Monat").Range("B2").Value = 9
Wenn unbedingt selektiert werden muss, dann besser mit Application.Goto:
Workbooks("Mappe.xls").Activate
Worksheets("Monat").Select
Range("B2").Select
- besser -
Application.Goto Workbooks("Mappe.xls").Worksheets("Monat").Range("B2")
16. Schleifen-Konstrukte und Collections
For-Each-Next anstelle For-Next verwenden
261:17
185:14
44:7
20:5
Gewöhnlich werden Objektvariablen in For-Each-Next-Schleifen verwendet. Bitte beachten Sie, dass die Objektvariable nach Gebrauch auf Nothing gesetzt werden sollte. Also obwohl zuvor keine explizite Zuweisung mittels Set vorgenommen wurde.
Dim objPivotTable As PivotTable
For Each objPivotTable In ActiveSheet.PivotTables
cboPivotTables.AddItem objPivotTable.Name
Next
Set objPivotTable = Nothing
17. Eigenschaftswerte in Variablen zwischenspeichern
Eigenschaftswerte sollten in Variablen zwischengespeichert werden, wenn sie mehrmals benötigt werden.
Der Zugriff auf eine Variable ist generell 10 bis 20 mal schneller als der Zugriff auf eine Eigenschaft.
Beispielsweise ist der Zugriff auf die Text-Eigenschaft innerhalb einer Schleife etwa 21 bis 26 mal langsamer als bei Verwendung einer Variable zur Zwischenspeicherung.
18. Funktionsargumente als Wert übergeben
ByRef
- besser -
ByVal
19. Range statt Evaluate verwenden
[A1]
- besser -
Range("A1")
20. LOF statt Open/Close verwenden
[A1]
- besser -
Range("A1")
21. With-Rahmenstrukturen verwenden
Sogenannte With-Rahmen (With-End With) sind gut geeignet, wenn mehrmals hintereinander auf das selbe Objekt zugegriffen werden muss.
Beispiel:
With Application.Workbooks(1).Worksheets(1)
.Range("A1").Value = "Hallo"
.Range("B1").Value = "Hello"
.Range("C1").Value = "Ola""
End With
With-End With-Strukturen können auch verschachtelt werden.
Beispiel:
With Application
With .Workbooks(1)
With .Worksheets(2)
.Range("A1").Value = 1
End With
End With
End With
Zudem kann With-End With auch mit Benutzerformularen (UserForms) verwendet werden.
Beispiel:
Private Sub UserForm_Initialize()
With Me
.Caption = "Fenstertitel"
.Tag = "Musterformular"
End With
End Sub
22. Variablen auf Modul-Ebene statt statische Variablen verwenden
Variablen sollten auf Modul-Ebene deklariert werden und nicht mit Static innerhalb von Prozeduren.
23. Inline Code vs. Prozeduren und Funktionen
Inline Code anstelle Prozedur-Aufrufe verwenden.
24. Zusammengehörender Code im gleichen Code-Modul ablegen
Prouzeduren und Funktionen in gleichem Modul wie der aufrufende Code ablegen.
25. Unqualifizierter Code
Unqualifizierte Aufrufe von Methoden und Eigenschaften sind tatsächlich geringfügig schneller als voll qualifizierter Code.
26. Anzahl Funktionsargumente reduzieren
...
Deklarieren von Datenfeldern
Dim avarRecords(500) -> 501 Elemente
Dim avarRecords(1 To 500) -> 500 Elemente
- oder -
Option Base 1
Dim avarRecords(500) -> 500 Elemente
Fortschrittanzeige in der Statuszeile
Grundsätzlich sollte man den Benutzer über den Fortschritt der Makroausführung informieren. Dies ist vor allem wichtig, wenn ein Vorgang länger dauert, bis er durchgeführt ist.
Application.StatusBar = "Erledigt: " & CStr(intProgress) & " %"
Application.StatusBar = "Eingelesen: " & CStr(lngRecordNumber) & " von " & CStr(lngTotalRecords)
Interface Requests
Die Optimierung der Interface Requests erreicht man am besten anhand eines
Arrays. Ein eindimensionales Array mit 3 Elementen (für die Werte der drei Spalten)
halbiert bereits die Ausführungszeit. Ein zweidimensionales Array mit 3 Elementen für
die Spalten und 1'000 Elementen für jeweils 1'000 Zeilen führt zu der absolut minimalen
Ausführungszeit von 2 Sekunden. Das Array wird mit den Daten aus der Datei gefüllt und
dann anhand der Transpose-Methode an die Exceltabelle übergeben. Bei 20'000 Datenzeilen
wird dieser Vorgang folglich 20 mal durchgeführt, da immer jeweils 1'000 Zeilen
verarbeitet werden. Sowohl "Cells" als auch "Resize" stellen einen
Interface Request dar (siehe Code); insgesamt werden somit 40 Requests durchgeführt (20 x
2 Requests). Im original Makro wurden die Daten pro Datensatz sowie pro Zelle eingetragen:
"Cells" wurde 3 mal in der Do-Loop-Schleife angesprochen; macht bei 20'000
Datensätzen total 60'000 Requests. Das optimierte Makro führt folglich 1'500 mal weniger
Zugriffe durch (60'000 / 40). Und genau hier liegt die ernorme Zeiteinsparung.
Stellt sich die Frage, weshalb man nicht das Array noch grösser machen kann und z.B.
immer 5'000 Zeilen oder sogar gleiche alle 20'000 Zeilen auf einen Schlag verarbeiten
soll. Das geht leider nicht, weil die verwendete Transpose-Methode eine Limitation
besitzt: Sie verträgt maximal 5'461 Array-Elemente. Bei 2'000 Zeilen aus der Datei und 3
benutzten Spalten hätte man die Limitation bereits überschritten, da 2'000 x 3 = 6'000
Elemente gibt. Als Folge würde der Laufzeitfehler 13 "Typen unverträglich"
erscheinen. Die maximale Anzahl Zeilen pro Schleifendurchlauf liegt bei 3 Spalten bei
exakt 1'820 Zeilen, da 3 x 1'820 = 5'460 Elemente gibt. Bei 1'821 Zeilen, d.h. eine Zeile
mehr, wären es bereits 5'463 Elemente - sprich zwei Elemente zuviel, was die oben
erwähnte Fehlermeldung auslösen würde. Der Unterschied zwischen 1'000 und 1'820 Zeilen
ist kaum bis überhaupt nicht spürbar (liegt bei ein paar Zehntelsekunden).
Excel VBA: Efficiency and Performance (by Dermot
Balson & William Mercer)
http://www.avdf.com/apr98/art_ot003.html
http://www.cpearson.com/excel/optimize.htm
http://www.microsoft.com/officedev/articles/movs101.htm
Designing for Performance and Compatibility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbcondesigningforperformancecompatibility.asp
Optimizing VBA Code
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deconoptimizingvbacode.asp
Optimizing Code
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconoptimizingcode.asp
Optimizing for Speed
http://msdn.microsoft.com/library/en-us/vbcon98/html/vbconoptimizingforspeed.asp
Your best tools for writing robust Automation code are:
-and-
The macro recorder in Office applications is an excellent tool for understanding how to accomplish a task with the application's object model and, in some situations, it gives you a great foundation for starting your Automation client. This was illustrated in the previous exercises by translating a recorded Word macro, line-by-line, to create perfectly functional Automation clients.
The macro recorder records actions in the user interface; it records every click and every keystroke you make. Thus, by its very nature, the macro recorder can often generate rather lengthy code that is not as efficient as it could be. To improve a recorded macro, you need an understanding of the application's object model. Obviously this article cannot cover every object in all the Office object models, but it can provide an overview of the more common techniques you can use to improve your Automation code.
Remember that the macro recorder records every action you take in the user interface. In the user interface, when you change an object's property, the macro recorder records both the action of selecting the object and the action of setting the object's property. To shorten this process, you could omit the selection action altogether, when possible, and set the property directly.
To illustrate, if you were to record a macro in Microsoft Excel while performing the following actions:
your recorded macro should look like this:
Workbooks.Add
Sheets("Sheet2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "Hello World"
Range("B3").Select
By examining the object model documentation for Excel, you can determine that a Range is a child of a Worksheet and a Worksheet is a child of a Workbook. Given this information, you can streamline the code to omit both the selection of the sheet and the selection of the cell:
Dim Book as Workbook
Set Book = Workbooks.Add
Book.Sheets("Sheet2").Range("B2").Value = "Hello World"
This is a small example and might not look like much, but if you are automating Excel to perform this operation 100 times or more, then this change can be significant.
Now consider a macro you record in Word to start a new document. Insert a 2 x 2 table and add data to all four cells in the table. Your recorded macro might look like this:
Documents.Add DocumentType:=wdNewBlankDocument ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:=2 Selection.TypeText Text:="a" Selection.MoveRight Unit:=wdCell Selection.TypeText Text:="b" Selection.MoveRight Unit:=wdCell Selection.TypeText Text:="c" Selection.MoveRight Unit:=wdCell Selection.TypeText Text:="d"
With an understanding of how the Word object model is constructed, you can improve the code by omitting the need to select each cell in the table to fill it with data:
Dim Doc As Document
Set Doc = Documents.Add
Dim Tbl As Table
Set Tbl = Doc.Tables.Add(Range:=Selection.Range, NumRows:=2, NumColumns:=2)
Tbl.Cell(1, 1).Range.Text = "a"
Tbl.Cell(1, 2).Range.Text = "b"
Tbl.Cell(2, 1).Range.Text = "c"
Tbl.Cell(2, 2).Range.Text = "d"
Using this solution, you are able to fill a cell in a Word table by referencing the cell by its row and column number rather than having to use the "move" methods to move from one cell to another.
There are reasons other than just performance for you to avoid selecting or activating objects in your Automation code. When an Office application is automated, it is often hidden and therefore does not have a window associated with it. Some methods or properties that rely on a window might not behave as expected when the application is not visible. So, to be safe, it is best that you avoid "select" and "activate" methods with Automation whenever possible.
When an Automation server is visible, changes you make to the server's content can force the server to refresh. If you have a relatively large task to accomplish with an Automation server, complete the task before making the application visible. If your situation requires that the server be visible while you automate it, you might benefit from telling the server not to refresh until the task is complete. The Application objects for Excel and Word have a ScreenUpdating property that you can set to False to prevent screen updates while your Automation code executes. Microsoft Access has a similar property: the Echo property of the Application object.
Some methods and properties can take either a single item or an array of items for an argument. Whether or not an array can be passed for an argument is documented in the Help for the property or method in question. Use the Office VBA documentation to your advantage.
Consider the following Excel macro code that populates four cells with data:
With Worksheets(1)
.Range("a1").Value = "Last Name"
.Range("b1").Value = "First Name"
.Range("c1").Value = "MI"
.Range("d1").Value = "Title"
End With
The Value property of a Range object can accept either a single value or an array of values. So, you can rewrite the code to use an array:
Worksheets(1).Range("a1:d1").Value = Array("Last Name", "First Name", "MI", "Title")
In this example, you are using a single dimensional array but the Value property of the Range object can be set to a two-dimensional array as well.
Note For C/C++ and MFC developers: You can pass a SAFEARRAY or COleSafeArray for the invoked Value property of a Range object.
For Visual Basic and MFC code samples that illustrate how you can use arrays to transfer data to an Excel worksheet, see the section "Automate Excel to Create and Format a New Workbook."
Office Automation servers provide many timesaving methods and properties that can simplify your Automation code when you are using repetitive tasks.
Again, consider Excel as an Automation server for a moment. Suppose you wanted to format all of the "used" cells in column "A," starting at row 1, with a bold font, but do not know exactly how many used cells there are in that column. You might try something like this:
'Where oSheet is a reference to a particular worksheet . . .
counter = 1
Do While (oSheet.Cells(counter,1).Value <> "")
oSheet.Cells(counter, 1).Font.Bold = True
counter = counter + 1
Loop
If you were to examine the Excel object model documentation, you would find that:
Armed with this knowledge, you can rewrite your code:
oSheet.Cells(1,1).Resize(oSheet.UsedRange.Rows.Count, 1).Font.Bold = True
This makes a significant improvement in your Automation code if you have a relatively large range of "used" cells to format.
Now consider a repetitive task you could simplify while automating Microsoft PowerPoint. If you had a slide with three shapes to which you wanted to apply the same text formats, you might try code similar to the following to format all three shapes:
'Where oSlide represents a Slide object . . .
With oSlide.Shapes("Text Box 2").TextFrame.TextRange.Font
.Name = "Arial"
.Size = 16
.Bold = True
End With
With oSlide.Shapes("Text Box 3").TextFrame.TextRange.Font
.Name = "Arial"
.Size = 16
.Bold = True
End With
With oSlide.Shapes("Text Box 4").TextFrame.TextRange.Font
.Name = "Arial"
.Size = 16
.Bold = True
End With
However, if you were to examine the object model documentation for Microsoft PowerPoint, you would find that there is a way to format all three shapes at once by using a ShapeRange object that represents multiple shapes. You can simplify your code with:
'Where oSlide represents a Slide object . . .
Dim oShapes As ShapeRange
Set oShapes = oSlide.Shapes.Range( _
Array("Text Box 2", "Text Box 3", "Text Box 4"))
With oShapes.TextFrame.TextRange.Font
.Name = "Arial"
.Size = 16
.Bold = True
End With
As you can see, there are benefits to finding ways to improve repetitive tasks. Understandably, it is difficult to know every single detail of an Automation server's object model and the features that it exposes. However, with a little research into the object model documentation and forethought regarding repetitive tasks you are automating, you can improve your Automation code and simplify some of the tasks you undertake.
To improve the performance of your code, you should minimize interface requests whenever possible. Because of the way a C/C++ or MFC programmer writes Automation code, minimizing interface requests is not as much of an issue as it is for Visual Basic programmers. Visual Basic provides the programmer the ability to drill down to many levels within the object model in a single statement. When writing Automation code, it is important to keep in mind how drilling down from one object to another affects the number of interfaces you are requesting from the Automation server. Consider this example that drills down from Excel's Application object to a Range object:
oApp.Workbooks.Item("Book1").Worksheets.Item("Sheet1").Cells(1,1).Value = 5
In this single line of code, you are requesting five interfaces: one interface each for a Workbooks collection object, a Workbook object, a Worksheets collection object, a Worksheet object, and a Range object. If this were the only line of code where you were accessing a range on this particular sheet, this would really not make a big difference in the larger scheme of things. But what if the following code is nested in a loop?
For counter = 1 to 100
oApp.Workbooks.Item("Book1").Worksheets.Item("Sheet1").Cells(counter,1).Value = 5
Next
In this loop, you would be making 5 x 100, for a total of 500 interface requests, which is a large number for this small portion of code. You can improve this number by setting object variables for items that you use frequently. In this particular example, you can benefit from setting an object variable for the worksheet that you are populating with data. Consider the following code that provides you with the same results:
Dim oSheet as Excel.Worksheet
Set oSheet = oApp.Workbooks.Item("Book1").Worksheets.Item("Sheet1") '4 Requests
For counter = 1 to 100
oSheet.Cells(counter,1).Value = 5 '1 Request
Next
With this improved code, you make 4 + (100 x 1), a total of 114 interface requests. When you compare 114 to 500 interface requests, you recognize a significant improvement and a very important one because it can greatly increase the performance of your Automation code.
There is another attraction to minimizing your interface requests. When automating any ActiveX component, there is a limit to the number of interface requests you can make within a tight loop on Windows 95 and Windows 98. This limit is roughly 64 kilobytes (KB). If you write code that comes close to or exceeds this limit, you might experience any of the following:
To avoid this limit, use these techniques whenever possible:
For more details on this Windows 95 and Windows 98 limitation, refer to the following article in the Microsoft Knowledge Base:
Q216400 PRB: Cross-Process COM Automation Hangs Client App on Win95/Win98
Artikel von Microsoft
Speed is often a major determining factor in a users overall impression of and satisfaction with an application. Unfortunately, many of the things that influence the speed of an application are beyond your control as a programmer: the speed of the processor, the lack of adequate memory, or the speed of data connections. For this reason, its often necessary to optimize your application so that it will run faster (or at least appear to run faster).
Optimizations for speed can be divided into three general categories: real speed (the actual time spent performing calculations and executing code), display speed (the time spent displaying graphics or painting the screen), and perceived speed (how fast your application appears to run). The types of optimizations that you will actually use depend on the type and purpose of the application — not all optimizations are appropriate or beneficial in all cases.
As with any type of optimization, you need to weigh the potential benefit against the cost. It doesnt make much sense to spend hours optimizing a procedure that is rarely called. Determine the areas where speed improvements will affect (and be noticed by) the most users, such as the initial load time for the application.
Unless you're doing tasks like generating fractals, your applications are unlikely to be limited by the actual processing speed of your code. Typically other factors — such as video speed, network delays, or disk activities — are the limiting factor in your applications. For example, when a form is slow to load, the cause might be the number of controls and graphics on the form rather than slow code in the Form_Load event. However, you may find points in your program where the speed of your code is the gating factor, especially for procedures that are called frequently. When that's the case, there are several techniques you can use to increase the real speed of your applications:
Even if youre not optimizing your code for speed, it helps to be aware of these techniques and their underlying principles. If you get in the habit of choosing more efficient algorithms as you code, the incremental gains can add up to a noticeable overall improvement in speed.
The default data type in Visual Basic is Variant. This is handy for beginning programmers and for applications where processing speed is not an issue. If you are trying to optimize the real speed of your application, however, you should avoid Variant variables. Because Visual Basic converts Variants to the appropriate data type at run time, operations involving other simple data types eliminate this extra step and are faster than their Variant equivalents.
A good way to avoid Variants is to use the Option Explicit statement, which forces you to declare all your variables. To use Option Explicit, check the Require Variable Declaration check box on the Editor tab of the Options dialog box, available from the Tools menu.
Be careful when declaring multiple variables: If you dont use the As type clause, they will actually be declared as Variants. For example, in the following declaration, X and Y are variants:
Dim X, Y, Z As LongRewritten, all three variables are Longs:
Dim X As Long, Y As Long, Z As LongFor More Information To learn more about Visual Basic data types, see "Data Types" in "Programming Fundamentals."
For arithmetic operations avoid Currency, Single, and Double variables. Use Long integer variables whenever you can, particularly in loops. The Long integer is the 32-bit CPU's native data type, so operations on them are very fast; if you cant use the Long variable, Integer or Byte data types are the next best choice. In many cases, you can use Long integers when a floating-point value might otherwise be required. For example, if you always set the ScaleMode property of all your forms and picture controls to either twips or pixels, you can use Long integers for all the size and position values for controls and graphics methods.
When performing division, use the integer division operator (\) if you dont need a decimal result. Integer math is always faster than floating-point math because it doesnt require the offloading of the operation to a math coprocessor. If you do need to do math with decimal values, the Double data type is faster than the Currency data type.
The following table ranks the numeric data types by calculation speed.
| Numeric data types | Speed |
| Long | Fastest |
| Integer | |
| Byte | |
| Single | |
| Double | |
| Currency | Slowest |
You can get and set the value of variables faster than those of properties. If you are getting the value of a property frequently (such as in a loop), your code runs faster if you assign the property to a variable outside the loop and then use the variable instead of the property. Variables are generally 10 to 20 times faster than properties of the same type.
Never get the value of any given property more than once in a procedure unless you know the value has changed. Instead, assign the value of the property to a variable and use the variable in all subsequent code. For example, code like this is very slow:
For i = 0 To 10
picIcon(i).Left = picPallete.Left
Next IRewritten, this code is much faster:
picLeft = picPallete.Left
For i = 0 To 10
picIcon(i).Left = picLeft
Next ILikewise, code like this . . .
Do Until EOF(F)
Line Input #F, nextLine
Text1.Text = Text1.Text + nextLine
Loop. . . is much slower than this:
Do Until EOF(F)
Line Input #F, nextLine
bufferVar = bufferVar & nextLine & vbCrLf
Loop
Text1.Text = bufferVarHowever, this code does the equivalent job and is even faster:
Text1.Text = Input(F, LOF(F))As you can see, there are several methods for accomplishing the same task; the best algorithm is also the best optimization.
This same technique can be applied to return values from functions. Caching function return values avoids frequent calls to the run-time dynamic-link library (DLL), Msvbvm60.dll.
While variables declared as Static are useful for storing a value over multiple executions of a procedure, they are slower than local variables. By storing the same value in a module-level variable your procedure will execute faster. Note, however, that you will need to make sure that only one procedure is allowed to change the module-level variable. The tradeoff here is that your code will be less readable and harder to maintain.
Although using procedures makes your code more modular, performing each procedure call always involves some additional work and time. If you have a loop that calls a procedure many times, you can eliminate this overhead by removing the procedure call and placing the body of the procedure directly within the loop. If you place the same code inline in several loops, however, the duplicate code increases the size of your application. It also increases the chances that you may not remember to update each section of duplicate code when you make changes.
Likewise, calling a procedure that resides in the same module is faster than calling the same module in a separate .BAS module; if the same procedure needs to be called from multiple modules this gain will be negated.
Using constants makes your application run faster. Constants also make your code more readable and easier to maintain. If there are strings or numbers in your code that dont change, declare them as constants. Constants are resolved once when your program is compiled, with the appropriate value written into the code. With variables, however, each time the application runs and finds a variable, it needs to get the current value of the variable.
Whenever possible, use the intrinsic constants listed in the Object Browser rather than creating your own. You dont need to worry about including modules that contain unused constants in your application; when you make an .exe file, unused constants are removed.
When writing Sub or Function procedures that include unmodified arguments, it is faster to pass the arguments by value (ByVal) than to pass them by reference (ByRef). Arguments in Visual Basic are ByRef by default, but relatively few procedures actually modify the values of their arguments. If you dont need to modify the arguments within the procedure, define them as ByVal, as in the following example:
Private Sub DoSomething(ByVal strName As String, ByVal intAge As Integer)
Typed optional arguments can improve the speed of your Sub or Function calls. In prior versions of Visual Basic, optional arguments had to be Variants. If your procedure had ByVal arguments, as in the following example, the 16 bytes of the Variant would be placed on the stack.
Private Sub DoSomething(ByVal strName As String, Optional ByVal vntAge As Variant, Optional ByVal vntWeight As Variant)Your function uses less stack space per call, and less data is moved in memory, if you use typed optional arguments:
Private Sub DoSomething(ByVal strName As String, Optional ByVal intAge As Integer, Optional ByVal intWeight As Integer)The typed optional arguments are faster to access than Variants, and as a bonus, you'll get a compile-time error message if you supply information of the wrong data type.
The ability to define and use collections of objects is a powerful feature of Visual Basic. While collections can be very useful, for the best performance you need to use them correctly:
Collections allow you to iterate through them using an integer For...Next loop. However, the For Each...Next construct is more readable and in many cases faster. The For Each...Next iteration is implemented by the creator of the collection, so the actual speed will vary from one collection object to the next. However, For Each...Next will rarely be slower than For...Next because the simplest implementation is a linear For...Next style iteration. In some cases the implementor may use a more sophisticated implementation than linear iteration, so For Each...Next can be much faster.
It is quicker to add objects to a collection if you don't use the Before and After arguments. Those arguments require Visual Basic to find another object in the collection before it can add the new object.
When you have a group of objects of the same type, you can usually choose to manage them in a collection or an array (if they are of differing types, a collection is your only choice). From a speed standpoint, which approach you should choose depends on how you plan to access the objects. If you can associate a unique key with each object, then a collection is the fastest choice. Using a key to retrieve an object from a collection is faster than traversing an array sequentially. However, if you do not have keys and therefore will always have to traverse the objects, an array is the better choice. Arrays are faster to traverse sequentially than collections.
For small numbers of objects, arrays use less memory and can often be searched more quickly. The actual number where collections become more efficient than arrays is around 100 objects; however, this can vary depending on processor speed and available memory.
Because of the graphical nature of Microsoft Windows, the speed of graphics and other display operations is crucial to the perceived speed of the application. The faster forms appear and paint, the faster your application will seem to the user. There are several techniques you can use to speed up the apparent speed of your application, including:
Unless you are using graphics methods (Line, PSet, Circle, and Print), you should set ClipControls to False for the form and for all frame and picture box controls (it may cause unpredictable results if your code includes graphics methods that draw behind other controls). When ClipControls is False, Visual Basic doesnt overpaint controls with the background before repainting the controls themselves. On forms that contain a lot of controls, the resulting speed improvements are significant.
For More Information See "Layering Graphics with AutoRedraw and ClipControls" in "Working with Text and Graphics."
When AutoRedraw is set to True for a form or control, Visual Basic maintains a bitmap to repaint that form or control. Although this improves the speed of simple repaints (for example, when the form or control is revealed after a window that covers it is removed), it slows graphics methods. Visual Basic has to perform the graphics methods on the AutoRedraw bitmap and then copy the entire bitmap to the screen. This process also consumes a considerable amount of memory.
If your application generates complex graphics but doesnt change them frequently, setting AutoRedraw to True is appropriate. But if your application draws graphics that must change frequently, you will get better performance if you set AutoRedraw to False and perform the graphics methods for the form or control in the Paint event.
For More Information See "Layering Graphics with AutoRedraw and ClipControls" in "Working with Text and Graphics."
This optimization improves the speed and minimizes the size of your application; use it whenever possible. When you are simply displaying pictures and reacting to click events and mouse actions on them, use the image control instead of the picture box. Dont use a picture box unless you need the capabilities only the picture box provides, such as graphics methods, the ability to contain other controls, or dynamic data exchange (DDE).
Every repaint is expensive. The fewer repaints Visual Basic must perform, the faster your application will appear. One way to reduce the number of repaints is to make controls invisible while you are manipulating them. For example, suppose you want to resize several list boxes in the Resize event for the form:
Sub Form_Resize ()
Dim i As Integer, sHeight As Integer
sHeight = ScaleHeight / 4
For i = 0 To 3
lstDisplay(i).Move 0, i * sHeight, _
ScaleWidth, sHeight
Next
End SubThis creates four separate repaints, one for each list box. You can reduce the number of repaints by placing all the list boxes within a picture box, and hiding the picture box before you move and size the list boxes. Then, when you make the picture box visible again, all of the list boxes are painted in a single pass:
Sub Form_Resize ()
Dim i As Integer, sHeight As Integer
picContainer.Visible = False
picContainer.Move 0, 0, ScaleWidth, ScaleHeight
sHeight = ScaleHeight / 4
For i = 0 To 3
lstDisplay(i).Move 0, i * sHeight, _
ScaleWidth, sHeight
Next
picContainer.Visible = True
End SubNote that this example uses the Move method instead of setting the Top and Left properties. The Move method sets both properties in a single operation, saving additional repaints.
The Line method is faster than a series of PSet methods. Avoid using the PSet method and batch up the points into a single Line method. Shape and line controls are appropriate for simple graphical elements that rarely change; complex graphics, or graphics that change rapidly, are generally best handled with graphics methods.
Often the subjective speed of your application has little to do with how quickly it actually executes its code. To the user, an application that starts up rapidly, repaints quickly, and provides continuous feedback feels "snappier" than an application that just "hangs up" while it churns through its work. You can use a variety of techniques to give your application that "snap":
Hiding forms instead of unloading them is a trick that has been around since the early days of Visual Basic 1.0, but it is still effective. The obvious downside to this technique is the amount of memory the loaded forms consume, but it can't be beat if you can afford the memory cost and making forms appear quickly is of the highest importance.
You can also improve the apparent speed of your application by prefetching data. For example, if you need to go to disk to load the first of several files, why not load as many of them as you can? Unless the files are extremely small, the user is going to see a delay anyway. The incremental time spent loading the additional files will probably go unnoticed, and you won't have to delay the user again.
In some applications you can do considerable work while you are waiting for the user. The best way to accomplish this is through a timer control. Use static (or module-level) variables to keep track of your progress, and do a very small piece of work each time the timer goes off. If you keep the amount of work done in each timer event very small, users won't see any effect on the responsiveness of the application and you can prefetch data or do other things that further speed up your application.
For More Information To learn more about the timer control, see "Timer Control" in "Using Visual Basics Standard Controls." For a discussion of background processing, see "Interrupting Background Processing" in "Responding to Mouse and Keyboard Events."
When you can't avoid a long delay in your program, you need to give the user some indication that your application hasn't simply hung. Windows uses a standard progress bar to indicate this to users. You can use the ProgressBar control in the Microsoft Windows Common Controls included with the Professional and Enterprise editions of Visual Basic. Use DoEvents at strategic points, particularly each time you update the value of the ProgressBar, to allow your application to repaint while the user is doing other things.
At the very least, you should display the wait cursor to indicate the delay by setting the forms MousePointer property to vbHourglass (11).
Apparent speed is most important when your application starts. Users' first impression of the speed of an application is measured by how quickly they see something after clicking on its name in the Start menu. With the various run-time DLLs that need to be loaded for Visual Basic for Applications, ActiveX controls, and so forth, some delay is unavoidable with any application. However, there are some things you can do to give a response to the user as quickly as possible:
When a form is first loaded, all of the code in the Form_Load event occurs before the form is displayed. You can alter this behavior by using the Show method in the Form_Load code, giving the user something to look at while the rest of the code in the event executes. Follow the Show method with DoEvents to ensure that the form gets painted:
Sub Form_Load()
Me.Show ' Display startup form.
DoEvents ' Ensure startup form is painted.
Load MainForm ' Load main application fom.
Unload Me ' Unload startup form.
MainForm.Show ' Display main form.
End Sub
The more complicated a form is, the longer it takes to load. Keep your startup form simple. Most applications for Microsoft Windows display a simple copyright screen (also known as a splash screen) at startup; your application can do the same. The fewer controls on the startup form, and the less code it contains, the quicker it will load and appear. Even if it immediately loads another, more complicated form, the user will know that the application has started.
For large applications you may want to preload the most commonly used forms at startup so that they can be shown instantly when needed. A satisfying way to do this is to display a progress bar in your startup form and update it as you load each of the other forms. Call DoEvents after loading each form so that your startup form will repaint. Once all the important forms have been loaded, the startup form can show the first one and unload itself. Of course, each form you preload will run the code in its Form_Load event, so take care that this doesn't cause problems or excessive delays.
Visual Basic loads code modules on demand, rather than all at once at startup. This means that if you never call a procedure in a module, that module will never be loaded. Conversely, if your startup form calls procedures in several modules, then all of those modules will be loaded as your application starts up, which slows things down. You should therefore avoid calling procedures in other modules from your startup form.
A large part of the time required to start a Visual Basic application is spent loading the various run-time DLLs for Visual Basic, ActiveX, and ActiveX controls. Of course, if these are already loaded, none of that time need be spent. Thus users will see your application start up faster if there is another application already running that uses some or all of these DLLs.
One way to significantly improve the startup performance of your applications is to provide another small, useful application that the user always runs. For example, you might write a small application to display a calendar and install it in the startup group for Windows. It will then load automatically on system startup, and while it is useful in itself, it also ensures that the various Visual Basic run-time DLLs are loaded.
Finally, with the Professional and Enterprise editions of Visual Basic you can divide your application into a main skeleton application and several component executables or DLLs. A smaller main application will load faster, and it can then load the other parts as needed.
In the past, available memory and system resources were often limiting factors in designing an application. With 32-bit operating systems, such as Windows 95/98 and Windows NT, these factors are rarely a concern for most Visual Basic programmers. However, there are a number of scenarios where minimizing the size of an application is still important.
Size is extremely important for applications that will be downloaded from the Internet or transferred as attachments to e-mail. For those not fortunate enough to have high-speed data connections, transferring a 1-megabyte file could take an hour or more. In addition to the .exe file, many applications will require additional .dll or .ocx files, adding to the size (and time) of the download. In these scenarios, you would want to optimize your applications size on disk.
Even if users wont be downloading your application, its usually a good idea to make your application as compact as possible. Smaller applications load faster, and because they consume less memory, you can run additional applications at the same time. You can often improve performance by optimizing your applications size in memory.
To learn more about size optimizations, see the following topics:
When reducing the size of an application is important, there are a number of techniques that you can apply to make your code more compact. In addition to reducing the applications size in memory, most of these optimizations will also reduce the size of the .exe file. As an additional benefit, a smaller application will load faster.
Most size optimization techniques involve eliminating unnecessary elements from your code. Visual Basic automatically eliminates certain elements when you compile your application. There is no reason to restrict the length or number of the following elements:
None of these elements affect the size of your application in memory when it is running as an .exe file.
Other elements, such as variables, forms, and procedures, do take up space in memory. It is usually best to streamline these. There are several techniques you can use to reduce the memory your application occupies when it is running as an .exe file. These techniques can reduce code size:
Each loaded form, whether visible or not, consumes a significant amount of memory (which varies with the number and types of controls on the form, the size of bitmaps on the form, and so on). Load forms only when you need to display them, and unload them (rather than hide them) when you no longer need them. Remember that any reference to properties, methods, or controls on a form, or a form variable declared with New, causes Visual Basic to load the form.
When you unload a form using the Unload method, only a portion of the memory occupied by the form is released. To free all memory, invalidate the reference to the form by using the Nothing keyword:
Set Form = Nothing
When designing your application, try to place as few controls on a form as possible. The actual limit depends on the type of controls as well as available system, but in practice, any form with a large number of controls will perform slowly. A related technique is to use control arrays where possible, rather than putting a large number of controls of the same type on a form at design time.
For More Information To learn more about control arrays, see "Working with Control Arrays" in "Using Visual Basics Standard Controls."
Label controls use fewer Windows resources than text boxes do, so you should use labels in place of text boxes whenever possible. For example, if you need a hidden control on a form for storing text, it is more efficient to use a label.
Even a data entry form that requires numerous text fields can be optimized using this technique. You can create a label for each field and use a single text box for input, moving it to the next label's location in the LostFocus event:
Private Sub Label1_LostFocus()
' Update Label1
Label1.Caption = Text1.Text
' Move the Textbox over the next label
Text1.Move Label2.Left, Label2.Top
' Update Text1 contents
Text1.Text = Label2.Caption
End Sub
You can make a label look like a text box by setting the BackColor and BorderStyle properties. Although this technique requires more code, it can significantly reduce resource usage for a form that contains numerous fields.
Data you place directly into your application at design time (as properties or as literal strings and numbers in your code) increases the memory the application consumes at run time. You can reduce memory by loading the data from disk file or resources at run time. This is particularly valuable for large bitmaps and strings.
For More Information For information on adding resources to your application, see "Resource Files" in "Advanced Programming Features."
Visual Basic loads modules on demand — that is, it loads a module into memory only when your code calls one of the procedures in that module. If you never call a procedure in a particular module, Visual Basic never loads that module. Placing related procedures in the same module causes Visual Basic to load modules only as needed.
The Variant data type is extremely flexible, but it is also larger than any of the other data types. When you must squeeze every last byte out of your application, consider replacing Variant variables, and especially arrays of Variant variables, with other data types.
Each Variant takes 16 bytes, compared to 2 for an Integer or 8 for a Double. Variable-length String variables use 4 bytes plus 1 byte per character in the string, but each Variant containing a string takes 16 bytes plus 1 byte per character in the string. Because they are so large, Variant variables are particularly troublesome when used as local variables or arguments to procedures, because they quickly consume stack space.
In some cases, however, using other data types forces you to add more code to compensate for the loss of flexibility that the Variant data type provides, resulting in no net reduction in size.
Consider using dynamic arrays instead of fixed arrays. When you no longer need the data in a dynamic array, use Erase or ReDim Preserve to discard unneeded data, and reclaim the memory used by the array. For example, you can reclaim the space used by a dynamic array with the following code:
Erase MyArray
Whereas Erase completely eliminates the array, ReDim Preserve makes the array smaller without losing its contents:
ReDim Preserve MyArray(10, smallernum)
Erasing a fixed-size array will not reclaim the memory for the array — it simply clears out the values of each element of the array. If each element was a string, or a Variant containing a string or array, then erasing the array would reclaim the memory from those strings or Variants, not the memory for the array itself.
The space used by (nonstatic) local string and array variables is reclaimed automatically when the procedure ends. However, global and module-level string and array variables remain in existence for as long as your program is running. If you are trying to keep your application as small as possible, you should reclaim the space used by these variables as soon as you can. You reclaim string space by assigning the zero-length string to it:
SomeStringVar = "" ' Reclaim space.
Similarly, you can reclaim some (but not all) of the space used by an object variable by setting it to Nothing. For example, to remove a Recordset object variable:
Private rs As New RecordSet
' Code to initialize and use recordset would go here
rs.Close ' Close the recordset
Set rs = Nothing ' Set the object reference to Nothing
If you dont explicitly set an object reference to Nothing, a reference to the object will remain in memory until the application is terminated; for an application that uses a lot of objects this can quickly consume your available memory and slow the application.
You can also reclaim space by unloading forms and setting them to Nothing rather than simply hiding them when they are no longer needed.
As you develop and modify your applications, you may leave behind dead code — entire procedures that are not called from anywhere in your code. You may also have declared variables that are no longer used. Although Visual Basic does remove unused constants, it does not remove unused variables and dead code when you create an .exe. Consider reviewing your code to find and remove unused procedures and variables. For example, Debug.Print statements, while ignored in the run-time .exe, are sometimes present in the .exe file.
Debug.Print statements with strings or variables as arguments are not compiled when you create an .exe. However, where Debug.Print statements have a function call as an argument, the Debug.Print statement itself is ignored by the compiler, but the function call is compiled. Then, when the application is run, the function is called but the return is ignored. Because functions that appear as arguments to Debug.Print will take up space and cycle time in an .exe, it may be beneficial to delete these statements before you make an .exe.
Use the Find command on the Edit menu to search for references to a particular variable. Or, if you have Option Explicit statements in each of your modules, you can quickly discover if a variable is used in your application by removing or commenting out its declaration and running the application. If the variable is used, Visual Basic will generate an error. If you dont see an error, the variable was not used.
Graphics (pictures and graphics methods) can consume a lot of memory. To some extent, this is unavoidable: Graphics contain a lot of information, so they tend to be large. But in many cases, you can reduce the impact that graphics have on the size of your application by applying some of the following techniques:
The picture controls in many Visual Basic applications exist merely to be clicked or to be dragged and dropped. If this is all you're doing with a picture control, you are wasting a lot of Windows resources. For these purposes, image controls are superior to picture controls. Each picture control is an actual window and uses significant system resources. The image control is a "lightweight" control rather than a window and doesn't use nearly as many resources. In fact, you can typically use five to 10 times as many image controls as picture controls. Moreover, image controls repaint faster than picture controls. Only use a picture controls when you need a feature only it provides, such as dynamic data exchange (DDE), graphics methods, or the ability to contain other controls.
When you set a Picture property at design time, you add the picture to the form and thereby increase the memory the form consumes at run time. You can reduce memory consumption by storing pictures in a resource file and using the LoadResPicture function to load them at run time. If you never use all the pictures associated with a form at the same time, this technique saves memory over storing all the pictures in controls on the form. It can speed up form load because not all the pictures need to be loaded before the form can be shown.
You can share the same picture between multiple picture controls, image controls, and forms. If you use code like this you only maintain one copy of the picture:
Picture = LoadPicture("C:\Windows\Chess.gif")
Image1.Picture = Picture ' Use the same picture.
Picture1.Picture = Picture ' Use the same picture.
Contrast that with this code, which causes three copies of the bitmap to be loaded, taking more memory and time:
Picture = LoadPicture("C:\Windows\Chess.gif")
Image1.Picture = LoadPicture("C:\Windows\Chess.gif")
Picture1.Picture = LoadPicture("C:\Windows\Chess.gif")
Similarly, if you load the same picture into several forms or controls at design time, a copy of that picture is saved with each form or control. Instead, you could place the picture in one form and then share it with the other forms and controls as described above. This makes your application both smaller (because it doesn't contain redundant copies of the picture) and faster (because the picture doesn't have to be loaded from disk multiple times).
Rather than placing bitmaps in controls, you can use the PaintPicture method to display bitmaps anywhere on forms. This is particularly useful when you want to tile a bitmap repeatedly across a form: You only need to load the bitmap once, but you can use PaintPicture to draw it multiple times.
When you are no longer using a picture in the Picture property of a form, picture box, or image control, set the Picture property to Nothing to empty it:
Set Picture1.Picture = Nothing
If you use the Image property of a picture box or form, Visual Basic creates an AutoRedraw bitmap (even if the AutoRedraw property for that form or picture box is False). When you have finished using the Image property, you can reclaim the memory used by this bitmap by using the Cls method before setting AutoRedraw to False. For example, the following code reclaims the memory used by the Image property for a control called mypic:
mypic.AutoRedraw = True ' Turn on AutoRedraw bitmap.
mypic.Cls ' Clear it.
mypic.AutoRedraw = False ' Turn off bitmap.
Although the default picture format is the bitmap (.gif), Visual Basic can also utilize other graphics file formats. Several painting and graphics programs allow you to save bitmaps in a standard compressed bitmap format called Run Length Encoded (.rle). Rle bitmaps can be several times smaller than their uncompressed counterparts, particularly for bitmaps that contain large swatches of solid color, and they aren't appreciably slower to load or display. Using metafiles (.wmf) can produce even greater savings — 10 times or more in some cases. Try to use metafiles at their normal size: They are much slower to paint when they have to be stretched larger or smaller.
You can also use .gif and .jpg formats. They are generally much smaller; however there is some tradeoff in image quality and loading speed.
Visual Basic enables you to think about the architecture of your application in new ways. Instead of a single, monolithic executable, you can write an application that consists of a core front-end executable supported by a number of ActiveX components. This approach offers several significant optimization benefits:
In addition, the components can be debugged independently and reused in other applications. This may not improve the speed of your application, but it may improve your speed in creating the next one.
To determine how to best optimize your application by segmenting it, you must evaluate the kinds of components you can create and how they fit into your application. There are three kinds of components you can create with the Professional or Enterprise editions of Visual Basic:
These three kinds are not exclusive: You could use all three in a single application. But from the standpoint of optimizing your application, they each have very different characteristics.
For More Information Component creation is discussed in depth in the Component Tools Guide included with the Professional and Enterprise editions of Visual Basic.
A cross-process component is an executable program that offers its services to other programs. Like all executables, it starts up and runs with its own stack in its own process space; thus, when a application acting as a client uses one of the objects provided by a component, the operation crosses from the client's process space to the component's — hence the name. Cross-process components offer some valuable features when compared to the other types:
Of these, the first and the last points are of particular interest from an optimization standpoint.
Because a cross-process component is a separate program, it can operate asynchronously with the component acting as a client. It has a separate "thread" that multitasks with the client program (technically speaking this is not a thread but a separate process; however, conceptually the two are equivalent). The two programs can communicate and share objects, but they run independently. This is particularly useful when your application needs to perform some operation that takes a long time. The client can call the component to perform the operation and then continue responding to the user.
Even if your application will run on a 32-bit system, you may not be able to make it 32-bit immediately if you rely on legacy 16-bit applications or components. However, if you segment your application using cross-process components, you can mix and match 16-bit and 32-bit components. This allows you to incrementally take advantage of 32-bit features and performance while preserving your investment in 16-bit components.
For all their strengths, cross-process components have a significant disadvantage: performance. This manifests itself in a couple of ways:
A cross-process component is an executable created with Visual Basic, so the same startup issues related to application startup also apply. The good news is that if you are calling a cross-process component written in Visual Basic from another Visual Basic program, almost all the support DLLs will already be loaded. This greatly reduces the time required to start the component. Many components are smaller than your average Visual Basic application, with few or no forms to load, which again improves load time. Nevertheless, a cross-process component will always be slower to start than an in-process component.
Once it is running, a cross-process component suffers from its very nature: Every interaction with the component is a cross-process call. Crossing process boundaries takes a lot of CPU cycles. So every reference to an object from the cross-process component is much more expensive than an equivalent reference to an object in the client application itself or an in-process component. Reducing the number of cross-process calls in your code can reduce the impact of the cross-process call overhead.
An in-process component offers its services to other programs within their process space. Compared to cross-process components, in-process components offer two advantages:
With an in-process component, no new process needs to be created and no run-time DLLs need to be loaded. This can make an in-process component considerably quicker to load compared to an equivalent cross-process component.
Because it is in-process, there is no cross-process overhead when referring to the methods or properties on an object supplied by the component. Objects from the component operate with the same efficiency as objects within the client application itself.
The Enterprise Edition of Visual Basic enables you to create remote components that execute on a separate machine elsewhere on the network. Although network overhead will inevitably exact a toll on application performance, you can make up for it by using the resources of additional CPUs. This is particularly true when you work with a remote component that is operating on data that is local to the machine containing the component. Since this data would have to be fetched across the network anyway, a component operating on it locally and returning only the results across the network can actually be more efficient.
For example, you might write an object in a component that can search for files matching a specified criteria on the local hard disk. By making this a remote component and placing a copy on each machine on the network, you could write a distributed file-finder program that searches all the network components in parallel, using all those CPU resources.
As you use more and more objects in your Visual Basic applications, optimizing your use of those objects becomes more and more important. There are several key techniques to making the most efficient use of objects:
In Visual Basic, referencing another applications object in your code (by getting or setting an objects property, or executing one of its methods) constitutes a cross-process call. Cross-process calls are expensive and you should try to avoid them if you are concerned about optimizing your application.
Visual Basic can use objects more efficiently if it can early bind them. An object can be early bound if you supply a reference to a type library containing the object, and you declare the type of the object:
Dim X As New MyObject
Or, equivalently:
Dim X As MyObject
Set X = New MyObject
Early binding enables Visual Basic to do most of the work of resolving the definition of the object at compile time rather than at run time, when it impacts performance. This also allows Visual Basic to check the syntax of properties and methods used with the object and report any errors.
If Visual Basic cannot bind an object early, it must bind it late. Late binding objects is expensive: At compile time you get no error checking, and each reference at run time requires at least 50% more work by Visual Basic.
Generally, you should always early bind objects if possible. The only times you should have to declare a variable As Object is if you do not have a type library for the object in question, or you need to be able to pass any kind of object as an argument to a procedure.
For More Information To learn more about early binding, see "Speeding Object References" in "Programming with Components."
When referencing other applications' objects from Visual Basic, you use the dot syntax "." to navigate an objects hierarchy of collections, objects, properties, and methods. It is not uncommon to create very lengthy navigation strings. For example:
' Refers to cell A1 on Sheet1 in the first workbook
' of an Microsoft Excel spreadsheet.
Application.Workbooks.Item(1).Worksheets.Item_
("Sheet1").Cells.Item(1,1)
In addition to being a rather lengthy string to type, this line of code is fairly difficult to read — and it is extremely inefficient.
When calling an object from Visual Basic, each "dot" requires Visual Basic to make multiple calls.
To write the most efficient applications, minimize the use of dots when referencing an object.
You can usually minimize the dots by analyzing the objects and methods available to you. For example, the above line of code can be shortened by removing the Item method (this is the default method for collections anyway, so youll rarely use it in code) and by using the more efficient Range method:
' Refers to cell A1 on Sheet1 in the first workbook
' of an Microsoft Excel spreadsheet.
Application.Workbooks(1).Worksheets("Sheet1")_
.Range("A1")
You can shorten this even further by rewriting the code so that it refers to the active sheet in the active workbook, instead of a specific sheet in a specific workbook:
' Refers to cell A1 on the active sheet in the
' active workbook.
Range("A1")
Of course, the above example assumes its OK to refer to cell A1 of any sheet that happens to be active.
Using the Set statement also allows you to shorten navigation strings and gives you a bit more control over your code. The following example uses the Dim and Set statements to create variables that refer to frequently used objects:
Dim xlRange As Object
Set xlRange = Application.ActiveSheet.Cells(1,1)
xlRange.Font.Bold = True
xlRange.Width = 40
Visual Basic provides the With...End With construct to set an implied object within code:
With Application.ActiveSheet.Cells(1,1)
.Font.Bold = True
.Width = 40
End With
If you are using a cross-process ActiveX component, you can't completely avoid making cross-process calls. However, there are several ways to minimize the number of cross-process calls you need to make. If possible, do not reference objects inside a For...Next loop. Cache values in variables and use the variables in loops. If you have to call a large number of methods on an object, you can greatly improve the performance of your application by moving the code into the component. For example, if the component is Word or Microsoft Excel, you can put a looping macro in a template in Word or a looping procedure into module in Microsoft Excel. You then call the macro or procedure from Visual Basic, which is a single call that launches a looping operation within the component.
If you are writing components, you can design the objects in the component to be efficient by reducing the cross-process calls required to perform an operation. For example, when you have several interrelated properties, implement a method with several arguments — one for each property. Calling the method requires a single cross-process call regardless of how many arguments it has, whereas setting each property requires a separate cross-process call. Likewise, if you anticipate that the component acting as a client will want to call your component in a loop (for example, to sum or average all the values in a list property), you can improve performance by providing methods that do the looping within your object and return the appropriate value.
By default, applications created in Visual Basic are compiled as interpreted or p-code executables. At run time, the instructions in the executables are translated or interpreted by a run-time dynamic-link library (DLL). The Professional and Enterprise editions of Visual Basic include the option to compile a native code .exe. In many cases, compiling to native code can provide substantial gains in speed over the interpreted versions of the same application; however, this is not always the case. The following are some general guidelines regarding native-code compilation.
Note that any calls to objects, DLLs or Visual Basic for Applications run-time functions will negate the performance benefits of native code. This is because relatively little time is spent executing code — the majority of time (usually around 90–95%) is spent inside forms, data objects, Windows .dlls, or the Visual Basic for Applications run time, including intrinsic string and variant handling.
In real-world tests, client applications typically spent about 5% of their total execution time executing the p-code. Hence, if native code was instantaneous, using native code for these programs would provide at most a 5% performance improvement.
What native code does is to enable programmers to write snippets of code or computationally intensive algorithms in Basic that were never possible before because of performance issues. Enabling these "snippets" to run much faster can also improve the responsiveness of certain portions of an application, which improves the perceived performance of the overall application.
Haben Sie Fragen,
Anregungen oder Hinweise?Zuletzt aktualisiert am
17.09.2004 / 18:30 Uhr
© 2002-2004 by Philipp von Wartburg, CH-8917 Oberlunkhofen
Alle Rechte vorbehalten