Office - Vba Code Optimization

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 1 von 19

All Products | Support | Search | microsoft.com Gui

Office | Access | Excel | FrontPage | Outlook | PowerPoint | Word | VBA |

December 10, Back to Microsoft Office Articles and Sample Apps 1998

VBA Code Optimisation By Ken Getz This article originally appeared in the conference proceedings for the Microsoft Office and VBA Solutions Conference and Exposition, held in London, England, July 12-15, 1998, and is reprinted with permission of Informant Communications Group, Inc. Please visit their Web site at http://www.informant.com/mod/index.asp Download the sample code discussed in this article.

Why Optimise? As with any large Windows development environment, you can make choices when writing your own Access applications that will affect the performance of your application. How you create your queries, how you organise your tables, and how you write VBA code can all affect your application's speed. This paper provides some suggestions about what you can do to make your Access applications work as well as possible, focusing on the choices you make when writing VBA code. Top

Tuning Your Application's Performance The faster your application and each of its components perform, the more usable it will be. No user (or developer) likes a slow application. Getting extra performance sometimes requires you to make trade-offs and may affect other aspects of the application's usability, stability, and maintainability. Thus, it's important to keep the following issues in mind as you tune your applications for speed. Some of the aspects of performance tuning: l

Hardware and memory

l

Access configuration

l

Database design

l

Query design

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. l

Forms design

l

Reports design

l

l

Seite 2 von 19

Single-user versus multi-user, file-server versus client/server application design VBA coding

To create applications that perform well, you will have to address many, if not all, of these areas. In this paper, I'll present methods you can use to test your hypotheses to solve any particular VBA question, and a series of suggestions about steps to take (and not to take) to make your VBA code work as well as possible. Top

Speeding Up VBA: Testing Hypotheses As in any programming language, VBA has many ways to accomplish the same task. Because you're dealing not only with a language but also with the interface and the underlying data, all tied together in the programming environment, the choices are often even more complicated than with other, more standard languages. The following sections propose a series of selected optimisations, some more potent than others, and some that are based on incorrect assumptions (that is, they don't help at all and perhaps even hurt). Probably no single application will be able to use each of these, but you can add the ones that help to your "bag of tricks" as you program in Access. You'll also find a method for timing those optimisations so you can create your own test cases. Top

Creating a Stopwatch Although you could use the VBA Timer function to calculate the time a specific process requires, it's not the wisest choice. Because it measures time in seconds since midnight in a singleprecision floating-point value, it's not terribly accurate. Even though you'll most likely be timing intervals larger than a single second, you'll want a bit more accuracy than the Timer function can provide. The Windows API provides the timeGetTime function (aliased as timeGetTime in the sample code), which returns the number of milliseconds that have passed since Windows was started. Note: Not that it matters for testing purposes, but Timer "rolls over" every 24 hours. timeGetTime keeps on ticking for up to 49 days before it resets the returned tick count to 0. Most likely, if you're timing something that runs for 49 days, you're not interested in milliseconds, but that's what you get. One more benefit, you'll see later: the act of calling timeGetTime is significantly faster than calling Timer. To test each of the proposed optimisations, you need some mechanism for starting and stopping the clock. The subroutine StartTimer stores the current return value from timeGetTime into a

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 3 von 19

StartTimer stores the current return value from timeGetTime into a global variable, lngStartTime. You must call this subroutine directly before any code you want to have timed. When you're done with the critical section of code, call the function EndTimer, which returns the difference between the current time and the time when you called StartTimer, or the elapsed time. Figure 1 shows the declarations and code for the timer functions. Private Declare Function timeGetTime Lib "winmm.dll" () As Long Dim lngStartTime As Long Sub StartTimer() lngStartTime = timeGetTime() End Sub Function EndTimer() EndTimer = timeGetTime() - lngStartTime End Function Figure 1: Code to perform timings, using the timeGetTime API function.

