Visual Basic for Applications Tips #15


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

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

Proudly presents:
Visual Basic for Applications

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

*1. RANDOM VALUES                 
        
Do you ever need a random value? It's a simple matter with the 
Randomize and Rnd functions. The following procedure will return a 
random value that falls between the two arguments, upper and lower:  

Function RandomNumber(upper As Long, lower As Long) As Long 
Randomize 
RandomNumber = Int((upper - lower + 1) * Rnd + lower) 
End Function 

If you'd like to test this function, open the Visual Basic Editor (by 
pressing Alt-F11) in any host application. Then, open a blank module 
and enter the above procedure. Next, open the Immediate window and run 
the statement 

?RandomNumber(10,5) 

and VBA will return a random value from 5 through 10.


*2. UNDERSTANDING RND                 
        
In our previous tip, we used the Rnd function to return random values. 
If you don't know what to expect, the Rnd function can return a few 
surprises. You see, the function's argument changes the seed value, 
which determines where VB begins to generate random values. The Rnd 
function accepts just one argument, in the form 

Rnd(value) 

Here are a few rules you should know about value if you plan to use 
Rnd: 
 - If value is greater than zero (or not supplied), Rnd returns the 
next random number in the sequence.  
- If value is less than zero, Rnd returns the same number.  
- When value equals zero, Rnd returns the most recently generated 
number.


*3. GREAT POINTERS                 
        
You've probably noticed that the new versions of Microsoft 
applications make use of a solid triangle as a pointer. You can use 
these yourself since they're included in the Marlett font. Just 
specify one of the values 3 through 6 and apply the Marlett font to 
the value. This table identifies the pointer direction with a value.  

3  Left pointer 
4  Right pointer 
5  Up pointer 
6  Down pointer 

Now, here's how to use them to add quality pointers to your controls: 
 - Add a label control to your form (object). 
- Using the table, enter the appropriate value as the label's Caption 
property. 
- Specify Marlett as the label's Font property.  

 
The Marlett font will display one of the four pointers, depending on 
the value you entered, instead of the value


*4. SELECT CASE WITHOUT CASE ELSE                 
        
The Select Case statement allows you to run an expression or condition 
by any number of possibilities and assign a unique action for each 
condition using the form 

Select Case expression 
    Case x 
         x action 
    Case y 
         y action 
    Case z 
         z action 
    Case Else 
         else action       
End Select 

The Case Else action acts as a net for expression when expression 
doesn't equal x, y, or z. The problem is, lots of folks don't bother 
to use it. After all, if you've provided a Case for all the 
possibilities, isn't it unnecessary? You might think so, but 
unexpected things do happen, and using the Case Else statement will 
help you catch unplanned errors. Simply add a Case Else action that 
alerts the user that expression doesn't fall within the expected 
parameters.


*5. UNDOING WORD COMMANDS                 
        
Most Windows applications have an Undo tool and command. Some will 
even support the keyboard shortcut Ctrl-Z, which will undo the last 
action. If you'd like to simulate the Undo command using VBA (Word), 
you'll need the Undo method. You can use this method in the form 

object.Undo(x) 

where object represents a document object and x is the number of 
actions to be undone.  

This method will undo the last action or a sequence of actions 
(depending on the value of x). In addition, this method works only on 
those commands displayed in the Undo list.


*6. SELECT CASE WITHOUT CASE ELSE                 
        
The Select Case statement allows you to run an expression or condition 
by any number of possibilities and assign a unique action for each 
condition using the form 

Select Case expression 
    Case x 
         x action 
    Case y 
         y action 
    Case z 
         z action 
    Case Else 
         else action       
End Select 

The Case Else action acts as a net for expression when expression 
doesn't equal x, y, or z. The problem is, lots of folks don't bother 
to use it. After all, if you've provided a Case for all the 
possibilities, isn't it unnecessary? You might think so, but 
unexpected things do happen, and using the Case Else statement will 
help you catch unplanned errors. Simply add a Case Else action that 
alerts the user that expression doesn't fall within the expected 
parameters.


*7. LITERAL DATES                 
        
You don't have to use a powerful function to express a date. You can 
use a literal date string instead, and they're generally faster. Save 
the functions for those times when you really need all that power. If 
you just need a date, express it as a literal date--similar to the way 
you express a string. Simply enclose most any established date string 
in pound signs. For instance, all of the following strings represent 
valid dates, and VBA will recognize them as dates because of the 
delimiters: 

#February 2, 2000# 
#2/2/00# 
#Feb 2, 2000# 
#02/02/2000# 
#2-Feb-00#


*8. COMPARING FLOATING POINT VALUES                 
        
Everyone's struggled with floating point value because you can't use 
the = operator due to the precision of Single and Double variables. 
You can run into the problem anywhere. I recently had a problem with 
Double values in the results of an Access Make Table query. If you run 
into this problem, try using the Round function instead of comparing 
the actual values directly. The Round function takes the form 

Round(value, places) 

where value is the number you're rounding and places is the number of 
decimal places you're rounding the value to.  

When comparing Single and Double variables, just be sure to use the 
same places argument.


*9. WORD MACROS                 
        
Menu items are carried out by internal macros. If you want to change a 
menu's task somewhat--perhaps enhance it a bit--you can do so by 
replacing the internal macro with your own. How? Simply create a new 
macro and use the menu item's name.  

The next question you're going to ask is how do you learn the menu 
item's macro name, right? Doing so is easy: 
 - Press Ctrl-Alt-+ (the one on the number keypad) and the cursor will 
turn into a cloverleaf.  
- Click the menu item whose macro you want to replace, and Word will 
open the Customize Keyboard dialog box.  
- The item's macro is listed in the Commands control.


*10. FOR...NEXT IN VBSCRIPT                 
        
If you're proficient at VBA, you probably find yourself using VBScript 
on occasion. One thing you need to watch for is the For...Next counter 
variable. If you try to apply VBA rules, you may have trouble. You 
see, VBA allows you to include the counter variable in the Next 
statement. In fact, doing so is a good idea when you're working with a 
long or nested loop. However, VBScript's For...Next loop doesn't allow 
for a counter variable in the Next statement. If you try to include 
the counter variable, the function will return an error.
