Visual Basic for Applications Tips #5



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

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

Proudly presents:
Visual Basic for Applications

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


*1. WHAT'S OPTION COMPARE?                 
        
Have you ever wondered what the Option Compare statement in the 
Declarations statement means? Well, it's something you'll 
probably rarely need to change, but an option you should be aware 
of just the same. This statement defines a module's sort order, 
which by default is Database. This means your system will refer 
to the locale settings for more information. 

This option has two settings: Binary and Text. Binary sorts 
according to Binary rules: 

A, B, C, 
A, b, c, 
?, C 

The Text setting will sort by Text rules--uppercase, then 
lowercase, then special characters: 

A, a, ? 
C, c, C


*2. COMMENTING YOUR CODE                 
        
Some developers comment every task. Others rarely comment. We 
recommend a medium--comment when the task or function is 
complex or relies on conditions that aren't apparent in 
the code. 

Commenting your code can be done in three ways. If you're like 
most folks, you start a new line with an apostrophe character 
and then enter your comment. Doing so forces VBA to ignore that 
line when running your code. However, commenting like this 
isn't the only way to do it. You can position a comment almost 
anywhere. You can even follow a line of code with a comment. 
The following shows both methods: 

'Get value 
iResponse = MsgBox "Please enter value" 

iResponse = MsgBox "Please enter value " 'Get value 

Either way will work, but which you use will depend on which 
you prefer. You can't, however, enter a comment after the 
continuation character ( _ ). 

A third method for commenting is to use the Rem (short for Remark) 
keyword. Simply enter the keyword, and then the comment. 

Deciding what code to comment is up to you.


*3. PRECEDENCE IN ARITHMETIC EXPRESSIONS                 
        
Our code often includes expressions that use operators such as 
+, -, *, /, ^, and \. If you don't wrap the expression in 
parentheses, VBA performs the operators from left to right 
based on the order of precedence: 

1) exponentiation (^) 
2) negation--minus sign before a value 
3) multiplication and division (* or /) 
4) integer division \ 
5) Mod operation 
6) addition and subtraction (+ and -) 

This means that the addition operator you place at the very 
beginning of the expression may be the very last operator to be 
evaluated. For instance, you might think the expression 

3 + 4 * 2 

equals 14. But VBA returns the value 11. That's because VBA 
multiplies 4 and 2 before it adds the value 3. If you want to 
force the addition first, you must surround the addition 
component with parentheses as shown below: 

(3 + 4) * 2


*4. TESTING AN ERROR TRAP                 
        
You probably add error-trapping code to your procedures using 
the On Error statement. When encountering an error, VBA routes 
control to the specific error-handler specified in the current 
On Error statement. The handler, in most cases, will depend on 
the Error object to determine the exact error, at which point, 
the handler can then reroute control as you've directed in your 
code. This routine is fairly standard in most applications. 
However, it can be difficult to test these handlers if you don't 
know about the Raise method. 

The Raise method creates an error on purpose. You determine the 
error with the method's only argument--a value that represents 
the error. For instance, if you want to create error 7, Out Of 
Memory, you'd simply include within your code at the appropriate 
spot the statement 

Err.Raise 7 

When you execute the procedure, this statement will create the 
conditions needed to then test your error-handling code. 

If you use Err.Raise, be sure to remove it when you're through 
debugging your application.


*5. REMOVING ERR.RAISE                 
        
In our last tip, we reviewed using the Raise method to simulate 
error situations so you can test error-handling code. After 
you're completely done with debugging, you should delete all of 
these statements. One easy way to ensure that you've found and 
deleted them all is to enter these statements at the left 
margin--do not indent them with your regular code. This simple 
trick makes the statements easy to spot. In addition, you can 
use the Find command from the Edit menu. Just be sure to check 
all modules thoroughly.


*6. CONTINUATION CHARACTER AND LITERAL STRINGS                 
        
We have mentioned that you can't follow a continuation character 
with a comment. In this case, you'd have to locate your comment 
someplace else. Another place where continuation characters can 
be a problem is in a literal string. You can't insert a 
continuation character anywhere within a literal string that's 
enclosed in quotation marks. You must use the continuation 
character before or after the string.


*7. USING SHORTCUTS WHILE DEBUGGING YOUR CODE                 
        
In our previous tip, we informed you of several keyboard 
shortcuts that you can use to navigate the VB Editor. Today, 
we'll continue that thought with some shortcuts that you can 
use while debugging (tracing) your code.  

F8--Step Into  
Shift-F8--Step Over  
Ctrl-Shift-F8--Step Out  
Ctrl-F8--Run To Cursor  
F5--Run  
Ctrl-Break--Break  
Shift-F9--Quick Watch  
F9--Toggle Breakpoint  
Ctrl-Shift-F9--Clear All Breakpoints


*8. REPEAT i IN NEXT LOOP                 
        
When using the For statement, you can omit specifying the loop's 
variable name in the Next statement. For instance, the code 

For i = 1 To 10 
... 
Next 

is a legitimate loop. However, the code 

For i = 1 To 10 
... 
Next i 

is more obvious. It is easy to see where the For loop ends. This 
may not seem important in such a simple example, but in a complex 
loop with many lines of code, it can make spotting the end 
much easier. 

If you want the loop to be even more meaningful, use a descriptive 
name--instead of i--for the looping variable. Simply name the 
variable as you would any other--by describing its purpose 
or origin.


*9. CREATING DESIGN-EFFICIENT FORMS                 
        
Your users will spend much of their time entering data on forms 
and clicking buttons, so it's important that you create forms 
that not only lead them from task to task, but also reduce 
eyestrain. A busy screen confuses users and slows them down. 
These simple-to-apply guidelines will make your forms easy to 
view and easy to use. 
- Use white space effectively by separating information from 
  the surrounding objects using margins and gutters and by using 
  blank lines between subgroups to show relationships. 
- Align fields vertically and set the tab order accordingly.
  Avoid horizontal movement from control to control. 
- Group all your related fields together. Use borders or white
  space to separate these groups from other groups. 
- Left-justify your label text.


*10. LEAVING OLD CODE                 
        
You may be tempted to delete lines of code as you replace them. 
Instead, consider commenting out the old code. Doing so uses few 
resources and can prove useful. Suppose that you find a bug in 
your code and replace it with code that works, then you delete 
the buggy code. Later you discover that the original code was 
correct after all. Because you deleted the code, you'll need to 
remember the old code and re-enter it. If you'd simply commented 
out the original code, you could uncomment that section and go 
back to work. 

Another benefit of retaining old code is the historical record 
you create. Having a complete history of where your code has been 
and how it's been updated can cut down on confusion and lend 
better understanding to the overall design and purpose of your 
application.
