Visual Basic for Applications Tips #41


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

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

Proudly presents:
Visual Basic for Applications

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


*1. A QUERYDEF BY THE SAME NAME

by Susan Sales Harkins

The Access QueryDef object is a useful tool for automating 
queries, but a QueryDef object stores a query definition--not 
the results. The results are held in a Recordset object.

When you want to save a QueryDef, you give it a name when you 
first create it. For instance, the following code declares and 
then sets a QueryDef object, which we'll save as "qryExample":

Dim qdf As QueryDef 
Set qdf = CurrentDb.CreateQueryDef("qryExample")

When creating QueryDef objects, be careful about the names you 
give them. If you try to create a QueryDef object using a name 
already given to an existing query, Access will return a 
run-time error.


*2. IF YOU'RE LOOKING FOR CODE

by Susan Sales Harkins

Regardless of what problem you need to solve with VBA, it's a 
good idea to look around for existing code so you don't have to
work quite so hard. A great place to start that search is at 
Helen Feddema's site at

http://ulster.net/~hfeddema/

You'll find technical articles and code samples for many 
situations--and she's already worked out most of the kinks. 


*3. DIM WARNING

by Susan Sales Harkins

If you're not new to programming but you're new to VBA, you may
try to declare your variables using the Dim statement
in this form:

Dim strFirst, strLast As String

Some programming languages allow this form, but VBA does not. 
Obviously, you're trying to declare both strFirst and strLast as
String variables. VBA will declare only strLast as a string.
Since you didn't explicitly declare strFirst, VBA will define 
strFirst as a Variant.


*4. DIM ADVICE

by Susan Sales Harkins

In our previous tip, we alerted you to an incorrect syntax form
that causes trouble when declaring variables using Dim. We also 
recommend that you position all your declaration statements 
together at the beginning of your procedure, although VBA allows
you to declare variables at any time. Grouping your Dim 
statements in this manner makes it easier to determine a 
variable's data type and scope while you're debugging, because 
you don't have to go hunting for the statement--they're all right
together at the beginning of the procedure.


*5. INPUTBOX HELP

by Susan Sales Harkins

You're probably familiar with the InputBox() function, which 
prompts users to enter data and then stores the response. Did 
you know you could display a Help button on the input box? You 
can, by using a couple of optional arguments in the form

InputBox(prompt, title, helpfile, context)

For instance, if your help information is in a help file named 
MyHelpFile.HLP, you might use the following statement:

InputBox("Enter a value", "Enter value", "MyHelpFile.HLP", 100)

MyHelpFile.HLP identifies the file that contains the help 
information, and 100 is a unique numeric value that identifies a
particular topic within that file. To create MyHelpFile.HLP, 
you'll need additional software.


*6. RETURN VERSION  
  
by Susan Sales Harkins  
  
Need to know the version of VBA your application is running? 
Perhaps the quickest way to return this information is to rely 
on VBA's Integrated Development Environment (IDE). This 
interface manages VBA projects by manipulating modules, forms, 
and source code. Not all VBA applications support this 
library--right now, it's available to Excel, PowerPoint, 
and Word.

First, you'll need to reference the appropriate library, 
Microsoft Visual Basic for Applications Extensibility. To do so,
open the Visual Basic Editor by pressing Alt-F11; select Tools, 
References; check the above library; and click OK. Then, enter 
the following procedure to return the current version of VBA:

Function GetVersion() As String 
Dim obj As VBIDE.VBE 
Set obj = Application.VBE 
GetVersion = obj.Version 
End Function


*7. PRINTING THE GUID

by Susan Sales Harkins

GUID stands for Globally Unique Identifier. This string is 
stored in the Registry, and it identifies the class of an object.
You can programmatically repair references using the GUID, but 
you'll have to know it in advance. An easy way to learn that 
string is to reference the object and then print the object's 
GUID property. The following procedure prints the VBE's GUID in
the Immediate window:

Function GetVersion() As String 
Dim obj As VBIDE.VBE 
Set obj = Application.VBE 
Debug.Print Application.VBE.VBProjects(1).References(1).GUID 
End Function


*8. DELETING MODULES

by Susan Sales Harkins

Deleting a module in the Visual Basic Editor isn't as intuitive
as you might expect, considering that the environment is so 
similar to the Windows Explorer. You probably think you can 
select a module in the Project Window and just click Delete. 
Unfortunately, that won't do the trick. Instead, you must 
right-click the highlighted module and select Remove from the 
shortcut menu to delete a particular module while working in 
the Project Window.


*9. SAVING WORD DOCUMENTS

by Susan Sales Harkins

There are a number of ways to save Word documents 
programmatically. First, you can use the simple statement

ActiveDocument.Save

to save the active document. If you're not sure the active 
document is the one you want to save, you'll need to specify a 
document using one of the following methods:

documents("nameofdocument").Activate 
ActiveDocument.Save

or

Documents("nameofdocument").Save

If you'd like to save all of the open documents, 
use the statement

Documents.Save


*10. SAVING EXCEL WORKBOOKS

by Susan Sales Harkins

In our previous tip, we showed you several methods for saving a
Word document using VBA. Excel is similar to Word in this 
respect. To save a workbook, it must be active, 
so use these statements:

Workbooks("nameofworkbook.xls").Activate 
ActiveWorkbook.Save

When saving all open workbooks, use this statement:

Workbooks("nameofworkbook.xls").Save
