Visual Basic for Applications Tips #4



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

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

Proudly presents:
Visual Basic for Applications

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


*1. USING CONSTANTS                 
        
VBA has several constants that represent values. For instance, 
vbNewLine represents Chr(13) and Chr$(13). Similarly, vbYesNo 
represents the value 4 in the MsgBox statement's type argument. 
Almost all built-in values have a corresponding constant. 
Whenever possible, you should use these constants instead of 
their value counterparts. The constants are more compatible 
with other VBA products--they almost always remain unchanged 
in upgrades, and they're more readable when debugging or 
reviewing code.


*2. ASSISTANTS TALK BACK IN WORD                 
        
Almost all VBA applications support the MsgBox function, but 
Word adds a bit of flair. Normally, the MsgBox function displays 
a dialog box with a specified message. However, if you call the 
function from WordBasic instead of VBA and an Office assistant is 
active, WordBasic will route the message through the assistant, 
not the dialog box you normally expect. While this is a neat 
feature that you can exploit, you'll want to use it carefully, 
since the WordBasic function parameters aren't the same as VBA's. 
Specifically, the type and title parameters are switched.


*3. FINDING CONSTANTS                 
        
Many arguments require a constant or its numeric equivalent, 
but it isn't always easy to find a list of possible responses. 
Generally, you can locate them in the Object Browser, if you 
just know what you're looking for. Most constants are clumped 
together in constructs called Enums (short for Enumerations). 

Some Enums are explicitly named--the suffix Enum is attached 
to the construct's name--and therefore easy to find. In Access, 
Enums are identified with the Ac prefix, but VBA is less 
consistent. Some Enums begin with the prefix Vb, while others 
have no prefix at all. 

When needing to identify the available constants for a property 
of method, open the Object Browser and choose the function in 
the Classes list. Then choose the appropriate method in the 
Methods list. The lower pane will display the arguments and, 
where appropriate, list the appropriate Enum. At this point, 
all you need to do is click the Enum in the lower pane, and VBA 
will update the Classes and Members lists accordingly. The 
Members list will contain all the constants, and selecting a 
constant in the Members list will update the contents of the 
lower pane. Specifically, the pane will display the constant, 
its value equivalent, and the name of the housing Enum.


*4. CONSTANTS IN VBSCRIPT                 
        
In a previous tip, we showed you how to find the possible 
constants for a given argument. Unfortunately, you can't use 
these constants in VBScript--you must use the value equivalents 
in any VBScipt code. Even if you upgrade to Outlook 2000, you'll 
need to adhere to this rule somewhat. Outlook 2000 supports VBA 
only as it applies to running the application. When constructing 
forms, you will still use VBScript.


*5. USING THE DATE TO NAME YOUR FILE                 
        
It isn't unusual to find companies that incorporate the date in 
their filenames. Fortunately, VBA makes it easy to save the 
current file using today's date. If you're using Word, you'll 
need the following macro: 
 
Sub SaveAsDate() 
Dim sFile As String, sPath As String 
sPath = "C:\" 
sFile = Format(Now(), "mmddyy") 
ActiveDocument.SaveAs (sPath & sFile) 
End Sub 
 
When using Excel, try this macro: 
 
Sub SaveAsDate() 
Dim sFile As String, sPath As String 
sPath = "C:\" 
sFile = Format(Now(), "mmddyy") 
ActiveWorksheet.SaveAs (sPath & sFile) 
End Sub 
 
As you can see, the only real difference between the two macros 
is the ActiveDocument and Active Worksheet object. You can easily 
modify this macro to include an additional string in the filename. 
Simply declare a new variable, define it, and then concatenate it 
to the other SaveAs method's arguments.


*6. USING THE AUTO LIST MEMBERS LIST                 
        
It's nearly impossible to memorize all the different functions, 
statements, and their appropriate arguments, methods, and 
properties. Who would even try? Fortunately, you don't have to, 
because VBA remembers for you. When entering code, the Auto List 
Members list displays a dropdown list of object types, 
properties, and methods. You enter the object, and VBA displays 
a list of choices. For instance, if you enter 
 
Dim frm As 
 
The Auto List Members list will display the different object 
types. At this point, you can select an item for the list, or 
you can give the list more information to narrow the search. If 
you enter F, the list will display the narrowed 
possibilities--Field, Fields, Form, and Forms. To choose Form, 
press the down arrow twice and then press Tab to enter Form as 
the object type. The following table lists the different actions 
you can take using the Auto List Members list: 

Tab or Ctrl+Enter--Enters the selected item Spacebar--Enters 
the selected item and a space character Enter--Enters the 
selected item and moves the cursor to the next line Esc--Closes 
the list


*7. ADDING LIBRARIES TO THE OBJECT BROWSER                 
        
You probably use the Object Browser to keep up with the VBA 
library and that of your favorite Office application. However, 
you aren't limited to the native classes and VBA. You can add 
objects in other libraries--libraries that belong to other 
Office applications. To do so, first you must create a reference 
to that library by choosing References from the Tools menu. 
Next, select a library in the Available Reference list box and 
click OK. 

Once you've created the reference, open the Object Browser 
(open a module and press F2). Open the Project/Library control's 
dropdown list, and you'll find the new library listed. You can 
proceed using the Object Browser with this new library as you 
would the native library.


*8. WORKING WITH THE DIFFERENT MODELS                 
        
The Office hierarchy is full of object models that can quickly 
become confusing if you don't know what belongs to which. For 
instance, earlier versions of Access consist of the Applications 
and the Data models--which are updated in Office 2000. 
Fortunately, we've found a good online reference that should 
help keep all the different models and objects straight. 
Try visiting   

http://www.inquiry.com/objects/index.html   

You'll find diagrams of all the Office models, and then some. 
We hope they update this site soon with the Office 2000 
information.   

Correction  
Our June 7 tip "Using The Date To Save Your File" has an error 
in it. If you're using Excel, replace ActiveWorksheet with 
ActiveSheet. We apologize for any inconvenience.  

Sub SaveAsDate()  
Dim sFile As String, sPath As String  
sPath = "C:\"  
sFile = Format(Now(), "mmddyy")  
ActiveSheet.SaveAs (sPath & sFile)  
End Sub


*9. NAMING VARIABLES                 
        
Variables are an easy way to increase efficiency in any 
application. But there are a few rules to follow when naming 
variables: 
- Variable names should start with an alphabetic character 
- Don't use these characters in the name: ., %, &, !, #, @, or $ 
- Don't use duplicate names within the same scope 
- Variable names are limited to 255 characters


*10. GIVING YOUR FUNCTION A DATA TYPE                 
        
You're probably familiar with declaring data types for variables, 
but did you know you can also declare a data type for your 
functions? Doing so will protect the validity of your data--just 
as data types protect variables. Let's consider the following 
function: 

Function GetMyValue() 
End Function 

The name itself implies that the results of this function should 
be a value. Unfortunately, you can't guarantee that it will be, 
unless you declare the function as a value data type. For 
instance, the function 

Function GetMyValue() As Integer 
End Function 

does guarantee that regardless of what goes wrong with the code, 
the function will not return anything other than an integer value. 
The function may return an error, but it won't return a 
non-integer.
