Visual Basic for Applications Tips #35


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

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

Proudly presents:
Visual Basic for Applications

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


*1. ADDING BLANK LINES TO LISTS                   
          
In our previous tip, we showed you how to add a second (or more) 
column of items to a list or combo box list. You can use the same 
method to display blank lines in a control. Specifically, insert a 
zero-length string between each item in the list in the form 

"item1";"";"item2";"";"item3";"" 

In addition, be sure to select the Value List setting from the Row 
Source Type property. The Column Count property should be set to 1.


*2. GLOBALS LOSE VALUE                   
          
Global variables can be useful, but take care when you depend on them, 
because they can be easily wiped clean by an unhandled error. When VBA 
encounters an error that's not rerouted by appropriate handling code, 
it cancels the value of all global variables. In addition, an 
inappropriately placed End statement can reset your global variables 
before you meant for that to happen. There's really no workaround for 
this behavior. Just make sure you include thorough error-handling code 
if your application includes global variables. (Also, avoid using the 
obsolete End statement.)


*3. FORMATTING PHONE NUMBERS                   
          
If you're using Access, you can use a control's Input Mask property to 
format a phone number a specific way. When you can't rely on a 
built-in feature, use VBA. For instance, if you want to allow an area 
code but it's not mandatory to have one, you might use a procedure 
similar to the following: 

Sub FormatPhone(phone As String) As String 
FormatPhone = Format(phone, "(@@@)&&&-&&&&") 
End Sub 

The Format() function returns an area code or spaces in parentheses 
followed by the phone number. When there's a possibility that there's 
no phone number to enter, you might try this procedure: 

Sub FormatPhone(phone As String) As String 
FormatPhone = Format(phone, "(@@@)


*4. WHAT DAY IS IT                   
          
VBA is very flexible when it comes to working with dates--it even 
supplies three functions you can use to determine the current date or 
time. All three functions--listed below--rely on your system's clock:

- Date: Returns only the date. 
- Time: Returns only the current time. 
- Now: Returns the current date and time as a value. The integer 
portion represents the date; the decimal portion represents the time. 

When you know you'll be working with only the date or the time, it's 
much easier to work with the Date and Time functions, respectively. 
Leave Now for those tasks that require both the date and time.


*5. ABOUT THE COLLECTION OBJECT                   
          
VBA's Collection object is a simple container for data, much like an 
array. Most of the time its members are other objects, but it can hold 
any type of data. Its claim to fame is its four methods:

- Add: As implied, this method adds an item to the collection.  
- Count: Returns the number of items in the collection. 
- Item: Retrieves a member from the collection. You can use an index 
or key value to identify the item. 
- Remove: Deletes a member from the collection. You'll use either the 
index or key value.


*6. THE ADD METHOD                   
          
In our previous tip, we introduced you to the Collection object. We 
mentioned that you could identify a member of the collection by its 
index or key value. Today, we'll define those two terms. Access 
assigns an index value when you add the item to the collection. 
Specifically, Access assigns the value of 0 to the first item, 1 to 
the second item, 2 to the third item, and so on.  

If you'd like a bit more flexibility, you can assign a key value when 
you add the item in the form 

col.Add "itemname", "key" 

where col is the collection, itemname is the name of the item you're 
adding to col, and key represents the value you're assigning as the 
key value. For instance, let's suppose you're adding names to a 
collection, named--appropriately enough--names. In this case, you 
might use a statement similar to 

names.Add "Joe Smith", "js" 

where js is the key value for the member named "Joe Smith".


*7. REFERENCING OBJECTS                   
          
In our previous tip, we showed you how to assign a key value to 
members of a collection as you add the item to the collection using 
the Add method. Besides using that key value, there are two other ways 
to refer to a collection member. First, you can use the index value in 
the form 

rst.Fields.Item(0) 

where rst represents a recordset that contains a collection of fields 
(Fields). You can shorten this syntax since Item is the default 
property: 

rst.Fields(0) 

You can also refer to a member by its name, in the form 

rst.Fields("nameoffield") 

where nameoffield represents the field you're referring to.


*8. CHECKING ARRAYS                   
          
When working with arrays, you might take a shortcut when determining 
the lower and upper boundary of that array. That shortcut would be 
specifying those boundaries as literal values. Let's look at a quick 
example that works and seems harmless--at first.  

For i = 0 To 4 
 var = myArray(i) 
Next i 

This simple For loop cycles through each element in the array and 
assigns the current element to a variable named var. All goes well 
until someone adds a sixth element to your array because your loop 
never picks up that new value, and you know that will create a 
problem.  

The solution is to refer to the upper and lower boundaries dynamically 
using the LBound() and UBound() functions in the form 

For i = LBound(myArray) To UBound(myArray) 

The LBound() function returns the lower boundary value and UBound 
returns the upper boundary level, so your loop never misses an 
element.


*9. ERROR-HANDLING TEMPLATE                   
          
Error handling is an important part of any application, but it can be 
difficult to add in the early stages. One problem is that simple error 
handling can mask unexpected errors. One solution to this problem is 
to add a simple error-handling procedure that alerts you to the error 
and either exits the procedure or continues, as appropriate to the 
procedure's task. For instance, the following error-handling routine 
displays a message when an error occurs and then terminates: 

Private Sub event() 

On Error GoTo errHandler 
...code... 
Exit Sub 

errHandler: 
MsgBox err.Number & vbCrLf & Err.Description, vbCritical, "Error 
Information" 
End Sub 

You can add this to almost any procedure. Occasionally, you'll want 
the application to continue, despite the error. In this case, you 
might want to try the following: 

Private Sub event() 

On Error GoTo errHandler 
...code... 

eventExit 
Exit Sub 

errHandler: 
MsgBox err.Number & vbCrLf & Err.Description, vbCritical, "Error 
Information" 
Resume eventExit 
End Sub 

The Resume statement in the error handler allows the program to 
continue. Once you're past the initial development stage, you can add 
more specific error-handling code.


*10. PREVENTING AN ABS() ERROR                   
          
The Abs() function returns the absolute value of a value. For 
instance, the simple expression 

absval = Abs(-3) 

would return the value 3 (positive 3).  

The Abs() function works only with numeric values. For this reason, 
you should check values you plan to use in an Abs() function using the 
IsNumeric() function. For instance, the following code executes the 
Abs() function only if the value being passed is a numeric value: 

If IsNumeric(val) Then 
 Abs(val) 
Else 
 .... 
End If 

If you omit this check and somehow the Abs() function encounters a 
non-numeric value, VBA will return an error.
