Visual Basic for Applications Tips #21


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

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

Proudly presents:
Visual Basic for Applications

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


*1. ADO, ADO, AND MORE ADO                   
          
There's a lot to be said about ADO, and most of it's Greek to the 
average user. If you'd like to see the official documentation on ADO, 
visit 

http://www.microsoft.com/data/doc.htm 

Once you access this page, click the ADO Section of the MDAC SDK link. 
This documentation is part of the Platform SDK documentation that 
comes with the MSDN Library.


*2. DISABLING THE CLOSE BUTTON                   
          
UserForms display a Close button (the little x) in the right corner of 
the title bar. If you want to inhibit this button, attach the code 
shown below to the userform's QueryClose event. You can't disable the 
button, but you can keep it from working as designed. 

Private Sub UserForm_QueryClose (Cancel As Integer, CloseMode As 
Integer) 
If CloseMode = vbFormControlMenu Then 
  Cancel = True 
End If 
End Sub 

If you'd like, you can insert a MsgBox statement right before the 
Cancel statement to inform your users that the Close button is 
inactive.


*3. HELP                   
          
You can use the Application.Help property to display online Help 
topics. However, you must know the Help item's context ID. For a 
comprehensive list of these identification numbers for Microsoft Excel 
2000, visit 

http://www.j-walk.com/ss/excel/tips/tip85.htm 

and follow instructions for downloading xl9HelpIDs.xls.


*4. RUNNING QUERIES IN ANOTHER DATABASE                   
          
Eventually, you may need to manipulate one database from within 
another. You can link tables and even import data without too much 
effort. If you want to run a query, you'll need VBA. The following 
procedure runs a query named qry1 in a database named db2 from a 
second database named db1: 

Function Rundb2qry1() 
Dim db2 As Database 
Set db2 = OpenDatabase("c:\my documents\db2.mdb") 
db2.Execute "qry1" 
db2.Close 
Set db2 = Nothing 
End Function


*5. API DECLARATIONS                   
          
Your Office CD probably contains API data you could put to good use. 
Check your CD for the file  

\Win32api 

This text file contains all the declarations for the Win32 API, and 
everybody knows the declaration is the hard part. Simply open the file 
in a text editor. (You may need WordPad; NotePad may not be able to 
handle the file.)  

If you copy the file to your hard drive, you'll find the file is 
read-only. Simply change its attributes if you want to modify the 
file.


*6. SUSPENDING CODE                   
          
The Assert method is new to VBA 6.0. This method will suspend 
execution depending on the Boolean result of its argument. You'll use 
this method in the form  

Debug.Assert expression 

where expression evaluates to the Boolean values True or False. If 
expression is True, VBA stops executing; if expression is False, the 
code continues.


*7. DEVELOPER TOOLS                   
          
Microsoft Office Developer offers several developer tools you'll 
probably want to have, among them:  
 -  The distribution license  
- COM Add-In designer 
- Visual Source Safe 
- Several ActiveX controls  
- VBA Code LibrarianHTML Help Workshop 


*8. EVENT OR PROPERTY                   
          
When you open an object's properties sheet, you'll see that object's 
events listed in the form  

On event 

As a result, you may think On belongs to the event name, but it 
doesn't. Anytime you see an event prefaced with On and a space between 
each word in the name, you're looking at the event's property name. In 
contrast, the actual event doesn't contain the word On and there's no 
space between the words. For example, On Click is the Click event's 
property. In addition, On Mouse Move is the MouseMove event's 
property.


*9. CANCEL AN EVENT--PART 1 OF 2                   
          
You've probably noticed that many events offer a Cancel parameter in 
the form 

Private Function Form_Open (Cancel As Integer) 

But do you know what it's for? In a nutshell, this parameter allows 
you to cancel an event. For instance, if you set the above Cancel 
parameter to True (or any non-zero value) at any point while VBA is 
executing the event's code, VBA will cancel the event--in this case, 
that's the FormOpen event.  

Not all events offer a Cancel parameter.


*10. CANCEL AN EVENT--PART 2 OF 2                   
          
In our previous tip, we told you what an event's Cancel parameter is 
for--canceling the event. However, you should be aware that setting 
the Cancel parameter's value to True only cancels the event. VBA will 
still execute the entire event. In other words, VBA will complete the 
function procedure regardless of the Cancel parameter's value.