Note: Most Windows programmers have used the GetTickCount function in previous versions of Windows to perform their high-resolution timing. Although that function returned its result in milliseconds, it was never more accurate than the clock timer in your PC, which measures time in increments of 1/18 second. The timeGetTime function, introduced in Windows 3.0 as part of the multimedia extensions, uses a different hardware timer and can actually measure time with millisecond accuracy. Before Windows 95 and Windows NT, you couldn't have been sure your users had the correct multimedia .DLLs on their system. With the new operating systems, you're assured that all users will have the necessary DLLs, and you can use timeGetTime without worry. Top

Getting Reasonable Results You will find that running any given test only once doesn't provide reliable results. There are just too many external forces in play when you're running under Windows. To get a reasonable idea of the benefit of a given optimisation test, you need to run the test code many times within the given test case and then run the test case many times, averaging the results. For simplicity, each of the tests in this paper takes as its only parameter a Long value indicating the number of times you want to run the test. Each function loops the specified number of times with the clock running and provides the elapsed time as the return value of the function. If you want to add your own tests to this test mechanism, you must follow those constraints when planning your tests. In addition, for each test case, you need two versions: a "slow" version (labelled Test1a in this example) and a "fast" version (labelled Test1b in this example). Once you've provided the two functions, you can call the function RunTests, which, in turn, calls both functions the specified number of times. RunTests averages the elapsed times the functions return and reports on the comparative speed of the two functions. Figure 2 shows the RunTests function.

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 4 von 19

Function RunTests(strFunc1 As String, strFunc2 As String, _ lngReptFunc As Variant, lngRepeatOp As Variant) As Variant ' Run two tests cases, comparing their relative timings and return ' a string showing the results of those timings. ' ' Modified From Access 97 Developer's Handbook ' by Litwin, Getz, and Gilbert (Sybex) ' Copyright 1997. All rights reserved. ' The assumption is that strFunc1 will be slower than strFunc2. ' In: ' strFunc1: the name of the "slower" function ' strFunc2: the name of the "faster" function ' lngReptFunc: the number of times to call each function ' lngRepeatOp: the number of times to loop, internally to each function ' Out: ' Return Value: A string representing the outcome of the tests Dim varI As Variant Dim varResults1 As Variant Dim varResults2 As Variant Dim varDiff As Variant Dim intAmount As Integer Dim strResult As String Dim varTemp As Variant For varI = 0 To lngReptFunc - 1 Call SetStatus("Running " & strFunc1 & "() Pass " & varI) varResults1 = varResults1 + Eval(strFunc1 & _ "(" & lngRepeatOp & ")") Next varI For varI = 0 To lngReptFunc - 1 Call SetStatus("Running " & strFunc2 & "() Pass " & varI) varResults2 = varResults2 + Eval(strFunc2 _ & "(" & lngRepeatOp & ")") Next varI varResults1 = varResults1 / lngReptFunc varResults2 = varResults2 / lngReptFunc varDiff = varResults1 - varResults2 If Abs(varDiff) < 0.005 Then varDiff = 0 ' Better check for division by 0 and ' overflow, both of which can occur from ' a very small value in varResults1. On Error GoTo RunTestsError intAmount = Int((varResults1 - varResults2) / _ varResults2 * 100) Debug.Print strFunc1 & " vs. " & strFunc2 & ":"; _ varResults1, varResults2, intAmount & "%" RunTests = intAmount RunTestsExit: ' Clear the status line. Call SetStatus Exit Function RunTestsError: MsgBox Error, vbExclamation, "RunTests()" RunTests = 0 Resume RunTestsExitEnd Function

Figure 2: The RunTests function.

Notice that RunTests takes four parameters, as shown in Figure 3. Parameter

Description

Datatype

strFunc1

Name of the "slow" function to test

String expression

strFunc2

Name of the "fast" function to test

String expression

VarReptFunc

Number of times to repeat the function call

Variant

VarReptOp

Number of times to repeat the operation in the function

Variant

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 5 von 19

Figure 3: RunTests parameters.

