Visual Basic for Applications
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.
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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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

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, sponsored by ECMS and Micro Eye. The URL for this site is 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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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

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.

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

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.

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.)
It's rather difficult to add an item to a list box on the fly. If you need this functionality but would rather skip the extra work, use the Microsoft Forms list box instead of the native list box. Microsoft Forms controls--and there are several to choose from--are more flexible than many of the native controls. To insert a Microsoft Forms 2.0 ListBox control into your Access form or userform, in Design View, choose Insert, Microsoft Forms 2.0 ListBox, then click OK. Or click More Controls on the Toolbox and select the control from the resulting list. Once you insert the control, you can use that control's properties and methods just as you would a native control's properties. One of the Microsoft Forms 2.0 ListBox methods is AddItem, which uses the syntax object.AddItem "item", index This procedure will add two items to the list box when the form is opened: Sub Form_Open() lst.AddItem "red" lst.AddItem "blue" End Sub To remove an item from a list, use the RemoveItem method in the form object.RemoveItem index where index is the position of the item you want to remove (the first item equals 0).
You can use a form's (or userform's) Load event to add or modify a control's tip text. Depending on the circumstances, you may want to modify all of the form's controls or just one. To do so, simply add a statement in the form Me!controlname.ControlTipText = "new control tip text" to the form's Load event. For instance, if you want a control named cboNames to display the following text as its control tip, "Choose a name," you'd use the following statement Me!cboNames.ControlTipText = "Choose a name"
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 lists FrontPage resources that should help get you started in your joint venture between FrontPage and VBA.
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.
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.
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.
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.)
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.
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.)
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 "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.
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.
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.
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.
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.
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)
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.
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.
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.
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#
If you've converted any DAO code from Access 97 to Access 2000, you may have run into trouble with the Database and Recordset object types, because they've both been replaced in ADO. That means the simple statements Dim dbs As Database Dim rst As Recordset would both return errors if you try to run them in Access 2000. Instead, you'll want to use the ADODB object in the form Dim dbs As ADODB.Connection Dim rst As New ADODB.Recordset Now, if you want to keep your DAO code or use DAO code in your Access 2000 modules, you should reference a DAO library. In the VBE, choose Tools, References; select a Microsoft DAO Object Library; and then click OK. Once you reference the correct library, the module will work with the DAO object references Database and Recordset.
When working in a VBA module, there are several keyboard shortcuts you can use to reposition the cursor. One you might not know about is Ctrl-Home. This keystroke combination will reposition the cursor at the very top of the module window. In other words, this keyboard shortcut positions the cursor at the very first position in the module--before any other characters. In most modules, this shortcut will position the cursor before the Option Explicit statement in the module's General Declarations section.
In a previous tip, we showed you how to add items to a Microsoft Forms 2.0 ListBox control. When you're using the AddItem method, you can also specify the position of the new item within the existing list. For instance, we used this simple procedure to add two items to a list: Sub Form_Open() lst.AddItem "red" lst.AddItem "blue" End Sub If you wanted to add the item "white" between the already existing items, use the statement lst.AddItem "white", 1 NOTE: The Office 2000 service release 1 is out and available for download at Unfortunately, this release seems worse than a little buggy -- it's causing real havoc on some systems, so you might want to consider waiting until all the kinks are worked out.
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.
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
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.
The most recent versions of the Visual Basic Editor sport new toolbars. Now there's a Debug toolbar, which includes many commands that were on the Standard toolbar. The Edit toolbar includes commands for writing better code. A UserForm toolbar has quick formatting tools. To open one of these new toolbars, simply right-click any open toolbar and make a choice from the context menu.
DAO 3.5 offers a new client/server connection mode. You can use this connection--ODBCDirect--to establish a connection with an ODBC data source, without hitting the Jet engine. There are a few other advantages when using ODBCDirect: - Your code runs faster and more efficiently since you don't load the Jet. - You can run asynchronous queries. - Batch changes are cached locally and sent to the server as a single batch.
When referring to a control in a form, you usually reference the form or use the Me identifier--even when the code is running in the form's module. For instance, if you want to reference a control named txtNames in a form named frmEmployees, you'd use the form frmEmployees!txtNames or Me!txtNames If you're adding code to an Outlook form, you can omit the form reference and simply refer to the control, as long as you're working in that form's module. If the code is in a separate module or behind another form, you must include the form's name in the reference.
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
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.
A few tips ago, we mentioned that you could use the Object Browser to learn the different intrinsic constants and their literal values. If you've already added a constant to your code and you'd like to learn its literal value, there's an easier way than launching the Object Browser. Right-click the constant and choose Quick Info from the context menu. VBA will display the constant and its literal value.
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.
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.
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 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.
When declaring an object variable, it's usually best to be as specific as possible. That's because the more specific you are, the more flexible your code is. Most objects have unique properties and methods, and unless you're specific about the object type, you may not be able to use those properties and methods in your code. Anytime you can't be specific because you don't know the object type you may be working with, you can use the Object data type.
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.
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 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.
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.
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.
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.
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.
You probably know that you can use a query's Top property to return only a specific number of records, instead of all the records. For instance, after sorting all your orders by the total value, you may want to select only the top ten sales. To do so, you'd specify 10 as the query's TOP property. You can do the same thing with SQL using the TOP keyword in the form SELECT TOP n field(s) FROM table More than likely, you'll need to also include an ORDER BY clause. For instance, to select the top ten best sales from a table named tblSales, you might use a statement similar to the following: SELECT TOP 10 * FROM tblSales ORDER BY TotalOrder where TotalOrder is the name of the field with the total sales value for each order. If you don't specify an argument for ORDER BY, SQL defaults to ascending order. When using the TOP keyword, don't confuse it with a value, as in the greatest or highest values. The keyword simply returns the first records in the query's results. You might be wondering how to return the last n records in a query, but there isn't one. The trick is to use the TOP keyword and sort the records in the opposite order--usually that means sorting in descending order. As a result, the records are reversed. Consequently, the TOP query still returns the first records in the query result. If you decide to use SQL to return the last records in a query, use a statement in the form SELECT TOP n field FROM table ORDER BY table.field DESC;
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.
Setting an object to Nothing once you're done with it is a good idea because it frees up memory. However, if you use the New keyword in your declaration statement, as in Dim obj As New objecttype you can't set that object variable to Nothing. Well, you can, but VBA will immediately create a new instance. The solution takes an extra step, but it's worth it in the long run. Don't use the New keyword in the declaration statement, as in Dim obj As objecttype Set obj = New objecttype Using this method to declare and define your object variable will allow you to terminate it later.
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.
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.
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 ("").
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.
Referring to colors in your code can be a bit of a nuisance because you must remember the color's corresponding integer. If you're working with the Windows standard colors, your work is made easier by a few intrinsic constants. Instead of looking up a color integer, you can simply use one of these constants: Black vbBlack White vbWhite Red vbRed Yellow vbYellow Blue vbBlue Green vbGreen Cyan vbCyan Working with these constants should prove much easier and more efficient than using the color's integer values.
Most developers use Debug.Print to display information during the debugging process. For the most part, you'll want to delete all these statements before distributing the application. A simple Find task in all your modules is a quick and easy way to find all of your Debug statements. If you miss a few, no harm is done, as the user will probably never even know they're there. Breakpoints are helpful too, since they automatically suspend the code at a particular point so you can review all the variables, and so on, before continuing. Unfortunately, it's easy to forget about a breakpoint, and they aren't as benign as Debug statements. A forgotten breakpoint can stop your application, and the user will have no idea what's wrong. An easy way to remember to remove all your breakpoints is to add a Debug statement after each breakpoint. Use the Debug.Print statement to print a message that the stop is intentional and your message is simply a reminder to remove the breakpoint at the appropriate time.
If a module is dedicated to string comparison and you want all comparisons not to consider letter case, you can avoid a lot of special coding with one simple statement. Include the Option Compare Text statement in the module's General Declarations section. If, on the other hand, you do want your comparison code to consider letter case, you should use the Option Compare Binary statement. (This is the default if there's no Option Compare statement.) Keep two things in mind when working with the Option Compare Text statement. First, it doesn't work in VBScript. Second, the results of your comparison may depend heavily on your Windows language settings.
An easy way to advance through a recordset is to use the Do...Loop statement. For instance, the following Do...Loop stops at each record in a recordset named rst and adds the string "OK" to the Check field. Do Until rst.EOF .Edit !Check = "OK" .Update .MoveNext Loop (If you're using ADO, you can omit the Update method.) One problem that we see occasionally is the accidental omission of the MoveNext statement. The Do...Loop statement won't automatically select the next record at the end of its task. You must do that by including the MoveNext statement. Without that statement, your statement will run forever.
In our previous tip, we talked about the difference between Collections and Containers in respect to open objects. Collections contain only open objects while Containers contain all the respective objects in the application. However, there's a new kid in town--the AllForms collection. This collection contains an AccessObject object for each form in the CurrentProject or CodeProject object. Function ChangeProperty() Dim obj As AccessObject Dim dbs As Object Set dbs = Application.CurrentProject For Each obj In dbs.AllForms DoCmd.OpenForm obj.Name, acDesign changing code... DoCmd.Close acForm, obj.Name, acSaveYes Next obj End Function
Chasing down a bug in your code is never fun or easy. In a perfect world, you'd find these bugs before they ever hatch, but that's just not practical. There are a few guidelines you can follow that may help you prevent error prone code: - Break your code into small, manageable pieces; don't write functions and subs that are hundreds of lines of code. - Comment abundantly, and make those comments count. - Explicitly declare all your variables; a misspelled variable can be very difficult to find. - Adopt and consistently use a naming convention.
If you get a ByRef Value Type Mismatch error, you might know what the error is but have a hard time finding its cause. Generally, this message means that a passed argument doesn't match the variable's declared data type. The most logical place to begin your search is in your Dim statement. Make sure that the variable's declared data type matches the argument's data type in the calling procedure. The following procedure declares the variable i as an Integer, then passes i to the procedure DoThisNow(). Unfortunately, DoThisNow() expects a string--as you can see in the function's name statement. When MyWork() tries to pass DoThisNow(), the integer variable DoThisNow() will return a mismatch error. (Compiling this code should catch the error.) Function MyWork() Dim i as Integer DoThisNow i End Function Function DoThisNow (arg As String) End Function The only way to resolve this error is to declare i as a String or Variant or to change the argument's declaration to an Integer or Variant.
We've offered several tips about arrays over the past few months, including instructions for changing an array's dimensions. Specifically, you can declare an array and then use the ReDim statement to set the array's dimensions. You might take this explanation to mean that you can change an array's dimensions, but that's not quite true. Using ReDim, you can only declare a dynamic array's dimensions--you can't ReDim a fixed array. Let's revisit the difference between a fixed and a dynamic array. First, a fixed array specifies the array's dimensions during the declaration process using the form Dim arr(1 To 3) As Integer When you declare an array in this manner, you can't change its dimensions. On the other hand, a dynamic array doesn't specify the dimensions--it simply declares the array in the form Dim arr() As Integer Once you know your array's dimensions, you use ReDim to specify those limits in the form ReDim arr(1 To 4) As Integer In addition, you can change the dimensions for a dynamic array simply by using the ReDim statement again.
Have you ever received this error: Expected end of statement It's probably the one I receive the most and I know exactly what it means, which makes the error easy to find and correct. Unfortunately, this error message is a bit obscure, so unless you know its meaning you may have trouble finding the cause of the error. Almost always, this error points to a missing delimiter. For instance, the statement strFullName = FirstName & " " & LastName & " will produce this error because there's an extra (or missing) double-quotation mark--the one at the end of the statement. To correct this statement, we can delete the extra quotation mark: strFullName = FirstName & " " & LastName or we can add another string and another quotation mark: strFullName = FirstName & " " & LastName & " " This last example is kind of silly since there's no good reason to add an extraneous space to the end of a person's name; in fact, doing so might cause trouble later on.
If you want to loop through all the forms in your database, be sure to specify the Forms Container and not the Forms Collection. Collections contain only open forms. This is true of all Collections and Containers. The following procedure will loop through all the forms in the current database, open that form, change a property, and then close the form. You'll want to use this procedure when you want to change the same property in all your forms or anytime you want access to all the forms in your application, not just the opened forms. Function ChangeProperty() Dim db As Database, cnt As Container, doc As Document Dim frm As Form Set db = CurrentDb Set cnt = db.Containers!Forms For Each doc In cnt.Documents DoCmd.OpenForm doc.Name, acDesign changing code... DoCmd.Close acForm, doc.Name, acSaveYes Next doc End Function Our next tip will offer an updated version of this procedure for Office 2000 users. (This procedure will work in Access 2000.)
You probably know that you can launch Word from inside another Office application. There's a lot of theory to the necessary code--accessing the right objects, and so on. If you'd just like to do the work and you're not interested in the science behind the task, you can use the following procedure to open a Word document: Sub OpenDocument() Dim wrd As Object Set wrd = GetObject(, "Word.Application") wrd.Visible = True Documents.Open "documents complete path" End Sub This procedure will work with just a wee bit of tweaking--simply specify your document's complete pathname in the Documents.Open statement.
VBA Date/Time fields or variables can handle a large range of date values. However, Outlook forms are a little more limited. A date on an Outlook form must fall between April 1, 1601, and August 31, 4500 (inclusive of both dates). Fortunately, this isn't a problem for most users. Unfortunately, we don't know of an easy workaround.
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.
Most tips are from TipWorld - :The Internet's #1 Source for Computer Tips, News, and Gossip