Visual Basic for Applications Tips #29



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

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

Proudly presents:
Visual Basic for Applications

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


*1. KEEPING COUNT IN A FOR LOOP                 
        
In a previous tip, we showed you how to reference fields without 
explicitly naming them. We used the following For loop to bind 
the fields in a recordset to the controls in a form: 

For i = 0 to 3 
Me("txt" & i) = rst(i) 
Next i 

If there are fewer than four fields in rst, the loop raises an 
error. To prevent that error, you can limit the loops to the 
exact number of fields in rst using this code: 

Dim flds As Integer 
flds = rst.Fields.Count 

For i = 0 to flds 
Me("txt" & i) = rst(i) 
Next i 

Just remember to keep the value of i straight. If your field 
names don't begin with the value 0, you will have to change the 
0 in the For statement to 1. In that case, you will need to 
compensate in flds by subtracting 1 (or the appropriate value) 
from the count. 

If you plan to use all the fields in rst, you can use the For...
Each loop instead.


*2. WHERE TO USE NOTHING                 
        
We often recommend that you set all your object and DAO (or ADO) 
variables to Nothing at the end of a procedure using the 
following syntax: 

ObjVariable = Nothing 

However, the best place to include this statement is when the 
function is done with the variable--not at the end of the 
procedure. There's no reason to wait until the end of the 
procedure to free up those resources. Instead, free up those 
resources as soon as the variables are no longer needed.


*3. DISPLAYING THE CURRENT PAGE NUMBER                 
        
When working in a lengthy document, you might need to know the 
current page number. You can click the thumb in the vertical 
scroll bar or you can use the following procedure: 

Private Sub CommandButton1_Click() 
Dim intPage As Integer 
intPage = Selection.Information(wdActiveEndAdjustedPageNumber) 
MsgBox "You are on page " & intPage 
End Sub 

The key to this procedure is the statement 

intPage = Selection.Information(wdActiveEndAdjustedPageNumber) 

The Information property has several constants that you can use 
to return information about the specified range. To learn more 
about this property and its many constants, search on Information 
in the Help system.


*4. DISABLING THE WINDOWS CLOSE BUTTON                 
        
All userforms sport the Windows Close button in the title bar--at 
the right edge. If you want to disable this button in Access, you 
can use an API call. If you're using Word or Excel, there's an 
easier way: Use the QueryClose event. Specifically, attach the 
following code to the userform's QueryClose event: 

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As 
Integer) 
If CloseMode <> vbFormCode Then Cancel = True 
End Sub 

If the CloseMode argument doesn't equal the vbFormCode constant, 
the event is cancelled. The userform will still display the 
button, but clicking it will have no effect. 

CloseMode will equal vbFormCode if the Unload event is invoked 
from code. Since clicking the Close button isn't code, the 
CloseMode <> vbFormCode expression is True. As a result, the 
statement sets the Cancel argument to True, which cancels 
the event.


*5. WINDOW SHORTCUTS                 
        
The Visual Basic Editor has three windows in addition to the work 
area--the Project window, the Properties window, and the 
Immediate window. All of these windows can be toggled on and off. 
If you need to access one quickly, just use one of the keystroke 
shortcuts listed below: 

Project Explorer--Press Ctrl-R 
Properties window--Press F4 
Immediate window--Press Ctrl-G


*6. THE ME IDENTIFIER                 
        
The official syntax for referencing a control is 

Forms!frmname!controlname 

However, using the Me identifier is quicker. The proper syntax 
for this identifier is 

Me!controlname 

The Me identifier assumes that you mean the active form, so it 
isn't always appropriate. But for those times when it is 
appropriate, you don't have to worry if you don't remember the 
name of the form, and you don't have to update the reference if 
you change the name of your form.


*7. REFERENCING OBJECTS                 
        
In our previous tip, we talked about using the Me identifier to 
save a few keystrokes when referencing forms and controls. There 
are three ways to reference an object using VBA code, and the 
circumstances will determine which method you use: 

Collection("objectname")--Often the preferred syntax if you know the 
object's name 
Collection(index)--Refers to an object by its position within the 
collection 
Collection!objectname--The traditional referencing method 

Let's look at a quick example now--let's reference a form named 
frmMyForm using all three methods. 

Forms("frmMyForm") 
Forms(frm) 
Forms(x) 
Forms!frmMyForm 

You may have noticed that we have four possibilities instead of 
three. That's because we've used the first syntax twice. The 
first refers to the form by name; the second refers to frmMyForm 
as the variable frm. Of course, you would need code to declare 
and set the variable. This makes the parenthetical reference more 
flexible than the other two. Furthermore, the index method is 
effective only if you know the object's position within the 
collection.


*8. USING INTELLISENSE                 
        
The Visual Basic Editor has several features that help you build 
your code, and almost all are available with a shortcut keystroke: 

List Properties/Methods--Press Ctrl-J 
When you enter code, you will often type the period character to 
separate objects from their properties and methods. When you type 
a period, the VB Editor will automatically open the List 
Properties/Methods window. This list will contain all the 
available properties and methods for the object in question. 
Simply double-click an item in the list to add it to your code. 
Or select the item and press Tab or the spacebar. If you press 
Enter, the VB Editor will insert the selected item and position 
the cursor on the next line. 

List Constants--Ctrl-Shift-J 
If you enter a function, the VB Editor will display the List 
Constants window. This window will display the appropriate 
constants for each argument. 

Parameter Info--Ctrl-Shift-I 
This window displays the different arguments that a function 
will require (or accept). The VB Editor displays this window 
whether you enter a built-in function or a user-defined function. 

Word Completion--Ctrl-Spacebar 
The VB Editor can even help you enter your code word for word. 
After entering the first few letters of a word, press 
Ctrl-Spacebar. The VB Editor will either fill in the word for 
you, or, if there's more than one possibility, the VB Editor will 
display a list of possible words from which you can choose.


*9. SETTING PROPERTIES                 
        
All VBA host applications allow you to set a control's properties 
with a property sheet or Properties window (the VBE). After 
locating the appropriate property field, you can type the setting. 
Some properties have predefined settings. When this is the case, 
you can choose the setting from a list of items. To do so, you 
simply click the field's drop-down list and choose an item. 

When a property has predefined settings, you can also toggle 
through those settings by clicking the property field label (the 
property name to the left of the property field). Simply click 
this label and VBA will toggle through the different property 
settings. Stop toggling when you reach the setting you want.


*10. A FEW DIR() TRICKS                 
        
Access users can use the Dir() function to check paths for valid 
files. This function uses the syntax 

Dir("path") 

where path is the file's entire path and filename, including the 
file's extension. If the file doesn't exist, the Dir() function 
returns a zero-length string (""). If the file does exist, the 
function returns the file's name. 

You might not realize that there's a little more to Dir() than 
just checking for a valid file. If you omit the path argument 
using the form 

Dir("") 

the function will return the name of the current database (MDB) 
file. The form 

Dir() 

will return the name of the first file in the current directory. 
Each time you repeat this function, it returns the next file in 
the current directory. This behavior can be a big help, or a 
nuisance, depending on what you're wanting to accomplish. 

The Dir() function is an Access function and not available in the 
other Office applications.
