Visual Basic for Applications Tips #34



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

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

Proudly presents:
Visual Basic for Applications

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


*1. UNDERSTANDING SPACES                 
        
The property sheet displays spaces between all the words in a 
property name. For instance, the Record Source property is 
displayed with a space between the words Record and Source. 
However, when referring to those properties using VBA, you omit 
the spaces. If you need to change a form's Record Source 
property, you would use a statement similar to 

Me.RecordSource = "table" 

or 

frm.RecordSource = "table" 

where frm is an object variable or the name of the form. (You 
can't change a form's Record Source property in run-time.)


*2. LEARNING COLOR VALUES                 
        
Ever wanted to use VBA to change the color of a control's 
background or the caption's font? If you know the value that 
represents the color you want, you have no problem. If you don't, 
you can always refer to the color palette in Form Design. Here's 
what you do. 

First, choose a control and open its property sheet. Next, select 
the appropriate color property--Back Color, Fore Color, and so on.
Open the color palette and click a color. Then, highlight the 
color value in the property field. 

At this point, you can use the cut and paste features to copy the 
color value to your code.


*3. TYPING A VARIANT                 
        
You probably know that the Variant data type holds any type of 
data. Fortunately, you don't have to struggle through tons of 
comparison code to determine the type of data stored in a 
Variant variable. All you need is the VarType function. This 
function takes the form 

VarType(variant) 

where variant represents your Variant variable. This function 
returns a value that identifies the type of data stored in 
variant. The different possibilities appear below, with the 
returned value first followed by the Variant data type. 

0--Empty (unitialized) 
1--Null 
2--Integer 
3--Long Integer 
4--Single 
5--Double 
6--Currency 
7--Date 
8--String 
9--Object 
10--Error value 
11--Boolean 
12--Variant (arrays) 
13--Data access object 
14--Decimal value 
17--Byte 
36--User Defined Type 
8192--Array


*4. SLOW VARIANTS                 
        
In our last tip, we showed you how to use the VarType() function 
to determine the type of data stored in a Variant variable. 
However, we discourage the use of the Variant data type unless 
it's absolutely necessary. Variants slow down your application 
because VBA must determine the type of data being stored. 
Variants also tend to encourage bad programming--it's easy to 
get a bit lazy if you know that you can always fall back on the 
Variant data type. We recommend that you explicitly declare all 
your data types.


*5. THE ME IDENTIFIER IS MORE EFFICIENT                 
        
When you reference a form or userform in an event procedure, we 
recommend that you use the Me identifier to refer to the form. 
For instance, if you wanted a command button's Click event to 
change the active form's color, you might use a procedure 
similar to this: 

Private Sub CommandButton1_Click() 
formname.BackColor = 0 
End Sub 

where formname represents the active form. 

However, the statement 

Me.BackColor = 0 

is more efficient. The Me identifier restricts Access's search 
for the form to the form that's running the code.


*6. INDEX BEFORE SQL                 
        
When running any kind of SQL statements, consider indexing any 
dependent fields before running the SQL statement if the table 
doesn't contain an index. For instance, if you're searching for 
a particular last name or a particular date, you should index 
the last name or date field before running the SQL search 
statement. Doing so should mean a faster search. To index a 
field, you can use the generic procedure: 

Function CreateInd(fldname As String, tblname As String) 
'create index 
Dim db As Database 
Set db = CurrentDb 
db.Execute "CREATE INDEX ind" & fldname & " ON " & tblname & "(" & 
fldname & ");" 
Set db = Nothing 
End Function 

Or, simply add the statement 

db.Execute "CREATE INDEX indexname ON tablename(fieldname); 

right before your SQL statement. Of course, you'll want to delete 
this index once you've completed your SQL task. You can use 
the procedure 

Function DeleteInd(fldName As String, tblname As String) 
'delete index 
Dim db As Database 
Set db = CurrentDb 
On Error Resume Next 
'doesn't matter if index doesn't exist 
db.Execute "DROP INDEX ind" & fldName & " ON " & tblname & ";" 
Set db = Nothing 
End Function 

or, as before, just add the statement 

db.Execute "DROP INDEX indexname fieldname ON tablename; 

after the SQL statement.


*7. BROWSING THE RIGHT PROJECT                 
        
Before you get to work with the Object Browser, make sure you're 
browsing the right project. Start by selecting the project in the 
Project Explorer and then launching the browser. Once the browser 
is running, you can check the project by clicking the 
Project/Library list box, which displays the available libraries 
for the chosen project.


*8. VALIDATING ENTRIES                 
        
As a rule, you don't let users enter just any old data. You make 
them enter appropriate data. For instance, if you were adding a 
series of values, you wouldn't let a user throw in a string of 
characters, such as "abc." If the user tried, the data entry 
control should refuse to accept the entry and display an 
explanation as to why the error occurred and how the user can 
resolve it. This process is known as validating the data. The 
following procedure warns users if they enter a string instead 
of a value in a text box named txtNumber: 

Private Sub txtNumber_AfterUpdate() 
If IsNumeric(Me!txtNumber.Value) Then 
Else 
    MsgBox "Please enter a valid number, vbOKOnly" 
End If 
End Sub 

Perhaps the biggest decision is deciding when to warn the user: 
as soon as the user enters one inappropriate character, when the 
user completes the current entry, or when the user attempts to 
move to a new record. The above procedure warns the user after 
completing the current entry and moving to the next control. 
(Depending on the data, you can often use the Change event.) If 
you want to check every character, try the KeyPress event. If 
you're working with an Access form and you don't want to disturb 
users until they've entered data in all the appropriate controls, 
you can use the form's Current or Deactivate event to validate 
all the data in the form. When working with userforms, use the 
Deactivate event.


*9. WORKING WITH EXTERNAL DATA                 
        
Many software applications incorporate the COM object model so 
you can use their objects in your applications. Here's how to 
see what foreign objects are available. Once you've installed 
the application, check your references by choosing Tools, 
References. Check the Available References list box for the 
application you want to borrow from. If it's listed, select it 
and click OK. Once you've referenced the application, open the 
Object Browser to see what objects are available to you.


*10. NEW RELEASE OF SCRIPT DEBUGGER                 
        
In our last tip, you learned how to install Scripting Runtime 
so you can work with files as objects. If you're also using 
Script Debugger, you should download the new release 1.0a, which 
repairs some known bugs. Even if you're running Windows NT or 
Windows 2000 with Internet Explorer 5, you should still download 
version 1.0a. You can download versions 1.0 and 1.0a from 

http://www.pcworld.com/r/tw/1%2C2061%2Ctw-vba11-23%2C00.html