RunTests returns an Integer indicating the comparison between the first and second functions, measured as a percentage: intAmount = Int((varResults1 - varResults2) / _ varResults2 * 100) In addition, RunTests prints a string to the Debug window (so it's useful without an interface, for informal testing), like this: Test1a vs. Test1b: 3.7

0.7

428%

This output shows the milliseconds elapsed while running each function, followed by the percentage improvement in the faster function. For example, to call RunTests to test functions Test1a and Test1b, running each function 10 times to average the results and having each function loop internally 10,000 times, call RunTests like this: strResult = RunTests("Test1a", "Test1b", 10, 10000) Top

Using the Eval Function RunTests uses the Eval function to execute each of the two tests. The Eval function takes a parameter to string code you want Access to execute. If you intend to use Eval to execute functions, you should be aware of the various limitations involved in using this technique. Access performs no error checking on the string you send to Eval. If it works, great. If not, you get, at best, a "User Defined Error" message. At worst, you crash (and lose data). You would be wise to check the string you're about to send to Eval by using MsgBox or Debug.Print to display the string before its execution. This way you can verify that the string to be executed is, in fact, laid out exactly as you think it ought to be. Scoping rules are always an issue. Eval can't interpret any local variables, nor can it handle private functions. You can remember the rules this way: any object you want to send to Eval must also work in macros. Just as VBA variables aren't available in macros, they aren't available once Eval gets hold of the string to be executed. RunTests uses local variables, but they become part of the string passed to Eval and are passed as values, not as variable names. The string you pass to Eval must represent a function that returns a value of some sort. That value will be the return value from the call to Eval. Neither subroutines nor expressions are allowed. Top

Twenty-One Possible Optimisations http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 6 von 19

In this section I present 21 possible optimisations, in no particular order. Some will actually make a difference in your applications; others are interesting ideas I thought might make a difference (and used to, in Access 2 or Access 95) but no longer actually do in Access 97. I've left in the "losers," mostly to indicate that some perceived optimisations just don't help. To test each hypothesis, I've created two similar versions of a simple function. The sample database includes the full source for both versions of each test so you can try them out yourself. Note: The effectiveness of each of the optimisations that follow depends on many factors, including the actual code in use at the time, the relative speed of your hard disk versus the processor in your computer, and other programs currently using Windows' memory and resources. Using Windows 95 or Windows NT and Access 97 makes the issues even more complex than they were when using Access 2. There might be a process running in the background that you're not aware of, and Access 97 and Jet both provide their own internal caching. The only sure way to provide accurate timings would be to remove all background processes and to reboot between each timing. That's not practical, so we'll mention again that the timing results presented here are for comparison purposes only. You'll need to decide for yourself in some of the marginal cases whether the optimisation will really help. Therefore, a word of warning: take any suggestions of optimisations with a grain of salt. Try them out in your own applications before swearing by them. As they say in the auto industry, "These numbers are for comparison only. Your mileage may vary." To simplify your experiments with the test cases presented here, you can use frmRunTests from the sample database. Figure 4 shows this form in use. It includes a combo box from which you can choose the specific test case to run and spin buttons allowing you to specify how many loops to execute inside the routine, as well as how many times to call each routine. The View Slower and View Faster buttons pop up forms that pull the source code for the functions directly from the basTests module, so you can look at the code as you test. Finally, the clock button starts the test, running the slow version as many times as you've requested and then running the faster version the same number of times.

Figure 4: frmRunTests allows you to choose a specific test and run it, resulting in a comparison between the slow and fast versions.

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 7 von 19

resulting in a comparison between the slow and fast versions.

Clearly, this method of testing is far from perfect. The order in which you run the tests might make a difference, and Access, Jet, and the operating system caches all make a difference, too. In informal testing (and that's all this can be — measurements of relative differences), none of these factors made much difference. Reversing the order of the tests made almost no difference. Remember, the goal of these tests is to determine which of two methods is faster, not to gather exact timings. In each case I ran the tests on a Pentium Pro 200 with 64MB of memory (which certainly removes the lack-of-hardware issue from the possible set of problems). The percentage differences I found depend totally on the specific tests I ran and the setup of the system, but I've tried to make them representative of the kinds of improvements you'd see, too. Note: Some of the differences may disappear or reverse in low-memory situations as things are swapped back and forth from your Windows swap file. The results of the performance tests are summarised in Figure 5. Test Number

Optimisation

Approximate Effectiveness of Speedup

1

Integer variables instead of variants

50–60%

2*

Integer division instead of real division

15-25%

3

Logical assignments instead of If…Then

30–35%

4

Len for testing for zerolength strings

30–40%

5

"Not var" to toggle True/False

60–70%

6

timeGetTime() instead of Timer()

Around 200%

7

Use object variables

200–300%

8*

Use implicit logical comparisons

10–20%

9*

Use "!" rather than string expression

50-60%

10

IsCharAlphaNumeric instead of Asc

Around 100%

11

Use DBEngine(0)(0) instead of CurrentDb

15000–20000% (running across a network)

12

If…Then…Else instead of IIf

Depends on your code (100%, in the test case)

13

db.Execute instead of

Around 200%

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 8 von 19

13

db.Execute instead of RunSQL

Around 200%

14

Be careful with string concatenation

300-500% (depends on the exact text)

15*

Remove comments from code

No improvement

16

Use bookmarks rather than FindFirst to find rows

Around 900%

17*

Drop default collection names in expressions

No difference

18

Refresh collections only when necessary

Around 1000% (depends on the number of objects)

19

Use Asc("x") = y rather than Chr$(y) = "x"

200–300%

20

Use vbNullString Instead of "" to Initialise Strings

Around 50%

21

Put most likely candidate first in Select Case

Around 600% (depends on the number of Cases)

*These items really aren't going to affect your code one way or the other! Figure 5: Summary of the results of the VBA performance tests.

Some of the comparisons are more dependent than others on the assumptions made. For example, Test2, which evaluates the difference between using Integer and real division, couldn't be constructed in too many different ways. It's doubtful you'd get results differing much from those in Figure 5 by rewriting the code. On the other hand, Test12, which compares decisions made using IIf and the If...Then construct, will give widely differing results depending on the details of what you're doing in the "true" and "false" cases of the construct. Thus, it's important to be aware of the assumptions made for each test when interpreting the results. Tip: In running these tests, I tried both the MDB file and a corresponding MDE file. In some of the tests, using an MDE file didn't change the comparative results but did make an improvement in the absolute results. (That is, the ratio of slower to faster didn't change, but the time elapsed for each test, in total, was less.) These tests aren't particularly indicative of how real applications will benefit from conversion to MDE format, but certainly, the conversion could improve the speed of your compiled code. You're very unlikely to use the specific code I've written in your own applications; it's the concepts that count. For each test case, the name of the procedure in basTests in the sample database is Test Na (the presumed slow version) or TestNb (the supposedly faster version), where N is the test number. For example, the code corresponding to test case 5 is Test5a and Test5b. Top

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi..

Seite 9 von 19

Test 1: Use Integers Instead of Variants Whenever Possible? Unless you specify otherwise, VBA creates all variables using its default type, Variant. To hold data of any simple type, variants must be at least as big and complex as any of the types they can contain. "Big and complex" equates with "slower," so avoid variants if at all possible. Of course, there will be many times when you can't avoid them, but if you're just ill informed or being lazy, your code will suffer. Note: This optimisation makes sense only within VBA. If you're working with data from tables in your code, you must use variants. Because variants are the only datatype that can hold null data, and it's usually possible for data from tables to be null, you'll avoid problems by using variants. In addition, you may find that attempting to use specific datatypes when working with Jet ends up slowing your code. Because Jet uses variants when it communicates with Access, when you place Jet data into specific datatypes, you're asking VBA to make a datatype conversion, and that takes time. Top

Test 2: Use Integer Division Whenever Possible? Access provides two division operators, the / (floating-point division) and \ (Integer division) operators. To perform floatingpoint division, Access must convert the operands to floating-point values. This takes time. If you don't care about the fractional portion of the result, you can save some time by using the Integer division operator instead. The results of this test were decidedly mixed. Using Integer division made almost no difference (and it did make a difference in Access 2.0). In some other examples, working with forms, for instance, this has made a difference. It may be that VBA is smart enough to use Integer math internally if it can tell that that's what will work most quickly. Top

Test 3: Use Logical Assignments When Possible? Like many other languages, Access handles logical values as integers. In addition, Access performs right-to-left expression evaluation. The combination of these two features allows you to make logical assignments directly as part of an expression. For example, many people write code like this: If x = 5 Then y = True Else

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 10 von 19 Else y = False End If Top

This code is wordier than it needs to be. The intent is to set the variable y to True if x is equal to 5 and False otherwise. The expression (x = 5) has a truth value of its own- -that is, it's either True or False. You can assign that value directly to y in a single statement: y = (x = 5) Although it may look confusing, VBA will interpret it correctly. Starting from the right, Access will calculate the value of the expression x = 5 (either True or False) and assign that value to the variable y. Other languages, including C and Pascal, use distinct assignment and equality operators, making this expression a little clearer. In C, for example, the statement would read: y = (x == 5) with the "=" performing the assignment and the "==" checking the equality. Anyplace you use an expression like the If...Then...End If statement above, you should be able to replace it with a single assignment statement. If you find these logical assignments hard to read, you may choose to skip using them, because the improvement in performance is slight. If, however, logical assignments seem natural to use and read, then by all means use them. Top

Test 4: Use Len to Test for ZeroLength Strings? There are several ways you can check to see whether the length of a particular string is 0. One method is to compare the string to "", and another is to compare the length of the string to 0. Comparing the results of the Len function to 0 is measurably faster. Top

Test 5: Use "Var = Not Var" to Toggle True/False? In many circumstances you need to toggle the state of a variable between True and False. You might be tempted to write code like this: If x = True Then x = False Else x = True End If

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 11 von 19 End If You might think that either of the following solutions would be an improvement over the original: If x Then x = False Else x = True End If or: x = IIf(x, False, True) Testing shows that neither is as good as the original expression (and the IIf solution is much slower). But the best solution is to use the following expression: x = Not x That way, if x is currently True, it will become False. If it's False, it will become True. Top

Test 6: Use timeGetTime Rather Than Timer? As mentioned earlier in this paper, the Windows API function timeGetTime (aliased as timeGetTime in the examples) returns the number of milliseconds that have elapsed since you started the current Windows session. The VBA Timer function returns the number of seconds that have elapsed since midnight. If you're interested in measuring elapsed times, you're far better off using timeGetTime, for three reasons: l

timeGetTime is more accurate.

l

timeGetTime runs longer without "rolling over."

l

Calling timeGetTime is significantly faster.

Calling timeGetTime is no more complex than calling Timer, once you've included the proper API declaration for it. In the declarations section of any standard module in your application, you'll need to include the statement Private Declare Function timeGetTime Lib "winmm.dll" () As Long With that declaration in place, you can call it from any module in your application, just as though it was an internal Access function. Top

Test 7: Cache Object References? http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 12 von 19

In writing code you often need to retrieve or set properties of the various forms, reports, and controls in your application. Generally, you refer to these objects with statements like this: strCaption = Forms!frmTest!cmdButton1.Caption

For a single reference to an object, there's not much you can to do speed up the reference. If, on the other hand, you're going to be referring to many of the properties of that object or using that object in a loop of some sort, you can achieve a substantial speed increase by pointing an object variable at that object and using that variable to reference the object. For example, if you were going to reference all of a specific control's properties, you would be well served to use code like this rather than refer to the control with the full syntax each time: Dim ctl as Control Set ctl = Forms!YourForm!YourControl Debug.Print ctl.ControlName Debug.Print ctl.Width ' etc... In addition, using VBA's With...End With syntax affords the same improvements. Your code may end up being more readable if you use cached object references, but if you can use With...End With, it, too, can speed up your code. Top

Test 8: Don't Use Explicit Logical Comparisons? When testing for the truth value of an expression in an IIf expression or an If...Then...Else statement, there is no point in actually comparing the condition to the value True. That is, these two expressions are completely equivalent: If x = True Then and: If x Then Leaving out the explicit comparison will make only a small difference in the speed of your code. You'd have to use this construct many, many times before this optimisation made any measurable difference in the speed of your code, but every little bit helps. Top

Test 9: Use "!" Instead of String Expression? Access gives you several choices when you're referring to elements of a collection. You can use either a direct, hard-coded reference, such as:

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 13 von 19 such as: strName = rst.Fields!LastName or a string expression containing the name, such as: strName = rst.Fields("LastName") In general, referring to items within a collection using a hard-coded reference (and a "!" operator) is somewhat faster. Top

Test 10: Use IsCharAlphaNumeric? You may find yourself needing to find out whether a particular character is an alphanumeric character (that is, checking to see whether it falls in the range of characters from A–Z, a–z, or 0–9). One standard method for doing this in VBA is to compare the Asc (UCase(character)) to the ANSI values for the ranges. The Windows API provides a function specifically for this purpose, IsCharAlphaNumeric (aliased as IsCharAlphaNumeric in the examples). In addition, you can use a similar API function, IsCharAlpha, to check a character to see whether it's between A and Z. An added bonus of using the Windows API functions is that they're internationalised. Many characters outside the normal A–Z range are considered legal text characters in other countries. The brute-force comparison method would fail on such characters. To top it all off, using the API method is significantly faster than performing the comparisons yourself. To use IsCharAlphaNumeric, you need to include the following declaration in your application: Private Declare Function IsCharAlphaNumeric _ Lib "User32" Alias "IsCharAlphaNumericA" (ByVal cChar As Byte) NOTE: Interestingly, in Visual Basic 5.0, it's even faster to use the Like operator than it is to use IsCharAlphaNumeric. That is, you can use "If Chr(x) Like "[A-Za-z0-0]" to check for validity. This method is slower than using VBA code, in Access 97. I have no idea why this is. Top

Test 11: Use DBEngine(0)(0) If Speed Is the Only Concern? If all you care about is raw speed, retrieving a reference to the current database with DBEngine(0)(0) is much faster than using CurrentDb. When you retrieve a reference with DBEngine(0)(0), like this: Dim db As Database Set db = DBEngine(0)(0) Access returns a reference to an object that's already open. When

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 14 von 19 Access returns a reference to an object that's already open. When you use CurrentDb, however, Access creates a new internal data structure, which obviously takes a bit longer — actually, a lot longer. In sample tests, using DBEngine(0)(0) was 500 to 1000 percent faster. However, don't forget the trade-offs. When you retrieve a reference to CurrentDb, you're guaranteed that its collections are refreshed at that time. If you use DBEngine(0)(0), you can make no such assumption, and you should refresh any collections you need to use. On the other hand, refreshing the collection will usually make using DBEngine(0)(0) slower than using CurrentDB. You'll have to make up your own mind. Top

Test 12: Watch Out for Slow IIf Components? Shorter code isn't necessarily faster. Although this fact is documented in both the Access online help and the manuals, it's easy to miss: in the IIf, Choose, and Select functions, VBA evaluates any and all expressions it finds, regardless of whether they actually need to be evaluated from a logical point of view. Given an expression like this: varValue = IIf(fFlag, Function1(), Function2())

VBA will call both Function1 and Function2. Not only can this lead to undesired side effects, it can just plain slow down your program. In a case like this you're better off using the standard If...Then...End If construct, which will execute only the portions of the statement that fall within the appropriate clause. Given the statement: If fFlag Then varValue = Function1() Else varValue = Function2() End If you can be assured that only Function1 or Function2 will end up being called. The same concepts apply for the Choose and Select functions. If you plan on calling functions from any of these functions, you may be better served by using an If...Then...End If or a Select Case statement. Beyond any optimisation considerations, IIf is very dangerous when dealing with numeric values and division. If this was your expression: intNew = IIf(intY = 0, 0, intX/intY) it would appear that you had appropriately covered your bases. Your code checks to make sure intY isn't 0 and returns an appropriate value if it is, rather than attempting to divide by 0. Unfortunately, if y is 0 this statement will still cause a run-time error. Because Access will evaluate both portions of the IIf expression, the division by 0 will occur and will trigger an error. In

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 15 von 19 expression, the division by 0 will occur and will trigger an error. In this case you need to either trap for the error or use the If...Then...Else statement. Top

Test 13: Use Execute instead of RunSQL? When running an action query from your application, you have three choices: you can use the RunSQL macro action, the Execute method of a database object to run SQL code, or you can create a QueryDef object and then use its Execute method. Using the Execute method of the database object or creating a temporary querydef and using its Execute method take about the same amount of time. On the other hand, using the Execute method of a database object requires one less line of code and seems like a simpler solution. Either solution is significantly faster than using DoCmd RunSQL. The sample code shows two ways to accomplish the same goal: deleting all the rows from tblContacts. The slower method uses the RunSQL action to run the SQL string: DELETE * From tblContacts The faster method uses the Execute method of the current database to execute the SQL string. Top

Using Temporary QueryDefs Access provides a useful mechanism for creating temporary QueryDef objects: just don't provide a name! If you use a zerolength string for the name parameter of the CreateQueryDef() method, Access creates a temporary "in-memory" query. You no longer have to worry about the proliferation of querydefs and name collisions, and since Access doesn't write temporary querydefs to disk, they're a lot faster, too. Another plus: you don't have to delete temporary querydefs- -they automatically disappear when the QueryDef object goes out of scope. For example, you can use code like this to create a temporary querydef: Dim db As Database Dim qdf As QueryDef Set db = CurrentDb() Set qdf = db.CreateQueryDef("", _ "SELECT * FROM tblCustomers WHERE Age > 30;") Top

Test 14: String concatenation is expensive? Many times, when writing code, you have the choice whether to write a string expression as one long expression, or to break it up into multiple expressions, concatenated together with the "&"

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 16 von 19 into multiple expressions, concatenated together with the "&" operator. Be careful: concatenation is a slow operation in VBA. Top

Test 15: Remove Comments from Loops? In Access 2.0, removing comments from your code made a difference in the execution speed. VBA is a compiled language, and comments ought not make a difference. As the tests show, this is true: removing comments will not affect the speed of code, all other things being equal. Be aware that excess comments consume memory, if you're using the decompiled version of the code (editing, for example), so they can adversely affect performance due to memory usage. This shouldn't concern you unless you have a massive number of comments- Their use far outweighs their detriments. In repeated trials, removing comments never seemed to help, and if anything, caused slightly worse performance more times than not. This technique gets an unqualified "don't bother." Unless you can prove to yourself that removing comments in your particular application makes any difference, leave them in. Top

Test 16: Use Bookmarks Rather than FindFirst to Locate Rows? If you're working with recordsets as part of your application, you may need to find a specific row, move to a different row, and then move back to the first. You can accomplish this in a number of ways, and some are faster than others. The fastest way is to use the Seek method of a table-type recordset. However, it's not always possible to use table-type recordsets. In those cases you might try using FindFirst to find the row, move to wherever you need to go, and then use FindFirst again to get back to the original row. Although this will work, there's a better method: use a bookmark to store your location before you move away. Then, when you want to move back to the selected row, the bookmark can get you there almost instantaneously. The example procedures first use the FindFirst method of a recordset to find a row. Then they move to the first row and back to the original row. The first version uses FindFirst for both record moves. The second one stores away a bookmark instead and uses that bookmark to move back to the original record. Your performance on this test will vary, depending on the number of rows in the recordset, whether you can use an index for the search, and how many times you execute the search. Top

Test 17: Drop Default Collection References? http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 17 von 19

References? When working with DAO and its objects and collections, you always have the option of writing out complete object references or leaving out default collections in the references. Although leaving out the default collections in full references may help speed your code, it does make the code harder to read and its intent less obvious. The following examples compare using a full reference to a field against using a shortened version: strName = DBEngine.Workspaces(0).Databases(0).TableDefs (0).Fields(0).Name or: strName = DBEngine(0)(0)(0)(0).Name In testing, using the shortened version provided little, if any, speed difference. Using different references might affect this acceleration, and you must always weigh the possible speed gains against readability losses. Top

Test 18: Refresh Collections Only When Necessary? If you're using DAO and want to make sure the collections you're working with are completely up to date, you must first use the Refresh method of the collection. On the other hand, refreshing collections is a very expensive operation in terms of time. The test case demonstrates just how expensive: in the sample case, with just two user-defined tables (and all the normal system tables), not refreshing the TableDefs collection before retrieving each TabledDef's RecordCount property was more than 1300 percent faster than refreshing first. With more objects, this difference would be even more noticeable. For large applications, the speed difference can be very noticeable. Of course, if you're working in a multi-user environment and need to peruse the entire list of objects, you don't have much choice; you may miss newly added objects unless you refresh the collection first. Top

Test 19: Use Asc("x") = y rather than Chr$(y) = "x"? You'll often need to compare a character to its ANSI value. To do so, you can either compare: If Chr$(y) = "x" or: If Asc("x") = y It turns out the second method, using Asc rather than Chr$, is

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 18 von 19 significantly faster. Because the Chr$ function must take the steps to fabricate a new string value (a costly operation), the Asc function can perform the comparison much faster. Top

Test 20: Use vbNullString instead of "" to Initialize Strings? When you want to re-initialise a string value so that it's empty, you normally use code like this: strItem = "" This creates a new string, and copies it into the strItem variable. It turns out that a more expeditious solution is to use the vbNullString constant, a pointer to an empty string. By using: strItem = vbNullString VBA doesn't have to create a new string each time you make the assignment, but uses its own internal pointer to an empty string, saving a substantial amount of work each time you initialise a string variable. Top

Test 21: Put the most likely candidate first in Select Case? VBA is not smart when it comes to Select Case statements. The only way it can process them is to visit each case in turn, until it finds a match. If you know that one case is more likely to occur than others, you can optimise your code by placing that Case first, and then arrange the rest in descending likelihood of occurrence. Top

Summary This paper has presented a variety of suggestions for improving the performance of your Access applications. The following topics were covered: l l

Suggestions for optimising your VBA code How to test your own optimisation ideas

Although I attempted to cover some major areas of optimisation, this paper is not meant to be comprehensive, but it makes for a good start. At every point in designing any application, you're faced with choices. These choices affect how well your application will work, and you need to be informed about the trade-offs in order to best make these choices. This paper focused on the major areas in which you can improve the performance of the VBA code in your

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00

Microsoft Office Developer Forum - Office - VBA Code Optimi.. Seite 19 von 19 which you can improve the performance of the VBA code in your applications. Much of the text and examples in this session have been extracted from: Access 97 Developer's Handbook Paul Litwin, Ken Getz, and Mike Gilbert © 1997. Sybex, Inc. (with permission of the publisher) Top

Ken Getz, a senior consultant with MCW Technologies, splits his time between programming, writing, and training. He specialises in tools and applications written in Microsoft Access, Visual Basic, and the rest of the Microsoft Office and Microsoft BackOffice suites. Ken is co-author of several Microsoft Access books, including Access 97 Developer’s Handbook [Sybex, 1997] and VBA Developer’s Handbook [Sybex, 1997]. He also cowrote the training materials and travels around the United States teaching Access 97 and VB5 for Application Developers Training Company. In addition, Ken is a Contributing Editor for Microsoft Office & Visual Basic for Applications Developer and Access-Office-VB Advisor magazines. This image means that the link points to servers that are not under Microsoft's control. Please read our official statement regarding other servers.

Top

Tell us about this article n -Very worth reading j k l m

n -Too detailed j k l m

n -Too technical j k l m

j -Worth reading k l m n

j -Just right k l m n

j -Just right k l m n

j -Not worth reading k l m n

j -Not detailed enough k l m n

j -Not technical enough k l m n

Submit

Write us at [email protected]. © 2000 Microsoft Corporation. All rights reserved. Legal Notices. Last Updated: February 21, 2000 Best experienced with

Click here to start.

http://www.microsoft.com/officedev/articles/movs101.htm

20.04.00