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

For Applications

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.

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

ADDING MULTIPLE CONTROLS THE EASY WAY Generally, you add controls to a userform by clicking the appropriate control button on the Toolbox and then clicking inside the userform. If you want to add a second (or several) controls of the same type, you probably return to the Toolbox and click the same button before inserting the additional control in the userform. Fortunately, this repetitive task isn't necessary. If you want to add several controls of the same type to a form or report, simply double-click the control button instead of using a single click. Double-clicking a control button temporarily selects that control, so you can insert as many controls as you like without clicking that button again on the Toolbox. To reset the current tool selection, click another control button or the Selection arrow on the Toolbox.

NEED A CALCULATOR? Normally, we like to share expressions and formulas that you can use in VB. 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 VB projects using Microsoft Web components.

SPEAKING OF CALCULATIONS In our previous tip, we told you about a Web site with downloadable calculators for all kinds of tasks. However, most of the time, you'll have to rely on your own expressions to get the results you need. Occasionally your expressions return errors and there are several reasons why VB can't evaluate your expression. Here are a couple of quick checkpoints to review before you start pulling out your hair: - Make sure you've included the right 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.

SPEED THINGS UP BY CHANGING YOUR COMPILE OPTIONS If you have the Professional or Enterprise edition of Visual Basic, you can speed things up a bit by changing the method of compilation to native code. Both versions offer two compilation options: Compile to P-Code, Compile to Native Code. P-Code (psuedo code) is a go-between for your application and your computer's processor. If you're using P-Code, Visual Basic translates each p-code statement to native code when you run the program. Compiling directly to native code eliminates this extra step.

UNLOADING FORMS You may think you've quit an application, but unless all the application's forms are closed, the application will think it's still running. If your application has only one form, you can add a procedure to the control and event that quits your application. For instance, the following procedure uses a command button named cmdQuit: Private Sub cmdQuit() Unload Me End Sub If you have multiple forms (more than one), you'll need something a little more complicated. The procedure below will loop through the Forms collection and unload each form: Private Sub Form_Unload (Cancel As Integer) Dim I As Integer For I = Forms.Count - 1 to 0 Step - 1 Unload Forms(i) Next End Sub Attach this procedure to the main form's Unload event. That way, when you close it to end your application, the code in the unload event will close any forms that remain open.

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.

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

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.

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.

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.

VIEWING MORE THAN YOU THOUGHT Do you sometimes wish you had two monitors and two pairs of hands when working in the 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 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

THE FASTEST COUNT

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 

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 

COPYING TOOLBAR BUTTONS It's fairly easy to copy a button to a toolbar using the Customize dialog box. Simply right-click a toolbar, select Customize, click the Commands tab, find the appropriate category, and then locate the button you're looking for in the Commands control. If, however, the button is already on a toolbar, there's an easier way. Simply open both toolbars--the one that already has the button and the one you want to copy the button to. Then, hold down the Alt button and drag the button from one toolbar to another. It really is that easy!

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 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, first locate the appropriate book in the Contents tab. Next, click the Print button. In the Print Topics dialog box, click Print the Selected Heading and All Subtopics. Finally, click OK twice. Furthermore, VB prints the topic continuously, rather than printing each heading on a separate page.

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 VB's 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 and click the Paste button on the Standard toolbar, or press Ctrl-V.

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.

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

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 you recordset is large, this is inefficient and unnecessary, because the SQL Count function is faster. For instance, the code strSQL = "SELECT Count(*) FROM table3" Set rst = db.OpenRecordset(strSQL) Debug.Print rst(0) is faster than Set rst = db.OpenRecordset("table", dbOpenDynaset) rst.MoveLast Debug.Print rst.RecordCount Granted, 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 quickly the number of records in a recordset. If you're working with a Jet database and 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 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 (in a Jet database).

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.

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

