Visual Basic for Applications Tips #38


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

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

Proudly presents:
Visual Basic for Applications

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


*1. VBSCRIPT VERSUS VBA                     
 
by Susan Sales Harkins  
             
Occasionally, you'll run into an application that supports 
VBScript instead of VBA. Or, like Outlook, the application 
may support portions of both. When this happens, you'll need 
to know how the two languages differ. The following list 
should help:

- Perhaps the biggest difference is that VBScript doesn't 
  support Outlook intrinsic constants. You'll have to declare 
  them yourself or use the actual values. 
- The Application object is intrinsic to VBScript (behind an 
  Outlook form). 
- The Item object is intrinsic to Outlook forms. 
- VBScript supports only Variant data types. 
- Outlook forms don't include a form object.


*2. RETURN THE CURRENT FOLDER

by Susan Sales Harkins

Did you realize that you could use VBA to grab the current 
folder in Windows Explorer? Simply refer to the ActiveExplorer 
method of the Application object in the form 

obj.ActiveExplorer.CurrentFolder 

where obj refers to the Application object. For instance, 
in Outlook you might use the following code: 

Dim obj As Application 
Dim objCurrentFolder As MAPI Folder 
Set obj = CreateObject("Outlook.Application") 
Set objCurrentFolder = obj.ActiveExplorer.CurrentFolde


*3. SORTING WITH ADO

by Susan Sales Harkins

It's easy to sort records using SQL--simply tack on an ORDER BY
clause. It's equally as easy to sort records with ADO using the
Sort method in the form 

rst.Sort "fieldname" 

where rst represents the recordset object and fieldname is the 
name of the field by which you want to sort the records. It 
couldn't be simpler. 

The Sort method defaults to an ascending sort; the DESC keyword
will sort in descending order. For instance, the statement 

rst.Sort "LastName DESC" 

will sort your records by the contents of the LastName field in 
descending order. In addition, you can sort on more than one 
field. This last statement will sort the records by the LastName
field and then the FirstName field: 

rst.Sort "LastName, FirstName" 

Just be sure to separate the field names with a comma character.


*4. INTRODUCING ADO+

by Susan Sales Harkins

One of the latest technologies from Microsoft is ADO+, which 
packs ADO and XML into one neat package. It's not available just
yet, but you can learn more about it and be ready when it's 
released. To read more about ADO+, visit  

http://msdn.microsoft.com/library/default.asp?URL=/library/
techart/adoplus.htm  

CORRECTION 
Steve Schroeder pointed out a terminology error in a recent tip.
Specifically, we showed you how to use the ActiveExplorer method
to select the current Windows Explorer folder. We identified the
wrong Explorer. This method grabs the active folder in the 
Outlook Explorer. We apologize for any inconvenience and thanks 
to Mr. Schroeder for being so sharp!


*5. FINDING ADO 2.5

by Susan Sales Harkins

If you're using Office 2000, you're probably familiar with ADO. 
However, you don't need Office 2000 as long as you have Windows 
2000, because this version also includes ADO 2.5. Unfortunately,
if you go looking for it, you may not find it. The folder

Program Files\Common Files\System\ADO

has plenty of files, but none with the 2.5 version number. 
That's because ADO 2.5 is in MSADO15.DLL.

You can see the libraries properties, including the version 
number, for yourself. Simply find the file using the Windows 
Find feature. Then, right-click the file, choose Properties, 
select the Version tab, and then select Product Version in the 
Item Name control.


*6. DATE LIMITS                     
 
by Susan Sales Harkins  
             
The Date and Variant data types have limits on dates. Both data 
types will handle dates between 100 A.D. and 9999 A.D. Keep 
these limits in mind when using other date functions. For 
instance, if the result of a DateAdd() function returns a date 
outside these limits, VBA will return an error. We can say this 
without too much worry because these limits won't affect 
most users.


*7. MORE ON DATES

by Susan Sales Harkins

Wrapping all your dates in a Format() function that displays 
specific regional settings may seem redundant, since date 
formats default to the system's regional settings. Unfortunately,
most people can change the Windows Regional settings to anything 
they want, and doing so can have some unexpected and unwanted 
repercussions for your date data. That's why we suggest going 
that extra mile where dates are concerned.


*8. STRING ERRORS

by Susan Sales Harkins

The Asc(), AscB(), and AscW() functions all return an integer 
that represents the character code of the first character of the
passed string. Asc() handles normal text, AscB() accepts Byte 
data, and AscW() works with Unicode systems. 

All three require a string argument. When you're passing a 
string variable to one of these functions, we recommend that 
you check the variable first. Why? You'll do so to ensure that 
the variable contains data. If you pass a zero-length string, 
VBA will return an error. The easiest way to check a string 
variable is to use the Len() function in the form 

If Len(string) > 0 Then 
    .... Code that uses the string 
Else 
     MsgBox "You can't pass a zero-length string to the Asc() 
function." 
End If 


*9. DON'T FORGET OBJECT VARIABLES

by Susan Sales Harkins

Error-handling routines often exit the current procedure. 
However, when you take this route, you risk leaving live 
objects in your wake, which at the very least will consume 
resources unnecessarily. You can easily prevent potential 
problems by including Set statements in your error-handling code
that return all your object variables to Nothing in the form 

Set obj = Nothing 

Include a statement for each live object immediately following 
your error-handling code, but before you exit the procedure. 


*10. CONTINUATION CHARACTERS IN COMMENTS                     
 
by Susan Sales Harkins  
             
You probably know how to use the continuation character in your
code statements. Did you realize you could also use this 
character in comments? For instance, the following comment 

'this is _ 
a comment _ 
that spans three lines 

uses the continuation character to connect all three lines. 
Notice that we've prefixed only the first line with the 
apostrophe character ('). 
