Visual Basic for Applications Tips #18


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

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

Proudly presents:
Visual Basic for Applications

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

*1. MODULE KEYBOARD SHORTCUT                 
        
When working in a VBA module, there are several keyboard shortcuts you 
can use to reposition the cursor. One you might not know about is 
Ctrl-Home. This keystroke combination will reposition the cursor at 
the very top of the module window. In other words, this keyboard 
shortcut positions the cursor at the very first position in the 
module--before any other characters. In most modules, this shortcut 
will position the cursor before the Option Explicit statement in the 
module's General Declarations section.


*2. SPECIFIC OBJECT DECLARATIONS                 
        
When declaring an object variable, it's usually best to be as specific 
as possible. That's because the more specific you are, the more 
flexible your code is. Most objects have unique properties and 
methods, and unless you're specific about the object type, you may not 
be able to use those properties and methods in your code. 

Anytime you can't be specific because you don't know the object type 
you may be working with, you can use the Object data type.


*3. QUICK CONSTANTS                 
        
A few tips ago, we mentioned that you could use the Object Browser to 
learn the different intrinsic constants and their literal values. If 
you've already added a constant to your code and you'd like to learn 
its literal value, there's an easier way than launching the Object 
Browser. Right-click the constant and choose Quick Info from the 
context menu. VBA will display the constant and its literal value.


*4. WATCH OUT FOR NULLS                 
        
In general, you'll want to avoid using a Null value in your 
expressions. That's because a Null in any mathematical expression 
causes the entire expression to evaluate to Null. For instance, the 
simple expression 

2 + Null 

will return Null, whereas the expression 

0 + 2 

will return 2.


*5. STRING COMPARISON                 
        
If a module is dedicated to string comparison and you want all 
comparisons not to consider letter case, you can avoid a lot of 
special coding with one simple statement. Include the Option Compare 
Text statement in the module's General Declarations section. 

If, on the other hand, you do want your comparison code to consider 
letter case, you should use the Option Compare Binary statement. (This 
is the default if there's no Option Compare statement.) 

Keep two things in mind when working with the Option Compare Text 
statement. First, it doesn't work in VBScript. Second, the results of 
your comparison may depend heavily on your Windows language settings.


*6. COLOR CONSTANTS                 
        
Referring to colors in your code can be a bit of a nuisance because 
you must remember the color's corresponding integer. If you're working 
with the Windows standard colors, your work is made easier by a few 
intrinsic constants. Instead of looking up a color integer, you can 
simply use one of these constants: 

Black  vbBlack 
White  vbWhite 
Red    vbRed 
Yellow vbYellow 
Blue   vbBlue 
Green  vbGreen 
Cyan   vbCyan 

Working with these constants should prove much easier and more 
efficient than using the color's integer values.


*7. REMOVING BREAKPOINTS                 
        
Most developers use Debug.Print to display information during the 
debugging process. For the most part, you'll want to delete all these 
statements before distributing the application. A simple Find task in 
all your modules is a quick and easy way to find all of your Debug 
statements. If you miss a few, no harm is done, as the user will 
probably never even know they're there. 

Breakpoints are helpful too, since they automatically suspend the code 
at a particular point so you can review all the variables, and so on, 
before continuing. Unfortunately, it's easy to forget about a 
breakpoint, and they aren't as benign as Debug statements. A forgotten 
breakpoint can stop your application, and the user will have no idea 
what's wrong. 

An easy way to remember to remove all your breakpoints is to add a 
Debug statement after each breakpoint. Use the Debug.Print statement 
to print a message that the stop is intentional and your message is 
simply a reminder to remove the breakpoint at the appropriate time.


*8. AVOIDING BUGS                 
        
Chasing down a bug in your code is never fun or easy. In a perfect 
world, you'd find these bugs before they ever hatch, but that's just 
not practical. There are a few guidelines you can follow that may help 
you prevent error prone code: 
 - Break your code into small, manageable pieces; don't write 
functions and subs that are hundreds of lines of code. 
- Comment abundantly, and make those comments count. 
- Explicitly declare all your variables; a misspelled variable can be 
very difficult to find. 
- Adopt and consistently use a naming convention.


*9. VBSCRIPT DATA TYPE                 
        
You probably know that VBScript only supports the Variant data type. 
If you use data type prefixes to denote a variable's data type, you 
might be affixing the prefix var to all your VBScript variables. 
However, we recommend that you not do so. Instead, use the appropriate 
prefix for the type of data the variable will be storing or expecting. 
That way, you can be reminded at a quick glance whether a variable 
contains (or should contain) numeric or string data.


*10. NAME THAT ERROR                 
        
Have you ever received this error: 

Expected end of statement 

It's probably the one I receive the most and I know exactly what it 
means, which makes the error easy to find and correct. Unfortunately, 
this error message is a bit obscure, so unless you know its meaning 
you may have trouble finding the cause of the error. 

Almost always, this error points to a missing delimiter. For instance, 
the statement 

strFullName = FirstName & " " & LastName & " 

will produce this error because there's an extra (or missing) 
double-quotation mark--the one at the end of the statement. To correct 
this statement, we can delete the extra quotation mark: 

strFullName = FirstName & " " & LastName 

or we can add another string and another quotation mark: 

strFullName = FirstName & " " & LastName & " " 

This last example is kind of silly since there's no good reason to add 
an extraneous space to the end of a person's name; in fact, doing so 
might cause trouble later on.