ADDING AN ITEM TO A LIST BOX To fill a list or combo box, you use the AddItem method. Did you know that you can add an item to a specific position within the list? You can if you include the method's index value in the form List1.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 List1: Private Sub Form_Load() List1.AddItem "Two" List1.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 Form_Click() List1.AddItem "One", 0 End Sub (Just remember that the index values begin with 0, not 1.)

CANCEL UNLOAD We've talked about using the Unload method to close all open forms before quitting an application. However, you can override that method using the form's QueryUnload event. This is the very last event triggered when you unload a form. If you change this event's Cancel argument to True, VB won't unload the form. This is a good way to avoid losing unsaved data. Simply set this argument to True right before you enter data and change it to False once the data is saved.

ENUM DATA TYPES In our last tip, we showed you how to create your own enumeration constant. Although it appears that you're creating a unique data type, you really aren't. Visual Basic treats all constant values in an enumeration as Long integers. If you assign a decimal value to an enumeration constant, Visual Basic will round that value to the nearest Long integer.

WHEN CANCEL DOESN'T WORK In our last tip, we talked about using a form's QueryUnload event to cancel an Unload method. Unfortunately, setting the event's Cancel argument to True is not a foolproof strategy. If you use the End statement to quit your program, VB won't trigger your form's QueryUnload event. The same is true if you click the End button or choose End from the Run menu in the development environment.

WORKING WITH ENUMERATIONS An enumeration is a special type of constant that automatically assigns values to its members. Using an enumeration is an easy way to work with a set of related constants. For instance, VB has a built-in enumeration named vbDayOfWeek, which contains constants for each day of the week. To see how it works, open a module and type vbDayOfWeek followed by a period. VB will automatically display a list of vbDayOfWeek's constants. To create your own enumeration, declare an enumeration type using the Enum statement in the Declarations section of a standard or public class module using the syntax Private Enum WorkDays or Public Enum WorkDays Then list the constant names. VB will assign the value 0 to the first constant named in the list and increase the value by 1 for each subsequent constant in the list. For instance, the enumeration Private Enum WorkDays Monday Tuesday Wednesday Thursday Friday End Enum would assign the values 0 through 4, respectively, to the items listed. To use the enumeration, declare a variable using the enumeration in the form Dim DayOff As WorkDays DayOff = Monday

CONTROL LIMITS Few of us ever reach the limit for controls on a form, which happens to be 254. Actually, that's not technically correct. Each form is limited to 254 control names. However, one control array serves as only one control name because all the controls in an array share the same name. Therefore, you can stretch that 254-control name limit quite a bit--as long as your resources hold out.

PRINTING A FORM'S DESCRIPTION In our last tip, we told you how to view a text version of a form or project in a text viewer. Once the text version is in your word processor, you can print it. You might be interested to know that there's a quicker way to print a form's description. With the form current, choose Print from the File menu. In the Print What section, select the Form As Text option, then click OK. VB will send the text version of your form directly to the default printer.

PROBLEM FORM? If VB encounters an error while loading a form, VB will create a log file to report that error. Once the form is loaded, VB will display a message telling you that an error log file was created. That log file has the same name as your form, except VB uses the extension .log instead of .frm. For instance, if your form's name is frmEntry.frm, VB will name the log file frmEntry.log. To learn more about the error that occurred, simply view the form's log file. If you encounter an error during the next loading process, VB will overwrite the prior error record, so the log doesn't keep an historical perspective.

VIEWING A FORM'S DESCRIPTION Most VB 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 or .vbp file in your word processor the same way you'd open any file to display a text version of your form that contains the following: - The version number of the file format - The form's description - The form's attributes - The form's VB code The following is the form description of a form with one command button: VERSION 6.00 Begin VB.Form Form1 Caption = "Form1" ClientHeight = 3195 ClientLeft = 60 ClientTop = 345 ClientWidth = 4680 LinkTopic = "Form1" ScaleHeight = 3195 ScaleWidth = 4680 StartUpPosition = 3 'Windows Default End Attribute VB_Name = "Form1" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = False Option Explicit Private Sub Form_Click() MsgBox Dir ("C:\rt\tipworld") End Sub

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? There are several reasons this might happen, but one you might not consider is the order of your statements. You see, 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.

