For best results: this site requires that cookies be enabled for proper operation - see Legal Page for more info

Select Any of These

Visual basic for applications

LAST UPDATED: 11 March 2009 14:37:20 -0600

CONCATENATING SPACES

We often concatenate variables using VBA. Most of the time we're working with SQL statements, but not always. An easy mistake when concatenating is to omit necessary spaces between the string text and the variables. For instance, if you wanted to concatenate a simple WHERE clause and a variable, you might try the syntax

"WHERE [fieldname] = " & variable

which would work just fine, because SQL anticipates the spaces around the equal sign. However, the syntax

DoCmd.RunSQL "SELECT * INTO" & variable & "FROM tablename WHERE tablename.fieldname" & strCriteria & ";"

doesn't work, although the statement appears to be correct. The problem is the missing space after the INTO clause and before the variable. There's also a missing space after the variable and before the FROM clause. The correct syntax would be

DoCmd.RunSQL "SELECT * INTO " & variable & " FROM tablename WHERE tablename.fieldname" & strCriteria & ";"

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

MORE ON CONCATENATING SPACES

In our last tip, we showed you a common mistake with concatenation--forgetting space characters. In our solution, we simply included the space characters as part of our text string. But that's certainly not the only way. You can concatenate a space character using the form

& " " &

but that's often unnecessary. For instance, the statement

DoCmd.RunSQL "SELECT * INTO " & variable & " FROM tablename WHERE tablename.fieldname" & strCriteria & ";"

is just as correct as

DoCmd.RunSQL "SELECT * INTO" & " " & variable & " " & "FROM tablename WHERE tablename.fieldname" & strCriteria & ";"

To make your statements more readable, you can define a constant to represent space characters using the statement

Const constSpace = " "

Including such a constant as follows

DoCmd.RunSQL "SELECT * INTO" & constSpace & variable & constSpace & "FROM tablename WHERE tablename.fieldname" & strCriteria & ";"

isn't necessary, as we've already shown. However, it might make your statements easier to decipher, but only if you use the constant consistently.

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

QUICKLY LEARNING THE VALUE OF A VARIABLE

