Visual Basic for Applications Tips #16


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

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

Proudly presents:
Visual Basic for Applications

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

*1. DRIVES AND FOLDERS                 
        
It's easy to change the default drive and folder using VBA. To change 
the drive, use the ChDrive function in the form 

ChDrive "x" 

where x is the name of the drive you're switching to. If you want to 
change the current folder, use the ChDir function in the form 

ChDir "folderpath" 

where folderpath represents the complete path to the folder you want 
to make the default folder.


*2. GRAMMAR CHECK                 
        
It's easy to start a grammar check in a Word document. Simply use the 
CheckGrammar method in the form 

object.CheckGrammer 

where object is the Document or Range object you want to check.  You 
can also check a specific string using the syntax 

Application.CheckGrammar(string)


*3. SHARING FILES BETWEEN VB AND VBA                 
        
Sharing VB forms and modules with a VBA application and vice versa can 
be an efficient use of your objects and code. Sharing is easy to do. 
Basically, all you have to do is import the file.  

Here's what to do if you're in VB: Export the form or module using the 
VBA application's export command. (In the VB Editor, right-click the 
file in the Project Explorer and select Export File from the context 
menu.) Then, choose Project, Add File. Next, locate the file you want 
to add in the Add File dialog box and click Open. 

If you're in a VBA application: Open the VB Editor by pressing 
Alt-F11. Then, choose File, Import File. Locate the file you want to 
import in the File Import dialog box, then click Open.


*4. WORKBOOK NAMES                 
        
The Excel Workbook object has several properties that return the 
workbook's name, path, and fullname. For instance, the statement 

workbook.FullName 

returns the full pathname of workbook. (The full pathname includes the 
drive, folder(s), and filename.) If you want just the workbook's name, 
you'll use the Name property in the form 

workbook.Name 

If you want the path (without the drive), use the Path property in the 
form 

workbook.Path 

Until you save a workbook, the Path property returns an empty string 
("").


*5. FINDING AN EMPTY RECORDSET                 
        
You can run a record count of a recordset to see if it's empty, but 
there's an easier way. The following procedure returns True if the 
recordset is empty and False if it isn't:  

Public Function RecordsetEmpty(rst As Recordset) As Boolean 
        RecordsetEmpty = rst.BOF = True And rst.EOF = True 
End Function 

You see, if the current position is both the beginning of the file and 
the end of the file, then there are no records. If either condition 
isn't true, RecordsetEmpty equals False.


*6. ECHO'S STATUS BAR MESSAGE                 
        
The Echo method freezes the screen so you can hide distracting tasks 
from the user. Did you know that you could also display a message in 
the status bar using the Echo statement? To do so, use the syntax 

Application.Echo echoon[, statusbartext] 

When echoon equals True, the host repaints the screen; when this 
argument is False, the host doesn't repaint the screen. The 
statusbartext argument is a string expression that the host displays 
in the status bar when the screen display is turned on or off. The 
statement 

Application.Echo False, "Processing information, please wait."  

would display the message  

Processing information, please wait. 

in the status bar.


*7. PLEASE OPEN THE DOOR                 
        
Would you like an application to open or close the CD-ROM door? Well, 
you're in luck, because there's an API that will do just that, and 
it's easy to use, unlike many APIs. First, add the following to a 
module's General Declarations section: 

Private Declare Function mciSendString Lib "winmm.dll" Alias 
"mciSendStringA" (ByVal lpstrCommand As String, ByVal 
lpstrReturnString As String, ByVal uReturnLength As Long, ByVal 
hwndCallback As Long) As Long 

Next, add the following two functions to your module: 

Function OpenDoor() 
  mciSendString "Set CDAudio Door Open Wait", 0&, 0&, 0& 
End Function 

Function CloseDoor() 
  mciSendString "Set CDAudio Door Closed Wait", 0&, 0&, 0& 
End Function 

To see your door-controlling functions at work, enter the following 
statements (one at a time, of course) in the Immediate window: 

?OpenDoor 
?CloseDoor


*8. AN EFFICIENT WAY TO CLOSE A FORM                 
        
It's common practice to have the user confirm a close request. The 
following procedure is an efficient method that can eliminate the need 
for code in your Close command button:  

Private Sub Form_Unload(Cancel As Integer) 
    Cancel = MsgBox("Do you want to quit now?", vbOKCancel) = vbCancel 
End Sub 

If you don't want to omit a Close command button, this procedure is 
still beneficial. If a user tries to close the form inappropriately 
(by bypassing the command button and clicking the Windows Close 
button), this procedure will force the user to confirm that choice. In 
addition, you can include code that performs any necessary close tasks 
that might otherwise be skipped without this failsafe.


*9. ADDING AN ITEM TO A LIST BOX                 
        
It's rather difficult to add an item to a list box on the fly. If you 
need this functionality but would rather skip the extra work, use the 
Microsoft Forms list box instead of the native list box. Microsoft 
Forms controls--and there are several to choose from--are more 
flexible than many of the native controls.  

To insert a Microsoft Forms 2.0 ListBox control into your Access form 
or userform, in Design View, choose Insert, Microsoft Forms 2.0 
ListBox, then click OK. Or click More Controls on the Toolbox and 
select the control from the resulting list. Once you insert the 
control, you can use that control's properties and methods just as you 
would a native control's properties. 

One of the Microsoft Forms 2.0 ListBox methods is AddItem, which uses 
the syntax 

object.AddItem "item", index 

This procedure will add two items to the list box when the form is 
opened: 

Sub Form_Open() 
lst.AddItem "red" 
lst.AddItem "blue" 
End Sub 

To remove an item from a list, use the RemoveItem method in the form 

object.RemoveItem index 

where index is the position of the item you want to remove (the first 
item equals 0).


*10. MORE ABOUT MS FORMS 2.0 LISTBOX CONTROL                 
        
In a previous tip, we showed you how to add items to a Microsoft Forms 
2.0 ListBox control. When you're using the AddItem method, you can 
also specify the position of the new item within the existing list. 
For instance, we used this simple procedure to add two items to a 
list:    

Sub Form_Open()   
lst.AddItem "red"   
lst.AddItem "blue"   
End Sub   

If you wanted to add the item "white" between the already existing 
items, use the statement   

lst.AddItem "white", 1   

NOTE: 

The Office 2000 service release 1 is out and available for download at  

http://www.microsoft.com/office/details/SR1.htm  

Unfortunately, this release seems worse than a little buggy -- it's 
causing real havoc on some systems, so you might want to consider 
waiting until all the kinks are worked out.