A FEW CODE LIMITATIONS VB is a very generous development tool. For the most part, few developers will ever tip the scales on any of the VB limitations. We mentioned in an earlier tip that a form could have as many as 254 names, but no more than 254. Almost all controls and objects have limitations. Even modules have limitations, although we doubt many developers ever cause VB to tilt in this particular area. For the record, module limitations are as follows: - A module can contain only 65,534 lines of code. - A single line of code can consist of no more than 1,023 bytes. - You can insert only 256 blank spaces before a line of text. - Each complete line of code can contain no more than 25 line-continuation characters.

ACTIVEX HELP In our last tip, we told you it's fine to use your registered ActiveX controls in your own applications, even if you didn't pay for them outright. Unfortunately, unless you have the documentation and Help files that you get when you 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 You may not need to purchase an ActiveX control to have it. You see, any application that uses an ActiveX control also 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 see what's available 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.

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.

IMAGE VERSUS PICTURE BOX You can speed up your application by reducing the size of your application. Easier said than done, right? There are many tricks you can use to reduce the size of an application, and here's an easy one. Don't overuse the Picture Box control. That control has special qualities: It can contain other controls, and it also provides graphics methods. Consequently, the Picture Box control also requires more memory. Whenever possible, use the Image control for simpler tasks such as displaying pictures and responding to Click events and mouse actions. The less complicated the task, the fewer bells and whistles your control needs. By following this simple rule, you'll save on resources.

MULTIPLE LINES IN TEXTBOX Most VB users will already know this short tip. However, new users who are familiar with VBA and the Office controls may be confused by the VB TextBox control. Both the Office and the VB TextBox control can display multiple lines of text. Using the Office control, you must press Ctrl-Enter at the end of each line of text to position the cursor at the beginning of a new line. You don't have to do this using the VB control. If you want the text to wrap in a VB TextBox control, simply set the control's MultiLine property to True. If you want to force a new line in the VB TextBox control, just press Enter.

NO SECRET CODE IN THOSE COMMENTS All developers know they're supposed to comment their code, and most do. Unfortunately, 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, make your comments whole sentences. As a final thought, avoid abbreviations unless they're universally known. If you end up revamping the application a few months or a year down the road, you'll be glad you took the extra effort with your comments.

REMOVING A FORM OBJECT You probably know that you should unload a form to free up resources once you're done with it. However, if you're using a collection to track forms, do you know how to release those resources? Unloading the form just isn't enough. Unloading the form won't remove the reference from the collection. You see, as long as the reference to the form exists, you can't reclaim that memory. After you unload the form, use the collection's Remove method to delete the form's object reference from the collection. Only then will you reclaim that memory.

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 display the active form's name, you might use a procedure similar to the following: Private Sub cmdName_Click() MsgBox formname.Name End Sub where formname represents the active form. However, the statement Me.Name is more efficient. The Me identifier restricts VB's search to the form that's running the code.

TYPE MISMATCH Do you hate the Type Mismatch error as much as I do? Well, any error is annoying, but this one seems to crop up way too often. For the most part, you won't see this error because you forgot to convert something properly--VB's pretty good at figuring out most data type assignments itself. 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 VB expected an expression. For instance, you can't print an object with the Debug object. These three mistakes are probably the most common reasons VB 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 that 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 you absolutely must. 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.