When you run code for debugging purposes, you can quickly learn the value of a variable in the Debug or Immediate window. Simply position the cursor over any variable (that's been run), and VBA will display that variable's value in a ToolTip control. This feature can be extremely helpful when a variable changes its value or if a procedure is returning erroneous data. You can set a breakpoint right after the statement that contains the variable so you can check the variable's value. (A Watch expression works in a similar manner.)

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

VIEWING THE OBJECT HIERARCHY

The Object Browser is a great tool for learning about properties and objects. But the browser can't provide an overall view of your application's object model. The browser lists all the objects in the Classes list, but it doesn't display that list as a hierarchy.

Fortunately, you can find a diagram of the application's object model in the Help system. First, choose Contents And Index from the Help menu. Next, click the Contents tab, open the entry for Visual Basic reference, and double-click the Shortcut entry. Then, open the Visual Basic Reference entry and select Microsoft ApplicationName Objects.

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

A SHORTCUT FOR REFERRING TO CONTROLS

When referring to a control on a form or userform, you probably use the syntax

userform1.Controls("commandbutton1")

or the syntax

userform1!commandbutton1

In the first statement, we use the dot identifier (.) to separate the form object from the Controls collection and name the specific control. In the second statement, we use the bang identifier (!) to separate the specific control from the Forms collection. What you might not realize is that the statement

userform1.commandbutton1

is also acceptable. In this case, the dot identifier is still separating the Controls collection from a specific form. However, we don't have to specify the Controls collection because it is the default. If we omit it, VBA assumes we're referring to the Controls collection.

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

FINDING AN OBJECT'S DEFAULT PROPERTY

In our last tip, we showed you a shortcut for referencing objects. Specifically, you can omit the object's default reference. At this point, you may be wondering how you know what an object's default property is. There's an easy way to find out. Just open the Object Browser and select the object in the Classes list. The Members list will update accordingly, and the default property will display a small blue circle right above it.

Let's look at a quick example. If you're not in the VB Editor, press Alt-F11. Once you open the VB Editor, press F2 to launch the Object Browser. Next, select any object in the Classes list. For instance, if you're using Word, select the Cell object (not the Cells collection object). Then, scroll down the Members list to the Range property. See the little blue circle just above that property? That means Range is the Cell object's default property.

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

UNDERSTANDING A COMMON BUT OBSCURE MESSAGE

Have you ever seen error 91:

Object variable or With block variable not set?

If so, you know how frustrating it can be to figure it out. It's that With reference that's the problem. It makes you think there's something wrong with your With syntax. But the real culprit is probably just a missing Set statement.

You see, you must use the Set statement to define an object variable. If you forget, VBA will return error 91. For instance, the following code declares an object variable and then refers to it without defining that variable properly:

Dim frm As Form frm = Forms!MyForm DoCmd.OpenForm frm

Remember, a form is an object, so frm represents an object variable. To properly define that variable, you need to remember the Set statement as follows:

Dim frm As Form Set frm = Forms!MyForm DoCmd.OpenForm frm

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

RETURNING A BIMONTHLY INTEGER

Lots of us do business in bimonthly increments--which means we divide our year into two segments per month, for a total of 24 segments. You can easily track these segments using the function

Function bimonthly(datestring As Date) Dim d As Integer, m As Integer, s As Integer d = Format(datestring, "d") m = Format(datestring, "m") If d > 15 Then s = m * 2 Else s = m * 2 - 1 End If Debug.Print s End Function

where datestring is the date value in question. Of course, you'll want to replace the Debug.Print statement with a more appropriate task for returning the results.

The first two lines of this function assign the day and month values to the variables d and m. The If...Else statement then determines whether d is in the first or last half of the month. If d is in the last half, the first conditional action

s = m * 2

multiplies the month integer (1 through 12) by 2. If d is in the first half of the month, the Else action

s = m * 2 - 1

multiplies the month integer by 2 and then subtracts 1.

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

USING WORD FORMATS

Applying professional-looking formats to a document is fairly easy. The following code will open a new document in Word from most Office applications:

Dim objWord As New Word.Application objWord.Visible = True objWord.Documents.Add

At this point, you can work in Word manually. Or you can send text from your application to Word using the statement

objWord.Selection.TypeText "yourtext"

where yourtext represents the text you want to format. If you want to select the entire document, use the statement

objWord.Selection.WholeStory

To modify the font size, use the statement

objWord.Selection.Font.Size = sizeinteger

where sizeinteger is the font size you require.

Of course, for this code to work, you must reference the Word library. To do so, choose References from the Visual Basic menu and select Microsoft Word 8.0 Object Library (or 9.0 if you're using Office 2000). Once you do so, use the Object Browser to find the dozens of properties and methods you can use to alter your document. Be sure to close your function with the statement

ObjWord = Nothing

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

USING THE GLOSSARY

Do you have as much trouble finding information in the Help system as I do? Sometimes, no matter what I enter, I get nothing, but I know there's bound to be useful information--I'm just not asking the right questions and searching on the right words. If you have this problem too, try using the glossary. First, open the Help system and select the Contents tab. Enter the word

glossary

and then double-click glossary when the Help system displays it. At this point, Help displays a list of words in alphabetical order. Simply locate the most appropriate word for the subject you're searching and click it. You might have to click a few different words, but you're bound to find what you're looking for.

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

USING ADO'S FIND METHOD

The Find methods in DAO and ADO aren't the same--although their task is still the same. The Find method searches a recordset for a record that matches specific criteria. In doing so, the DAO Find method allows you to combine multiple conditions using the And operator. However, the ADO method doesn't. The ADO method uses the form

rst.Find fieldname operand value

where fieldname is the name of the field you're searching; operand is a logical operator, such as =; and value is the data you're trying to match. A simple ADO Find task might resemble the following:

rst.Find "LastName = Smith"

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

PARSING WITH FORMAT()

Parsing is one of those tasks that most developers hate--mostly because you can't do it with a generic round of code. Invariably, you end up writing unique code for every new parsing task. One thing you might consider is using Format() instead of string functions. Doing so isn't always appropriate, but when it is, it can be simpler to write and apply. For instance, let's suppose you have a nine-character string and you need to insert a space character (or any other character for that matter) after every third character. To do so, you might consider a typical parsing expression such as

results = Left$(9digitstring, 3) & " " & Mid$(9digitstring, 4, 3) & " "

But the Format() function is more efficient and easier to read:

results = Format$(9digitstring, "!@@@ @@@ @@@")

This alternative to parsing isn't always available; you can't always avoid string functions. But when Format() can do the trick, you might as well use it.

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

TO COMMENT OR NOT TO COMMENT

Comments are an important part of your code, and we've discussed whether to comment or not to comment in previous tips. What we haven't discussed, though, is what your comments should say. So, we took a short poll of a few developers and here's the gist of their responses:

Comments should convey the purpose of the code, not how it works. You can decipher the code and figure out how it works. However, you might not know why it exists--that's why you use comments. For instance, you don't need comments to break down an expression by its mathematical importance. Instead, the comment needs to tell you that the following code figures each customer's discount, or each salesperson's commission, or a student's final grade.

Comments shouldn't extend off the edge of the screen. Instead of one long line, enter several short lines, all of which are visible onscreen without scrolling.

Comments can and probably should include notes about problems that you encountered while testing and the solutions you tried. These notes may keep you from trying the same thing (and failing again) a year or so down the road.

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

RETURNING THE DEFAULT DIRECTORY

Need to know where Word will save your documents before you save them? If so, the Options object is just what you need. The following statement will return the destination (default) folder:

Options.DefaultFilePath(wdUserTemplatesPath)

To try it, press Alt-F11 to open the VB Editor and enter the statement in the Immediate window. Be sure to precede the statement with the ? character, so VBA will print the result.

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

WHAT'S THE PATH SEPARATOR?

Do you ever concatenate file and directory names to create a complete path? Most of us do at some point or another. There's one problem though--if you hardcode the path separator, you could run into trouble. The path separator is the character used to separate the directories and files. Most of the time that character is the backslash character (\).

If the system default isn't the backslash character and you hardcode the backslash character, your function will fail. Fortunately, if you're using Word, you can use the PathSeparator property in the form

Application.PathSeparator

It's that simple. Instead of hardcoding the separator character, simply concatenate the result of the PathSeparator property.

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

RETURNING A FORM'S NAME

In our last tip, we showed you how to use the Forms.Count statement to return the number of loaded forms (in the Immediate window). If you want to identify your forms, open the Immediate window and run the statement

? Forms(0).Name

This statement will return the name of the first form in the collection. If there's more than one form, replace the index value with the value 1 and rerun the statement. Continue in this manner until you've identified all the loaded forms.

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

UNREGISTERING A DLL

Earlier in the year we talked about Regsvr32.exe--a utility for registering DLLs. Unfortunately, sometimes the utility can't register the DLL because an existing version of the DLL is still registered. When you need to determine if a DLL is already registered and where, you can use Regedit.exe's Find feature. It's probably the most efficient method.

To find a registered DLL, choose Run from the Start menu and enter

regedit

Once Windows launches this utility, choose Find from the Edit menu and enter the name of the DLL you're trying to find. Then, click Find Next. If the DLL is registered, the Find feature will take you directly to the appropriate key.

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

INSERT WHITESPACE

You can use white space to make your code more readable. The most logical place for inserting white space (a blank line) is at the end of a task. Some developers enter blank lines only between sub and functions. Others enter a blank line before or after comments. Still others also separate each section of code with a blank line (by section, we mean task).

Where to insert blank lines is up to you, but doing so can improve the readability of your code. Eventually, you'll be able to find sections at a glance by skimming through the blank lines.

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

PROTECTING GLOBAL VARIABLES

It's easy to make a variable available to your entire procedure--simply declare the variable as a public variable. You should do so with caution. Why? Because not only is your variable available to all procedures, it's also updateable by all procedures. When one procedure updates a public variable, that variable is updated for all the procedures--whether or not you meant to do so. So, if your strategy's not solid, one procedure could affect another in a negative way.

An easy way to avoid this problem is to copy the public variable to a local variable (at the procedure level). Doing so will protect the variable--at the local level now--from further updates. Any updates will affect only the public variable, not the local copy.

This tip is for the odd times when you run into this situation--not for every public variable you write. Of course, the best solution is to avoid the public variable altogether if updating it in one procedure has a negative effect on another procedure.

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

NON-MICROSOFT VBA APPLICATIONS

VBA isn't just for Microsoft products anymore. Several applications now license VBA, so if you need more power than Office provides, you won't need to learn a new language if you use one of the following applications:

Visio--graphics package Corel WordPerfect Office 2000--word processing, spreadsheet, and database suite Corel Draw 9--graphics package AutoCAD--design application M.Y.O.B. Accounting--accounting package for small businesses Micrografx iGrafx series--business graphics OmniTrader--securities tracking and analysis

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

ONE CLICK OPENS THE VISUAL BASIC EDITOR

You probably press Alt-F11 to open the Visual Basic Editor, but there's an easier way. Most Office VBA applications offer the Visual Basic Editor button--you'll find it on the Visual Basic toolbar. If you use the editor a lot, you should consider copying this button to one or more of the standard toolbars. That way, you can open the editor at any time with one quick click.

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

COPYING TOOLBAR BUTTONS

In our last tip, we told you about the Visual Basic Editor button, a toolbar button that you can copy to the standard toolbars for quick, one-click access to the Visual Basic Editor. An easy way to copy any toolbar button is to hold down the Alt button and drag the button from one toolbar to another. That means you can display the Visual Basic toolbar, hold down the Alt key, drag the Visual Basic Editor button to the Standard toolbar, and then close the Visual Basic toolbar. It really is that easy!

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

STEALING FROM HELP

The Help system is full of code fragments that you can put to good use as is or with a few minor adjustments. So before you try to reinvent the wheel, do a quick search in the VBA Help system for any code that you can use as the basis for your current task. When you find something you can put to use, highlight those lines with your mouse, right-click the selection, and choose Copy (or press Ctrl-C). Then, switch to a module in the Visual Basic Editor and click the Paste button on the Standard toolbar, or press Ctrl-V.

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

INSTALLING ALL THE HELP FILES

We mentioned in our last tip that the Help system is full of code that you can cut and paste to your modules. However, unless you installed a complete set of VBA Help files, those Help files may not be available to you. If you're working with Office, the Setup program installs some but not all of the VBA Help files. If you'd like all of the Help files, run Setup again, but this time choose the Custom install. Then, open each application, select the Help And Sample Files option, and then click the Change option. In the resulting dialog box, select Online Help For Visual Basic and click OK.

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

INSTALLING OUTLOOK 97 HELP FILES

If you took our last tip to heart and installed the VBA Help files, and you're using Office 97, you didn't install Outlook's VBA Help files. If you need these files, you'll have to copy them from the Valupack\Morehelp folder on your Office CD. Specifically, you're looking for Vbaoutl8.hlp. Once you find it in the Valupack folder, be sure to copy it to the Office 97 folder.

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

IF FINDNEXT DOESN'T WORK

You can use the Recordset object's FindNext method to locate records that match criteria. Simply use the form

rst.FindNext criteria

where criteria is a SQL WHERE clause without the WHERE. For instance, you might use the statement

rst.FindNext "[LastName] = 'Smith'"

to find the next record with the string Smith in the LastName field.

However, occasionally these statements don't work, and you can waste a lot of time trying to figure out what's wrong with the criteria component. Most likely, there's nothing wrong with your criteria component. Rather, you've not defined the correct recordset type. You need to define the recordset as a dynaset using the form

db.OpenRecordset("tablename", dbOpenDynaset)

If you omit the type argument for your statement, Access opens a table-type recordset (dbOpenTable) unless you specify a linked table or a query. In those cases, the default is the dynaset-type. Before you waste a lot of time trying to restate your criteria expression, check the recordset's type.

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

THE ADO FIND METHOD

DAO's Find method can evaluate multiple criteria connected with the And operator, but the ADO Find method can't. When you need to search on multiple criteria in ADO, use the Filter property as shown here:

Function MultiSearch() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Set cnn = CurrentProject.Connection rst.ActiveConnection = cnn rst.Open "Products", cnn, adOpenKeyset, adLockOptimistic rst.Filter = "SupplierID = 15 AND CategoryID = 2" MsgBox rst!ProductID rst.Close End Function

DAO also has a Filter property, but it works differently from the ADO property. The DAO Filter property works only on subsequent recordsets; the ADO Filter property will filter the current recordset.

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

UNDERSTANDING NULLS

Don't let Null values intimidate you--they can be confusing, even for the experts. Fortunately, sticking to a few basic principles should help you out. A Null value indicates one of two conditions: - The value is missing or unknown. - The value doesn't apply.

When a value is missing or unknown, it means the information may exist, but we simply don't know it. That doesn't mean it does exist; we simply don't know. Occasionally, the information doesn't apply to a particular record and you must leave a field blank. Let's suppose you've got a table of phone numbers and two fields are blank. In one case, you know the person has a phone; you just haven't been able to acquire the number yet. Eventually, you may fill that field. In the second case, you know the person doesn't have a phone; that field will remain empty (unless the person gets a phone). In each case, the field returns a Null value.

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

MORE ON RECORDSETS

We've already discussed Recordset objects a bit this month. But there's one more detail you should know about. The RecordCount property requires a Long data type. If you assign anything else, your code will return an error. The problem is, this necessity simply isn't intuitive, but the explanation makes sense. An Integer data type handles values from -32,678 to 32,767, but a table can store many more records than 32,767.

By enforcing the Long data type, VBA ensures there will be no problems returning the correct record count.

---------------------------------------------- RUNNING AN EVENT WITH THE EVENT

When you're adding VBA code to an event, you'll probably want to test the code often. This can be a nuisance if you have to execute the event each time you want to test the subprocedure. Fortunately, you don't have to. When you're ready to test event code, simply press F5. This button has the same effect as choosing Go/Continue from the Run menu. You can also click the Go/Continue button on the Visual Basic toolbar.

---------------------------------------------- PROPERTY OR METHOD?

If you're fairly new to VBA, you may be a little confused about properties and methods, since the syntax for both is similar. By that, we mean you separate a property and a method from its object with the dot identifier (.). For instance, to set a property, you'd use the form

object.property

To execute a method, you'd use the form

object.method

They look pretty much the same, don't they? However, their functions are very different.

Properties represent an object's attributes, and methods represent that object's behavior. In other words, if you want a font to be blue, you'd set the object's Fore or Font property to a value that represents blue in the form

cmdButton.Fore = 16711680

Methods, on the other hand, generally execute some behavior or action. For instance, the Requery method updates the data underlying a specific form (or object). Requerying a form might require a statement similar to

frm.Requery

---------------------------------------------- FASTER SEARCHES

You probably know that you can combine the LIKE operator with the * character to find approximate matches. For instance, all of the following are legitimate search strings:

LIKE "rabbits" LIKE "rab*" LIKE "*abbits" LIKE "*abb*

and will match the string rabbits. However, LIKE "rabbits" and LIKE "rab*" are both faster than the other two examples. That's because Access sets a temporary index when you use the LIKE operator with an asterisk at the end of a string. When possible, use the asterisk character at the end of a LIKE search string.

---------------------------------------------- THE FASTEST COUNT

You probably know that you can count records in a recordset by using the RecordCount property. However, to do so, you must populate the entire recordset by executing the MoveLast method. If your recordset is large, this is inefficient and unnecessary, because the SQL Count function is faster. For instance, the procedure

Function GetCount() Dim db As Database, strSQL As String, rst As Recordset Set db = CurrentDb strSQL = "SELECT Count(*) FROM table3" Set rst = db.OpenRecordset(strSQL) Debug.Print rst(0) End Function

is faster than

Function GetCount() Dim db As Database, rst As Recordset Set db = CurrentDb Set rst = db.OpenRecordset("table", dbOpenDynaset) rst.MoveLast Debug.Print rst.RecordCount End Function

In a small database, you may not notice the difference. However, if you have thousands of records, you should definitely notice an improvement.

---------------------------------------------- THE TWO COUNTS

In our last tip, we showed you how to use the SQL Count(*) statement to return the number of records in a recordset quickly. If you're tempted to replace the * character with a field name because you think referencing a specific field will be faster, don't. You see, the Jet has special optimization rules for the * character, and it actually runs faster than specifying a field name. Specify a field name in a SQL Count function only when you need a count of that particular field.

---------------------------------------------- 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"

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.)

---------------------------------------------- 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.

---------------------------------------------- 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

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

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.

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

DOCKABLE WINDOWS

We've talked a bit about dockable toolbars, but some windows are also dockable. Simply double-click the window's toolbar to dock a floating window. For the most part, you'll use this feature in the Visual Basic Editor. (With objects like forms and reports, a double-click on the title bar maximizes or restores the object.) Not all windows are dockable, though. A quick glance is all you need to discern whether a window is dockable or not. If the title bar has only a Close button, the window isn't dockable. All dockable windows also have the Minimize and Maximize (or Restore) buttons.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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

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

EASY WAY TO GET CURRENT RECORD

When working with database tables, you may use the Move method to change the current record in a recordset. Specifically, this method has one argument, which allows you to specify the number of rows you want to move. For instance, to move forward two rows, you'd use the statement

rst.Move 2

Similarly, to move backward two rows, you'd use the statement

rst.Move -2

If you use 0 as the Move method's argument, VBA will retrieve the latest data from the current record. This behavior is helpful when you want to make sure that the current record is the most recent data.

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

ACTIVEX LEGALITIES

Often, you don't have to purchase an ActiveX control to have it. You see, any application that uses an ActiveX control registers that control on your system. Once the control is registered, you're free to use it, even though you didn't acquire it by purchasing the control outright. You'll find these additional controls on your Toolbox. Just click the More Controls tool to view a list of all the registered nonnative ActiveX controls residing on your system.

Although you can use these ActiveX controls in your own applications, you can't distribute them. If you decide you want to distribute one of these controls, you'll need to acquire a license to do so.

---------------------------------------------- ACTIVEX HELP

In our last tip, we told you that it's fine to use your registered ActiveX controls in your own applications, even if you didn't pay for it outright. (You did pay for these controls, when you purchased the hosting application.) Unfortunately, unless you have the documentation and Help files that you get when you actually purchase the control, taking advantage of the control may not be an easy task. You'll need to know about the control's properties and methods to use it.

If you're lucky, the Help files may be installed. To find out, insert the control in a form and then open that control's property sheet. Select any property and press F1. If the files are there, pressing F1 should access them and, of course, you're free to use them.

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

OUTLOOK DEVELOPMENT

Outlook is one of the more difficult Office products to program. I say that not because it's complicated, but because it is limited. Outlook 2000 forms finally support VBA, but automating Outlook can still be a challenge.

If you're looking for information on VBA and Outlook (or Exchange), one of the best sites we've come across is OutlookExchange.com, sponsored by ECMS and Micro Eye. The URL for this site is

http://www.outlookexchange.com/

You'll find plenty of code samples, tips, and documentation for the Outlook power user. If you're not a power user, take a look anyway, because there's a ton of documentation that just may help turn you into one.

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

THE VALUE OF AN INTRINSIC CONSTANT

VBA offers several intrinsic constants--predefined values that can't be changed. For instance, the DAO model offers several constants that represent the different record status conditions. In addition, all of the Office applications have native intrinsic constants.

Although we recommend that you use the constant instead of the value in your code, sometimes you need to know the value. To learn a constant's actual value, simply run it in the Immediate or Debug window. For instance, to learn the value for the dbRecordNew DAO constant, open the Immediate or Debug window and type the statement:

?dbRecordNew

and press Enter. The response is the value 2. You can use this technique with almost any constant, as long as you've referenced the appropriate library.

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

OFFICE ERROR MESSAGES

If you're using a Microsoft Office program as your application's host, you might be interested in an Excel workbook that lists all the Office error messages and their corresponding values. The name of this file is Errormsg.xls, and it is available for download from

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

Once you've downloaded the file--an EXE file that you should find in the

Program Files\ORKTools\Download\Documents\Cstalert

folder--run it. The EXE file will install several files, including Errormsg.xls. At that point, simply open Errormsg.xls in Excel. Each Office application has its own sheet--just click the corresponding tab to view the error messages for an application. Since you're working with an Excel workbook, you can easily add your own notes and information to each record for future reference.

---------------------------------------------- VIEWING MORE THAN YOU THOUGHT

Do you sometimes wish you had two monitors and two pairs of hands when working in a module window? If you need to view different parts of your code at the same time, simply split the worksheet into two panes. I find a horizontally split module particularly useful when I need to return to the beginning of a function or sub procedure to add a declaration. I just hop up to the top pane, add the declaration, and then hop back down to the bottom window and return to my code. This isn't the only use for a split module--it just happens to be my favorite.

If you want a horizontal split, drag down the split box (the small rectangle that rests on top of the vertical scroll bar). You'll take similar steps to create a vertical split, except drag the split box that's to the right of the horizontal scroll bar. Once you've split your module into two panes, you can scroll either pane to find any section of the same module.

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

DELETING A SPLIT SCREEN

In our last tip, we showed you how to split a module into two scrollable windowpanes. This tip is particularly useful when you're working with a large module. To return your view to just one pane, simply remove the split. When you're ready to return to a single screen, drag the split bar back to its originating split box. Perhaps the easiest way to eliminate a split module is simply to double-click the split bar.

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

PRINTING HELP TOPICS

You can easily print Help topics by clicking the Print icon in the Help window. However, as you know, most Help topics are spread across several pages with many subheadings. That means that you must access each one and print it to get a set of the entire Help topic. Right? Not anymore.

To print the entire topic, locate the appropriate book in the Contents tab. Click the Print button, and in the Print Topics dialog box, click Print The Selected Heading And All Subtopics. Then, click OK twice.

Furthermore, your host application will print the topic continuously, rather than printing each heading on a separate page.

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

NO SECRET CODE IN THOSE COMMENTS

All developers know that they're supposed to comment their code, and most do. However, some don't do a very good job. Too often developers want to take shortcuts with their code and write phrases and abbreviations. Do yourself a favor and use proper English in your comments. In addition, use whole sentences for your comments. As a final thought, avoid abbreviations unless they're universally known. If you end up being the application's maintenance developer, you'll be glad you were so thorough.

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

MINIMIZING OLE REFERENCES

VBA methods and properties use the OLE IDispatch interface, and that takes time. Consequently, minimizing the number of methods or properties can speed things up. Here's the general rule of thumb: Every time you use the dot identifier (.), VBA hits the OLE Idispatch interface. For example, this statement contains three dots:

Workbooks(1).Sheets(1).Range("A1").Value = "abc"

This statement will call on the interface three times, which of course takes more processing time than a statement with only one dot identifier.

Although it isn't always practical, keeping the dot identifiers to a minimum is one good way to speed up your code.

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

USING OBJECT VARIABLES

In our last tip, we recommended that you use the dot identifier sparingly because it slows down your code. Here's an option for reducing the number of dot identifiers in references. When you find yourself repeating the same object reference, use an object variable instead. For instance, we can optimize the statement we used in our last tip example:

Workbooks(1).Sheets(1).Range("A1").Value = "abc"

by setting the Workbook reference as follows:

Set sheet = Workbooks(1).Sheets(1) sheet.Range("A1").Value = "abc"

We've reduced each call by one dot, which doesn't seem like a big deal, but every dot helps.

---------------------------------------------- USING THE WITH STATEMENT

Our last two tips have been about reducing the number of dot identifiers in references. One more way you can eliminate references is to use the With statement. Let's rewrite our example using the With statement. It calls the Workbooks method and the Sheets method only once each:

With Workbooks(1).Sheets(1) .Range("A1").Value = "abc" .Range("A2").Value = "def" End With

Each statement in the non-With alternative:

Workbooks(1).Sheets(1).Range("A1").Value = "abc" Workbooks(1).Sheets(1).Range("A2").Value = "def"

calls both the Workbooks and the Sheets. This simple example cuts the calls in half.

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

A DAO DUH MOMENT

Normally, we try to reclaim resources as soon as possible by closing objects we're done with and setting object variables to Nothing. Have you ever tried to delete a temporary table or query that you know exists and received an error? This might happen for any number of reasons, but one you might not consider is the order of your statements. If you're working with an open recordset that's based on that temporary table or query, you can't delete the data source until you close the recordset. This situation is one case where you'll just have to wait until you close the recordset. Only then can you delete the temporary data source.

---------------------------------------------- NEED A CALCULATOR?

Normally, we like to share expressions and formulas that you can use in VBA. However, we've found a Web site that may make some of your work unnecessary. The Calculators On-Line Center at

http://www-sci.lib.uci.edu/HSG/RefCalculators.html

offers more than 5,000 Web calculators. You'll find calculators to handle all sorts of tasks, from a lye calculator (for making soap) to a capital gains calculator.

You can easily convert and incorporate these calculators into your VBA projects using Microsoft Web components.

---------------------------------------------- SPEAKING OF CALCULATIONS

Even though you can find Web sites with downloadable calculators for all kinds of tasks, most of the time you'll have to rely on your own expressions to get the results you need. Occasionally your expressions will return errors, and there are several reasons why VBA or your host application can't evaluate your expression. Review these possibilities before you start pulling out your hair: - Make sure you've included the appropriate number of parentheses (each opening parenthesis requires a closing parenthesis). - Make sure you've supplied all the required arguments for any functions or procedures. - Check all your object and variable references to make sure they're correct.

---------------------------------------------- TURNING OFF WARNINGS

When you run an Access action query, Access will display a message warning you that you are about to modify the existing data. If you don't want to display this warning, you can turn it off temporarily using the SetWarnings method. However, we recommend that you not add the SetWarnings statement until the procedure is complete, debugged, and running as expected. During the testing and debugging process, those messages can give clues as to whether the code is running as expected. The statement

DoCmd.SetWarnings False

turns off the display. Substitute the False value with True to turn on the display. If you're using Excel, use the Application object's DisplayAlerts method in the form:

Application.DisplayAlerts = False

to inhibit warnings. Then, use the statement

Application.DispayAlerts = True

to return to normal.

---------------------------------------------- PASS THE PASSWORD

If you're communicating with a password-protected workbook, don't worry. You can still get in, just as long as you know the password. Use the statement:

Workbooks("YourWorkbook.xls").Unprotect "YourPassword"

Then, use the statement:

Workbooks("YourWorkbook.xls").Worksheets("ProtectedSheet").Unprotect "YourPassword"

to unprotect worksheets within that workbook.

---------------------------------------------- THREE TYPES OF DIVISION

VBA supports three types of division: floating-point, integer, and modulus. A floating-point division is what you learned in grade school. You simply divide one number by another, which returns an integer and a decimal value, when appropriate. For instance, the following expression:

10 / 4

returns the value 2.5.

Integer division divides one number by another, but returns only the integer position of the result. When using integer division, use the backward slash instead of the forward slash in the form:

10 \ 4

which will return just the value 2.

The final method, modulus, divides one number by another, but returns only the remainder (or the decimal portion). It also requires the Mod operator. Our expression looks like:

10 Mod 4

and returns the value 5.

---------------------------------------------- VIEWING A FORM'S DESCRIPTION

Most VBA files are saved in binary format, which makes reading these files a bit difficult. Forms and projects, however, are saved as ASCII text and are easily readable in a text viewer. Simply open the .frm file in your word processor the same way you'd open any other file to display a text version of your form that contains:

The version number of the file format The form's description The form's attributes The form's code

The following is the form description of a form with one command button:

VERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm1 Caption = "UserForm1" ClientHeight = 3225 ClientLeft = 45 ClientTop = 330 ClientWidth = 4710 OleObjectBlob = "UserForm1.frx":0000 StartUpPosition = 1 'CenterOwner End Attribute VB_Name = "UserForm1" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = False Private Sub CommandButton1_Click() MsgBox "This is a test" End Sub

To save your form or userform as a separate file, select the form in the VB Editor and then choose Export File from the File menu.

---------------------------------------------- TYPE MISMATCH

Do you hate the Type Mismatch error as much as I do? Any error is annoying, but this one seems to crop up way too often. When this error appears, it generally means you've made one of the following mistakes: - You've attempted to define a variable or set a property with the wrong data type. For instance, you can't pass a string to a procedure that expects an integer. If you don't know what type of data you might have to accommodate, use the Variant data type. - You tried to pass an object to a procedure that's expecting a single property or value. - You used a module or project name where VBA expected an expression. For instance, you can't print an object with the Debug object.

These three mistakes are probably the most common reasons VBA returns the Type Mismatch error. Fortunately, all three errors are relatively easy to find and resolve.

---------------------------------------------- MOVING AROUND

In December, we showed you how to move backward through a recordset using the Move method in the form:

rst.Move -2

where rst is the name of a recordset. When you specify a negative value, VBA moves the current record position backward through the recordset.

What you might not realize is that this holds true even if your recordset is a forward-only type. The one stipulation is that the record you're trying to access must be in the current set of cached records. If the Move method tries to move to a position before the first cached record, VBA will return an error.

---------------------------------------------- A QUICK PRINT

You can quickly print a form and its code from the VB Editor. Simply select the form you want to print and then choose Print from the File menu on the VB Editor's Standard toolbar. The VB Editor will then display the Print dialog box, which will offer several options. You can print the form's image, its code, or both. You can even change the print quality (the default is High) or send the form to a print file.

---------------------------------------------- WORD BUG

The Is operator doesn't always work as expected in Word--specifically, it has trouble with the Range object. The Is operator compares two object variables. You use this operator in the form:

object1 IS object2

If the objects are the same, the statement is True. If they aren't the same, the statement is False. For instance, the following code compares the same worksheet to itself:

Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ActiveSheet Set wks2 = ActiveSheet MsgBox wks1 Is wks2

The message box will display the value True, since the two sheets are the same. However, when used with the Range object, the Is operator runs into trouble. The following code will incorrectly return the False value:

Dim rng1 As Range, rng2 As Range Set rng1 = ActiveSheet.Rows(2) Set rng2 = ActiveSheet.Rows(2) MsgBox rng1 Is rng2

There is an easy workaround. When working with the Range object, use the equal operator in the form:

MsgBox rng1 = rng2

---------------------------------------------- CHANGING A CONTROL'S NAME

It's a good idea to name a control as soon as you create it, if you plan to give it a name other than its default. If you decide to give the control a more descriptive name later, you'll have to update all the code that references that control. Most importantly, you'll need to update the control's event procedures. VBA won't update these for you, which explains why your control ceases to work if you change its name. Simply locate the original procedure in the module and update the name. For instance, if you rename a command button named CommandButton1 to cmdOpen, you should find the original procedure name:

CommandButton1_Click()

and rename it accordingly:

cmdOpen_Click()

---------------------------------------------- UNIVERSAL CURRENCY

When referring to currency in your code, avoid strings. For instance, it might be tempting to use the following statement:

strAmount = "$999.99"

If you use this statement, there's no way to control the way your application displays this currency amount, which means it has no international appeal. Even though your application may never see the international market, it's just not a good idea to hard code formats unless it's absolutely necessary. Fortunately, you can avoid the problem altogether if you use the CCur function in the form:

strAmount = CCur(amount)

The CCur function will provide internationally aware conversions from any other data type to Currency.

---------------------------------------------- HIDING FORMS

You can use the Hide method to close the current form so that you can return your document or activate another form. If the form is modal, the method must be in an event procedure belonging to the form. When this is the case, you can run the hide method by simply adding it to your code--you don't need to specify the form, since VBA knows you mean the current form. Most likely, you'll add the Hide method to an OK, Cancel, or Close command button.

---------------------------------------------- RESETTING ARRAY ELEMENTS

Generally, the first element in an array is 0. You can force the first element to be 1 by using the Option Base statement. Specifically, enter the statement:

Option Base 1

in the General section of your module. As a result, the elements in your array will begin with the value 1 instead of 0. For instance, the following procedure's first element will be 1:

Option Base 1

Function TestArray() Dim iMyArray(3) As Integer iMyArray(1) = "x" iMyArray(2) = "y" iMyArray(3) = "z" MsgBox iMyArray(1) End Function

---------------------------------------------- MORE ON RESETTING ARRAY ELEMENTS

In a previous tip, we showed you a quick way to force an array's elements to begin with the value 1 by entering the statement Option Base 1 in the module's General section. You can accomplish the same thing when you declare your array. For instance, the statement:

Dim iMyArray(3) As Integer

declares an integer array with three elements. By default, the value 0 will represent the first element, 1 will represent the second, and 2 will represent the third. If you want to force the first value to be something other than 0, simply say so in the declaration statement in the form:

Dim iMyArray(firstelement To lastelement) As Integer

For example, if you wanted to start with the value 1, you'd use the statement:

Dim iMyArray(1 To 3) As Integer

CORRECTION: MOB FORMING...stop SEND REINFORCEMENTS... stop You transform a few values and suddenly they want to lynch you. All kidding aside, we did make a mistake in our division tip awhile back. We said the result of the expression 10 Mod 4 was 5. Of course, that's incorrect. The Mod operator always returns the remainder, which in this expression is 2. We apologize for any inconvenience. Thank you to everyone who gently pointed out our error.

---------------------------------------------- HOW FAST DOES YOUR APPLICATION LOAD?

Let's face it--appearances count, and if your application appears to load fast, it will make a great first impression. One way to improve load time is to trim down your startup form. Here are a few guidelines you can follow to streamline your startup forms and help your application load quickly: - Use as little code in your startup form as possible. - Store all the code that your startup form requires in that form's module. (Don't call function procedures from a standard module.) - ActiveX controls require more time to load, so avoid using ActiveX controls in your startup form.

If you follow these simple guidelines, your startup form should load quickly. Of course, other objects may still be loading, but perception is the key in this discussion. The faster that first form loads, the faster the user perceives he or she is getting to work.

---------------------------------------------- FORM EVENTS

There are several form (userform) events, but some of them can be confusing. Take the Initialize and Activate events. It's difficult to know just which one to use. In fact, there's a big difference between the two events: - Initialize: Use this event when you want to run a task when VBA first loads the form. - Activate: You'll use this event when you want to run a task each time the form is displayed.

As you can see, choosing the right event can be critical to your form's success.

---------------------------------------------- REPLACE WITH NOTHING

Most of you probably know that you can use the Replace() function to replace one string with another. To do so, you use the function in the form:

Replace(string,findstring,replacementstring)

where string is the string you're searching, findstring is the character or substring you want to replace, and replacementstring is the string you mean to substitute for findstring.

What you might not realize is that you can use Replace() to delete a character or substring by specifying a zero-length string as replacementstring. For example, the following statement:

Replace("Now you see it now you don't", "it", "")

Returns the string "Now you see now you don't." We completely removed the pronoun "it."

Be careful with that zero-length string though. The zero-length string, "", doesn't equal the string " " (where there's a space between the two apostrophe characters). These two string characters aren't interchangeable.

---------------------------------------------- ANOTHER CONSTANT TIP

A fairly common sight when working with strings is the expression:

Chr(13) & Chr(10)

This combination concatenates a carriage return and a line feed. However, there's an intrinsic constant you should use instead--vbCrLf. We recommend you use constants whenever possible. They're more readable and easier to remember (most of the time) than the value they represent.

---------------------------------------------- KEEP AN EYE ON YOUR VARIABLES

If you have room, you should keep the Locals window open when you're debugging. This window displays all the variables in the current procedure. Specifically, the window displays the variable names, values, and data types. If your procedure updates a variable, the window will reflect that change. To open the Locals window, click the Locals Window button on the Debug toolbar. Or, you can choose Locals Window from the View menu.

---------------------------------------------- ADDING AN ITEM TO A LIST BOX

To fill a list or combo box, you use the AddItem method. Did you know you could add an item to a specific position within the list? You can if you include the method's index value in the form:

ListBox1.AddItem "One", 0

where 0 represents the first position in the list. For instance, the following procedure will display the items "Two" and "Three" in a list box named ListBox1.

Private Sub Form_Activate() ListBox1.AddItem "Two" ListBox1.AddItem "Three" End Sub

This second procedure will add the item "One" to the beginning of that same list when you click the form.

Private Sub CommandButton1() ListBox1.AddItem "One", 0 End Sub

(Just remember that the index values begin with 0 and not 1.)

---------------------------------------------- AN IMMEDIATE WINDOW SHORTCUT

When you want to run an expression, function, or variable in the Immediate window, you don't have to retype it. You can drag it from its module to the Immediate window and then run it. You may need to revamp it just a bit, but you'll save yourself a little time by not having to re-enter long expressions, etc. Dragging the statement also cuts down on typos.

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

AN ARRAY TIDBIT

An array can store any type of data--strings, dates, currency values, or numbers. However, an array can hold only one type of data. You can't specify one array element as an integer and another as a string. Fortunately, there's a way around this limitation. Simply define your array as a Variant data type.

As you probably know, a Variant can store any type of data. By declaring your array as a Variant, that array can store any type of data, which might come in handy when working with different fields in a database.

You should be aware that a Variant array will consume more memory than the other data types. In the right circumstances, the additional memory can be an acceptable trade for the added flexibility the Variant supplies.

---------------------------------------------- CONCATENATION OPERATORS

If you convert older applications, you may run into the plus sign (+) used as a concatenation character. Older versions of VBA (VB and BASIC) used the plus sign before the ampersand became the prevalent concatenation operator. VBA continues to support the plus sign for the sake of backward compatibility. If you still use the plus sign as your operator of choice, we recommend that you begin using the ampersand instead, even though VBA still supports the plus sign.

Each new version usually brings replacements, and as a rule, VBA will continue to support replaced keywords, operators, etc. for a while. Eventually, Microsoft usually drops the originals to make room for newer features. This means that some day, VBA may no longer support the plus sign as a concatenation operator. We recommend that you familiarize yourself with replacements when a new version is released and start using those replacements right away.

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

AVAILABLE FONT SIZES

The Font Size tool on the Formatting toolbar lists sizes 8 to 72 (in most host applications), but you're not limited to just those sizes. If you want a smaller or larger font, use the VBA FontSize property. As a rule, you can specify a font size of 1 to 127 using VBA. However, just because VBA will allow you to specify a font size doesn't mean your printer can print it. When working with unusual font sizes, you should test the point size you choose to make sure your printer and font can accommodate that size.

---------------------------------------------- LEARNING ABOUT OBJECTS

If you're new to VBA, you may find learning the various objects, and the methods and properties that go with each object, a bit overwhelming. Fortunately, a tool in most Office applications can help you become more familiar with the object model. That tool is the Macro Recorder, and you'll find it in Word and Excel. You see, when you write a macro in Word or Excel, you're really creating a VBA procedure.

To record a macro, first choose Tools, Macro, then select Record New Macro. Enter a name for the macro in the Macro Name control, or accept the default. Identify the document where you want to store the document in the Store Macro In drop-down list. If you don't, VBA will add the macro to your normal.dot template. Click OK, then perform the tasks you want the macro to repeat. Click Stop Recording in the Macro Recorder toolbar when you're done.

Now you need to review the code, so you can learn more about the objects and properties you just manipulated. To do so, first choose Tools, Macro, then select Macros (or press Alt-F8). Select the macro you just recorded in the Macros dialog box and click Edit to display the procedure in the VB Editor.

Although the recorder does have its limits, you can learn about the object model and the many properties and methods just by reviewing your macro code.

---------------------------------------------- A TIP FOR THE VBA BEGINNER

Almost everyone indents their code a bit, and if you want to know just when to add an indent or tab, several guidelines can help. If you don't indent as you enter the code, you can do so later. In fact, there are two ways to indent existing code. First, you can select the code you want to indent and choose Edit, Indent. The second way is simpler. Highlight the code you want to indent and press Tab.

If you get carried away and add a few too many indentations, you can outdent your code as easily as you indented it. After selecting the code you want to outdent, you have two options: You can choose Edit, Outdent, or you can press Shift-Tab.

---------------------------------------------- RESIZING AN ARRAY

VBA allows you to resize an array. By resize, we mean you can change the number of elements the array stores. For instance, the statement

Dim iMyArray(3) As Integer

declares an integer array with three elements. If you need to change the number of elements in an existing array, you should use the Redim statement in the form

Redim arrayname(x) As datatype

For instance, if we wanted to resize iMyArray to handle 10 elements, we'd use the statement

Redim iMyArray(10) As Integer

Be careful when you resize an array because the elements will lose their values.

---------------------------------------------- MORE ON RESIZING AN ARRAY

In our previous tip, we showed you how to use the Redim statement to resize an array. We also mentioned that when you resize an array, the elements lose their values. Fortunately, you can retain the element values using the Preserve keyword in the form

Redim Preserve arrayname(x) As datatype

If you use the Preserve keyword in your Redim statement, VBA will retain the value of each existing element in your array.

---------------------------------------------- A FASTER LOOP

All the loop statements need a way to know when to stop. The For...Next statement uses a value and Do...While uses a condition. You can specify a specific value, or you can use an expression. A good way to speed up your loop is to use variables instead of expressions as your loop's stop value. For instance, the following For loop uses the number of forms as its stop value:

For lCounter = 0 To Forms.Count - 1

Next lCounter

Unfortunately, VBA must evaluate the Count property before executing each loop, which will slow things down a bit.

A faster alternative is to assign the result of the Count property to a variable and then use the variable as the loop's stop value, as shown:

lCount = Forms.Count - 1 For lCounter = 0 To lCount ... Next lCounter

Now, VBA evaluates the Count property only once. The result is a faster loop. (If your loop deletes or adds forms, you may need to reevaluate the Count property with each loop.)

---------------------------------------------- A VBA ALTERNATIVE

VBA isn't always the most efficient solution. Occasionally, there are noncode solutions that are more efficient, but because we're accustomed to using code, we continue to use code. For instance, do you use command buttons to open other objects or files? If so, you should know that a hyperlink is often quicker and it's always easier.

You should consider a hyperlink solution when using a command button to open another form or report. Here's what you do: In the VB Editor, open the form that would normally contain the command button that you're eliminating. Choose Insert, Hyperlink. In the Insert Hyperlink dialog box, skip the first text box and enter the name of the object you want to open in the Named Location In File (Optional) control. Finally, click OK.

Unfortunately, you can't automatically insert a hyperlink in an Excel or Word userform.

---------------------------------------------- ADDING FRONTPAGE TO THE SUITE

FrontPage is new to the Office suite and is available with Office 2000 Premium. As you might expect, FrontPage supports VBA. However, FrontPage uses VBA differently than the other applications, as FrontPage (and Outlook) supports a single project. Other applications associate a project with each document. In other words, each Excel workbook can have a VBA project, but FrontPage allows only one VBA project--regardless of how many items are open.

If you'd like more information, make sure you've installed the FrontPage Visual Basic Reference Help files (vbafp4.chm and vbafplm4.chm). In addition, this Microsoft site

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

lists FrontPage resources that should help get you started in your joint venture between FrontPage and VBA.

---------------------------------------------- AN EFFICIENT WAY TO CLOSE A FORM

It's common practice to have the user confirm a close request. The following procedure is an efficient method that can eliminate the need for code in your Close command button:

Private Sub Form_Unload(Cancel As Integer) Cancel = MsgBox("Do you want to quit now?", vbOKCancel) = vbCancel End Sub

If you don't want to omit a Close command button, this procedure is still beneficial. If a user tries to close the form inappropriately (by bypassing the command button and clicking the Windows Close button), this procedure will force the user to confirm that choice. In addition, you can include code that performs any necessary close tasks that might otherwise be skipped without this failsafe.

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

AVOIDING A WORD PRINTING PROBLEM

It's easy to print part or all of a Word document with VBA code. For instance, the following code prints the current page:

ActiveDocument.PrintOut Range:= wdPrintCurrentPage ActiveDocument.Close

But what if the document doesn't print? Generally, that can happen when Word prints the document in the background, and VBA doesn't pause long enough for Word to spool the document. VBA closes the document before the printer knows what it's supposed to print.

There's an easy fix for this problem--simply set the PrintOut method's Background argument to True before you send the print parameter. For instance, the code below turns on the background printing, then tells VBA to print the current page before closing the document. As a result, Word and VBA both wait until the print job is complete before closing the document.

ActiveDocument.PrintOut Background:=True, Range:= wdPrintCurrentPage ActiveDocument.Close

---------------------------------------------- CHECKING BACKGROUND PRINTING

A few tips ago we discussed a problem that can occur when Word is printing in the background. You can check for this setting manually by choosing Tools, Options, clicking the Print tab, and reviewing the Background printing option in the Printing options section. You can also check the state of this option using the following VBA statement:

booBackground = Application.Options.PrintBackground

The function we gave you a few days ago will work regardless of your application's settings. However, at some point, you may need to check the user's current settings in this regard.

---------------------------------------------- CHECKING EXCEL

Using VBA, you can make sure a specific sheet exists in your worksheet. First, open the VB Editor by pressing Alt-F11. Then, add a new module by choosing Insert, Module. Next, add this procedure:

Public Function SheetExists(name As String) As Boolean SheetExists = False For Each sht In ThisWorkbook.Worksheets If sht.Name = name Then SheetExists = True End If Next sht End Function

When you call the function, you'll need to pass the name of the sheet you're looking for. If the For loop encounters that sheet, SheetExists will return a True value. If the function doesn't encounter the sheet, the function returns False.

Be careful: This function is case-sensitive. If you're looking for Sheet1 and you enter sheet1, the function will return False even if Sheet1 exists.

---------------------------------------------- CLOSING OPEN MODULES

In our previous tip, we suggested you avoid using the Compile All Modules command during the development stage. There's something else you should be aware of: Using the Compile All Modules command loads all your modules into memory, and VBA won't automatically unload them once it's run the called procedure. That means modules continue to consume memory unnecessarily. We suggest that when you use the Compile All Modules command, you also close your file afterward to close all those open modules and free up the memory they're consuming. (Not all VBA hosts offer a Compile All Modules command.)

---------------------------------------------- COMPARING FLOATING POINT VALUES

Everyone's struggled with floating point value because you can't use the = operator due to the precision of Single and Double variables. You can run into the problem anywhere. I recently had a problem with Double values in the results of an Access Make Table query. If you run into this problem, try using the Round function instead of comparing the actual values directly. The Round function takes the form

Round(value, places)

where value is the number you're rounding and places is the number of decimal places you're rounding the value to.

When comparing Single and Double variables, just be sure to use the same places argument.

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

COMPILING DURING DEVELOPMENT

While you're still in the development stage of an application, don't use the Compile All Modules command. The first time you make changes to any of your code, you will undo the compile, so a Compile All Modules command at this stage of the game is a waste of time. When you need to compile code during the development stage, use the Compile Loaded Modules option. This command compiles only the modules that are called by the open module. (Not all VBA hosts offer a Compile All Modules command.)

---------------------------------------------- DLL HELP

DLLs provide a tremendous amount of functionality, and they can also be a pain to work with--or without, depending on the situation. If you're tired of guesswork, read Rick Anderson's article online at

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

"The End of DLL Hell" was originally published in Microsoft's MSDN News newsletter, and it provides a thorough look at just what DLLs are, how they work together, and why they can cause so much trouble. In addition, the article reviews a few third-party products that help you troubleshoot DLL problems.

---------------------------------------------- DRIVES AND FOLDERS

It's easy to change the default drive and folder using VBA. To change the drive, use the ChDrive function in the form

ChDrive "x"

where x is the name of the drive you're switching to. If you want to change the current folder, use the ChDir function in the form

ChDir "folderpath"

where folderpath represents the complete path to the folder you want to make the default folder.

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

ECHO'S STATUS BAR MESSAGE

The Echo method freezes the screen so you can hide distracting tasks from the user. Did you know that you could also display a message in the status bar using the Echo statement? To do so, use the syntax

Application.Echo echoon[, statusbartext]

When echoon equals True, the host repaints the screen; when this argument is False, the host doesn't repaint the screen. The statusbartext argument is a string expression that the host displays in the status bar when the screen display is turned on or off. The statement

Application.Echo False, "Processing information, please wait."

would display the message

Processing information, please wait.

in the status bar.

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

FINDING AN EMPTY RECORDSET

You can run a record count of a recordset to see if it's empty, but there's an easier way. The following procedure returns True if the recordset is empty and False if it isn't:

Public Function RecordsetEmpty(rst As Recordset) As Boolean RecordsetEmpty = rst.BOF = True And rst.EOF = True End Function

You see, if the current position is both the beginning of the file and the end of the file, then there are no records. If either condition isn't true, RecordsetEmpty equals False.

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

FOR...NEXT IN VBSCRIPT

If you're proficient at VBA, you probably find yourself using VBScript on occasion. One thing you need to watch for is the For...Next counter variable. If you try to apply VBA rules, you may have trouble. You see, VBA allows you to include the counter variable in the Next statement. In fact, doing so is a good idea when you're working with a long or nested loop. However, VBScript's For...Next loop doesn't allow for a counter variable in the Next statement. If you try to include the counter variable, the function will return an error.

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

GRAMMAR CHECK

It's easy to start a grammar check in a Word document. Simply use the CheckGrammar method in the form

object.CheckGrammer

where object is the Document or Range object you want to check. You can also check a specific string using the syntax

Application.CheckGrammar(string)

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

GREAT POINTERS

You've probably noticed that the new versions of Microsoft applications make use of a solid triangle as a pointer. You can use these yourself since they're included in the Marlett font. Just specify one of the values 3 through 6 and apply the Marlett font to the value. This table identifies the pointer direction with a value.

3 Left pointer 4 Right pointer 5 Up pointer 6 Down pointer

Now, here's how to use them to add quality pointers to your controls: - Add a label control to your form (object). - Using the table, enter the appropriate value as the label's Caption property. - Specify Marlett as the label's Font property.

The Marlett font will display one of the four pointers, depending on the value you entered, instead of the value.

---------------------------------------------- LEADING ZEROS

There are several solutions for adding leading zeros to a value, and most of them are more convoluted than they need to be. One of the simplest methods for adding leading zeros isn't all that intuitive, but it's simple and it works. You see, we'll be using the Right function to add leading zeros. In a nutshell, you add the value to a 10-based number that's one place larger than the number of characters you need for each entry. For instance, if you want all values to have five characters, using leading zeros to fill in as needed, you'd use the number 100000--that's one place more than five. The function

Right(value + 100000, 5)

will return five characters from the result of adding value to 100000--including leading zeros. If value equals 30, the function will return 00030; a value of 4321 will return 04321, and so on.

---------------------------------------------- LEARNING NEW TRICKS

Each new version of VBA brings replacement actions and keywords. As a rule, when Microsoft updates an action or keyword, you can still use the previous version--at least for a while. However, we recommend that you make a habit of using the new replacements instead of their predecessors, even when those predecessors still work just fine.

Once Microsoft updates a keyword or action with a new one, your time using the original version is limited. After a release or two, Microsoft usually drops replaced keywords and actions to make room for new features. That means that someday in the future, you'll try to use the old statement or action and you'll receive an error, and the reason might not be obvious. We recommend that you familiarize yourself with replacements when a new version is released, and start using those replacements right away.

---------------------------------------------- LITERAL DATES

You don't have to use a powerful function to express a date. You can use a literal date string instead, and they're generally faster. Save the functions for those times when you really need all that power. If you just need a date, express it as a literal date--similar to the way you express a string. Simply enclose most any established date string in pound signs. For instance, all of the following strings represent valid dates, and VBA will recognize them as dates because of the delimiters:

#February 2, 2000# #2/2/00# #Feb 2, 2000# #02/02/2000# #2-Feb-00#

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

MORE ON CLOSING ALL MODULES

In our previous tip, we suggested you close your file after running the Compile All Modules command. The truth is, you'll probably want to close your file a couple of times during every work session (if your sessions are long and your file is large).

When you call a function, VBA opens the module that contains your code. Unfortunately, VBA doesn't offer a programmatic way to close that module once you've run the procedure. That means you eventually end up with lots of modules open--and consuming memory--long after you need them. To free up memory used by open modules, simply close and reopen your database. (You don't have to close the host application, just the current file.) Doing so will close all your modules and free up the memory they would otherwise be consuming.

Although this tip is very similar to the previous tip, it's separate from the Compile All Modules command. So, if your application doesn't offer this command, this particular tip may still be helpful.

---------------------------------------------- MORE ON EXCEL HEADERS AND FOOTERS

In our previous tip, we gave you a macro that prints a workbook's name in the file's footer. You might be wondering if there's a way to print the filename in one of the other footers or even a header. For instance, what if you want to print the name in the center of the footer or in the right portion of the header?

Fortunately, the solution is simple. First, let's review the original macro:

Sub NameInFooter() ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName End sub

Now, to change the location of the file's name, simply replace the LeftFooter property with one of the following properties: - CenterFooter - RightFooter - LeftHeader - CenterHeader - RightHeader

---------------------------------------------- NAME THAT ERROR

Working with VBA returns errors, no matter how good you are at VBA. The thing is, often VBA just displays an error code, which isn't much help considering there are thousands of those values in the Office structure. Fortunately, VBA's Err function will return the value of the most recent runtime error. The combined expression

Error(Err)

will return an explanation of the most recent error (Err). You can use this expression in your code to display a descriptive message when your code returns an error. Or, after churning up an error, open the Debug or Immediate window and type

?Error(Err)

to learn immediately what the error was.

---------------------------------------------- PLEASE OPEN THE DOOR

Would you like an application to open or close the CD-ROM door? Well, you're in luck, because there's an API that will do just that, and it's easy to use, unlike many APIs. First, add the following to a module's General Declarations section:

Private Declare Function mciSendString Lib "winmm.dll" Alias "mciSendStringA" (ByVal lpstrCommand As String, ByVal lpstrReturnString As String, ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long

Next, add the following two functions to your module:

Function OpenDoor() mciSendString "Set CDAudio Door Open Wait", 0&, 0&, 0& End Function

Function CloseDoor() mciSendString "Set CDAudio Door Closed Wait", 0&, 0&, 0& End Function

To see your door-controlling functions at work, enter the following statements (one at a time, of course) in the Immediate window:

?OpenDoor ?CloseDoor

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

PRINTING THE WORKBOOK PATH IN AN EXCEL HEADER

Some people find printing the workbook's name in a footer helpful. Doing so creates a quick reference for the file's location on your printout. There's no built-in feature for printing the file's name, but you can do so with a simple macro. To create this macro, open the VB Editor by clicking Alt-F11. Then, select the correct project in the Project window (which you'll find in the upper-left corner). Next, select Insert, Module. In that module, enter this procedure:

Sub NameInFooter() ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName End sub

To close the VB Editor, simply click the Close button at the top-right corner of your screen.

When you're ready to run the macro, choose Tools, Macro, then choose Macros in the resulting dialog box (or you can press Alt-F8). Next, select NameInFooter and click Run. You won't see the effects immediately. If you click the Print Preview button, you can see the filename in the footer.

---------------------------------------------- RANDOM VALUES

Do you ever need a random value? It's a simple matter with the Randomize and Rnd functions. The following procedure will return a random value that falls between the two arguments, upper and lower:

Function RandomNumber(upper As Long, lower As Long) As Long Randomize RandomNumber = Int((upper - lower + 1) * Rnd + lower) End Function

If you'd like to test this function, open the Visual Basic Editor (by pressing Alt-F11) in any host application. Then, open a blank module and enter the above procedure. Next, open the Immediate window and run the statement

?RandomNumber(10,5)

and VBA will return a random value from 5 through 10.

---------------------------------------------- REMEMBER TO UPDATE COMMENTS WHEN YOU CHANGE CODE

Only one thing is worse than no comments in your source code and that's incorrect comments. Here's what happens--the developer makes a change to the code but forgets to note those changes in the comments. So instead of deciphering code because there are no comments to point the way, you're stuck trying to match comments to code when there simply is no match. It might take a while to figure out that there's nothing wrong with your logic and that the problem is with the comments. So whenever you change your code, don't forget to update the comments appropriately.

---------------------------------------------- SELECT CASE WITHOUT CASE ELSE

The Select Case statement allows you to run an expression or condition by any number of possibilities and assign a unique action for each condition using the form

Select Case expression Case x x action Case y y action Case z z action Case Else else action End Select

The Case Else action acts as a net for expression when expression doesn't equal x, y, or z. The problem is, lots of folks don't bother to use it. After all, if you've provided a Case for all the possibilities, isn't it unnecessary? You might think so, but unexpected things do happen, and using the Case Else statement will help you catch unplanned errors. Simply add a Case Else action that alerts the user that expression doesn't fall within the expected parameters.

---------------------------------------------- SHARING FILES BETWEEN VB AND VBA

Sharing VB forms and modules with a VBA application and vice versa can be an efficient use of your objects and code. Sharing is easy to do. Basically, all you have to do is import the file.

Here's what to do if you're in VB: Export the form or module using the VBA application's export command. (In the VB Editor, right-click the file in the Project Explorer and select Export File from the context menu.) Then, choose Project, Add File. Next, locate the file you want to add in the Add File dialog box and click Open.

If you're in a VBA application: Open the VB Editor by pressing Alt-F11. Then, choose File, Import File. Locate the file you want to import in the File Import dialog box, then click Open.

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

SPEEDIER OLE

You've probably heard the terms early-binding and late-binding, but you may not know what they mean. In a nutshell, they refer to when you declare a specific object type when working with OLE objects. For instance, you can use early-binding to declare a Word object using this code:

Dim myWord as Word.Basic

Or you can make a generic declaration, as follows:

Dim myWord as Object

This is called late-binding and eventually you will link myWord to a specific object. At that point, myWord will inherit the linked object's attributes. But until then, it's a generic object.

Now, this tip is about speed, really, not early-binding and late-binding. The issue is, which is faster? Early-binding is faster because VBA checks your object reference at compile time. With late-binding, VBA checks the object each time you use it.

So why would anyone use late-binding? You'll need late-binding when you don't have access to an object's type library, or when you're working with a server that doesn't support early-binding.

---------------------------------------------- THE ADO MODEL

If you're still not familiar with ADO but you're ready to start, you might want to start with the ADO Object model. You can find a diagram of this model and an explanation of each object at

http://www.microsoft.com/data/ado/prodinfo/progmod.htm

After you've reviewed the model and read the documentation, pull down the Resources And Feedback menu and select ADO. You'll find resources for ADO available online listed.

---------------------------------------------- UNDERSTANDING RND

In our previous tip, we used the Rnd function to return random values. If you don't know what to expect, the Rnd function can return a few surprises. You see, the function's argument changes the seed value, which determines where VB begins to generate random values. The Rnd function accepts just one argument, in the form

Rnd(value)

Here are a few rules you should know about value if you plan to use Rnd: - If value is greater than zero (or not supplied), Rnd returns the next random number in the sequence. - If value is less than zero, Rnd returns the same number. - When value equals zero, Rnd returns the most recently generated number.

---------------------------------------------- UNDOING WORD COMMANDS

Most Windows applications have an Undo tool and command. Some will even support the keyboard shortcut Ctrl-Z, which will undo the last action. If you'd like to simulate the Undo command using VBA (Word), you'll need the Undo method. You can use this method in the form

object.Undo(x)

where object represents a document object and x is the number of actions to be undone.

This method will undo the last action or a sequence of actions (depending on the value of x). In addition, this method works only on those commands displayed in the Undo list.

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

USING SPLIT

If you're still struggling with string parsing, you can relax. VBA 6 introduces a new function--Split()--that makes parsing much easier. The Split() function returns a one-dimensional array containing a specified number of substrings. It uses the syntax

Split(expression[, delimiter[, count[, compare]]])

where expression is a string containing substrings and delimiters. The delimiter argument is optional; if you omit it, the function will use the space character as the delimiter. The count argument is optional and represents the number of substrings to be returned; the value -1 returns all substrings. Finally, the compare argument is also optional; it's a numeric value that determines the type of comparison when evaluating the substrings.

The following function is a simple example of how you can use this new function to make short work of your parsing tasks:

Function SplitString() Dim sSet As String, iCounter As Integer Dim arrSet() As String sSet = "We,parsed,this,string" arrSet = Split(sSet, ",") For iCounter = LBound(arrSet) To UBound(arrSet) MsgBox arrSet(iCounter) Next iCounter End Function

The Split() function parses the different substrings from the string "We,parsed,this,string"--using the comma character as the delimiter. Then, the MsgBox function displays each substring separately.

---------------------------------------------- WORD MACROS

Menu items are carried out by internal macros. If you want to change a menu's task somewhat--perhaps enhance it a bit--you can do so by replacing the internal macro with your own. How? Simply create a new macro and use the menu item's name.

The next question you're going to ask is how do you learn the menu item's macro name, right? Doing so is easy: - Press Ctrl-Alt-+ (the one on the number keypad) and the cursor will turn into a cloverleaf. - Click the menu item whose macro you want to replace, and Word will open the Customize Keyboard dialog box. - The item's macro is listed in the Commands control.

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

WORKBOOK NAMES

The Excel Workbook object has several properties that return the workbook's name, path, and fullname. For instance, the statement

workbook.FullName

returns the full pathname of workbook. (The full pathname includes the drive, folder(s), and filename.) If you want just the workbook's name, you'll use the Name property in the form

workbook.Name

If you want the path (without the drive), use the Path property in the form

workbook.Path

Until you save a workbook, the Path property returns an empty string ("").

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

WORKING IN THE VISUAL BASIC EDITOR

Sometimes when you're changing properties using VBA code, the setting isn't obvious. Fortunately, the Visual Basic Editor displays the active object's properties in the lower-left corner. When you need to know a setting, return to the form or report in Design view and apply that setting. Then, return to the Visual Basic Editor and view the modified object's properties in the properties window. This method is particularly useful when you don't know the correct syntax for the property.

Let's consider a simple example. Suppose you want to display a control's text in bold print. You need to know the integer value that represents the bold setting. (We recommend using intrinsic constants when available.) First, you click the View Microsoft Access button on the editor's Standard toolbar. Then, you change the control's Font Weight property to Bold in the property sheet. When you return to the Visual Basic Editor, review the properties in the property window for the FontWeight property setting, which should be 700. Now you know the integer value, and you can complete your code statement. Just don't forget to return to the form and change the object's property back to its original setting.

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

WORD TEMPLATES

Templates are common in Word and other Office applications because they reduce repetitive formatting tasks. Using VBA, you use the Open method to open a template for modification in the form

Documents.Open templatename

If you want to base a new document on a template, don't use the Open method. Instead, use the Add method in the form

Documents.Add newdocumentname

If you try to create a new document using the Open method, you'll simply make changes to the .dot file instead of creating a new .doc file.

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

WORD STARTUP

Most custom applications take control of the application's environment from the get-go. This means that your application may implement features that are needed throughout the entire work session. To control Word programmatically from the time you launch the application, you have two choices:

Add a public procedure to any code module and name that procedure AutoExec. Name a code module AutoExec and add a public procedure named Main. You can also control the way your application closes by adding an AutoExit procedure or module to the project.

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

WORD HEADERFOOTER OBJECT

Word's HeadFooter object is a bit odd in that it has no methods. However, this object has several properties:

Application: Returns an Application object. Creator: Returns a 32-bit integer that indicates the application in which the specified object was created. Exists: Determines whether a first-page or odd-page header or footer exists. (The primary header and footer always exist.) Index: A read-only property that returns a number that indicates the position of an item in a collection. IsHeader: A read-only property that returns True if the specified HeaderFooter object is a header; returns False when the object isn't a header. LinkToPrevious: Links the specified header or footer to the corresponding header or footer in the previous section. PageNumbers: Returns a PageNumbers collection, which holds all of the PageNumber objects for the header or footer. Parent: Returns the Parent object. Range: Returns a representative Range object for the portion of a document that's contained in the specified header or footer. Shapes: Returns a representative Shapes collection that represents all the Shape objects in the specified header or footer. WORD AUTO DOCUMENTS

In our previous tip, we explained how to control the Word environment from the time you first launch it. Similarly, you can also add this type of control to a document. Technically, you'll be using WordBasic features instead of VBA. To control a document or a set of documents from the time the document(s) is opened:

Add a public procedure named AutoOpen to the document. If your document opens a new document based on a template, name the procedure AutoNew. Name a module AutoOpen or AutoNew and add a public procedure named Main. VBA does offer its own solution. Simply use the template's or document's Open event. If you want to add code to the document's closing sequence, create an AutoClose module or procedure, or add code to the document's Close event.

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

WHITE PAPERS FOR DATA ENGINE

If you're an Access 2000 user, you probably know that this latest version offers more choices than ever for working in a client/server environment. To help you choose between the new Data Engine (MSDE), the Jet, or Access 2000 features, read "The Data Engine Links to the Access 2000 Data Engine Options" white paper. You'll find this up-to-date information at

http://www.microsoft.com/office/access/MSDtaEng.htm

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

WHERE'S MIN AND MAX

Typically, you can't use the Min() and Max() functions in VBA. If you're working in Access, you can use the Dmax() and Dmin() functions, but even they aren't adequate in every case. If you need to learn the minimum or maximum value, you can try a Boolean data type in the form

Dim boo As Boolean boo = a < b

If boo equals True, you know that a is less than b. If boo equals False, you know a is greater than b.

---By Susan Harkins

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

WHEN YOU FORGET SET

In our previous tip, we discussed an easy way to help you remember to use the Set statement when defining object variables--use the obj prefix when naming all object variables. If you do forget the Set statement, VBA will return a rather unhelpful error message: Invalid use of property. Anytime you see this property and there's an equal sign in the offending line of code, you've probably run into a forgotten Set statement.

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

WHEN TO ELSE OR ELSEIF

The block If allows any number of conditional checks. First, the If statement itself specifies a condition. If that condition isn't met, control will pass to an ElseIf or Else clause. (You can also omit both and simply end the statement with an End If clause.) How do you know which to use? If you want to specify additional conditions, use ElseIf in the form

If condition1 Then ... ElseIf condition2 Then ... ElseIf condition3 Then ... End If

You'll use the Else clause when you want to catch what falls through the cracks. That's because the Else clause doesn't accept a condition. For instance, we could add an Else clause to our example:

If condition1 Then ... ElseIf condition2 Then ... ElseIf condition3 Then ... Else ... End If

Any value or expression not caught by condition1, condition2, or condition3 will be handled by the Else statement.

The thing to remember is that you can't add an ElseIf clause after the Else clause. Every If statement can have numerous ElseIf clauses, but only one Else.

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

WHEN TO BEEP

You're probably familiar with the Beep() function, but a lot of developers ignore it. The reasons are varied, but one good reason is that this function depends on the computer's hardware, so you can't control the tone or volume. That means beeps can be inaudible or so low they might as well be inaudible. If you do choose to include beeps in your application, we have a word of advice. Use them sparingly--an application full of beeps will only annoy your users. However, one spot where a beep may be appropriate is at the end of a long process, which may go unattended by your users because of its length. A short beep can alert them that the process is complete so they can return to their system and continue their work.

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

WHEN ISDATE DOESN'T WORK

The IsDate() function validates an entry to confirm that the entry is a valid date. But there's one spot where it just won't work--in a text box control's Change event. That's because the Change event checks every keystroke. So, as you begin to enter a date, the IsDate() function won't recognize the entry as a valid date, because technically it isn't. An entry isn't a valid date until it's complete.

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

WHAT DAY IS IT

VBA is very flexible when it comes to working with dates--it even supplies three functions you can use to determine the current date or time. All three functions--listed below--rely on your system's clock:

Date: Returns only the date. Time: Returns only the current time. Now: Returns the current date and time as a value. The integer portion represents the date; the decimal portion represents the time. When you know you'll be working with only the date or the time, it's much easier to work with the Date and Time functions, respectively. Leave Now for those tasks that require both the date and time.

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

WATCH OUT FOR NULLS

In general, you'll want to avoid using a Null value in your expressions. That's because a Null in any mathematical expression causes the entire expression to evaluate to Null. For instance, the simple expression

2 + Null

will return Null, whereas the expression

0 + 2

will return 2.

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

WARNING ABOUT WITH

The With statement has been around for a while now, and most of us are taking full advantage of its functionality. We do have one word of warning when including the With statement in a procedure. Don't include code that branches from the With block. Likewise, don't use code that flows into the With block. Doing either will cause VBA to execute a With statement without the End statement, or vice versa, and result in an error.

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

VBSCRIPT VERSUS VBA

Occasionally, you'll run into an application that supports VBScript instead of VBA. Or, like Outlook, the application may support portions of both. When this happens, you'll need to know how the two languages differ. The following list should help:

Perhaps the biggest difference is that VBScript doesn't support Outlook intrinsic constants. You'll have to declare them yourself or use the actual values. The Application object is intrinsic to VBScript (behind an Outlook form). The Item object is intrinsic to Outlook forms. VBScript supports only Variant data types. Outlook forms don't include a form object.

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

VBSCRIPT DATA TYPE

You probably know that VBScript only supports the Variant data type. If you use data type prefixes to denote a variable's data type, you might be affixing the prefix var to all your VBScript variables. However, we recommend that you not do so. Instead, use the appropriate prefix for the type of data the variable will be storing or expecting. That way, you can be reminded at a quick glance whether a variable contains (or should contain) numeric or string data.

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

VBSCRIPT AND OUTLOOK

Outlook forms support VBScript. However, they don't support the intrinsic Outlook constants. You can still use them, though, by explicitly declaring those constants. Or if you plan to use the constant only once, simply use the constant's literal value.

If you'd like to learn all the intrinsic constants and their literal values, use the Object Browser.

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

USING SET

When you declare an object variable, you must also use the Set

statement to define that variable. Failing to do so will produce an

error when you compile your code. Since you don't have to use Set with

all variables, it can be easy to forget about Set when working with

object variables. One way to help you remember is to use the obj

prefix when you name all your object variables. The prefix will help

you remember to use the Set statement when you define the variable.

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

USING CONTINUATION CHARACTERS

VBA allows you to enter long lines of code in a module, but they're hard to read because you can't see the entire line. When this is the case, you can use the continuation character (the underscore character) to break a line into multiple lines. For instance, you might consider breaking the statement

MsgBox "This is a really long line, perhaps we should break it.", vbOKOnly, "Here's the title."

between the arguments as shown here:

MsgBox "This is a really long line, perhaps we should break it.", _ vbOKOnly, "Here's the title."

You can break a single line many times, but you can't break a line in the middle of a string. We've indented the second line to make it more readable, but doing so isn't necessary.

---By Susan Harkins

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

USING ARRAY AS A METHOD

You're probably accustomed to using the Array() function to create an array. Did you realize that you could also use the VBA object model's Array method? The proper syntax is

VBA.Array(x, x, x)

Array(x, x, x)

The following procedure is a simple example:

Function ArrayMethod() Dim varArrayList As Variant varArrayList = VBA.Array("one", "two", "three") MsgBox = varArrayList(2) End Function

You could also use this syntax:

Function ArrayMethod() Dim varArrayList As Variant varArrayList = Array("one", "two", "three") MsgBox = varArrayList(2) End Function

Both procedures do the same thing--they both return the text "two" in a message box.

Just one thing to remember when using this method: Option Base has no effect. The first element is always 0.

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

TRIMMING DATA ENTRY--PART 1 OF 2

Many people trim imported data because it often drags along unwanted space characters. Before committing data to a field in your table, run it by one of the three trimming functions:

Trim: Removes both leading and trailing spaces. RTrim: Removes trailing spaces. LTrim: Removes leading spaces.

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

TRIMMING DATA ENTRY--PART 2 OF 2

In our previous tip, we recommended you trim imported data using Trim, Rtrim, or LTrim before committing your data to a field. Imported data isn't the only data that might contain unnecessary space characters. Sometimes people enter space characters during the data entry process without even realizing it. For instance, a data entry operator might rest his or her thumb a bit too heavily on the Spacebar. For this reason, you might want to trim string entries by attaching Trim, RTrim, or LTrim to each control's update event.

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

THE YEAR IN QUESTION

The Year() function returns the year portion of a date as a Variant or Integer data type. What you might not realize is that this function will accept two-digit year components. For instance, the function

Year(#11-27-00#)

will return the value 2000.

There are a few rules to remember when passing two-digit year components:

All two-digit year values equal to or greater than 30 are considered part of the 20th century--for example, 30 returns 1930, 57 returns 1957, and 99 returns 1999. All two-digit year values less than 30 are considered part of the 21st century--for example, 00 returns 2000, 05 returns 2005, and 29 returns 1029.

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

THE VISIO OBJECT MODEL

If you work with Visio, you're probably interested in learning more about the application's object model. You can view a graphical representation of the Visio object model at

http://msdn.microsoft.com/voices/news/object.asp

From this page, you can download the graphic file. If you'd rather view the model on screen, you can click the graphic for a larger view.

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

THE FRIEND KEYWORD

A relatively new addition to VBA (with version 5.0) is the Friend keyword. You'll use this keyword to determine a procedure's scope. Generally, sub functions are limited to the class module that contains them. Using the Friend keyword in the form

Friend Sub name

exposes name to other modules within the same project. However, you can't access the procedure from outside the project (as you can when using the Public keyword).

You can use Friend with properties, sub, and function procedures.

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

THE FOR...EACH STATEMENT IN WORD

I make good use of the For...Each statement. In fact, I probably use it more than any other statement. For instance, if I want to find a particular word in a Microsoft Word document and apply some kind of formatting to it, I use the For...Each statement. The procedure below is a good example; it applies bold formatting to every occurrence of the word "vital" in the current document.

For Each Wrd In Selection.Words If Trim(Wrd) = "vital" Or Trim(Wrd) = "Vital" Then Wrd.Bold = True Next Wrd

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

THE ADD METHOD

In our previous tip, we introduced you to the Collection object. We mentioned that you could identify a member of the collection by its index or key value. Today, we'll define those two terms. Access assigns an index value when you add the item to the collection. Specifically, Access assigns the value of 0 to the first item, 1 to the second item, 2 to the third item, and so on.

If you'd like a bit more flexibility, you can assign a key value when you add the item in the form

col.Add "itemname", "key"

where col is the collection, itemname is the name of the item you're adding to col, and key represents the value you're assigning as the key value. For instance, let's suppose you're adding names to a collection, named--appropriately enough--names. In this case, you might use a statement similar to

names.Add "Joe Smith", "js"

where js is the key value for the member named "Joe Smith".

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

TESTING ERR

Many developers use the Err object to return the current error. In fact, using Err is the same as using the statement

Err.Number

since the Number property is the Err object's default.

When working with OLE servers, remember that they often return negative error values, so any error-handling code should accommodate negative error values. (Actually, these values are unsigned longs, but VBA doesn't support this data type.)

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

SWITCH EFFICIENCY--PART 1 OF 2

VBA's Switch() function evaluates a list of expression and returns an associated value upon finding the first expression that evaluates to True. This function works with two sets of elements--the expressions and a corresponding value for each expression in the form

Switch(expression1, value1, expression2, value2, expression3, value3)

When expression1 evaluates to True, the function returns value1; if expression2 equals True and expression1 is False, the function returns value2; and so on. If two expressions return True, Switch() considers the first in the list.

With functions of this sort, you can sometimes improve performance a bit by putting the expressions that are most likely to be selected at the beginning of the expression list or function. However, that's not true with Switch(). All the expressions are evaluated, so you'll gain nothing by positioning the expressions in any special order.

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

SWITCH EFFICIENCY--PART 2 OF 2

In our previous tip, we talked about the Switch() function. Because the Switch() function evaluates all the expressions listed, you increase the risk of raising a run-time error. An error can occur even if one of the expressions evaluates to True and the error will take precedence over the True expression. For this reason, you need to consider error handling an important part of any procedure that contains the Switch() function.

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

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.

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

STRING ERRORS

The Asc(), AscB(), and AscW() functions all return an integer that represents the character code of the first character of the passed string. Asc() handles normal text, AscB() accepts Byte data, and AscW() works with Unicode systems.

All three require a string argument. When you're passing a string variable to one of these functions, we recommend that you check the variable first. Why? You'll do so to ensure that the variable contains data. If you pass a zero-length string, VBA will return an error. The easiest way to check a string variable is to use the Len() function in the form

If Len(string) > 0 Then .... Code that uses the string Else MsgBox "You can't pass a zero-length string to the Asc() function." End If

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

STRING COMPARISON

If a module is dedicated to string comparison and you want all comparisons not to consider letter case, you can avoid a lot of special coding with one simple statement. Include the Option Compare Text statement in the module's General Declarations section.

If, on the other hand, you do want your comparison code to consider letter case, you should use the Option Compare Binary statement. (This is the default if there's no Option Compare statement.)

Keep two things in mind when working with the Option Compare Text statement. First, it doesn't work in VBScript. Second, the results of your comparison may depend heavily on your Windows language settings.

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

SQL JOIN TABLE SURPRISE

Be careful about specifying the exact table in a SQL Inner Join statement. For instance, the statement

strSQL = "SELECT * INTO tblJoined FROM tblProductMaster INNER JOIN tblOrderHistory ON tblProductMaster.PartNumber = tblOrderHistory.PartNumber WHERE tblOrderHistory.Gen = 'Gen1';"

specifies all the fields by using the * character. You might think the * character refers to just tblProductMaster--the table we specify in the From clause. However, that's not quite true because we're working with a joined recordset. Instead, SQL includes all the fields from both joined tables, tblProductMaster and tblOrderHistory.

When working with an Inner Join, be sure to indicate the specific table in the SELECT clause in the form

strSQL = "SELECT tblProductMaster.* INTO tblJoined FROM tblProductMaster INNER JOIN tblOrderHistory ON tblProductMaster.PartNumber = tblOrderHistory.PartNumber WHERE tblOrderHistory.Gen= 'Gen1';"

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

SQL DELETE WARNING

The SQL DELETE clause sometimes refers to a single column, but don't assume that field's value is the only one being deleted. The SQL DELETE clause deletes an entire record; it makes no difference if the clause references only a single field. For instance, the SQL statements

DELETE FROM tblMyStuff WHERE Flag = True;

and

DELETE FROM tblMyStuff.OrderID WHERE Flag = True;

do the same thing--they both delete all the records in tblMyStuff where the Flag field contains the True value. The second statement won't delete just the value in the OrderID but the entire record.

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

SQL DELETE

Most SQL statements allow you to work with specific fields, but not the DELETE statement. You must specify all the records. This mistake is easy to make when you're working with a JOIN. For instance, you might use the statement

DELETE Orders.ID FROM Orders INNER JOIN Temp ON Orders.ID = OrderDetail.ID;

to delete all the records in Orders where the ID value equals the ID value in OrderDetail. It won't work, however, because SQL won't delete just one field. Instead, use the statement

DELETE Orders.* FROM Orders INNER JOIN Temp ON Orders.ID = OrderDetail.ID;

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

SPECIFIC OBJECT DECLARATIONS

When declaring an object variable, it's usually best to be as specific as possible. That's because the more specific you are, the more flexible your code is. Most objects have unique properties and methods, and unless you're specific about the object type, you may not be able to use those properties and methods in your code.

Anytime you can't be specific because you don't know the object type you may be working with, you can use the Object data type.

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

SORTING WITH ADO

It's easy to sort records using SQL--simply tack on an ORDER BY clause. It's equally as easy to sort records with ADO using the Sort method in the form

rst.Sort "fieldname"

where rst represents the recordset object and fieldname is the name of the field by which you want to sort the records. It couldn't be simpler.

The Sort method defaults to an ascending sort; the DESC keyword will sort in descending order. For instance, the statement

rst.Sort "LastName DESC"

will sort your records by the contents of the LastName field in descending order. In addition, you can sort on more than one field. This last statement will sort the records by the LastName field and then the FirstName field:

rst.Sort "LastName, FirstName"

Just be sure to separate the field names with a comma character.

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

SORTING EXCEL WORKSHEETS

There's no built-in feature that alphabetically sorts Excel worksheets. But the following macro will:

Sub AlphaSort() Dim iCount As Integer Application.ScreenUpdating = False iCount = Sheets.Count For i = 1 To iCount - 1 For j = i + 1 To iCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i) End If Next j Next i End Sub

To create this macro, open the VB Editor by pressing Alt-F11. Choose Insert, Module and enter the above macro. Return to your worksheet and run the macro to sort the worksheets in the current workbook. To run the macro, press Alt-F8, select AlphaSort, and click Run.

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

SIZING UTILITY

Screen resolution is a common problem with working with Access because everyone has different preferences. Winsize is a free utility available from PC Magazine that displays the pixel dimensions of dialog boxes and pop-up menus. For more information, visit

http://www.zdnet.com/pcmag/stories/solutions/0,8224,2470110,00.html

As always, never download anything from the Internet unless you have updated virus software.

SETTING WATCH EXPRESSION SCOPE

An often overlooked debugging feature is the ability to set watch expressions. You may want to watch a global variable. Or you may want to keep tabs on a local variable while its procedure is running. To add a watch expression, pull down the Debug menu and select Add Watch. At this point, you'll have to make some decisions:

You can specify the subroutine in which you want to watch the variable. You can specify a break when the value changes or is True, which is helpful if you're watching a flag and want to trace how the value changes.

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

SETTING PASSWORDS IN WORD DOCUMENTS

Documents often contain sensitive or otherwise confidential data. Fortunately, it's easy to password-protect a document. If your application is automated, you may want to include this possibility. You can do so by prompting users to enter a password during the save process and then passing their response to the SaveAs method's Password argument. This argument is a string that Word will require to open the document in the future.

Documents that are public but permanent don't require a password to open, but they should require a password to modify. When this is the case, solicit a string and pass it to the SaveAs method's WritePassword argument.

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

SETTING BREAKPOINTS

In our previous tip, we showed you how to set a breakpoint and reset a variable. To set a breakpoint, we told you to position the cursor within the line of code where you want VBA to stop executing and then click the Toggle Breakpoints button on the Debug toolbar. But there's a simpler way. Click the gray margin to the left of the appropriate statement, and the VB Editor will automatically insert a breakpoint. To remove the breakpoint, click the existing breakpoint icon (the little dot in the margin).

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

SEND REPORT TO PRINTER

When opening an Access report, you probably use the DoCmd object's OpenReport method in the form

DoCmd.OpenReport reportname, view, filter, wherecondition

where reportname is the valid name of a report in the current database; view is one of three intrinsic constants--acNormal, acDesign, or acPreview; filter identifies a valid query; and wherecondition is a SQL WHERE clause. All arguments determine how and what is displayed in the resulting report.

By default, VBA assumes the constant acNormal if you omit this argument, which means Access prints the report automatically. If you want only to view the report, use the constant acPreview.

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

SEEK IN RECORDSETS

If you're looping through a recordset, you should probably use the Find method and avoid Seek. That's because the Seek method always starts its search at the beginning of the recordset. Consequently, you'll always end up at the first matching record and you'll never reach any matching records beyond that first record.

The only way Seek will work in such an arrangement is if you change the search criteria before executing each Seek method.

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

SAVING A LITTLE CODE

Calling one procedure from another is fairly common. If the called procedure performs a task, you probably need to know whether that task was completed successfully. When this is the case, you might have the called procedure return a Boolean value--True if the task is completed, False if not. (False is the Boolean data type default.)

If you assign the Boolean results of a called procedure to a variable and then test that variable for its value, you can save yourself a bit of code. For instance, the following code assigns the results of DoIt() to a variable named booDone:

Dim booDone As Boolean

... booDone = DoIt If booDone Then ... Else ... End if

You can just as easily omit the booDone variable as follows:

If DoIt Then ... Else ... End If

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

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

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

RETURNING THE USER'S NAME

A frequent reader question is how to return the user's network login identification (user's name). There's a quick and easy API that will handle this task for you. Simply enter the declaration statement in a module's General Declarations section. Then, add GetName() to the module and call it as required:

Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" _ (ByVal lpName As String, ByVal lpUserName As String, _ lpnLength As Long) As Long

Function Getname() As String Dim strUserName As String strUserName = Space(255) WNetGetUser "", strUserName, 255 Getname = Trim(strUserName) End Function

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

RETURN THE NUMBER OF UPDATED RECORDS

When you run a fixed action query, Access tells you how many records you're about to change. You can do the same thing programmatically using ADO's Execute method. This method runs action queries and will also tell you the number of records affected by the action. Simply pass a Long variable as the method's second parameter in the form

cnn.Execute "SQL statement", lngRecords

For instance, the following code runs an Update query on a table named Orders and returns the number of records updated via the variable lngRecords:

Dim cnn As Connection Dim rs As New Recordset Dim lngRecords As Long Set cnn = CurrentProject.Connection cnn.Execute "UPDATE Orders SET Total = Total * .10", lngRecords MsgBox lngRecords

The variable lngRecords stores the number of updated records.

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

RETURN THE CURRENT FOLDER

Did you realize that you could use VBA to grab the current folder in Windows Explorer? Simply refer to the ActiveExplorer method of the Application object in the form

obj.ActiveExplorer.CurrentFolder

where obj refers to the Application object. For instance, in Outlook you might use the following code:

Dim obj As Application Dim objCurrentFolder As MAPI Folder Set obj = CreateObject("Outlook.Application") Set objCurrentFolder = obj.ActiveExplorer.CurrentFolder

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

RESERVED WORDS

If you create a new class, you'll probably create a few methods to complete custom tasks. Just be careful when you name these methods. Remember, you can't use words reserved by VBA. For instance, if your method opens a file, you may be tempted to name that method Open. However, if you do so, VBA will report an error when you try to declare the method. That's because Open is a reserved word. If this happens, simply change the method's name to resolve the conflict.

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

REMOVING BREAKPOINTS

Most developers use Debug.Print to display information during the debugging process. For the most part, you'll want to delete all these statements before distributing the application. A simple Find task in all your modules is a quick and easy way to find all of your Debug statements. If you miss a few, no harm is done, as the user will probably never even know they're there.

Breakpoints are helpful too, since they automatically suspend the code at a particular point so you can review all the variables, and so on, before continuing. Unfortunately, it's easy to forget about a breakpoint, and they aren't as benign as Debug statements. A forgotten breakpoint can stop your application, and the user will have no idea what's wrong.

An easy way to remember to remove all your breakpoints is to add a Debug statement after each breakpoint. Use the Debug.Print statement to print a message that the stop is intentional and your message is simply a reminder to remove the breakpoint at the appropriate time.

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

RELEASING ARRAYS

Once you're done with an array, you should use the Erase statement to release the memory being used by that array. The proper syntax for this statement is

Erase array

where array is the name of the array. This function will empty a dynamic array. However, it will merely reset a fixed array's elements as follows:

Numeric 0

Variable-length string zero-length string ("")

Fixed-length string 0

Boolean False

Variant Empty

User-defined type Each member is treated separately

Object Nothing If you use the Erase statement, you must redimension an array using the ReDim statement before using it again.

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

REFERENCING OBJECTS

In our previous tip, we showed you how to assign a key value to members of a collection as you add the item to the collection using the Add method. Besides using that key value, there are two other ways to refer to a collection member. First, you can use the index value in the form

rst.Fields.Item(0)

where rst represents a recordset that contains a collection of fields (Fields). You can shorten this syntax since Item is the default property:

rst.Fields(0)

You can also refer to a member by its name, in the form

rst.Fields("nameoffield")

where nameoffield represents the field you're referring to.

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

REDUCE CODING TIME

If your application has a built-in macro recorder, you can save a lot of time writing code. Many coded tasks can be created quickly, with little effort, using the macro recorder. You may have to tweak the code a bit, but why reinvent the wheel? Start with the macro recorder, change what you must, and get to the next task just that much quicker.

Some of the Microsoft Office applications have a macro recorder. Select Tools, Macro, Record New Macro. If the recorder is not available, you won't find these items on the Tools menu.

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

QUICKLY POPULATING DYNAMIC ARRAYS

One way to define a dynamic array's elements is to refer to each index value using the form

MyArray(0) = 1 MyArray(1) = 10 MyArray(2) = 100

and so on. A quicker method is to use the Array() function. After declaring a Variant variable, assign the array elements using the syntax

Dim MyArray() As Variant MyArray = Array(1,10,100)

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

QUICK PROPERTY SETTING

Generally, we manipulate an object's properties using the syntax

object.property = newsetting

If you need to know the current property setting, you probably leave the module and return to the object to view its properties sheet. Instead, run a statement in the Debug window in the form

? object.property

The Debug window will respond by displaying the current property setting for object. Just make sure object references the object properly. For instance, if you're checking a control on a form, be sure to identify the form and the control in the form

? Forms!formname!controlname.property

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

QUICK CONSTANTS

A few tips ago, we mentioned that you could use the Object Browser to learn the different intrinsic constants and their literal values. If you've already added a constant to your code and you'd like to learn its literal value, there's an easier way than launching the Object Browser. Right-click the constant and choose Quick Info from the context menu. VBA will display the constant and its literal value.

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

PREVENTING DATE COMPONENT ERRORS

The Hour(), Minute(), and Second() functions return the hour, minute, and second component, respectively. All three functions require one argument--a valid date. For instance, the statement

Hour(#13:24:22#)

returns the integer 13. However, if the argument isn't a valid time, all of these functions return a

Type mismatch

error. Therefore, we suggest you use the IsDate() function to check the argument before using it in one of these time functions.

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

PREVENTING AN ABS() ERROR

The Abs() function returns the absolute value of a value. For instance, the simple expression

absval = Abs(-3)

would return the value 3 (positive 3).

The Abs() function works only with numeric values. For this reason, you should check values you plan to use in an Abs() function using the IsNumeric() function. For instance, the following code executes the Abs() function only if the value being passed is a numeric value:

If IsNumeric(val) Then Abs(val) Else .... End If

If you omit this check and somehow the Abs() function encounters a non-numeric value, VBA will return an error.

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

POPULATING LIST BOX WITH QUERY RESULTS

Richard L. sent in this useful DAO procedure for populating a list or combo box with the results of a query. Simply attach the procedure to the appropriate event--the control's GotFocus event or the form's Current event:

Dim rs As Recordset Dim strSQL As String Dim strResult As String Set rs = CurrentDb.openrecordset("queryname", dbOpenDYNASET) strResult = "" If rs.RecordCount <> 0 Then Do Until rs.EOF If strResult <> "" Then strResult = strResult & ", " strResult = strResult & rs("fieldname") rs.MoveNext Loop End If rs.Close Set rs = Nothing Me!List0.RowSourceType = "Value List" Me!List0.RowSource = strResult

Be sure to update queryname and fieldname accordingly, where queryname identifies the query results you're after and fieldname is the field that contains the data you want to list in your control.

If you're using ADO, try the following code:

Dim cnn As Connection Dim rs As New Recordset Dim strSQL As String Dim strResult As String Set cnn = CurrentProject.Connection rs.Open "queryname", cnn, adopendynaset, adLockOptimistic strResult = "" If rs.RecordCount <> 0 Then Do Until rs.EOF If strResult <> "" Then strResult = strResult & ", " strResult = strResult & rs("fieldname") rs.MoveNext Loop End If rs.Close Set rs = Nothing Me!List0.RowSourceType = "Value List" Me!List0.RowSource = strResult

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

PASSING PARAMETERS

There's always a bit of confusion about passing parameters. When you pass a parameter to a function, VBA passes that variable by reference. That means if you change the value of the parameter within the subroutine or function, VBA will change the value of the original variable as well. This may or may not be what you want. If you want to prevent this behavior, pass the parameter as ByVal, as shown here:

Private Sub Test(ByVal var As String)

You don't have to use ByVal when you call the function.

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

OUTLOOK SHORTCUT

When referring to a control in a form, you usually reference the form or use the Me identifier--even when the code is running in the form's module. For instance, if you want to reference a control named txtNames in a form named frmEmployees, you'd use the form

frmEmployees!txtNames

Me!txtNames

If you're adding code to an Outlook form, you can omit the form reference and simply refer to the control, as long as you're working in that form's module. If the code is in a separate module or behind another form, you must include the form's name in the reference.

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

OUTLOOK DATES

VBA Date/Time fields or variables can handle a large range of date values. However, Outlook forms are a little more limited. A date on an Outlook form must fall between April 1, 1601, and August 31, 4500 (inclusive of both dates). Fortunately, this isn't a problem for most users. Unfortunately, we don't know of an easy workaround.

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

OPENING A WORD DOCUMENT

You probably know that you can launch Word from inside another Office application. There's a lot of theory to the necessary code--accessing the right objects, and so on. If you'd just like to do the work and you're not interested in the science behind the task, you can use the following procedure to open a Word document:

Sub OpenDocument() Dim wrd As Object Set wrd = GetObject(, "Word.Application") wrd.Visible = True Documents.Open "documents complete path" End Sub

This procedure will work with just a wee bit of tweaking--simply specify your document's complete pathname in the Documents.Open statement.

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

OPEN WORKBOOKS FROM THE VBE

When working in an Excel module, you might need temporary access to an unopened workbook. You could stop what you're doing, return to the workbook environment, and use the File, Open command. Or you can enter the following command in the Immediate window:

Workbooks.Open "workbookname"

where workbookname identifies the workbook by name to open the workbook. Be sure to specify the complete path and the filename's extension.

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

ONE DIFFERENCE BETWEEN COLLECTIONS AND CONTAINERS

If you want to loop through all the forms in your database, be sure to specify the Forms Container and not the Forms Collection. Collections contain only open forms. This is true of all Collections and Containers. The following procedure will loop through all the forms in the current database, open that form, change a property, and then close the form. You'll want to use this procedure when you want to change the same property in all your forms or anytime you want access to all the forms in your application, not just the opened forms.

Function ChangeProperty() Dim db As Database, cnt As Container, doc As Document Dim frm As Form Set db = CurrentDb Set cnt = db.Containers!Forms For Each doc In cnt.Documents DoCmd.OpenForm doc.Name, acDesign ...property changing code... DoCmd.Close acForm, doc.Name, acSaveYes Next doc End Function

Our next tip will offer an updated version of this procedure for Office 2000 users. (This procedure will work in Access 2000.)

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

OBJECT RETURNING FUNCTIONS

Functions often return a value, and when this is the case, we generally declare the function's data type. For instance, if a function will return an integer, you might use the function statement

Private Function ReturnValue() As Integer

You might not realize that a function can also return an object reference. When a function does so, you must remember to use the Set statement to assign the function's reference. The following procedure example illustrates what we mean:

Private Function GetObject() As SomeObjectType Dim obj As SomeObjectType ....<code that defines object> Set GetObject = obj Set obj = Nothing End Function

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

NUMERIC NULLS

Technically, there's no such thing as a numeric null--if there's no value, the value equals 0. However, you probably encounter the annoying message

Invalid Use of Null

enough to know there really is a numeric null! More often than not, code returns this message because the function or expression you've run doesn't equal anything. For instance, the Dlookup() function will return a Null value if it doesn't encounter the field entry you've specified in the function's where argument. And that's where the error comes in--because VBA won't allow you to assign a Null value to a numeric variable.

To avoid this error, wrap your functions and expressions that might return a Null value in an IsNull() function. The IsNull() function will return True when the function or expression returns a Null value instead of returning that annoying error message.

---By Susan Harkins

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

NULL IS ALWAYS FALSE

The only way to check for a Null value is with IsNull. For instance, you might try the statement

If var = Null Then

to determine whether var is Null. However, this statement always evaluates to False, even if var is Null. You see, any expression that contains a Null is always Null and all Nulls are False. You can check this behavior using the following procedure:

Function NullCheck() Dim var As Variant var = Null If var = Null Then MsgBox True Else MsgBox False End If End Function

Even though we've assigned a Null value to the variable var, the If condition always evaluates to False.

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

NULL CONSTANT

Last month we told you that the Variant data type is the only one to accept Null values. Even though you can assign a Null value to a Variant data type, you can't test for it using a simple statement such as

If var = Null

Instead, you must use the VarType() function and its vbNull constant to determine whether a variable contains Null by using the syntax

If VarType(var) = vbNull

The vbNull constant equals the value 1 (or "1" if the variable is a string), so you can't use this constant to assign a Null value.

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

NAME THAT ERROR

Have you ever received this error:

Expected end of statement

It's probably the one I receive the most and I know exactly what it means, which makes the error easy to find and correct. Unfortunately, this error message is a bit obscure, so unless you know its meaning you may have trouble finding the cause of the error.

Almost always, this error points to a missing delimiter. For instance, the statement

strFullName = FirstName & " " & LastName & "

will produce this error because there's an extra (or missing) double-quotation mark--the one at the end of the statement. To correct this statement, we can delete the extra quotation mark:

strFullName = FirstName & " " & LastName

or we can add another string and another quotation mark:

strFullName = FirstName & " " & LastName & " "

This last example is kind of silly since there's no good reason to add an extraneous space to the end of a person's name; in fact, doing so might cause trouble later on.

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

MULTIPLE CASE EXPRESSIONS

The Select Case statement accepts multiple conditions in a single Case statement. However, VBA evaluates them separately. In other words, you should think of multiple conditions in terms of the OR operator and not the AND operator. For example, the following Case statement may seem valid:

Case Is > 10, Is < 20

You might think this statement will return True if a value is greater than 10 but less than 20. In other words, you're looking for values between 10 and 20. However, that's not how it works. Once a condition is met, the others are ignored. Therefore, if the value you're comparing is greater than 10, this statement returns True, even when the value is greater than 20. In addition, any value that's not greater than 10 will certainly be less than 20, and any value that's not less than 20 will be greater than 10. Therefore, the condition always returns True.

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

MSDN FAVORITES

As useful as the MSDN is, finding what you need can take several tries. The keywords that seem logical to me seem never to work. If you suffer with the same problem, we can't offer you any help in finding the Help information you need--the first time. But we can make it easier for you to access that data once you've finally located it the first time. After you find a topic you know you'll be reviewing again, simply create a bookmark to it. Then, the next time you want to see that topic, select it from the Favorites tab.

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

MOVING RECORDSETS

Working with recordsets in Excel isn't your normal run-of-the-mill VBA task. If you need this functionality, check out the code on Dev Ashish's site at

http://home.att.net/~dashish/modules/mdl0035.htm

You'll find three examples of using Excel's CopyFromRecordset method to copy records using Automation.

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

MORE ON DATES

Wrapping all your dates in a Format() function that displays specific regional settings may seem redundant, since date formats default to the system's regional settings. Unfortunately, most people can change the Windows Regional settings to anything they want, and doing so can have some unexpected and unwanted repercussions for your date data. That's why we suggest going that extra mile where dates are concerned.

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

MORE INFORMATION FOR ERROR MESSAGES

Tired of puzzling error messages? If so, download and run Extralert.exe to install Microsoft Office 2000 Customizable Alerts. This program will offer to find more information. Visit

http://officeupdate.microsoft.com/2000/downloadDetails/alerts.htm

to download the program. Once you've installed it, Access 2000 will (sometimes) display a Web Help button along with the normal error message. Clicking the button will take you to updated information on Microsoft's site. (Of course, you'll need an Internet connection for this feature to work.)

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

MORE EOF() TRIVIA

When working with the EOF() function, you're used to thinking in terms of True and False. What you might not realize is that this function isn't a true Boolean data type. In fact, this function returns an Integer that's either -1 (True) or 0 (False). However, this doesn't pose a problem or limit your code in any way, because VBA recognizes 0 and -1 as Boolean values even when you're working with non-Boolean data types.

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

MODULE KEYBOARD SHORTCUT

When working in a VBA module, there are several keyboard shortcuts you can use to reposition the cursor. One you might not know about is Ctrl-Home. This keystroke combination will reposition the cursor at the very top of the module window. In other words, this keyboard shortcut positions the cursor at the very first position in the module--before any other characters. In most modules, this shortcut will position the cursor before the Option Explicit statement in the module's General Declarations section.

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

MODELESS PROPERTIES WINDOW

When working in the Visual Basic Editor or Design View, you probably open the Properties sheet to modify object properties. Once you modify a property, do you close it and then reopen it again if you need it? If so, you don't need to. The Properties window (sheet) is modeless, which means you can access other objects while it's open. In fact, leaving the window open is very convenient, because the window displays the properties of the selected object. That means you can jump from one object to another without having to update the Properties window.

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

MISSING OBJECT TYPES

If you've converted any DAO code from Access 97 to Access 2000, you

may have run into trouble with the Database and Recordset object

types, because they've both been replaced in ADO. That means the

simple statements

Dim dbs As Database

Dim rst As Recordset

would both return errors if you try to run them in Access 2000.

Instead, you'll want to use the ADODB object in the form

Dim dbs As ADODB.Connection

Dim rst As New ADODB.Recordset

Now, if you want to keep your DAO code or use DAO code in your Access

2000 modules, you should reference a DAO library. In the VBE, choose

Tools, References; select a Microsoft DAO Object Library; and then

click OK. Once you reference the correct library, the module will work

with the DAO object references Database and Recordset.

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

MISSED EVENTS

Changing a control's value programmatically is a fairly common and useful task. But there's something you should know if you choose this route. Changing a control's value triggers both the BeforeUpdate and AfterUpdate events. However, when you change a control's value programmatically, the control doesn't trigger either of these events. So you'll want to be careful about placing code in these events when the control might be updated by VBA.

The form's BeforeUpdate and AfterUpdate events are still triggered if you alter a control programmatically.

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

MAIL MERGE SOURCE

Microsoft Word handles mail-merge tasks with ease. However, you might like to know what's going on behind the scenes. The Fieldnames Collection tracks the field names in your mail merge. You'll find this collection in the MailMerge DataSource object. You'll use the ActiveDocument object in the form

ActiveDocument.MailMerge.DataSource.FieldNames

If you want to loop through all the fields, you can use a simple For...Each statement similar to the following:

Dim fld as Variant For Each fld In ActiveDocument.MailMerge.DataSource.FieldNames Msgbox fld Next fld

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

LET'S PLAY HIDE THE CURSOR

There's an API that will hide the cursor for you, but you must use it carefully because it could be difficult to turn it back on. The function itself is simple. Just add the following statement to the General Declarations section of your module:

Private Declare Function ShowCursor Lib "user32" (ByVal bShow As Long) As Long

Then, to turn off the cursor, simply use the call

ShowCursor 0

To redisplay the cursor, use the call

ShowCursor -1

We'd like to note that when you use this API, the cursor is still active--you just can't see it.

---By Susan Harkins

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

KEEP IT SIMPLE

The two conditional statements

Do While Not rs.EOF Do Until rs.EOF

are equivalent, but not necessarily the same. The first cycles through your looping code while the EOF property isn't False. The second loops until EOF is False. Is one preferable? We think so. We recommend you avoid using the Not operator when possible. There's nothing wrong with the Not operator; it just adds an extra step to interpreting the code. If it's not necessary, it's inefficient.

Having said all that, we do recognize that you can't just replace every occurrence of the Not operator--and we're not suggesting you should. We're saying that you should evaluate your choice carefully and if there's an alternative, use it. Don't waste a lot of time on this quest, however. If using the Not keyword works but it takes you two hours to come up with a non-Not alternative, don't waste your time thinking about it. Go ahead and use the Not keyword.

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

ISNULL AND NULL

Most of the time, you probably use the IsNull() function to determine whether a variable is Null. You won't want to use IsNull() with the Find method because the ADO Find method doesn't recognize the IsNull() function. If you need to search for a Null value, you'll need to specify the value in the form

Field = Null

Field <> Null

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

INTRODUCING ADO+

One of the latest technologies from Microsoft is ADO+, which packs ADO and XML into one neat package. It's not available just yet, but you can learn more about it and be ready when it's released. To read more about ADO+, visit

http://msdn.microsoft.com/library/default.asp?URL=/library/techart/adoplus.htm

CORRECTION Steve Schroeder pointed out a terminology error in a recent tip. Specifically, we showed you how to use the ActiveExplorer method to select the current Windows Explorer folder. We identified the wrong Explorer. This method grabs the active folder in the Outlook Explorer. We apologize for any inconvenience and thanks to Mr. Schroeder for being so sharp!

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

INT() VERSUS CINT()

You're probably familiar with the Int() function, which returns the integer portion of a value. Similarly, the CInt() function does much the same thing. The main difference between the two functions is the data type of the value it returns:

Int() returns the same data type as the value it's passed. CInt() always returns an Integer data type.

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

IMPLICITLY TRUE

When writing an If statement, you can take advantage of VBA's implicit form of True to reduce your code just a bit. If so, you should be aware of the differences between the implicit and explicit forms of True and False. When expressed explicitly in the form

If var = True Then

var must evaluate to -1 to return True.

In contrast, the statement

If var Then

is True much more often than the previous statement because the implicit form of True includes any non-zero value. Consequently, only 0 evaluates to False. Keep this behavior in mind when depending on the implicit True value--the results might not be what you expected.

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

IF...END FASTEST IF

You can use the Iif() function in your VBA code, but avoid it because it's slow. The If...End If statement is always faster than the Iif() function despite what you might think. For instance, even a function as simple as the following:

str = Iif(Flag = 0, "No", "Yes")

is slower than the equivalent If...End If statement:

If Flag = 0 Then str = "No" Else str = "Yes" End If

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

IF YOU NEED A LITTLE HELP

Many developers create wizards and utilities for their own purposes and then make them available as shareware. You'll find such many files of this type at

http://www.colbyconsulting.com/

You'll also find free offerings and some demonstration files. For instance, you'll find the following:

A callback demo for list boxes A demo of how to set and clear references from inside a project A wizard for setting and resetting the custom properties of any MDA/B/E from outside A demo of how to build a back end, export a set of tables to the back end, and link all those tables into the front end A full-blown error handler insertion wizard As always, when downloading and using shareware, be sure to pay the bill. Doing so ensures that developers continue to supply us with affordable and free utilities and wizards that make our work easier and our time more productive.

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

HOW TO REDIM

We've offered several tips about arrays over the past few months, including instructions for changing an array's dimensions. Specifically, you can declare an array and then use the ReDim statement to set the array's dimensions. You might take this explanation to mean that you can change an array's dimensions, but that's not quite true. Using ReDim, you can only declare a dynamic array's dimensions--you can't ReDim a fixed array.

Let's revisit the difference between a fixed and a dynamic array. First, a fixed array specifies the array's dimensions during the declaration process using the form

Dim arr(1 To 3) As Integer

When you declare an array in this manner, you can't change its dimensions. On the other hand, a dynamic array doesn't specify the dimensions--it simply declares the array in the form

Dim arr() As Integer

Once you know your array's dimensions, you use ReDim to specify those limits in the form

ReDim arr(1 To 4) As Integer

In addition, you can change the dimensions for a dynamic array simply by using the ReDim statement again.

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

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.

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

GLOBALS LOSE VALUE

Global variables can be useful, but take care when you depend on them, because they can be easily wiped clean by an unhandled error. When VBA encounters an error that's not rerouted by appropriate handling code, it cancels the value of all global variables. In addition, an inappropriately placed End statement can reset your global variables before you meant for that to happen. There's really no workaround for this behavior. Just make sure you include thorough error-handling code if your application includes global variables. (Also, avoid using the obsolete End statement.)

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

GENERIC ERROR MESSAGE

No matter how careful you are, errors do creep into your application. When this happens, you'll want to know which error has occurred. Fortunately, you can add the following message box statement to your error handler to display the current error number and its description:

MsgBox Err.Description & vbCrLf & Err.Number

You can drop this into almost any error-handling routine.

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

FORMATTING PHONE NUMBERS

If you're using Access, you can use a control's Input Mask property to format a phone number a specific way. When you can't rely on a built-in feature, use VBA. For instance, if you want to allow an area code but it's not mandatory to have one, you might use a procedure similar to the following:

Sub FormatPhone(phone As String) As String FormatPhone = Format(phone, "(@@@)&&&-&&&&") End Sub

The Format() function returns an area code or spaces in parentheses followed by the phone number. When there's a possibility that there's no phone number to enter, you might try this procedure:

Sub FormatPhone(phone As String) As String FormatPhone = Format(phone, "(@@@)&&&-&&&&;No phone") End Sub

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

FOLLOWING EVENTS

Confused about the order that events are triggered? Welcome to the club. Perhaps the easiest way to follow the trail of events is to add a Debug.Print statement to each event in the form

Debug.Print "event"

Then, run the form or report in question. When you're done, open the Immediate window by pressing Ctrl-G and review the results--a list of each event triggered in the order those events were triggered by your actions. Of course, if you don't trigger an event, it won't appear in the list.

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

FINDING CONSTANTS

If you know just one constant in an enumerated set, you can quickly display them all in the Object Browser. First, open the browser by pressing F2 (in an open module). Then, enter the known constant in the Search Text control and press Enter or click the Search tool. The Browser will display the constant in the Search Results control in the middle of the browser. In addition, the Classes control will update accordingly--showing the Class the constant belongs to. That means the Members of the control to the right will display all the constants that apply to that particular Class.

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

FINDING ADO 2.5

If you're using Office 2000, you're probably familiar with ADO. However, you don't need Office 2000 as long as you have Windows 2000, because this version also includes ADO 2.5. Unfortunately, if you go looking for it, you may not find it. The folder

Program Files\Common Files\System\ADO

has plenty of files, but none with the 2.5 version number. That's because ADO 2.5 is in MSADO15.DLL.

You can see the libraries properties, including the version number, for yourself. Simply find the file using the Windows Find feature. Then, right-click the file, choose Properties, select the Version tab, and then select Product Version in the Item Name control.

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

FASTEST SELECT CASE

Did you know that the Select Case statement skips any remaining Case actions once VBA finds a matching Case value? It does, and that means you should always use the most likely match as the first case, the second most likely match as the second case, and so on as long as you can make that determination. This arrangement is much more efficient than an unordered arrangement. That's because once VBA finds a matching condition, it immediately executes the corresponding Case action and skips any remaining conditions.

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

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.

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

ERROR-HANDLING TEMPLATE

Error handling is an important part of any application, but it can be difficult to add in the early stages. One problem is that simple error handling can mask unexpected errors. One solution to this problem is to add a simple error-handling procedure that alerts you to the error and either exits the procedure or continues, as appropriate to the procedure's task. For instance, the following error-handling routine displays a message when an error occurs and then terminates:

Private Sub event()

On Error GoTo errHandler ...code... Exit Sub

errHandler: MsgBox err.Number & vbCrLf & Err.Description, vbCritical, "Error Information" End Sub

You can add this to almost any procedure. Occasionally, you'll want the application to continue, despite the error. In this case, you might want to try the following:

Private Sub event()

On Error GoTo errHandler ...code...

eventExit Exit Sub

errHandler: MsgBox err.Number & vbCrLf & Err.Description, vbCritical, "Error Information" Resume eventExit End Sub

The Resume statement in the error handler allows the program to continue. Once you're past the initial development stage, you can add more specific error-handling code.

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

EQUAL OBJECTS

When comparing variables, you may use the equal sign to determine a variable's value. For instance, the statement

If var = 0 Then

determines if the variable named var equals 0.

You can't use the equality operator (=) when comparing object variables. Instead, you must use the Is operator in the form

If objMyObject Is Nothing Then

where objMyObject is an object variable.

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

EOF STUFF

VBA has two EOF nametags, and confusing them is easy:

The EOF() function tests the position of the file pointer in a file opened with the Open statement. The Recordset object property, EOF, returns 0 or -1 to indicate the state of the record pointer in a recordset. If the cursor is at the EOF (end of file) position, the property is -1 (True).

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

ENGINE BASICS

You've probably read the term "engine" in relationship to VBA, but if you're like many users, you don't know what it means. The database engine is the software that stores and retrieves (and indexes) your data. You just don't know about the engine because it works behind the scenes and you never directly interface with it. The good part is, you don't need to know about the Jet (the name of the database engine) to take advantage of it. However, next time someone mentions it, you'll be able to follow the conversation (at least a bit).

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

DON'T FORGET OBJECT VARIABLES

Error-handling routines often exit the current procedure. However, when you take this route, you risk leaving live objects in your wake, which at the very least will consume resources unnecessarily. You can easily prevent potential problems by including Set statements in your error-handling code that return all your object variables to Nothing in the form

Set obj = Nothing

Include a statement for each live object immediately following your error-handling code, but before you exit the procedure.

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

DO IT AT LEAST ONCE

The Do loop repeatedly executes specific codes. Unless you supply either the Until or While keywords, the loop will execute indefinitely. Most of us are used to seeing either conditional keyword expressed at the beginning of the loop in the form

Do Until ... Loop

Do While ... Loop

Placing the keyword at the beginning of the loop has the sometimes unwanted limitation of inhibiting the loop altogether. If you need to execute the loop at least once, you can position the keywords at the end of the loop in the form

Do ... Loop Until

Do ... Loop While

Using this form, VBA will execute the loop's code, at least once, regardless of the current conditions.

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

DISPLAYING THE MONTH

If you need to return the month component from a date, you can use the Month() function in the form

Month(date)

which will return a month's integer value. For instance, if date is April 12, 2000, the Month() function will return the value 4. Be sure to use the pound sign delimiters when date is a date string (rather than an expression that returns a date). For instance, you might use the statement

Month(#August/21/2000#)

to return the value 8.

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

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.

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

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 If you've already purchased another version of Microsoft Office, you can still purchase the developer tools in Microsoft Office 2000 Developer Tools.

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

DECLARING A NEW CLASS--PART 1 OF 3

Once you've defined a new class, you'll need to create a new instance of that class by declaring the new object variable in the form

Dim objNewClass As NewClass

where NewClass identifies the custom class. The next step is to define the variable in the form

Set objNewClass = NewClass

If you forget the New keyword, VBA will generate a runtime error when you try to execute any of the new class's properties or methods.

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

DECLARING A NEW CLASS--PART 2 OF 3

In our previous tip, we showed you how to declare and set a new instance of a custom class properly. If you're working in a simple application, you can save yourself a line of code by using the form

Dim objNewClass As NewClass

However, be careful where you use this shortcut. If you need to know exactly when VBA creates the object, you'll find including the New keyword in the Set statement much easier to follow.

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

DECLARING A NEW CLASS--PART 3 OF 3

Once you've created a new class, you'll use the Property Set procedure to create custom properties. Then you'll use the Property Let procedure to set the current property and the Property Get procedure to retrieve the current property. However, you won't always use both, as a property can be read-only. In that case, you'll use only the Property Get procedure to retrieve the property. If you use only the Property Let procedure, than you'll create a write-only property--you can set it, but you can't retrieve it.

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

DEBUGGING WITH BREAKPOINTS

One of my favorite debugging tricks uses breakpoints. After I set a breakpoint, VBA executes the code up to the breakpoint and then stops. This allows me to set a variable so I can test the code for certain conditions. Here's how it works. First, open the module, select the statement where you want VBA to stop executing, and click the Toggle Breakpoint button on the Debug toolbar. Next, run the code. When VBA stops the code, jump to the Immediate window and enter a statement in the form

variable = newvalue

Then click the Run Sub/UserForm button on the Standard toolbar. The remaining code will use newvalue. This is a great way to set a variable for testing when the available data doesn't meet the necessary conditions that newvalue represents.

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

DATEDIFF BEHAVIOR

VBA's DateDiff() function returns a Variant value representing a timed interval between two specific dates. For instance, the statement

DateDiff("yyyy", #1/1/2000#, #1/1/2001#)

would return the value 1, because the two dates have one year between them.

There's one unexpected behavior you should know about. When trying to determine the years between December 31 of one year and January 1 of the next, this function will return 1, even though there is only one day between the two dates. When you think it out, the result makes perfect sense, because the dates are in two different years. However, at first, the function's response can be disconcerting, since the dates are only one day apart.

CORRECTION DO OVER! DO OVER! We let some typos creep into a recent tip. Remember our tip entitled USING ARRAY AS A METHOD? Here's the correct procedure. Thanks to all who pointed out the mistake.

Function ArrayMethod() Dim varArrayList As Variant varArrayList = VBA.Array("one", "two", "three") MsgBox varArrayList(2) End Function

Function ArrayMethod() Dim varArrayList As Variant varArrayList = Array("one", "two", "three") MsgBox varArrayList(2) End Function

Both procedures do the same thing--they both return the text "three" in a message box.

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

DATE LIMITS

The Date and Variant data types have limits on dates. Both data types will handle dates between 100 A.D. and 9999 A.D. Keep these limits in mind when using other date functions. For instance, if the result of a DateAdd() function returns a date outside these limits, VBA will return an error. We can say this without too much worry because these limits won't affect most users.

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

DATA TYPE FOR NULLS

The only data type that will accept a Null value is the Variant data type. Therefore, when you're considering what data type to assign to a variable, don't forget about Null values. If it's possible that your code may pass a Null to the variable, be sure to declare that variable as a Variant. If you don't, VBA will return an error when it encounters a Null.

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

DAO/ADO CONFLICT

If you're running DAO code in Access 2000, you probably know that you need to add the DAO library (Microsoft DAO 30, 3.51, or 3.6 Object Library) to your references by choosing Tools, References (in the Visual Basic Editor). If your DAO code still returns an error, remove the Microsoft ActiveX Data Objects 2.0 or 2.1 Library from your references, and your DAO code should run just fine (provided there are no programming errors).

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

DAO REFERENCES

You probably know that you can use DAO in an Access 2000 module. This flexibility is great for those who don't know ADO yet or are converting an Access 97 database to Access 2000. However, should you decide to use DAO in an Access 2000 database, be sure to reference the most recent version--DAO 3.6. Selecting the older version, 3.51, may cause problems in an Access 2000 module.

To create a reference, open any module and select Tools, References.

Susan Sales Harkins is a private consultant specializing in Access and VBA development. She currently has two technical books on the shelves: "Using Microsoft Access 97" and "Using Microsoft Access 2000." Both are QUE publications.

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

CUSTOM COLLECTIONS

You can create your own Collections in VBA, but there are a few points you should be aware of when you do. First, custom Collections are one-based. This means, the first object is object 1, the second is object 2, and so on. If you delete an object from the middle of the Collection, VBA will adjust the subsequent object values. Second, the Collection object variable is just like any other variable and subject to the procedure's scope and lifetime. If you declare a Collection object in a procedure, that object will disappear, along with all the objects it contains, as soon as the procedure terminates.

The gist is, don't depend on a Collection's object position, and consider declaring Collection objects as module or global variables.

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

CONTROLLING RANDOM VALUES

Many tasks call for random values, and you can easily provide these values using the Rnd() function. This function always returns a value between 0 and 1 (in other words, a decimal value).

If you need to limit random values to a particular range, you can do so using the simple formula

Int((highest - lowest + 1) * Rnd + lowest)

where highest and lowest represent the top and bottom values in the range.

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

CONTINUING LONG LINES OF CODE--PART 1 OF 2

Long strings are fairly common in VBA code, but don't try to wrap a string onto multiple lines. The string must be complete on one line, or you must use the line continuation character to break a long line into multiple, smaller lines, as shown here:

str = "This is a long string that will be very hard to read as it will eventually extend off screen."

str = "This is a long string" _ & " that will be very hard to read" _ & " as it will eventually extend off screen."

Be sure to include spaces because the continuation character won't automatically add them for you.

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

CONTINUING LONG LINES OF CODE--PART 2 OF 2

In our previous tip, we showed you how to break up a long string into multiple lines. We also mentioned that you must remember to include your own space characters because the continuation character won't add them for you. We recommend that you adopt the habit of including space characters in one of two ways:

Include the space at the end of a line and before the continuation character. Include the space at the beginning of the next line. Choose one of the above and use it consistently. That way, you can avoid forgetting about the space character altogether or adding two space characters--one at both positions.

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

CONTINUATION CHARACTERS IN COMMENTS

You probably know how to use the continuation character in your code statements. Did you realize you could also use this character in comments? For instance, the following comment

'this is _ a comment _ that spans three lines

uses the continuation character to connect all three lines. Notice that we've prefixed only the first line with the apostrophe character (').

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

CONSIDER FUTURE RESERVED WORDS

Avoiding reserved words is tricky because Microsoft continually adds new words to the list with each upgrade. That means a field or table name that once worked can suddenly cause problems when you convert to the next version. If Access suddenly balks at a table or field name it accepted before, check for a reserved name. Try a simple test--change the field or table name and see how well it works. If the problem disappears, you'll know you have to change a field or table's name permanently.

Another consideration when naming tables and fields is whether your database may someday be upsized to SQL, since this language has its own list of reserved words. If upgrading is a possibility, you'll want to avoid these reserved words when naming your fields and tables, even when they're not reserved in Access.

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

COLOR CONSTANTS

Referring to colors in your code can be a bit of a nuisance because you must remember the color's corresponding integer. If you're working with the Windows standard colors, your work is made easier by a few intrinsic constants. Instead of looking up a color integer, you can simply use one of these constants:

Black vbBlack White vbWhite Red vbRed Yellow vbYellow Blue vbBlue Green vbGreen Cyan vbCyan

Working with these constants should prove much easier and more efficient than using the color's integer values.

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

CLIPPIT SECURITY ALERT

Microsoft has announced a new security alert with the Office 2000 UA Control. This control provides the Show Me functionality in the Office Help system. This security hole is a little different from the viruses and worms we've become accustomed to. Malicious hackers can attach code to their Web sites that will later invade your system. For more information and a patch, visit

http://officeupdate.microsoft.com/2000/downloadDetails/Uactlsec.htm

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

CLICK EVENT ORDER

When you click on most controls, you produce a Click event. Right? Well, let's see. When you click a text box, the control fires its Click event--that much is true. However, if you click inside the text box component of a combo box, you don't trigger that control's Click event. So what's the difference? The combo and list box controls don't fire their Click event until the user actually selects an item from the control's list. This behavior is considerably different from other controls because they trigger their Click event as soon as the control receives the focus. So consider the outcome carefully when you attach code to a combo or list box's Click event.

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

CHECKING ARRAYS

When working with arrays, you might take a shortcut when determining the lower and upper boundary of that array. That shortcut would be specifying those boundaries as literal values. Let's look at a quick example that works and seems harmless--at first.

For i = 0 To 4 var = myArray(i) Next i

This simple For loop cycles through each element in the array and assigns the current element to a variable named var. All goes well until someone adds a sixth element to your array because your loop never picks up that new value, and you know that will create a problem.

The solution is to refer to the upper and lower boundaries dynamically using the LBound() and UBound() functions in the form

For i = LBound(myArray) To UBound(myArray)

The LBound() function returns the lower boundary value and UBound returns the upper boundary level, so your loop never misses an element.

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

CATCHING RECORDSET NULLS

You may not anticipate Null values in a recordset, but sometimes they're there just the same. If you aren't prepared for the Null value, your application will come to a screeching halt with the dreaded

Invalid Use of Null

error. To avoid this, you should test field values for Null using the IsNull function in the form

If IsNull(rst!fieldname) Then

where rst represents the recordset and fieldname is the field you're accessing.

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

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.

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

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.

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

CALLING EVENT PROCEDURES

Most VBA applications are loaded with event procedures. Occasionally, you may want to reuse these event procedures at other times--and you can, simply by calling the event. For instance, you may want to run a command button's procedure from another event or procedure. When this happens, you'd call the event in the form

commandbuttonname_Click

Calling an event procedure in this fashion isn't wrong. However, you might consider writing a function procedure and calling it from the button's Click event and any other procedures or event. We find this arrangement a bit easier to debug and maintain.

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

CALLING AN EVENT

You probably know that you can call one procedure from another by using the Call statement or simply expressing the function's name. For instance, either of the following will call the same procedure:

Call DoMyWork(arg1, arg2) DoMyWork arg1, arg2

If you omit the Call statement, be sure to eliminate the parentheses around the argument list.

Did you realize that you could call an event procedure the same way? Simply precede the event name with the Call statement in the form

Call cmdSave_Click()

Keep in mind that events are private to their object, so you can call an event only from within the same object (module).

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

BYREF ERROR

If you get a ByRef Value Type Mismatch error, you might know what the error is but have a hard time finding its cause. Generally, this message means that a passed argument doesn't match the variable's declared data type. The most logical place to begin your search is in your Dim statement. Make sure that the variable's declared data type matches the argument's data type in the calling procedure. The following procedure declares the variable i as an Integer, then passes i to the procedure DoThisNow(). Unfortunately, DoThisNow() expects a string--as you can see in the function's name statement. When MyWork() tries to pass DoThisNow(), the integer variable DoThisNow() will return a mismatch error. (Compiling this code should catch the error.)

Function MyWork() Dim i as Integer DoThisNow i End Function

Function DoThisNow (arg As String) End Function

The only way to resolve this error is to declare i as a String or Variant or to change the argument's declaration to an Integer or Variant.

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

AVOIDING BUGS

Chasing down a bug in your code is never fun or easy. In a perfect world, you'd find these bugs before they ever hatch, but that's just not practical. There are a few guidelines you can follow that may help you prevent error prone code:

Break your code into small, manageable pieces; don't write functions and subs that are hundreds of lines of code. Comment abundantly, and make those comments count. Explicitly declare all your variables; a misspelled variable can be very difficult to find. Adopt and consistently use a naming convention.

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

AVOID LIBRARY CONFLICT

Now that Office 2000 supports ADO, you might find working between the different versions difficult as older versions still run DAO. Converting an earlier file to an Office 2000 file can also cause a bit of confusion. You can eliminate this problem by specifying the library name before the object variable in the form

Dim db As DAO.Database Dim rst As DAO.Recordset

Dim rs As ADODB.Recordset

As long as you specify the library name, there won't be a question as to which library is in use.

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

ARRAY() IGNORES OPTION BASE

In our previous tip, we showed you how to assign values to a dynamic array quickly by using the Array() function. If you choose this route, keep in mind that the Array() function will ignore the module's Option Base setting. The Array() function elements are always 0-based.

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

API SITE

Some of us avoid APIs because we don't like to work that hard. Fortunately for us, there's a great site that lists many of the most commonly used APIs. You'll find a list of the supporting platforms, a description of the API, an example of how to use it, and much more. The next time you need an API, visit this site:

http://www.vbapi.com/ref/index.html

You may just save yourself a lot of time and heartburn.

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

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.

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

ANOTHER QUICK NULL CATCH

Our previous tip showed you an easy way to avoid Null errors by catching Nulls before they have a chance to stop your code. Another quick method for catching Null values is to use the Format property in the form

var = Null str = Format(var)

where str is a String variable and var is a Variant. The following procedure runs a quick check for Nulls using this method:

Function CatchNull() Dim var As Variant, str As String var = Null str = Format(var) MsgBox str = vbNullString End Function

If var is Null, the message box displays True (False when var isn't Null).

Using Format returns a zero-length string, and the vbNullString constant, used in this context, will catch a zero-length string.

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

ANOTHER ONE ON DATES

When using the Day() and Year() functions, did you know that your date needn't contain a day or year component? For instance, the statement

Day(#Feb 2000#)

would default to the value 1, which represents the first day of the month. The Year() function is similar in that it defaults to the current year. The statement

Year(#Feb 3#)

would default to the year 2000 (assuming 2000 is the current year).

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

AN UNLIKELY ERROR STATEMENT

Most of us are familiar with the On Error Resume Next statement, and we use it frequently. This statement ignores the statement that produces an error and resumes execution with the very next line. VBA offers a similar statement that is seldom used--and with good reason. The On Error Resume statement will also ignore the error, but instead of executing the Next statement, Resume tries to re-execute the line that caused the error in the first place.

Few of us will ever find use for this statement, and we recommend you avoid it. However, in the right circumstances, it can be beneficial. Unfortunately, it's not uncommon to omit the Next statement accidentally. If your application is one of the few VBA applications that doesn't offer the Resume statement, you don't need to worry, as your application won't like the incomplete statement anyway. If the On Error Resume statement is valid in your application, just being aware of the situation may save you a future headache.

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

AN EMPTY STRING CONSTANT

Most of us define an empty string as "", in the form

str = ""

In the run of any normal application, you may use the string "" many times, but there's a more efficient way. The constant vbNullString points to an internal empty string. Using this constant saves VBA the time of creating the new string each time you use "".

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

AN EASY JULIAN DATE

A Julian date is the day of the year, beginning with January 1. For instance, January 1 is 1, obviously, February 1 is 32, and March 1 is 61. Although most modern applications don't use Julian dates anymore, you may run into an occasion where you need to calculate a date's Julian date value. Fortunately, it's easy. Just use the simple expression

strDate = Format(dte, "y")

where dte represents the date you're converting. If you're converting the current date, replace the dte argument with the Date function.

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

AGGREGATE FUNCTIONS CAN BE SLOW

Aggregate functions grab values from tables based on criteria, and they're very useful in the right circumstances. If you're working with local tables or even small-networked tables, you can probably use aggregate functions without affecting the overall performance of your application. However, when you're working with large tables on a network, aggregate functions can slow things down significantly. Why? Because aggregates search your entire table without using an index. That means Access compares your criterion to every record in the table.

An alternative is to open a recordset and return the value that way--be sure to assign an appropriate index. Using this method, Access will stop searching for the appropriate record and value once it matches the criteria. In addition, the procedure will perform what's known as a binary search, which means it won't look at each record. Instead, it makes spot checks and then, depending on the result, continues moving forward or backs up--much the same way you might find a name in a telephone book.

Building a recordset, setting an index, and then searching for a matching value certainly requires more code, but sometimes more is better, and this is one of those cases.

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

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.

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

ADO RECORDSETS ARE FORWARD-ONLY BY DEFAULT

Forward-only recordsets are efficient when you need to move through your data rapidly. On the other hand, if you need to move around, you'll need another option. When working with ADO, you should remember that the Connection.Execute method creates a forward-only recordset. This type of recordset can be used only with the MoveNext statement. To move back and forth, create a recordset and use the Open method to retrieve the data, specifying a recordset type other than forward-only.

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

ADO CURRENT PROJECT

We've had a lot of people ask how to use ADO to connect to the current database. That's because ADO is typically used with foreign data (although we'll all be using it in the future, since Microsoft plans eventually to replace DAO with ADO). As a result, most examples and documentation stick with connections to files other than the current file. If you'd like to use ADO in the current database, use the following code to open a connection to the current database:

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset Set cnn = CurrentProject.Connection

Then, refer to your cnn variable when you open your recordset in the form

rst.Open source, cnn, cursortype, locktype

where source identifies your data source, cursortype is one of four constants that identify the position of your cursor, and locktype is also a constant that specifies your locking preference. As a rule, you'll use adOpenKeyset and adLockOptimistic, respectively. Now, let's suppose you want to open a recordset based on a table named tblMyWork in your .mdb file. To do so, you'd use the code

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset Set cnn = CurrentProject.Connection rst.Open "tblMyWork", cnn, adOpenKeyset, adLockOptimistic

You can replace the cnn variable with the connection reference in the form

rst.Open "tblMyWork", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

---By Susan Harkins

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

ADDING ITEMS TO LISTS

The combo and list box controls allow you to enter a list of items. When you choose this route, you enter in the control's Row Source property a setting in the form of

"item1";"item2";"item3"

You must also specify the Value List setting for the Row Source Type property. The result is a list of items, one right after the other, in the same order as they appear in the Row Source property setting.

You might not realize that you can display more than one column of items in a Value List control. To do so, you simply add a second item to the list in the form

"item1a";"item1b";"item2a";"item2b";"item3a";"item3b"

Access will display items 1a and 1b in the first row, items 2a and 2b in the second row, and items 3a and 3b in the third row. Just be sure to update the Column Count property to reflect the appropriate number of columns.

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

ADDING BLANK LINES TO LISTS

In our previous tip, we showed you how to add a second (or more) column of items to a list or combo box list. You can use the same method to display blank lines in a control. Specifically, insert a zero-length string between each item in the list in the form

"item1";"";"item2";"";"item3";""

In addition, be sure to select the Value List setting from the Row Source Type property. The Column Count property should be set to 1.

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

ACCESSING OBJECT PROPERTIES

If you're using object properties, consider storing the property value in a variable so it can be used multiple times. A local variable is much faster to access than an object's property. (This relates to how the object is stored in memory.) As a rule of thumb, if you refer to the same property more than a few times, create a variable for it.

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

ABOUT THE COLLECTION OBJECT

VBA's Collection object is a simple container for data, much like an array. Most of the time its members are other objects, but it can hold any type of data. Its claim to fame is its four methods:

Add: As implied, this method adds an item to the collection. Count: Returns the number of items in the collection. Item: Retrieves a member from the collection. You can use an index or key value to identify the item. Remove: Deletes a member from the collection. You'll use either the index or key value.

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

ABOUT RESET

If you're using the Open and Close statements to work with disc files, you should know about the Reset statement. Normally after opening a file, you use the Close statement to close those files when you're done with them. However, you should consider including an emergency procedure that uses the Reset statement to close all the open files at once.

Besides closing all the open files, Reset will also write the contents of the current file buffer to disk before closing the files. Just remember that Reset works only with files opened by the Open statement.

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

ABOUT FILE.MOVE

The new File object allows you to move files easily using its Move method. Simply use the syntax

oFile.Move destination

where oFile is the File object and destination identifies where you're moving the file. When using this method, keep in mind the lack of any rollback capability. This means that an error--in particular a fatal error (like a power outage)--could disrupt this process. You won't lose the file, but the method might fail to move the file properly.

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

ABOUT COLLECTION'S COUNT PROPERTY

Several tips ago, we talked a bit about the Collection object and its Add method. We also showed you how to refer to an element by using its index value. Did you realize Collections are 1-based? That means the first item added to the collection has an index value of 1, not 0, as you might expect. As a result, iterating through the members of a collection is a bit easier than those that are 0-based. That's because you can use the Collection's Count property as the stop value in a For...Next statement. For instance, the statement

For I = 1 To col.Count

(where col represents the Collection object) would cycle a number of times equal to the number of members in col.

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

ABOUT ALLFORMS

In our previous tip, we talked about the difference between Collections and Containers in respect to open objects. Collections contain only open objects while Containers contain all the respective objects in the application. However, there's a new kid in town--the AllForms collection. This collection contains an AccessObject object for each form in the CurrentProject or CodeProject object.

Function ChangeProperty() Dim obj As AccessObject Dim dbs As Object Set dbs = Application.CurrentProject For Each obj In dbs.AllForms DoCmd.OpenForm obj.Name, acDesign ...property changing code... DoCmd.Close acForm, obj.Name, acSaveYes Next obj End Function

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

A TERMINAL DO LOOP

An easy way to advance through a recordset is to use the Do...Loop statement. For instance, the following Do...Loop stops at each record in a recordset named rst and adds the string "OK" to the Check field.

Do Until rst.EOF .Edit !Check = "OK" .Update .MoveNext Loop

(If you're using ADO, you can omit the Update method.)

One problem that we see occasionally is the accidental omission of the MoveNext statement. The Do...Loop statement won't automatically select the next record at the end of its task. You must do that by including the MoveNext statement. Without that statement, your statement will run forever.

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

A SQL DEBUGGING TRICK

There's an easy way to debug your SQL statements--simply copy the SQL statement from its module to a query SQL window and run it. Chances are the query design grid will return a much more specific error message that will better help you pinpoint the error. Unfortunately, concatenated variables kind of put a damper on this debugging method, because you must replace the concatenated variables with real data before running the statement in the SQL window. Sometimes this is a bigger nuisance than just debugging the statement on your own. Fortunately, help is available--if you're willing to plan a little better.

When executing a SQL statement in VBA, always assign the statement to a variable. Then, immediately after the defining statement, include a Debug.Print statement that prints the SQL statement to the Debug window. When there's an error in the statement, simply open the Debug window and paste that version of the statement to the query SQL window. The reason? The Debug version won't contain the concatenated components.

To demonstrate, let's look at a quick example. The statement

"SELECT * INTO tblNumberOfLocations FROM tblMinimum WHERE Gen = 'Gen" & strYear & "';"

selects all the fields from tblMinimum where the Gen field equals the concatenated result of the string 'Gen' and the value of the variable named strYear. The Debug.Print statement in SQLTest() prints a concatenated version of the SQL statement in the Debug window.

Function SQLTest() Dim db As Database, strSQL As String, strYear As String Set db = CurrentDb strYear = "1" strSQL = "SELECT * INTO tblNumberOfLocations FROM tblMinimum WHERE Gen = 'Gen" & strYear & "';" Debug.Print strSQL db.Execute strSQL End Function

If there's an error in the statement, simply open the Debug window and copy the statement

SELECT * INTO tblNumberOfLocations FROM tblMinimum WHERE Gen = 'Gen1';

to the query design grid's SQL window and run it. You won't have to modify a thing first.

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

A REFEDIT CONTROL ALTERNATIVE

In Microsoft Excel, you probably use the RefEdit control when you need to solicit a range from the user. If you'd rather not fool with a control, you can use the simple procedure below. This procedure takes advantage of the InputBox() function's range type (that's what the number 8 represents).

Function UserRange() Dim MyRange As Range Set MyRange = Application.InputBox("Select a range of cells", , , , , , , 8) End Function

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

A QUERYDEF BY THE SAME NAME

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.

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

A CONDITIONAL MACRO

VBA doesn't have any trouble making decisions. You can use If or Select Case to check for conditions and direct your code accordingly. You might not realize that macros can also make decisions. For example, let's suppose that you want to open a form. In addition, you want the option to cancel the entire macro instead of opening the form. In this case, you might use the InputBox() function to determine whether the macro continues (and opens the form) or quits. First, open the Conditions column by clicking the Conditions Columns button on the Macro Design toolbar. Next, enter in the Conditions column the expression

InputBox("Do you want to open the form?") = "Yes"

When you run the macro, this expression will display an input box. If you respond to that box by entering the string

"Yes"

the macro will continue. If you enter anything else, the macro will quit.

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

PUT BUGS ON NOTICE!

If you're having a problem getting Access or VBA to behave the way you know it should, don't assume you've made a mistake. The problem might not be you--you might be struggling with a bug. Try checking this comprehensive site to see whether others have encountered the problem and how they resolved it:

http://www.mvps.org/access/bugs/index.htm

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

OPEN FAVORITES

You can set the Default database folder option to the Favorites folder if you want to default to that folder when creating, saving, and opening files. Such an arrangement isn't always appropriate, and fortunately, there is a VBA solution--use the RunCommand method in the form

DoCmd.RunCommand acCmdFavoritesOpen

Doing so will open the Open dialog box to the Favorites folder without changing any settings or defaults.

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

EXCEL DATE AND TIME STAMP

Need to document your workbooks? You can with a simple macro. Every time someone saves your workbook, the following procedure will update the time and date in the active sheet's header.

To create the procedure, first open the VB Editor by pressing Alt-F11. Double-click ThisWorkbook in the Project Explorer and select Workbook from the Object control. Choose BeforeSave from the Procedure control, then insert the following code:

ActiveSheet.PageSetup.RightHeader = Now

To see the results, simply return to the workbook and save it. Then, preview the active sheet. The one issue you need to be aware of is that the new date and time stamp appears only on the active sheet. This works well if you want to stamp each sheet as users update it.

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

DISPLAY FILE NAME IN SHEET FOOTER OR HEADER

One of the most frequently asked questions we receive is how to print the file's name in the sheet's header or footer. We covered this issue last year, but because it's such a common question, we'll repeat the information. There's no built-in feature for printing the file's name, but you can do so with a simple macro. To create this macro, open the VB Editor by clicking Alt-F11. Then, select the correct project in the Project window (which you'll find in the upper-left corner). Next, select Insert, Module. In that module, enter this procedure:

Sub NameInFooter() ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName End sub

To close the VB Editor, simply click the Close button (X) at the top-right corner of your screen.

When you're ready to run the macro, choose Tools, Macro, Macros. Then, select NameInFooter and click Run. You won't see the effects immediately. If you click the Print Preview button, you can see the file name in the footer.

You might be wondering if there's a way to print the file name in one of the other footers or even a header. For instance, what if you want to print the name in the center of the footer or in the right portion of the header? The solution is simple--replace the LeftFooter property in the macro with one of these properties:

CenterFooter RightFooter LeftHeader CenterHeader RightHeader To avoid a few keystrokes, attach this macro to the workbook's BeforeSave event. If you change the file's name, be sure to update the header after the initial save.

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

CHECKING DATE ENTRIES

There's a running debate about formatting entries. Some developers force users to enter data in a specific way or the application rejects the entry--that's one way to avoid errors. Others accept entries and then attempt to format the data correctly. For instance, the following procedure checks an entry and attempts to create a date. If the passed entry is convertible, VBA accepts it. If not, VBA will return an error, so you'll need some kind of error handling if you decide to go this route.

Function CheckDate(pdate As Variant) If pdate <> "" Then CheckDate = Format(CDate(pdate), "mm/dd/yyyy") End If End Function

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

IF YOU'RE LOOKING FOR CODE

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.

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

CALLING ALL PRINTERS

Before printing reports, you may need to offer your users the opportunity to set print options. If so, you could solicit information by using input boxes, but why not just display the Print dialog box and let them set those options themselves? If this sounds like a good idea to you, try the procedure shown here to display the Print dialog box. The users can choose from default printers, change the default print range, specify the number of printed copies, and so on.

Public Function PrintOut() On Error GoTo ErrorTrap DoCmd.RunCommand acCmdPrint Exit Function

ErrorTrap: If Err.Number = 2501 Then Exit Function Else MsgBox Err.Number & vbCrLf & Err.Description End If End Function

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

A QUERYDEF BY THE SAME NAME

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.

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

RETURN VERSION

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

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

RENAMING MODULES

Have you ever tried to rename a module in the Project Window? If so, you probably found the adventure a tad frustrating. You can't double-click the name to put it in edit mode the same way you can filenames in the Windows Explorer. Nor is there a Rename command on the Edit menu. You can't even right-click a module and rename it. To rename a module in the Visual Basic Editor, you must open the Properties window and modify the module's Name property. To display the Properties window, click F4 or choose View, Properties Window.

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

PRINTING THE GUID

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

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

INPUTBOX HELP

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.

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

DIM WARNING

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.

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

DIM ADVICE

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.

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

DELETING MODULES

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.

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

SAVING WORD DOCUMENTS

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

Documents("nameofdocument").Save

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

Documents.Save

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

SAVING EXCEL WORKBOOKS

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

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

MOUSEPOINTER FOR ERROR-HANDLING ROUTINES

In our previous tip, we showed you how to inform your user visually when a task may take a few minutes by using the MousePointer property in the form

Screen.MousePointer = vbHourglass

Be sure to return your mousepointer to normal using the statement

Screen.MousePointer = vbDefault

at the end of a task. However, the end of the task isn't the only place you'll want to include this statement. Be sure to include it in error-handling routines. In addition, if you call an outside procedure or function, you might need to include a statement that resets the mousepointer in that function or procedure. The latter suggestion is a little tricky and isn't always necessary, but if there's any chance that this called function or procedure could be the last stop in your task, you should consider resetting the mousepointer--just in case.

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

LET THE USERS KNOW THE APP IS BUSY

If an application is going to be busy for a while, you really should let the user know. The easiest way is to change the mousepointer to the readily understood hourglass icon. This quick-change act is easy to include in most procedures. Simply include the statement

Screen.MousePointer = vbHourglass

right before the code that will tie things up for a while. Once the task is complete, be sure to reset the mousepointer using the statement

Screen.MousePointer = vbDefault

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

STABILIZING VBA

Working with VBA a lot has a tendency to destabilize Windows (specifically versions 95 and 98). If you start receiving General Protection Faults, it's time to reboot. There's really nothing else you can do. We recommend that you reboot a few times each day during any serious development sessions. Doing so should help you prevent those errors altogether.

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

REPLACE WORD TEXT

Replacing text in a Word document is fairly easy using the Find and Replace feature, but this process take a while because Word scrolls through the entire document, highlighting each occurrence of the word. If you'd like a quicker search and replace, try the following procedure:

Function FindStuff() Dim strFind As String Dim strReplace As String strFind = InputBox("What are you replacing?") strReplace = InputBox("What is the replacement text?") With ActiveDocument.Content.Find .ClearFormatting .Replacement.ClearFormatting .Text = strFind .Replacement.Text = strReplace .Execute Replace:=wdReplaceAll End With End Function

VBA will prompt you for the text you're replacing and the replacement text. Then, the procedure replaces the appropriate text without visibly looping through the document. Word allows you to undo these changes. Simply select Edit, Undo after you run the procedure and before you take any further action.

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

REMOVE A DLL FILE

DLLs make Office and VBA tick, but they also tend to blow things up. Why? Because DLLs come in different versions and applications sometimes call an older version that's obsolete. It's a good idea to get rid of DLLs you no longer need. To do so, you'll run the regsvr32.exe utility from the Windows Run command in the form

Regsvr32.3x3 /u dllpath

where /u is the unregister switch and dllpath is the DLL's full pathname.

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

OLE DB PROVIDERS

When accessing a database, you need an OLE DB provider (if you're using ADO). Now that Office 2000 supports ADO, we've received a lot of inquiries for specific providers. If you don't have the provider you need, check out Microsoft's third-party OLE DB site at

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

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

LET MACROS LIGHTEN YOUR LOAD

Generally, these tips are about VBA, but most people refer to VBA procedures as macros. That's why we thought some of you might be interested in a shareware program named Macro Anywhere, which is available from

http://www.pcworld.com/fileworld/file_description/0,1458,7419,00.html

This program lets you set a keyword or keystroke combination to produce text--from a short headline to an entire document.

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

WHERE IS ARRAY

Most of us use the Object Browser to locate a particular function, method, or property so we can learn more about it. As long as the appropriate library is referenced, you should be able to locate your information, right? Not always. There are a few pieces you won't find. For instance, the Object Browser doesn't display information on the Array method. That's because this method is a member of the HiddenModule module and it's hidden from view. You can still use the Array function--you just can't read about it in the Object Browser.

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

USE STOP TO DEBUG

You might not realize it, but you can work error handling into a regular procedure. For instance, a Select Case or If statement is the perfect spot. Let's suppose a procedure includes a Select Case statement that accounts for every possible situation the statement might encounter. You might think you don't need an Else statement, but including one is a good idea. That way, if the unexpected happens, the code in the Else statement can warn you. For example, the Else action code might display a message box that clearly identifies the error. After receiving this message, you can press Ctrl-Break and examine the variables to discern just what triggered the Else clause.

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

ROUNDING NUMBERS WHEN USING CINT()

The CInt() function coverts an expression to an integer and rounds any decimal portion. This behavior is a little different from Int(), since that function simply truncates the decimal portion of a number. However, you need to be aware of how CInt() rounds. Specifically, when the decimal portion is .5, CInt() always rounds to the nearest even number. For instance, .5 rounds to 0, because 1 is an odd value; 1.5 rounds to 2.

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

REPLACE EXCEL TEXT

In our previous tip, we showed you a VBA procedure that replaces text in a Word document. You can do the same in an Excel sheet using the following procedure:

Function FindStuff() Dim strFind As String Dim strReplace As String strFind = InputBox("What are you replacing?") strReplace = InputBox("What is the replacement text?") Cells.Replace What:=strFind, Replacement:=strReplace, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End Function

This procedure works only with the active sheet. You can't undo these changes.

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

PASS THE ARRAY PLEASE

Arrays are great tools when you're storing multiple elements, but did you realize that you could pass an array? You can, and it's a simple task. Simply state the array by name, followed by an empty set of parentheses in the form

Call MyProcedure MyArray()

This statement would pass all the elements in MyArray() to the procedure named MyProcedure.

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

LIST FONTS

Word has a collection named FontNames that contains a list of all the available fonts. If you'd like to see a list of these fonts, you can run the following procedure:

Function FontNamesList() Dim varFont As Variant For Each varFont In FontNames Debug.Print varFont Next End Function

This procedure will list all the available fonts in the Visual Basic Editor's Immediate window.

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

LIMITING REPLACEMENTS TO A RANGE IN EXCEL

Our past two tips have covered replacing text in Word and Excel. When working in Excel, you can specify whether you want to replace text in the active sheet or a specific range. To limit replacements to a specific range, use the procedure

Function FindStuff() Dim strFind As String Dim strReplace As String strFind = InputBox("What are you replacing?") strReplace = InputBox("What is the replacement text?") Range("nameofrange").Replace What:=strFind, Replacement:=strReplace, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End Function

where nameofrange identifies a valid range.

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

CHOOSE() NOT CHOOSY

The Choose() function selects an item from a predefined list based on its position. For instance, the function

Choose(intValue, "one", "two", 3)

will return "one", "two", or 3 depending on the value of intValue. What you might not realize is that Choose() doesn't care whether the items in the list are of the same data type. That means you can mix them up. In our example, "one" and "two" are definitely strings, but 3 is a value. This behavior makes Choose() extremely flexible.

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

CALLING PROCEDURES FROM CHOOSE()

When we talked about the Choose() function in our previous tip, we said that this function accepts different data types in its list of choices. It's even more flexible than that, though. You can even call procedures from this function. For instance, the function

Choose(intValue, FunctionOne(), FunctionTwo())

will return the result of either FunctionOne() or FunctionTwo() as its result.

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

CALL STATEMENT MAKES CODE EASIER TO READ

In our previous tip, we used the Call statement to pass control to that procedure. However, as you probably know, the Call statement is not required. So why did we use it? Because using the Call statement makes the code easier to read and decipher. It's easy to see with just a quick glance that the statement

Call MyProcedure

is passing control to MyProcedure and the keyword Call is the visual clue.

In the end, it's really up to you. If you're the only one viewing your code, you can easily omit quick clues such as this.

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

VALIDATING DATA

Validating data is a big part of any application, but you might have trouble deciding when to validate entries. You might consider using the Change event for each control that accepts new data. There's nothing wrong with this choice, but keep in mind that every time the user changes the entry, VBA will trigger the Change event. Ultimately, this setup can be inefficient.

A better choice might be the LostFocus event. Attaching your validation procedures to this procedure guarantees that your application will check the entry only when the user tries to leave the control, instead of every time the user changes the contents of the control.

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

RETURN TO THE LAST WINDOW

If you frequently work with more than one window open, you might easily get lost. Fortunately, there's a keyboard shortcut for returning to the last active window. You can press Ctrl-Shift-F6 to select the last window.

This shortcut works equally well in the Visual Basic Editor. Simply press Ctrl-Shift-F6 to retrace your steps through the open modules.

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

RETURN THE SELECTED ITEM IN A LIST BOX

Patricio G. submitted this quick and easy method for returning the selected item in a list box (or combo box) control as a value. Most of the time you'll return the actual item, but occasionally you'll need to know the position of that item in the list. This simple procedure adds 1 to the selected item's index value and then prints it in the Immediate window. You'll want to enhance the procedure to suit your particular needs, but this procedure supplies the heart of the task. Thanks, Patricio!

Private Sub lstname_DblClick(Cancel As Integer) Debug.Print Me! lstname.ItemsSelected.Item(0) + 1 End Sub

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

READING FOR THE OBJECT IMPAIRED

Many people are using VBA and other (almost) object-oriented languages without knowing what object-oriented design is. That lack of knowledge can inhibit your ability to advance your programming skills. If you're a beginner in this area, there's an introduction to the subject at

http://compsci.about.com/science/compsci/library/weekly/aa111300a.htm

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

QUICK MODULE LAUNCH

Peter T. sent this quick tip for launching the Visual Basic Editor (in Excel) to the current sheet's module. First, right-click the sheet tab. (You can right-click any tab--right-clicking a tab makes it the current sheet.) Next, choose View Code from the resulting submenu. When you do, Access launches the Visual Basic Editor and defaults to the active sheet's module.

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

DELETEFOLDER IS PERMANENT

We've talked about the FileSystemObject in past tips. One of this object's methods, DeleteFolder, makes short work of cleaning up your directories by deleting a folder and all its file and subfolders. When executing this method, you need to be very sure of your action because this method permanently deletes folders and their contents. You can't retrieve them from the Recycle Bin.

In addition, if an error occurs, the DeleteFolder method exits immediately and there's no way to rebuild the partially affected folder.

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

CINT() RETURNS INTEGER DATA TYPE

In our previous tip, we told you to be careful when using the CInt() function because when the argument's decimal portion is .5, this function rounds to the nearest even number. There's something else about CInt() you'll want to know. CInt() always returns an Integer data type. Other functions of this type, such as Fix() and Int(), return the same data type as the passed argument.

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

CHECK FOR NOTHING

Most of us are familiar with the habit of clearing an object variable when we're done with it, using Nothing. Although checking objects for their current state is less common, sometimes it's necessary. However, you can't use the equals operator to check for Nothing, the same way you use it to assign Nothing. For instance, the statement

Set obj = Me

assigns the current form to the object variable, obj, and uses the equals operator to do so. When checking an object, you must use the Is operator in the form

If obj Is Nothing Then ...

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

CANCEL EXCEL PRINT TASK

You won't always want users to be able to print data--confidential data is an example. One possible solution is to remove the Print button from the toolbars and the Print command from the File menu, but there's also a VBA solution. Add the procedure

Private Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True End Sub

to the ThisWorkbook module. This procedure also inhibits the Print Preview feature.

A user can disable this macro by opening the workbook with macros disabled.

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

USING NAMED ARGUMENTS

One of the more difficult aspects of working with functions (and sometimes procedures) is remembering all the arguments and the order in which you must include them. In addition, when reviewing the statement, you can't really tell just what the arguments mean, so debugging can also be difficult unless you have a photographic memory. That's why named arguments are such a great addition to VBA. Instead of writing

DoMyWork arg1, arg2, arg3, arg4

you can use named arguments in the form

DoMyWork arg1: value1, arg2: value2, arg3: value3, arg4: value4

Of course, in a real example, argx would be fairly descriptive of the argument's purpose.

If you omit an argument, you don't have to accommodate that omission by remembering an extra comma to denote its absence. For instance, the function

DoMyWork arg1, , , arg4

where arg2 and arg3 are optional becomes

DoMyWork arg1: value1, arg4: value4

In addition, order no longer matters. The next statement is just as valid as the previous:

DoMyWork arg4: value4, arg1: value1

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

THE VERSION PROPERTY

Whether you need to know the current version of VBA or the actual application, you can depend on the Version property. Specifically, when checking for the current version of VBA, run the following statement in the Visual Basic Editor's Immediate window:

Debug.Print Application.VBE.Version

If you're running Office 2000, it should return 6.00. To check on the actual application, run the statement

Debug.Print Application.Version

Office 2000 applications will return 9.00.

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

COPY AN ENTIRE FOLDER

Jim D. submitted this excellent tip for copying the contents of an entire folder--avoid VBA code and use the Microsoft Script Runtime library. The following procedure requires just a few short lines to accomplish what once was a fairly complex task:

Function CopyTest(source As String, destination As String) Dim obj As Scripting.FileSystemObject Set obj = New Scripting.FileSystemObject obj.CopyFolder source, destination End Function

If the destination folder doesn't exist, the CopyFolder method creates it, but the procedure will return an error if the source folder doesn't exist.

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

WRAPPING MACRO

Matt M. sent us this quick and easy wrapping macro. Simply select an Excel cell or range and run the macro to wrap text. By wrap, we mean the text stops at the right cell border and wraps to the next line instead of extending past the border. You'll find this format in the Alignment tab--right-click the cell or range, choose Format Cells, click the Alignment tab, and look for the Wrap Text option in the Text control section.

This macro toggles the format off and on. Once you've applied it, you can just as easily remove it by selecting the cell or range and running the macro a second time. In addition, we found the macro also works on noncontiguous ranges.

Sub Wrap() Dim aCell As Range For Each aCell In Selection aCell.WrapText = Not aCell.WrapText Next aCell End Sub

Thanks for sharing the macro, Matt!

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

WORK SLOWDOWN

You can suspend VBA code in several ways, but probably the simplest is to use the API Sleep function. Unlike many APIs, the Sleep function is easy to use. Simply drop this declaration into a module:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Then, call the function in the form:

Sleep numberofmilliseconds

For instance, the statement

Sleep 1000

would suspend your code for 1,000 milliseconds.

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

WORD FILENAMES

You may know that the WindowName() function returns the name of the current document (in Word). It also includes the file's extension--.dot, which may not be what you want. If you'd like to omit the extension, use this code: strName = WindowName() bytExt = InStr(strName, ".") strName = Left(strName, bytExt - 1) MsgBox strName ' display the name

The first line assigns the result of the WindowName() function to the strName variable. The next line finds the dot character in strName and assigns that character's position to bytExt. Now we can get rid of the extension by using the Left() function to return all the characters from the beginning of strName up to the dot character (bytExt - 1). The last statement simply displays the results.

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

TOGGLING IN A BOOLEAN STATE

Boolean states are common in code--whether you're working with Yes/No, On/Off, True/False, or -1/0. Almost anytime you're in this type of either/or situation, you can probably use toggling code. In other words, you can use one statement to express both states by adding a Not operator. For instance, if you want to switch back and forth between enabling a control, you might use a statement in the form

control.Enabled = Not control.Enabled

The result of executing the statement is the reversal of the button's enabled state before executing the code. In other words, if the button is enabled before you run the statement, it will be disabled after you run the statement, and vice versa.

This type of toggling code will work with all kinds of situations--just make sure you're working in an either/or situation.

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

TAX TIME!

No, it's not April yet, but if you work with payroll withholding taxes, you might want to check out Boon Docks, Inc. at

http://www.taxupdate.com/software/index.html

You can download a free evaluation copy of TaxControls--an ActiveX DLL that you can drop into almost any VBA application. This software provides current tax tables and quick, easy access to those tables. If you decide to purchase the software, updates are sent automatically by email as required.

Don't download anything from the Internet unless you have current virus protection installed on your system.

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

STANDARD DIALOG BOXES

Several dialog boxes are standard across the Office suite. For instance, Open, Save, and Print dialog boxes are practically identical throughout the applications. Besides being easy to use, they're also readily available programmatically by calling the Dialogs object. A simple example is offered in the procedure below. This procedure opens the Show dialog box to the default folder and automatically limits the task to the .htm and .html files in that folder. At this point, you carry on as you normally would by specifying a file and clicking Open.

Function ShowOpenDialog() Application.Dialogs(xlDialogOpen).Show "*.htm" End Function

Our example works in Excel. You'll need to check your application for the appropriate dialog constants.

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

SCHEDULING NO WORK FOR THE WEEKEND

Many scheduling tasks include only weekdays when counting the number of working days between two dates. There are a lot of ways to determine whether a particular date falls during the week or on the weekend, but one of the easiest is the following:

dteWday = WeekDay(date) Mod 6 = 1

Keep in mind that this statement relies on VBA's first day of the week default, which is Sunday.

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

NO RECORDCOUNT

The Recordset object does have a RecordCount property, but if you're using ADO you should know it doesn't always work. Specifically, if you define your Recordset object with a forward-only cursor (CursorType:=adForwardOnly), the RecordCount property will always return -1, regardless of how many records it contains. If you think you might need a record count, create a keyset (CursorType:=adOpenKeyset) or static (CursorType:=adOpenStatic) recordset.

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

NAMING EXCEL SHEETS

Excel's guidelines for naming sheets differ a bit from the rules governing naming workbooks. You can use a few of the restricted characters when naming a sheet, but you're limited to just 31 characters. Gustav B. sent in a user-defined function for naming Excel sheets. After soliciting a name from the user, he calls this function to make sure the entry doesn't contain any forbidden characters:

Function TrimExcelSheetName(ByVal strSheetName As String) As String ' Replaces characters in strSheetName that are ' not allowed by Excel in a sheet name. ' Truncates length of strSheetName to bytSheetNameLen. ' 2000-12-07. Gustav Brock, Cactus Data ApS, Copenhagen On Error Resume Next Const cstrInValidChars As String = "\/:*?WEBMASTER" Const cstrReplaceChar As String * 1 = "-" Const cbytSheetNameLen As Byte = 31 Dim bytLen As Byte Dim bytPos As Byte Dim strChar As String Dim strTrim As String strSheetName = Trim(strSheetName) bytLen = Len(Left(strSheetName, cbytSheetNameLen)) For bytPos = 1 To bytLen Step 1 strChar = Mid(strSheetName, bytPos, 1) If InStr(cstrInValidChars, strChar) > 0 Then strChar = cstrReplaceChar End If strTrim = strTrim & strChar Next bytPos TrimExcelSheetName = strTrim End Function

Basically, the function cycles through the first 31 characters in the user's entry, replacing each occurrence of the characters \, /, :, *, ?, [, and ] with a hyphen character (-). You can substitute any valid character for the hyphen character--even an empty string (""). Thanks for the help, Gustav!

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

MSGBOX AS ARGUMENT

Most of the time we see the MsgBox statement used alone in the form

MsgBox prompt

where prompt is the message the statement displays. However, you can also use MsgBox as the argument of another statement. For instance, the following statement displays a message box if the specified condition is False:

Iif(condition, trueaction, MsgBox("Sorry, that didn't work"))

You're not limited to the Iif statement, of course, but it's probably one of the more likely candidates for this type of nesting.

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

Visual basic for applications

CONCATENATING SPACES

MORE ON CONCATENATING SPACES

Send mail to CompanyWebmaster with questions or comments about this web site. Copyright © 2009 HALO Computer Technology Last modified: 03/11/09

Send mail to CompanyWebmaster with questions or comments about this web site.
Copyright © 2009 HALO Computer Technology
Last modified: 03/11/09