Visual Basic for Applications Tips #23


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

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

Proudly presents:
Visual Basic for Applications

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


*1. SEEK IN RECORDSETS                   
          
If you're looping through a recordset, you should probably use the 
Find method and avoid Seek. That's because the Seek method always 
starts its search at the beginning of the recordset. Consequently, 
you'll always end up at the first matching record and you'll never 
reach any matching records beyond that first record.  

The only way Seek will work in such an arrangement is if you change 
the search criteria before executing each Seek method.


*2. AN EMPTY STRING CONSTANT                   
          
Most of us define an empty string as "", in the form  

str = "" 

In the run of any normal application, you may use the string "" many 
times, but there's a more efficient way. The constant vbNullString 
points to an internal empty string. Using this constant saves VBA the 
time of creating the new string each time you use "".


*3. MISSED EVENTS                   
          
Changing a control's value programmatically is a fairly common and 
useful task. But there's something you should know if you choose this 
route. Changing a control's value triggers both the BeforeUpdate and 
AfterUpdate events. However, when you change a control's value 
programmatically, the control doesn't trigger either of these events. 
So you'll want to be careful about placing code in these events when 
the control might be updated by VBA.  

The form's BeforeUpdate and AfterUpdate events are still triggered if 
you alter a control programmatically.


*4. IF...END FASTEST IF                   
          
You can use the Iif() function in your VBA code, but avoid it because 
it's slow. The If...End If statement is always faster than the Iif() 
function despite what you might think. For instance, even a function 
as simple as the following: 

str = Iif(Flag = 0, "No", "Yes") 

is slower than the equivalent If...End If statement: 

If Flag = 0 Then 
     str = "No" 
Else 
     str = "Yes" 
End If


*5. FASTEST SELECT CASE                   
          
Did you know that the Select Case statement skips any remaining Case 
actions once VBA finds a matching Case value? It does, and that means 
you should always use the most likely match as the first case, the 
second most likely match as the second case, and so on as long as you 
can make that determination. This arrangement is much more efficient 
than an unordered arrangement. That's because once VBA finds a 
matching condition, it immediately executes the corresponding Case 
action and skips any remaining conditions.


*6. DATA TYPE FOR NULLS                   
          
The only data type that will accept a Null value is the Variant data 
type. Therefore, when you're considering what data type to assign to a 
variable, don't forget about Null values. If it's possible that your 
code may pass a Null to the variable, be sure to declare that variable 
as a Variant. If you don't, VBA will return an error when it 
encounters a Null.


*7. ISNULL AND NULL                   
          
Most of the time, you probably use the IsNull() function to determine 
whether a variable is Null. You won't want to use IsNull() with the 
Find method because the ADO Find method doesn't recognize the IsNull() 
function. If you need to search for a Null value, you'll need to 
specify the value in the form 

Field = Null  

or  

Field <> Null


*8. WORD STARTUP
          
Most custom applications take control of the application's environment 
from the get-go. This means that your application may implement 
features that are needed throughout the entire work session. To 
control Word programmatically from the time you launch the 
application, you have two choices:

- Add a public procedure to any code module and name that procedure
AutoExec.  
- Name a code module AutoExec and add a public procedure named Main.

You can also control the way your application closes by adding an
AutoExit procedure or module to the project.


*9. WORD AUTO DOCUMENTS                   
          
In our previous tip, we explained how to control the Word environment 
from the time you first launch it. Similarly, you can also add this 
type of control to a document. Technically, you'll be using WordBasic 
features instead of VBA. To control a document or a set of documents 
from the time the document(s) is opened:

- Add a public procedure named AutoOpen to the document. If your 
document opens a new document based on a template, name the procedure 
AutoNew.

- Name a module AutoOpen or AutoNew and add a public procedure named 
Main.

VBA does offer its own solution. Simply use the template's or 
document's Open event. If you want to add code to the document's 
closing sequence, create an AutoClose module or procedure, or add code 
to the document's Close event.


*10. CALLING AN EVENT                   
          
You probably know that you can call one procedure from another by 
using the Call statement or simply expressing the function's name. For 
instance, either of the following will call the same procedure: 

Call DoMyWork(arg1, arg2) 
DoMyWork arg1, arg2 

If you omit the Call statement, be sure to eliminate the parentheses 
around the argument list.  

Did you realize that you could call an event procedure the same way? 
Simply precede the event name with the Call statement in the form 

Call cmdSave_Click() 

Keep in mind that events are private to their object, so you can call 
an event only from within the same object (module).
