Visual Basic for Applications Tips #24


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

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

Proudly presents:
Visual Basic for Applications

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


*1. NULL CONSTANT                   
          
Last month we told you that the Variant data type is the only one to 
accept Null values. Even though you can assign a Null value to a 
Variant data type, you can't test for it using a simple statement such 
as

If var = Null 

Instead, you must use the VarType() function and its vbNull constant 
to determine whether a variable contains Null by using the syntax 

If VarType(var) = vbNull 

The vbNull constant equals the value 1 (or "1" if the variable is a 
string), so you can't use this constant to assign a Null value.


*2. DAO/ADO CONFLICT                   
          
If you're running DAO code in Access 2000, you probably know that you 
need to add the DAO library (Microsoft DAO 30, 3.51, or 3.6 Object 
Library) to your references by choosing Tools, References (in the 
Visual Basic Editor). If your DAO code still returns an error, remove 
the Microsoft ActiveX Data Objects 2.0 or 2.1 Library from your 
references, and your DAO code should run just fine (provided there are 
no programming errors).  


*3. WORKING IN THE VISUAL BASIC EDITOR                   
          
Sometimes when you're changing properties using VBA code, the setting 
isn't obvious. Fortunately, the Visual Basic Editor displays the 
active object's properties in the lower-left corner. When you need to 
know a setting, return to the form or report in Design view and apply 
that setting. Then, return to the Visual Basic Editor and view the 
modified object's properties in the properties window. This method is 
particularly useful when you don't know the correct syntax for the 
property. 

Let's consider a simple example. Suppose you want to display a 
control's text in bold print. You need to know the integer value that 
represents the bold setting. (We recommend using intrinsic constants 
when available.) First, you click the View Microsoft Access button on 
the editor's Standard toolbar. Then, you change the control's Font 
Weight property to Bold in the property sheet. When you return to the 
Visual Basic Editor, review the properties in the property window for 
the FontWeight property setting, which should be 700. Now you know the 
integer value, and you can complete your code statement. Just don't 
forget to return to the form and change the object's property back to 
its original setting.


*4. QUICKLY POPULATING DYNAMIC ARRAYS                   
          
One way to define a dynamic array's elements is to refer to each index 
value using the form 

MyArray(0) = 1 
MyArray(1) = 10 
MyArray(2) = 100 

and so on. A quicker method is to use the Array() function. After 
declaring a Variant variable, assign the array elements using the 
syntax 

Dim MyArray() As Variant 
MyArray = Array(1,10,100)


*5. ARRAY() IGNORES OPTION BASE                   
          
In our previous tip, we showed you how to assign values to a dynamic 
array quickly by using the Array() function. If you choose this route, 
keep in mind that the Array() function will ignore the module's Option 
Base setting. The Array() function elements are always 0-based.


*6. REDUCE CODING TIME                   
          
If your application has a built-in macro recorder, you can save a lot 
of time writing code. Many coded tasks can be created quickly, with 
little effort, using the macro recorder. You may have to tweak the 
code a bit, but why reinvent the wheel? Start with the macro recorder, 
change what you must, and get to the next task just that much quicker.  

Some of the Microsoft Office applications have a macro recorder. 
Select Tools, Macro, Record New Macro. If the recorder is not 
available, you won't find these items on the Tools menu.


*7. EQUAL OBJECTS                   
          
When comparing variables, you may use the equal sign to determine a 
variable's value. For instance, the statement  

If var = 0 Then 

determines if the variable named var equals 0.  

You can't use the equality operator (=) when comparing object 
variables. Instead, you must use the Is operator in the form 

If objMyObject Is Nothing Then  

where objMyObject is an object variable.


*8. ANOTHER QUICK NULL CATCH                   
          
Our previous tip showed you an easy way to avoid Null errors by 
catching Nulls before they have a chance to stop your code. Another 
quick method for catching Null values is to use the Format property in 
the form 

var = Null 
str = Format(var) 

where str is a String variable and var is a Variant. The following 
procedure runs a quick check for Nulls using this method:  

Function CatchNull() 
Dim var As Variant, str As String 
var = Null 
str = Format(var) 
MsgBox str = vbNullString 
End Function 

If var is Null, the message box displays True (False when var isn't 
Null).  

Using Format returns a zero-length string, and the vbNullString 
constant, used in this context, will catch a zero-length string.


*9. GENERIC ERROR MESSAGE                   
          
No matter how careful you are, errors do creep into your application. 
When this happens, you'll want to know which error has occurred. 
Fortunately, you can add the following message box statement to your 
error handler to display the current error number and its description: 

MsgBox Err.Description & vbCrLf & Err.Number 

You can drop this into almost any error-handling routine.


*10. AN UNLIKELY ERROR STATEMENT                   
          
Most of us are familiar with the On Error Resume Next statement, and 
we use it frequently. This statement ignores the statement that 
produces an error and resumes execution with the very next line. VBA 
offers a similar statement that is seldom used--and with good reason. 
The On Error Resume statement will also ignore the error, but instead 
of executing the Next statement, Resume tries to re-execute the line 
that caused the error in the first place.  

Few of us will ever find use for this statement, and we recommend you 
avoid it. However, in the right circumstances, it can be beneficial. 
Unfortunately, it's not uncommon to omit the Next statement 
accidentally. If your application is one of the few VBA applications 
that doesn't offer the Resume statement, you don't need to worry, as 
your application won't like the incomplete statement anyway. If the On 
Error Resume statement is valid in your application, just being aware 
of the situation may save you a future headache.