USING THE FILE OBJECT Did you know you could work with files as objects? You can, but you may need to reference the SCRRUN.DLL library (Windows\System) first. If you're running Windows 98 or NT with the Option Pack, or if you've installed VBA6, the library should already be installed. If you're working with VBA5 or Windows 95, you need to install the dll. If you can't find it on your system, you can download the file from http://msdn.Microsoft.com/scripting/ Once you know you have the dll on your system, create a reference to it by choosing References from the Project menu and choosing Microsoft Scripting Runtime. If you use the Scripting Runtime on a distributed product, make sure each system that runs your program has the dll installed.

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 an application, check your references by choosing References from the Project menu. Check the Available References list box for the name of the application you want to borrow from. If it's listed, check it and click OK. Once you've referenced the application, open the Object Browser to see which of the application's objects are available to you.

WORKING WITH ICONS AND BITMAPS If you don't want to use text to convey a control or form's purpose, you can always use an icon or bitmap. In fact, this is very common, and there are plenty of both to choose from. However, you might want to follow these two simple rules when you consider substituting text with either: - Make sure the icon or bitmap you choose is a standard. Anything but a universally known standard may fail to get your point across. - Avoid using icons or bitmaps that include text. For one thing, what's the point? For another, they take more time to draw.

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.

CONCATENATION OPERATORS If you convert older applications, you may run into the plus sign used as a concatenation character. Older versions of VB (and BASIC) used the plus sign before the ampersand became the prevalent concatenation operator. VB 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 VB still supports the plus sign. Each new version usually brings replacements, and as a rule, VB 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 someday, VB 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.

DIFFERENT VIEWS FOR THE IDE By default, VB's IDE is in what's known as a multiple document interface (MDI) view. This view shows all the distinct windows within one large IDE window. If you find this setup difficult to work in, try switching to single document interface (SDI) view. All the windows are still displayed; however, they exist independently of each other--there is no larger containment window. To change from MDI view to SDI view, first select Tools, Options. Then, click the Advanced tab, select SDI Development Environment, and click OK. The next time you run VB, it will display the different IDE windows in SDI view. There's no right or wrong to which view you work in--it's a matter of personal preference.

KEEPING UP WITH FORMS A lot of forms in the same application can be a little difficult to keep up with. One way to keep track of your forms while testing and debugging is to check for the number of open forms. You can do so by pausing the current form (click Break) and then entering the statement ?Forms.Count in the Immediate window. This statement will return the number of loaded forms, including the paused form.

LOOK MA! NO ARRAY! In earlier versions of VB, you added controls programmatically using control arrays. This meant the form had to have at least one control for you to create and add others at runtime. If you're using VB 6.0, you're no longer limited by this requirement. You can add a control at runtime using the Controls collection Add method. The code below is a simple example of how to add a command button to a form using 6.0. Option Explicit Private WithEvents cmdButton As CommandButton Private Sub Form_Load() Set cmdButton = Controls.Add("VB.CommandButton", "Button") With cmdButton .Visible = True .Width = 3000 .Caption = "I'm the first." End With End Sub Sub cmdButton_Click() cmdButton.Caption = "Look Ma! No array!" End Sub When you run the form, VB will display a command button with the caption "I'm the first." in the upper-left corner of the form. When you click the command button, the caption will change to "Look Ma! No array!" Use the .Top and .Left properties to specify an exact position for the control.

MISSING SPACES We often concatenate variables when working with SQL statements. One of the easiest mistakes to make when working with variables and SQL is to omit a necessary space between the variable and the SQL text. It can also be very difficult to find because you're not really thinking about spaces--you're more likely to be concentrating on logic and syntax errors. For example, the simple statement "WHERE fieldname=" & variable works fine because SQL anticipates the spaces around the equal sign. However, the statement "SELECT * INTO" & variable & "FROM tablename WHERE tablename.fieldname =" & strCriteria & ";" won't work because of missing spaces. Specifically, the statement needs a space in the following places: - after the INTO clause and before the variable - after the variable and before the FROM clause The correct syntax is "SELECT * INTO " & variable & " FROM tablename WHERE tablename.fieldname =" & strCriteria & ";" If you've been writing SQL statements for a long time, you may have learned the hard way to check for these spaces first.

