Visual Basic for Applications Tips #25


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

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

Proudly presents:
Visual Basic for Applications

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


*1. OBJECT RETURNING FUNCTIONS                   
          
Functions often return a value, and when this is the case, we 
generally declare the function's data type. For instance, if a 
function will return an integer, you might use the function statement 

Private Function ReturnValue() As Integer 

You might not realize that a function can also return an object 
reference. When a function does so, you must remember to use the Set 
statement to assign the function's reference. The following procedure 
example illustrates what we mean: 

Private Function GetObject() As SomeObjectType 
Dim obj As SomeObjectType 
....<code that defines object> 
Set GetObject = obj 
Set obj = Nothing 
End Function


*2. IMPLICITLY TRUE                   
          
When writing an If statement, you can take advantage of VBA's implicit 
form of True to reduce your code just a bit. If so, you should be 
aware of the differences between the implicit and explicit forms of 
True and False. When expressed explicitly in the form 

If var = True Then 

var must evaluate to -1 to return True.  

In contrast, the statement 

If var Then 

is True much more often than the previous statement because the 
implicit form of True includes any non-zero value. Consequently, only 
0 evaluates to False. Keep this behavior in mind when depending on the 
implicit True value--the results might not be what you expected.  


*3. CATCHING RECORDSET NULLS                   
          
You may not anticipate Null values in a recordset, but sometimes 
they're there just the same. If you aren't prepared for the Null 
value, your application will come to a screeching halt with the 
dreaded  

Invalid Use of Null 

error. To avoid this, you should test field values for Null using the 
IsNull function in the form 

If IsNull(rst!fieldname) Then 

where rst represents the recordset and fieldname is the field you're 
accessing. 


*4. WHEN ISDATE DOESN'T WORK                   
          
The IsDate() function validates an entry to confirm that the entry is 
a valid date. But there's one spot where it just won't work--in a text 
box control's Change event. That's because the Change event checks 
every keystroke. So, as you begin to enter a date, the IsDate() 
function won't recognize the entry as a valid date, because 
technically it isn't. An entry isn't a valid date until it's complete.


*5. NULL IS ALWAYS FALSE

The only way to check for a Null value is with IsNull. For instance, 
you might try the statement 

If var = Null Then 

to determine whether var is Null. However, this statement always 
evaluates to False, even if var is Null. You see, any expression that 
contains a Null is always Null and all Nulls are False. You can check 
this behavior using the following procedure: 

Function NullCheck() 
Dim var As Variant 
var = Null 
If var = Null Then 
    MsgBox True 
Else 
    MsgBox False 
End If 
End Function 

Even though we've assigned a Null value to the variable var, the If 
condition always evaluates to False.


*6. PREVENTING DATE COMPONENT ERRORS                   
          
The Hour(), Minute(), and Second() functions return the hour, minute, 
and second component, respectively. All three functions require one 
argument--a valid date. For instance, the statement 

Hour(#13:24:22#) 

returns the integer 13. However, if the argument isn't a valid time, 
all of these functions return a  

Type mismatch 

error. Therefore, we suggest you use the IsDate() function to check 
the argument before using it in one of these time functions.


*7. MULTIPLE CASE EXPRESSIONS                   
          
The Select Case statement accepts multiple conditions in a single Case 
statement. However, VBA evaluates them separately. In other words, you 
should think of multiple conditions in terms of the OR operator and 
not the AND operator. For example, the following Case statement may 
seem valid: 

Case Is > 10, Is < 20 

You might think this statement will return True if a value is greater 
than 10 but less than 20. In other words, you're looking for values 
between 10 and 20. However, that's not how it works. Once a condition 
is met, the others are ignored. Therefore, if the value you're 
comparing is greater than 10, this statement returns True, even when 
the value is greater than 20. In addition, any value that's not 
greater than 10 will certainly be less than 20, and any value that's 
not less than 20 will be greater than 10. Therefore, the condition 
always returns True.


*8. ACCESSING OBJECT PROPERTIES                    
          
If you're using object properties, consider storing the property value 
in a variable so it can be used multiple times. A local variable is 
much faster to access than an object's property. (This relates to how 
the object is stored in memory.) As a rule of thumb, if you refer to 
the same property more than a few times, create a variable for it.


*9. A CONDITIONAL MACRO                   
          
VBA doesn't have any trouble making decisions. You can use If or 
Select Case to check for conditions and direct your code accordingly. 
You might not realize that macros can also make decisions. For 
example, let's suppose that you want to open a form. In addition, you 
want the option to cancel the entire macro instead of opening the 
form. In this case, you might use the InputBox() function to determine 
whether the macro continues (and opens the form) or quits. First, open 
the Conditions column by clicking the Conditions Columns button on the 
Macro Design toolbar. Next, enter in the Conditions column the 
expression  

InputBox("Do you want to open the form?") = "Yes" 

When you run the macro, this expression will display an input box. If 
you respond to that box by entering the string  

"Yes"  

the macro will continue. If you enter anything else, the macro will 
quit.


*10. QUICK PROPERTY SETTING                   
          
Generally, we manipulate an object's properties using the syntax 

object.property = newsetting 

If you need to know the current property setting, you probably leave 
the module and return to the object to view its properties sheet. 
Instead, run a statement in the Debug window in the form  

? object.property 

The Debug window will respond by displaying the current property 
setting for object. Just make sure object references the object 
properly. For instance, if you're checking a control on a form, be 
sure to identify the form and the control in the form 

? Forms!formname!controlname.property
