Visual Basic for Applications Tips #7



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

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 COMPILE?                 
        
After entering a procedure, you probably choose Compile Project 
from the Debug menu. But you may wonder what's going on behind 
the scenes during a compile. First, VBA checks the code for 
compile errors--anything VBA can't interpret. Generally, compile 
errors are caused by syntax errors. If no errors are found, VBA 
then translates the code into a form your computer understands. 
That way, the computer can execute your code more quickly.


*2. PASSING OPTIONAL ARGUMENTS                 
        
Passing arguments from one procedure is how functions talk to one 
another. Passing an argument is simple--you specify it in the 
main function in the form 

Function DoMyWork(argument As datatype) 

Then, you pass the necessary information to the function in 
the form 

=DoMyWork(argument) 

If you fail to pass the required argument by omitting it from 
the function 

=DoMyWork() 

the function will return an error. Occasionally you have a 
function that may or may not need additional information to do 
its job. In these cases, you can declare the argument as 
optional by using the syntax 

Function DoMyWork(Optional argument as datatype) 

Now, VBA will execute the function with or without the passed 
argument. So our function will accept either call: 

=DoMyWork(argument) 
=DoMyWork() 

You can also combine required and optional arguments. For 
instance, the function 

Function DoMyWork(Optional argument1 as datatype, argument2 
as datatype) 

will accept two arguments, but requires only one--argument2.


*3. INFORMATION ON ADO                 
        
If you're still waiting for the ADO (ActiveX Data Objects) train, 
you may find yourself left at the station! Office 2000 includes 
an enhanced version of ADO, and with that product's release, ADO 
will quickly become the model of choice. (Among serious 
developers, it already is.) 

ADO was designed as a client-server system and should increase 
usage of Microsoft products in Internet applications, because 
ADO can access many types of data--not just relationship 
database data. Now you can use ADO to access Web pages, 
spreadsheets, and documents. Eventually, ADO will replace DAO 
and RDO, which access only relational databases. 

You can learn more about ADO and how it fits into your future 
computing needs by visiting these Web sites: 

Microsoft ActiveX Data Objects at 

http://www.microsoft.com/data/ado/ 

OLE DB Technology at 

http://www.oledb.com/ole-db/guide.html 

Using ActiveX Data Objects (ADO) via Visual Basic at 

http://support.microsoft.com/support/kb/articles/Q168/3/35.asp


*4. IIF() IS OUT OF ORDER                 
        
In a previous tip, we told you that the Iif() function is a bit 
slower than the If...Then...Else construct. That's because the 
Iif() statement doesn't evaluate its arguments in the order you 
might expect. Both the trueaction and falseaction arguments are 
evaluated before the conditional test is evaluated. This means 
an invalid expression in the falseaction can return an error, 
even if the trueaction argument evaluates to True. 

In contrast, the If...Then...Else construct evaluates actions 
only as long as they are needed to complete the function. For 
instance, if the conditional test is met, VBA evaluates only 
the trueaction. If the conditional test isn't met, VBA 
evaluates only the falseaction.


*5. AVOID RETYPING STATEMENTS                 
        
Most of us use the Immediate window (or the Debug window in 
earlier versions of Access) to test functions and expressions. 
You simply type in the expression and press Enter to see the 
results. Then, you probably return to the module to make 
modifications and then test your changes. If you're retyping the 
function or expression each time you return to the Immediate 
window, you're wasting time and effort. You don't need to. Simply 
position the cursor at the end of the function or expression 
and press Enter.


*6. QUICKLY LOOPING THROUGH A RECORDSET                 
        
A common database task is to loop through all the records in a 
recordset. Typical code for completing such a task might 
resemble the following: 

Do While Not rst.EOF 
 ...task... 
 rst.MoveNext 
Loop 

However, this structure can be slow because the code checks for 
the end of the file at the beginning of each loop. You can speed 
things up by eliminating this check using the following routine: 

rst.MoveLast 
iTotal = rst.RecordCount 
rst.MoveFirst 

For iCounter = 1 To iTotal 
 ...task... 
 rst.MoveNext 
Next iCounter 

Instead of checking to see if you've reached the end of the file, 
the For loop simply ticks off the appropriate number of cycles. 
This structure will speed up your search a great deal--as much 
as 30 percent.


*7. ADD INFORMATION WITH COLOR                 
        
The more information you can give your users, the better. An easy 
way to convey information quickly is with color. For instance, if 
you want users to know that a particular field is 
required--meaning that they must enter data in that field before 
they can go to the next record--you can create a label control 
and enter the message "This is a required field, you must enter 
data." But this solution requires that your users stop and read 
the message, which can waste time if they must do so repeatedly. 

Instead, use the same color consistently throughout your 
application to send a signal to your users that they must enter 
data in that field to continue. Your users will learn and adjust 
quickly, and at a glance they will know that a field requires an 
entry without having to interrupt their work. On the other hand, 
if most of your fields are required, it might be just as 
effective to apply special coloring to the fields that 
aren't required. 

You can use color to denote all kinds of information. Just be 
sure to use color consistently and sparingly.


*8. DISPLAYING A 12-HOUR TIME FORMAT                 
        
Most of us use a 12-hour clock to tell time; computers keep a 
24-hour clock. That means your PC or application may return a 
time that's not readily known to you. How many of us know that
 22:00 means 10:00 PM? 

If you're accustomed to a 12-hour clock, you don't have to give 
it up just because your computer runs on a 24-hour clock. 
Instead, use the Format() function to display time in familiar 
terms, using the following syntax: 

FORMAT(Time,"hh:nn AM/PM") 

This function will return the current time using the AM/PM 
clock. You can also pass a time value to the function instead 
of using the Time() function. Just be sure to delimit the value 
properly using the # delimiter.


*9. HIDING THE MOUSE POINTER                 
        
The mouse pointer is an integral part of almost any Windows 
application, and the Office applications are no exception. Many 
of us work with the mouse pointer extensively. Others, however, 
still rely on keyboard shortcuts and avoid using the mouse. 

You may not realize it, but you can hide the mouse pointer by 
using an API call. Fortunately, it's one of the more simple 
calls; the declaration is short and the calls are simple. To 
begin, add the following declaration to a module: 

Declare Function ShowCursor Lib "user32" (ByVal bShow As Long) 
As Long 

To hide the mouse pointer, simply make the following call: 

booResult = ShowCursor(False) 

To unhide the mouse pointer, set ShowCursor to True using the 
following statement: 

booResult = ShowCursor(True) 

Don't forget to declare booResult as a Boolean data type. In 
addition, be very careful how you position these two calls. You 
don't want to turn off the mouse pointer without giving the user 
or the application the option to turn it back on at some point. 

If you're still using a 16-bit system, you'll need the 
following declaration: 

Declare Function ShowCursor Lib "User" (ByVal bShow As Integer) _
      As Integer


*10. OPENING A DROP-DOWN LIST                 
        
You probably click the drop-down arrow to the right of a combo 
box's text box to open that control's list. However, you can 
have VBA open the list for you. Specifically, you can use the 
Dropdown method in the form 

Comboname.Dropdown 

If you're using Access, you'll want to attach this method to the 
control's GotFocus event. If you're using Word or Excel, try 
using the control's Enter event. If the control is the first 
control to receive the focus when opening the form or userform, 
you might want to attach this method to the form's Open or the 
userform's Activate event.