MOVING AROUND When working with database tables and recordsets, you may use the Move method to change the current record. Specifically, use this method's NumRecords argument to specify the number of rows you want to move. For instance, to move forward two records, you'd use the statement rst.Move 2 where rst is the name of your recordset. Similarly, to move backward two records, you'd use the statement rst.Move -2 If you use 0 as the Move method's argument, VB will retrieve the latest data from the current record. This behavior is helpful when you want to make sure that you retrieve the most recent data.

MOVING BACKWARD In our previous tip, we showed you how to move backward in a recordset by specifying a negative value as the Move method's NumRecords argument. This behavior is especially convenient when you're working with a forward-only recordset, because it allows you to move backward in an otherwise forward-only set of records. 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, VB will return an error. 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.

NEW PROPERTY FOR SLIDER CONTROL The Slider control (or Trackbar control) isn't new to VB 6.0, but the Text property is. This property will display a string in a ToolTip window--either just above or below the slider thumb. The setup is simple to use: - Enter the string you want to display in a ToolTip window as the Slider control's Text property. - Use the Slider control's TextPosition property to determine whether VB displays the ToolTip window above or below the slider thumb.

NEW WAY TO VALIDATE DATA VB 6.0's new Validate event and CausesValidation property simplify data validation. Before 6.0, you had to attach data-verifying code to the control's LostFocus event and then use the SetFocus method to keep the user from selecting another control. Unfortunately, this generally meant the user couldn't do anything at all until he or she entered the correct data. The Validate and CausesValidation properties are much more flexible than the previous solution. When you enter data, you fire the Validate event. If you set the event's Cancel parameter to True, the user can't select any other controls except controls with the CausesValidation property set to False if the entered data doesn't match the conditions in your Validate event code. That means the user has access to other controls even though the application is verifying data. In a nutshell, you'll use the Validate event to test entries and block access to other controls when data isn't appropriate. The exception will be the controls you want them to access, and you'll set the CausesValidation properties of those controls to False. Most likely a control used in this manner will display more information or allow the user to opt out of the form.

NOW IT'S A COMMENT, NOW IT'S NOT If you're moving to VB from Office and VBA, you'll be glad to know that commenting and uncommenting is automated with VB. It's much easier in VB to comment or uncomment a block of code, since you don't have to enter or delete each apostrophe character manually. To comment a block of text, highlight the text, then click the Comment Block button on the Edit toolbar. Similarly, to uncomment a block of text, highlight the text, then click the Uncomment Block button on the Edit toolbar. If you're a veteran VB user, you've known about this feature for a long time, but VBA users who are taking the plunge into VB will find this great news!

POSITIONING YOUR FORMS THE EASY WAY So much of what we do in VB is drag and drop--wouldn't it be nice if you could determine your form's position on screen with a simple drag-and-drop task? Well, you can if you open the Form Layout window in the IDE. To do so, simply choose Form Layout Window from the View menu. In response, VB will open the Form Layout window in the bottom-right corner of your screen. This window displays a simple monitor graphic and a representation of the active form in its current position. Set the form's position at runtime by simply dragging the form image around the Form Layout window. If you right-click the window, VB will display a shortcut menu. From this menu, you can easily set the form's startup position: - Manual: Allows you to move the forms around the Form Layout window manually. - Center Owner: Positions the form in the center of the monitor with the host or owner window underneath it. - Center Screen: Positions the form in the center of the monitor. - Windows Default: Positions the form in the top-left corner of the monitor.

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. It's that simple!

