For best results: this site requires that cookies be enabled for proper operation - see Legal Page for more info
Starting December 1, 2006 Techsinfo.be will no longer be available please update your links to http://techinfo.e2uhosting.net Thank you
|
For best results: this site requires that cookies be enabled for proper operation - see Legal Page for more info Starting December 1, 2006 Techsinfo.be will no longer be available please update your links to http://techinfo.e2uhosting.net Thank you |
|
Select Any of These |
Visual basic for applicationsLAST UPDATED: 22 May 2006 19:19:24 +0200 CONCATENATING SPACESWe 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 SPACESIn 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 NULLS In our last tip, we talked about Null values as values that are unknown or don't apply. We used phone numbers to illustrate our point. If the phone number is unknown or if someone doesn't have a phone, you'll leave the phone field blank for that person. However, if you know the person has no phone, you might want to consider an alternative to Null--to avoid confusion. When there is no phone (versus no phone number), consider using an empty string ("") or an actual string value, such as "no phone" or "N/A", so you can readily tell the difference between an unknown phone number and no phone. ---------------------------------------------- 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" or frm.RecordSource = "table" where frm is an object variable or the name of the form. (You can't change a form's Record Source property in run-time.) ---------------------------------------------- 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) or 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 a |