Visual Basic for Applications Tips #36


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

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

Proudly presents:
Visual Basic for Applications

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


*1. USING ARRAY AS A METHOD                   
          
You're probably accustomed to using the Array() function to create an 
array. Did you realize that you could also use the VBA object model's 
Array method? The proper syntax is 

VBA.Array(x, x, x) 

or  

Array(x, x, x) 

The following procedure is a simple example: 

Function ArrayMethod() 
Dim varArrayList As Variant 
varArrayList = VBA.Array("one", "two", "three") 
MsgBox = varArrayList(2) 
End Function  

You could also use this syntax: 

Function ArrayMethod() 
Dim varArrayList As Variant 
varArrayList = Array("one", "two", "three") 
MsgBox = varArrayList(2) 
End Function 

Both procedures do the same thing--they both return the text "two" in 
a message box.  

Just one thing to remember when using this method: Option Base has no 
effect. The first element is always 0.


*2. WHEN TO BEEP                   
          
You're probably familiar with the Beep() function, but a lot of 
developers ignore it. The reasons are varied, but one good reason is 
that this function depends on the computer's hardware, so you can't 
control the tone or volume. That means beeps can be inaudible or so 
low they might as well be inaudible. If you do choose to include beeps 
in your application, we have a word of advice. Use them sparingly--an 
application full of beeps will only annoy your users. However, one 
spot where a beep may be appropriate is at the end of a long process, 
which may go unattended by your users because of its length. A short 
beep can alert them that the process is complete so they can return to 
their system and continue their work.


*3. ABOUT COLLECTION'S COUNT PROPERTY                   
          
Several tips ago, we talked a bit about the Collection object and its 
Add method. We also showed you how to refer to an element by using its 
index value. Did you realize Collections are 1-based? That means the 
first item added to the collection has an index value of 1, not 0, as 
you might expect. As a result, iterating through the members of a 
collection is a bit easier than those that are 0-based. That's because 
you can use the Collection's Count property as the stop value in a 
For...Next statement. For instance, the statement 

For I = 1 To col.Count 

(where col represents the Collection object) would cycle a number of 
times equal to the number of members in col.


*4. DATEDIFF BEHAVIOR                   
          
VBA's DateDiff() function returns a Variant value representing a timed 
interval between two specific dates. For instance, the statement  

DateDiff("yyyy", #1/1/2000#, #1/1/2001#)  

would return the value 1, because the two dates have one year between 
them.   

There's one unexpected behavior you should know about. When trying to 
determine the years between December 31 of one year and January 1 of 
the next, this function will return 1, even though there is only one 
day between the two dates. When you think it out, the result makes 
perfect sense, because the dates are in two different years. However, 
at first, the function's response can be disconcerting, since the 
dates are only one day apart.   

CORRECTION 
DO OVER! DO OVER! 
We let some typos creep into a recent tip. Remember our tip entitled 
USING ARRAY AS A METHOD? Here's the correct procedure. Thanks to all 
who pointed out the mistake. 

Function ArrayMethod() 
Dim varArrayList As Variant 
varArrayList = VBA.Array("one", "two", "three") 
MsgBox  varArrayList(2) 
End Function 

Function ArrayMethod() 
Dim varArrayList As Variant 
varArrayList = Array("one", "two", "three") 
MsgBox  varArrayList(2) 
End Function 

Both procedures do the same thing--they both return the text "three" 
in a message box.


*5. ANOTHER ONE ON DATES                   
          
When using the Day() and Year() functions, did you know that your date 
needn't contain a day or year component? For instance, the statement  

Day(#Feb 2000#) 

would default to the value 1, which represents the first day of the 
month. The Year() function is similar in that it defaults to the 
current year. The statement 

Year(#Feb 3#) 

would default to the year 2000 (assuming 2000 is the current year).


*6. SETTING PASSWORDS IN WORD DOCUMENTS                   
          
Documents often contain sensitive or otherwise confidential data. 
Fortunately, it's easy to password-protect a document. If your 
application is automated, you may want to include this possibility. 
You can do so by prompting users to enter a password during the save 
process and then passing their response to the SaveAs method's 
Password argument. This argument is a string that Word will require to 
open the document in the future.  

Documents that are public but permanent don't require a password to 
open, but they should require a password to modify. When this is the 
case, solicit a string and pass it to the SaveAs method's 
WritePassword argument.


*7. MORE INFORMATION FOR ERROR MESSAGES                   
          
Tired of puzzling error messages? If so, download and run 
Extralert.exe to install Microsoft Office 2000 Customizable Alerts. 
This program will offer to find more information. Visit 

http://officeupdate.microsoft.com/2000/downloadDetails/alerts.htm  

to download the program. Once you've installed it, Access 2000 will 
(sometimes) display a Web Help button along with the normal error 
message. Clicking the button will take you to updated information on 
Microsoft's site. (Of course, you'll need an Internet connection for 
this feature to work.)


*8. MODELESS PROPERTIES WINDOW                   
          
When working in the Visual Basic Editor or Design View, you probably 
open the Properties sheet to modify object properties. Once you modify 
a property, do you close it and then reopen it again if you need it? 
If so, you don't need to. The Properties window (sheet) is modeless, 
which means you can access other objects while it's open. In fact, 
leaving the window open is very convenient, because the window 
displays the properties of the selected object. That means you can 
jump from one object to another without having to update the 
Properties window.


*9. ENGINE BASICS

You've probably read the term "engine" in relationship to VBA, but if 
you're like many users, you don't know what it means. The database 
engine is the software that stores and retrieves (and indexes) your 
data. You just don't know about the engine because it works behind the 
scenes and you never directly interface with it. The good part is, you 
don't need to know about the Jet (the name of the database engine) to 
take advantage of it. However, next time someone mentions it, you'll 
be able to follow the conversation (at least a bit).


*10. ADO RECORDSETS ARE FORWARD-ONLY BY DEFAULT                    
          
Forward-only recordsets are efficient when you need to move through 
your data rapidly. On the other hand, if you need to move around, 
you'll need another option. When working with ADO, you should remember 
that the Connection.Execute method creates a forward-only recordset. 
This type of recordset can be used only with the MoveNext statement. 
To move back and forth, create a recordset and use the Open method to 
retrieve the data, specifying a recordset type other than 
forward-only.