RESETTING THE TAB INDEX PROPERTY A control's TabIndex property determines that control's position in the tab order sequence. For instance, the control with a TabIndex of 0 is the first control to receive focus. When you next press Tab, your form will select the control with the TabIndex of 1, and so on. Initially, this value is relative to the order in which you add controls to your form. This means the first control you create has a TabIndex value of 0; the next control will receive a value of 1; and so on. However, it's common to move controls around during the design stage, so you'll probably need to update the TabIndex property for a few, if not all, of your controls once you've completed the form. A quick and easy way to reset the tab sequence is to select the control that should be last in the order and set its TabIndex property to 0. Then, select the next-to-the-last control in the sequence and repeat this process. Doing so will force the last control in the sequence (and the first control you set) to update its property to 1. Next, select the next-to-the-next-to-the-last control in the sequence and set its property to 0. When you do, the next-to-the-last control will reset itself to 1, forcing the last control to reset itself to 2. Continue in this manner until you reach the control that you want to be first in the order. At this point, all of the controls should be in order.

RETURNING A FORM'S NAME In our previous 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 by name, 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 0 with the value 1 and rerun the statement. Continue in this manner until you've identified all the loaded forms.

SEEING ALL THE TEXT In an earlier tip, we talked about setting a TextBox control's MultiLine property to True if you want the control to hold/display more than a single line of text. When you set this control's MultiLine property to True, you should also consider setting the ScrollBars property. Specifically, you should set the ScrollBars property to the setting 2--Vertical. That way, you can scroll through all the lines of text if the size of the control doesn't accommodate all the text. Otherwise, you may see only part of the data.

THREE TYPES OF DIVISION VB supports three types of division: floating-point division, integer division, and modulus. Floating-point division is what you learned in grade school--you simply divide one number by another and return an integer and a decimal value, when appropriate. For instance, the expression 10 / 4 will return 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 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 previous expression looks like 10 Mod 4 using modulus division, which will return the value 5.

ADDING LINE NUMBERS TO CODE You probably know you can comment your code using the apostrophe character or the REM statement. Did you know that you could number your lines of code? You can, and it's easy. Just add the number to the very beginning of each line of code. For instance, the following procedure Private Sub Form_Load() With cmdButton .Visible = True .Width = 3000 .Caption = "I'm the first." End With End Sub becomes Private Sub Form_Load() 01 With cmdButton 02 .Visible = True 03 .Width = 3000 04 .Caption = "I'm the first." 05 End With End Sub When numbering lines of code, make sure each number is at the beginning of the line. In addition, don't number the procedure's name or ending statement.

AVAILABLE FONT SIZES The Font Size tool on most Formatting toolbars lists sizes 8 to 72. Officially, you can specify a font size of 1 to 127 using the FontSize property. However, just because VB will allow you to specify a specific font size doesn't mean your printer can print it. When working with unusual font sizes, you should test the results to make sure your printer and the current font can accommodate that size.

LIST BOX ALTERNATIVES It's fairly common to store a record's identification value and a descriptive text field. For instance, if the list box displays employee names, the list box probably contains the employee names and the employee identification values. The list box displays the names because they're easier to recognize and work with. But the list box actually stores the identification value of the selected name and not the name. If you're using the standard ListBox, you might want to consider the Data ListBox instead. The Data ListBox provides a simplified method to accomplish the same thing. Simply set the DataField property to the descriptive text field (the employee name). Then, set the BoundColumn property to the record's identification value field (the employee's identification number). When you select an employee name from the control, the BoundText property will equal the record's identification value for that employee.

MEMOS SLOW THINGS DOWN When a data source contains a Memo field, you might want to consider where you display that field. You see, Memo fields tend to slow down your form's performance. I'm not suggesting that you not display them at all, but I am suggesting that you not display them on your main data form unless you absolutely must. Often, you don't need to see each record's memo data anyway. When this is the case, display the Memo field on another tab or a pop-up form so the user still has quick and easy access to that data.

MORE ON RESETTING ARRAY ELEMENTS In our previous tip, we showed you a quick way to force an array's elements to begin with the value 1. You enter 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

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

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

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, and the seed value 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 want 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.