Visual Basic for Applications Tips #10


----------------------------------------------

TipWorld - http://www.tipworld.com
The Internet's #1 Source for Computer Tips, News, and Gossip

Proudly presents:
Visual Basic for Applications

----------------------------------------------

*1. MINIMIZING OLE REFERENCES                 
        
VBA methods and properties use the OLE IDispatch interface, and that 
takes time. Consequently, minimizing the number of methods or 
properties can speed things up. Here's the general rule of thumb: 
Every time you use the dot identifier (.), VBA hits the OLE Idispatch 
interface. For example, this statement contains three dots: 

Workbooks(1).Sheets(1).Range("A1").Value = "abc" 

This statement will call on the interface three times, which of course 
takes more processing time than a statement with only one dot 
identifier. 

Although it isn't always practical, keeping the dot identifiers to a 
minimum is one good way to speed up your code.


*2. USING OBJECT VARIABLES                 
        
In our last tip, we recommended that you use the dot identifier 
sparingly because it slows down your code. Here's an option for 
reducing the number of dot identifiers in references. When you find 
yourself repeating the same object reference, use an object variable 
instead. For instance, we can optimize the statement we used in our 
last tip example: 

Workbooks(1).Sheets(1).Range("A1").Value = "abc" 

by setting the Workbook reference as follows: 

Set sheet = Workbooks(1).Sheets(1) 
sheet.Range("A1").Value = "abc" 

We've reduced each call by one dot, which doesn't seem like a big 
deal, but every dot helps.


*3. USING THE WITH STATEMENT                 
        
Our last two tips have been about reducing the number of dot 
identifiers in references. One more way you can eliminate references 
is to use the With statement. Let's rewrite our example using the With 
statement. It calls the Workbooks method and the Sheets method only 
once each: 

With Workbooks(1).Sheets(1) 
        .Range("A1").Value = "abc" 
        .Range("A2").Value = "def" 
End With 

Each statement in the non-With alternative: 

Workbooks(1).Sheets(1).Range("A1").Value = "abc" 
Workbooks(1).Sheets(1).Range("A2").Value = "def" 

calls both the Workbooks and the Sheets. This simple example cuts the 
calls in half.


*4. A DAO DUH MOMENT                 
        
Normally, we try to reclaim resources as soon as possible by closing 
objects we're done with and setting object variables to Nothing. Have 
you ever tried to delete a temporary table or query that you know 
exists and received an error? This might happen for any number of 
reasons, but one you might not consider is the order of your 
statements. If you're working with an open recordset that's based on 
that temporary table or query, you can't delete the data source until 
you close the recordset. This situation is one case where you'll just 
have to wait until you close the recordset. Only then can you delete 
the temporary data source.


*5. NEED A CALCULATOR?                 
        
Normally, we like to share expressions and formulas that you can use 
in VBA. However, we've found a Web site that may make some of your 
work unnecessary. The Calculators On-Line Center at  

http://www-sci.lib.uci.edu/HSG/RefCalculators.html  

offers more than 5,000 Web calculators. You'll find calculators to 
handle all sorts of tasks, from a lye calculator (for making soap) to 
a capital gains calculator.  

You can easily convert and incorporate these calculators into your VBA 
projects using Microsoft Web components.


*6. SPEAKING OF CALCULATIONS                 
        
Even though you can find Web sites with downloadable calculators for 
all kinds of tasks, most of the time you'll have to rely on your own 
expressions to get the results you need. Occasionally your expressions 
will return errors, and there are several reasons why VBA or your host 
application can't evaluate your expression. Review these possibilities 
before you start pulling out your hair: 
 - Make sure you've included the appropriate number of parentheses 
(each opening parenthesis requires a closing parenthesis).   
- Make sure you've supplied all the required arguments for any 
functions or procedures.   
- Check all your object and variable references to make sure they're 
correct.


*7. TURNING OFF WARNINGS                 
        
When you run an Access action query, Access will display a message 
warning you that you are about to modify the existing data. If you 
don't want to display this warning, you can turn it off temporarily 
using the SetWarnings method. However, we recommend that you not add 
the SetWarnings statement until the procedure is complete, debugged, 
and running as expected. During the testing and debugging process, 
those messages can give clues as to whether the code is running as 
expected. The statement 

DoCmd.SetWarnings False 

turns off the display. Substitute the False value with True to turn on 
the display. If you're using Excel, use the Application object's 
DisplayAlerts method in the form: 

Application.DisplayAlerts = False 

to inhibit warnings. Then, use the statement 

Application.DispayAlerts = True 

to return to normal.


*8. THREE TYPES OF DIVISION                 
        
VBA supports three types of division: floating-point, integer, and 
modulus. A floating-point division is what you learned in grade 
school. You simply divide one number by another, which returns an 
integer and a decimal value, when appropriate. For instance, the 
following expression: 

10 / 4 

returns the value 2.5. 

Integer division divides one number by another, but returns only the 
integer position of the result. When using integer division, use the 
backward slash instead of the forward slash in the form: 

10 \ 4 

which will return just the value 2. 

The final method, modulus, divides one number by another, but returns 
only the remainder (or the decimal portion). It also requires the Mod 
operator. Our expression looks like: 

10 Mod 4 

and returns the value 5.


*9. VIEWING A FORM'S DESCRIPTION                 
        
Most VBA files are saved in binary format, which makes reading these 
files a bit difficult. Forms and projects, however, are saved as ASCII 
text and are easily readable in a text viewer. Simply open the .frm 
file in your word processor the same way you'd open any other file to 
display a text version of your form that contains: 

The version number of the file format 
The form's description 
The form's attributes 
The form's code 

The following is the form description of a form with one command 
button: 

VERSION 5.00 
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm1 
   Caption         =   "UserForm1" 
   ClientHeight    =   3225 
   ClientLeft      =   45 
   ClientTop       =   330 
   ClientWidth     =   4710 
   OleObjectBlob   =   "UserForm1.frx":0000 
   StartUpPosition =   1  'CenterOwner 
End 
Attribute VB_Name = "UserForm1" 
Attribute VB_GlobalNameSpace = False 
Attribute VB_Creatable = False 
Attribute VB_PredeclaredId = True 
Attribute VB_Exposed = False 
Private Sub CommandButton1_Click() 
MsgBox "This is a test" 
End Sub 

To save your form or userform as a separate file, select the form in 
the VB Editor and then choose Export File from the File menu.


*10. TYPE MISMATCH                 
        
Do you hate the Type Mismatch error as much as I do? Any error is 
annoying, but this one seems to crop up way too often. When this error 
appears, it generally means you've made one of the following mistakes: 
 - You've attempted to define a variable or set a property with the 
wrong data type. For instance, you can't pass a string to a procedure 
that expects an integer. If you don't know what type of data you might 
have to accommodate, use the Variant data type. 
- You tried to pass an object to a procedure that's expecting a single 
property or value. 
- You used a module or project name where VBA expected an expression. 
For instance, you can't print an object with the Debug object. 

 
These three mistakes are probably the most common reasons VBA returns 
the Type Mismatch error. Fortunately, all three errors are relatively 
easy to find and resolve.