In general, you can do more to improve the speed of your code by choosing more efficient algorithms than by implementing particular coding “tricks.” However, certain techniques can help you write more efficient code. This section gives tips you can use to speed up your code.
If you refer more than once to the value of a property or control on a form, or to a DAO object or its property, create object variables and refer to the variables rather than using full identifiers. This approach is especially effective for speeding up a looping operation on a series of properties, controls, or objects.
When you make form references within an event procedure, use the Me keyword to refer to the form. This restricts the search for the form to the instance of the form in which code is currently running.
If your code has strings or numbers that don’t change, declare them as constants instead of variables. Using constants makes your application run faster. Constants also make your code more readable and easier to maintain. Constants are resolved once when your program is compiled, with the appropriate value written into the code. With variables, on the other hand, 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.
See Also For information on constants, see Chapter 4, “Working with Variables, Data Types, and Constants.”
Avoid using the Ilf function if either of the return expressions takes a long time to evaluate. When you use the Ilf function, Microsoft Access always evaluates both expressions, even though it returns only one of them. It’s often more efficient to replace the Ilf function with an If...Then...Else statement block.
References to objects and their methods and properties are resolved either when an application is compiled or when it runs. To improve execution speed when working with Automation objects, instead of using a Variant data type or the generic Object data type, declare objects as they are listed in the Classes box in the Object Browser. This ensures that Visual Basic recognizes the specific type of object you’re referencing, allowing the reference to be resolved at compile time.
See Also For information on object types and on using the Object Browser, see Chapter 5, “Working with Objects and Collections.”
Use the Integer or Long data type for math when the size and type of numbers permit. The Variant data type, though more flexible, uses more memory and processor time as it translates between data types. The following table ranks the numeric data types by calculation speed.
| Numeric data types | Speed |
| Integer, Long | Fastest |
| Single, Double | Next-to-fastest |
| Currency | Next-to-slowest |
| Variant | Slowest |
See Also For more information on data types, see Chapter 4, “Working with Variables, Data Types, and Constants.”
Some functions have two versions, one that returns a Variant data type (for example, the Str function) and one that returns a String data type (for example, the Str$ function). Use string functions when working with strings. Your operations run faster when you use string functions because Microsoft Access doesn’t need to perform type conversions.
See Also For more information on string functions, search the Help index for “returning strings.”
Consider using dynamic arrays instead of fixed-size arrays because you can reclaim memory when you no longer need the data in a dynamic array. Use either the Erase statement or the ReDim statement with the Preserve keyword to discard unneeded data and reclaim the memory used by the array. For example, you can reclaim the memory used by a dynamic array by using the Erase statement, as follows:
Erase intArray
While the Erase statement completely eliminates the array, the ReDim statement used with the Preserve keyword makes the array smaller without losing its contents.
ReDim Preserve intArray(10, conNewUpperBound)
Erasing a fixed-size array doesn’t 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 data type containing a string or an array, then erasing the array would reclaim the memory from those strings or Variant data types, not the memory for the array itself.
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 inline 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 chance that you won’t remember to update each section of duplicate code when you make changes.
When locating records that satisfy a specified criteria, the Find methods are much more efficient than the Seek method when used on a field that is indexed.
See Also For information on the Find methods, see “Finding a Record in a Dynaset- or Snapshot-Type Recordset Object” in Chapter 9, “Working with Records and Fields.”
Use bookmarks instead of the FindNext method or some other means to return to a particular record. By using the Bookmark property, you can write a procedure to find a target record, store its bookmark value in a variable, move to other records, and return to the original record by referring to the bookmark.
See Also For more information on the Bookmark property, search the Help index for “Bookmark property.”
If your application includes add-ins and doesn’t need to write to them, load the add-in databases for read-only access rather than for read/write permission. The read-only database loads and runs faster because a locking information (.ldb) file doesn’t need to be maintained.
See Also For information on working with add-ins, see Chapter 17, “Creating Wizards, Builders, and Menu Add-ins.”
While your application runs, each called procedure is placed in its own public block of memory. Microsoft Access incurs some overhead when creating and managing these blocks. You can reduce some of this overhead by combining short procedures into larger procedures and by consolidating your procedures into fewer modules. Keep in mind that using larger procedures in fewer modules makes your code more difficult to debug and maintain.
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. Place related procedures in the same module so that Visual Basic only needs to load that module.
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. Consider reviewing your code to find and remove unused procedures and variables; for example, Debug.Print statements.
To search for references to a particular variable, use the Find command (Edit menu). 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 its declaration and running the application. If the variable is used, Visual Basic generates an error. If you don’t see an error, the variable was not used.
If your application has places in which the contents of a string variable or a Variant data type containing a string isn’t needed, assign a zero-length string ("") to that variable. If you no longer need an object variable, set that variable to the Nothing keyword to reclaim the memory used by the object reference.
Note You can also use compiler directives and conditional compilation to ignore portions of code based on constant values that you specify. For more information, see Chapter 7, “Debugging Visual Basic Code.”
Comments in code use memory. If your application contains many lines of commented code, consider stripping the comments from your code in the delivered application. Before delivering the application, save the code to a text file. Make a copy of the text file and strip out the comments. Then load the stripped code back into your application. Retain the text file that contains the commented code for your reference and future development work.
If it is appropriate for the situation your application will be used in, consider saving your application as an MDE file. Saving a database containing code as an MDE file removes the source code from the file and provides some additional optimization of memory use.
See Also For more information on MDE files, see “Saving Your Application as an MDE File” in Chapter 14, “Securing Your Application.”
Compile your modules before saving them and before delivering your application. This decreases the time it takes to load the module when your application calls a procedure that resides there. To compile all procedures in all modules in the current database, click Compile And Save All Modules on the Debug menu in the Module window.