Visual Basic
If you use any Microsoft Office objects in your VB applications, you might be interested in an Excel workbook that lists all the Office error messages and their corresponding values. This file, Errormsg.xls, is available by download at
Once you've downloaded the file--an EXE file, which 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, open Errormsg.xls in Excel. Each Office application has its own sheet--simply 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.
DAO's Find method can evaluate multiple criteria strung connected with the And operator, but the ADO Find method can't. When you need to search on multiple criteria in ADO, use the Filter property as shown in the following code: rst.Open "tablename", cnn, adOpenKeyset, adLockOptimistic rst.Filter = "field1 = criteria1 AND field2 = criteria2" MsgBox rst!fieldx rst.Close DAO also has a Filter property, but it works differently from the ADO property. The DAO Filter property works only on subsequent recordsets; the ADO Filter property will filter the current recordset.

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.


DAO's Find method can evaluate multiple criteria connected with the And operator, but the ADO Find method can't. When you need to search on multiple criteria in ADO, use the Filter property as shown here: Function MultiSearch() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Set cnn = CurrentProject.Connection rst.ActiveConnection = cnn rst.Open "Products", cnn, adOpenKeyset, adLockOptimistic rst.Filter = "SupplierID = 15 AND CategoryID = 2" MsgBox rst!ProductID rst.Close End Function
DAO also has a Filter property, but it works differently from the ADO property. The DAO Filter property works only on subsequent recordsets; the ADO Filter property will filter the current recordset.

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


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. To do so the hard way, drag the split bar back to its originating split box. The easiest way to eliminate a split module is to simply double-click the split bar.


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

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

Function GetCount() 
Dim db As Database, rst As Recordset 
Set db = CurrentDb 
Set rst = db.OpenRecordset("table", dbOpenDynaset) 
Debug.Print rst.RecordCount 
End Function 
In a small database, you may not notice the difference. However, if you have thousands of records, you should definitely notice an improvement.

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

All the loop statements need a way to know when to stop. The For...Next 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, VB 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 below: lCount = Forms.Count - 1 For lCounter = 0 To lCount Next lCounter Now, VB 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.)
When using the Select Case statement, always have a Case Else that will pick up any cases not matched in your list. Even if you're sure that extraneous values can't be processed by your code, it's a good idea to include a Case Else, as shown here: Select Case intTest Case 1: ' do something Case 2: ' do something else Case Else: ' this is probably an error, so ' display an appropriate message End Select
An Efficient way to close
When you close a form, it's common practice to have the user confirm the action. The procedure below is an efficient method that eliminates 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
An annoying feature of VB 6.0 (or Visual Studio 6.0) is the online Help. Every time I need help, I have to insert one of my MSDN Library CDs. Doing so isn't a big deal, but it's a disruption I can do without. If it bothers you too, you can install the help files directly to your hard disk. To do so, rerun the install again, except this time choose the Custom option. At this point, simply select the Help files and continue. The next time you need help, you won't have to reach for your CDs. (Keep in mind that these files require about 12 MB.)
If you've done much work with databases, you will already understand the need to check for null values. A null value is a special value that indicates that no value has been stored in a particular field in a database table. Nulls cannot be manipulated in the same way empty strings can. Because of this problem, you have to do one of two things. For fields that are holding string data, you can convert a NULL to an empty string with this code:
strNonNull = strNullable & "" 
By concatenating an empty string to a possibly null field, you change it from a null field to an empty string, which you can work with. For fields that hold numeric data, it's not so easy. You'll have to use the IsNull function to determine whether a field is null before attempting to manipulate it. If you don't, you will eventually be rewarded with the runtime error Invalid Use of Null.
Everyone has 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.
If you're trying to connect to your Access 2000 databases from Visual Basic, it's pretty easy to do using ADO. Besides needing to have Access 2000 (or just the Jet runtime DLLs) installed on the machine where your program is running, you need to change your connection string to use the Jet 4.0 provider instead of the Jet 3.51 provider. Here's some sample code you can use: Dim dcnDB As New ADODB.Connection dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & " Data Source=C:\Visual Studio\nwind.mdb" dcnDB.Open
Here's a quick way to convert a hexadecimal number to a long (or whatever data type you need): Dim strNumber As String Dim lngNewNum As Long strNumber = "FFFF00" lngNewNum = CLng(Val("&H" & strNumber))
In our opinion, one of the best features of the VB 6 environment is the support for Data Links. No longer do you have to keep both VB and a database tool open--you can do it all within VB. Part of this functionality is the ability to "memorize" database connections in Data Links. The important thing to remember is that these Data Links are not stored with your project; instead, they are stored as part of your development environment on a single machine. This means that you'll have to either copy or re-create them if you change machines.
A question I often get from readers is whether there is a decompiler for Visual Basic--that is, a program that can look at an .exe file and extract the source code from it. After doing a bit of research, I found that there no longer is a VB decompiler available for recent versions of VB. This question comes up a lot when people have lost their source code but still have the executable file. The best solution to this is to make lots of backup copies or to use a tool like SourceSafe (or any other version control software) to make it easier to manage your source code. If you were looking for a decompiler to take someone else's code, guess what? That's illegal... and you're still out of luck.
One of the best ways to pass batches of data back from COM components is through a disconnected ADO recordset. A disconnected recordset is created by first specifying the CursorLocation property as adUseClient. You then create the recordset as usual. When you've created the recordset, you set the ActiveConnection to Nothing, which disconnects it from the server. You can then pass the whole recordset to the destination application without having to worry about maintaining the connection.
One thing I like to do in my data entry forms is to be as flexible as possible in allowing data entry. For instance, dates can be entered in many different ways, all of which are valid. To allow for this, I add a little code in the LostFocus event of the text box in question: Private Sub txtBox_LostFocus() If txtBox <> "" Then txtBox = Format(CDate(txtBox), "mm/dd/yyyy") End If End Sub As long as the user enters something that vaguely resembles a date to Visual Basic, it will convert the entry to mm/dd/yyyy format and put it back in the box. This allows for flexibility but makes it easy to let the user see whether the value he/she typed was actually valid. You can also add your own error handling to handle cases in which the date entered isn't valid.
Ever since Microsoft did away with the traditional help files, we have found ourselves pulling our hair out whenever we have to look up material in MSDN. It always seems that the keywords we pick bring up the wrong topics. However, there is a feature we recently noticed in MSDN: Favorites. This lets you find a topic, such as the ADO Programmer's Reference, and create a bookmark to it. You can then go directly to that topic just by picking it from the Favorites tab. This will make navigating the MSDN Library a bit easier in the future.
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 VB, 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 VB rules, you may have trouble. You see, VB 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 a 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, you'll return an error.
In case you haven't heard that term, the Julian date is the day of the year, starting with January 1. This was a common way to store dates on mainframe computers. Since we now have more powerful date data types, it's not so common as it once was. However, if you do need to generate the Julian date for a date, here's a quick way to do it: strDate = Format(Date, "y") This tip was supplied by David Herron. Thanks, David.
One of the more professional touches you can add to your application is the ability to use the Registry. Unfortunately, the built-in GetSetting and SaveSetting functions don't quite do the Registry justice. These routines can look in only one particular section of the Registry (Software\VB and VBA Program Settings) and can't look at any other application's settings. While this is helpful for new users, it really limits what power users can do. If you need access to the rest of the Registry, you can read an article at the VB Techniques Web site that shows how to build a class to read the Registry using the appropriate API calls.
You've probably noticed that the new versions of MS 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 5 and apply the Marlett font to the value. The following 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. First, add a label control to your form (object). Then, using the table, enter the appropriate value as the label's Caption property. Finally, specify Marlett as the label's Font property.
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 that we've found 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.
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 VB 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're using a TreeView control in your application, you've probably noticed that it takes a while to load data into it. For this reason, we load only a single level of data at a time. The control has the Expand and Collapse events, which indicate that a node needs to have its data loaded. Using these events with an intelligent window makes it much easier to manage large amounts of data in this flexible control.
For those of you interested in storing images in a database, we have two words for you: Don't bother. While you can do it, it's much easier to do the following: First, create a field in your table to store a filename. Store the image on disk somewhere with that filename. Link to the image from your web application or your client/server application. In our experience, it's much easier to work with images in this fashion unless you have some high-performance systems specifically designed for working with large batches of images. For more users, storing the files on a shared disk makes them easier to get to for both Web and client/server applications.
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, VB will retain the value of each existing element in your array.
If you find that your form has gotten too complicated, or you need to make another group of OptionButton controls, for instance, you'll need to move your controls inside a container control, such as a frame. Before you delete your controls and re-create them, follow these steps. First, draw the Frame control somewhere on your form where you can see the controls that you want to put in the frame. Next, highlight all the controls you want to put in the frame and select Edit, Cut, or press Ctrl-X. Then, click on the Frame control and select Edit, Paste, or press Ctrl-V. The controls will be dropped inside the frame and can be repositioned within the frame borders. Any code you've written or any property values you've set for the controls will be preserved.
A reader asked how he could search a database using two LIKE clauses to allow for multiple word searches at the same time. This is easy to do using SQL. Here's an example of how to do it: Dim strWord1 As String Dim strWord2 As String Dim strSQL As String strSQL = "SELECT * FROM tblCustomers WHERE LastName LIKE '%" & strWord1 & "%' OR " _ "LastName LIKE '%" & strWord2 & "%'" You can simply OR the conditions together, which will give you the union of the results from both parts of the query.
Several tips ago we talked about displaying the contents of one field in a list box while returning the value of a corresponding field. You simply bind the control to both fields, and the list box will display two columns instead of one. Then, you have the option of hiding one of those columns. You can take this one step further by concatenating fields and displaying those results while hiding the actual fields you combined. For instance, most of us enter first and last names in different fields. If we want to display a list of names in a list box, we can do so by displaying both the first and last name fields, but it doesn't look so hot. If you're willing to use a SQL statement as the control's DataSource property, you can display a list of names in a more familiar format--first name, space, and then the last name. Simply use a SQL statement in the form SELECT EmployeeID, LastName, FirstName, LastName & ', ' & FirstName AS Name FROM tblEmployees Then, hide the EmployeeID, LastName, and FirstName fields. Once you've finished, the list box will display only a list of full names, first name first, with a space in-between the two names. If you want the names in alphabetical order by last name, that's no problem either. Simply add an ORDER BY clause to the statement in the from SELECT EmployeeID, LastName, FirstName, LastName & ', ' & FirstName AS Name FROM tblEmployees ORDER BY LastName, FirstName
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 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
When you're finishing up an application, be sure to remove any controls you haven't used from your toolbox. Also, remove any libraries you have referenced but currently aren't using. If you're not sure, try to remove the library (or control). If it's in use, VB will tell you so. Otherwise, you'll save yourself some disk space when you attempt to deploy the application.
VB 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 should 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 When you resize an array, the elements lose their value.
In a previous tip, we discussed resizing arrays but apparently left out a few key facts. To create a resizable array, the array first has to be defined without dimensions, like so:
Dim a_intValues() As Integer 
When you want to resize the last dimension of the array, you can use the Preserve keyword to preserve existing values, like so:
Redim Preserve a_intValues(10) As Integer 
Of course, if you're making the array smaller, values that are in cells that are "downsized" won't be preserved. All other values will be kept.
If you're using MDI forms (child and parent) in your application, you should be aware of some restrictions on them. First, you can't switch whether a form is a child or not at runtime. This question has come up once or twice, and the answer is that the architecture prevents this from happening. Another restriction is that MDI child forms cannot be shown modally--they can be shown only within the MDI parent form as modeless forms. A final rule is that you can have only one MDI form per application. If you feel that you need more than one, I'd suggest looking at the overlap between the functions and combine the functions from your two MDI forms into a single MDI form.
The Select Case statement allows you to run an expression or condition by any number of different possibilities and assign a unique action for each 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 it 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 your VB forms and modules with a VBA application and vice versa can be an efficient use of your objects and code. Fortunately, 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. First, 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 resulting menu.) Choose Project, Add File. Locate the file you want to add in the Add File dialog box. Then click Open. If you're in a VBA application: Open the VB Editor by pressing Alt-F11. Choose File, Import File. Locate the file you want to import in the File Import dialog box. Then click Open.
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 the following 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 VB checks your object reference at compile time. With late-binding, VB checks the object each time you use it. So why would anyone use late-binding? You'll use 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.
In a previous tip, I stated that you should add a code module with an empty Sub Main to your DLL projects. It turns out that this is no longer (or possibly never was) necessary. You can just add your classes, compile, and go. I often have a code module with shared routines used by all the classes, but Sub Main does not need to be one of those routines.
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 several resources for ADO available online listed.
A user recently asked if there were good ways to move data from one database to another. Here are a couple of suggestions for you if you're in the same situation. The first thing I'd try would be SQL Server Data Transformation Services. These powerful tools can work with a wide variety of databases, including Oracle, Access, and of course SQL Server. They allow a lot of manipulation during the transfer of data, such as the remapping of fields from one table to another, reformatting data, and so on. If this method won't work, the next most flexible (but somewhat more time-consuming) way is to open two database connections and move each record individually. You'll have the flexibility to do whatever you need to in order to move the record from one table to another, but you'll have to do it all yourself.
If you're doing a lot of data manipulation, try to use forward-only, read-only recordsets as much as possible. These recordsets are optimized for fast access and don't require the resource overhead that either static or dynamic recordsets take. Also, when you're done, be sure to Close the recordset and set it to Nothing. This will release the resource back to the system. Supposedly, the system will automatically clean up these references, but we always like to explicitly close our objects personally, just to make sure they're closed.
If you are a new programmer, one of the things you probably aren't doing is declaring your variables. Because of VB's "feature" of declaring variables as it encounters them in code, a typo can turn into a second variable that you weren't expecting to get. For this reason, as well as our own sanity, we always use Option Explicit at the top of every file in our VB projects. We also instruct Visual Basic to automatically add this statement to new files by changing the Require Variable Declaration option in the Tools, Options dialog box. This has saved us hours of debugging over the years and is worth doing in every project.
Even though you can make extremely long lines of code, it's easier to read when the lines are narrower than the width of your screen. To do this, you can use the continuation character, which is the underscore character. You can break any line into multiple lines by using continuation characters between keywords, as shown here: MsgBox "http://www." & "microsoft & ".com" This breaks into these lines, as an example: MsgBox "http://www." _ & "microsoft" _ & ".com" The indentations on the second and third lines are not required, but they do make it easier to tell when you've separated lines like this. Also note that you can't use a continuation character in the middle of a string. You have to break the string into multiple, smaller strings, as we did in this example.
If you have to store or select date values from Access or SQL Server, be sure to enclose the date/time values within pound signs (#) and single quote characters, like so: strSQL = "SELECT * FROM Emp WHERE HireDate = '#06/19/70#'" The pound signs indicate to the SQL engine that the date needs to be handled differently. Depending on your system, you may be able to leave the single quotes off; however, they won't hurt you if they are there.
If you've ever had to create a procedure with lots of parameters, you know it can get confusing when you have to call the procedure. Here's a quick example: Private Sub DoSomething(arg1 As Integer, arg2 As String, arg3 As Long, arg4 As String) When you call this, you can do like so: DoSomething value1, "value2", value3, "value4" However, this doesn't really document what you're passing to the subroutine. VB supports the use of named parameters, which allows you to specify what parameter goes with what value, like so: DoSomething arg1:=value1, arg2:="value2", arg3:=value3, arg4:="value4" The bonus with this method is that you can mix up the arguments and the call will still go through properly. This is especially helpful if you're using a procedure with optional arguments; in fact, it is required in most cases using optional arguments because the system won't know to skip arguments unless you leave blank spaces between commas.
If you're using Visual Basic 6.0 and want to use Access 2000 databases, the best ADO provider to use is the Jet 4.0 OLE DB Provider. This provider is automatically installed when you install Office 2000 on your machine. Within your VB program, be sure to reference the appropriate provider when making your data connections, whether you're doing it through code, a data control, or the data environment. If you don't use the Jet 4.0 provider with an Access 2000 database, you'll get errors when you try to read data.
If you're building COM components for your Web applications, remember that VBScript knows only one data type: Variant. This has two implications for COM components. The first is in parameters you might have to supply to a function or subroutine. If your COM component is set to accept a Long, for instance, you'll have to use the CLng function on the input value before passing it. This is fine, but an easier way to handle this is to accept a Variant and do any conversions after the value is inside the COM component. This has the added benefit of keeping all the conversion code in one place. Also remember to do any necessary validation on the input. The other implication is in return values from functions. While you might want to return an ADODB.Recordset object to your code, for instance, you should use a return type of Variant. You'll still be able to return the object, and VBScript will know how to handle the object, since you're using the Set statement to store the result in a Variant variable.
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 later. Well, you can, but VBA will immediately create a new instance. The solution takes an extra step, but it's worth the trouble in the long run. Don't use the New keyword in the declaration statement as shown below: 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.
You know that VB6 comes with a Toolbar Wizard, but you can't find it, right? You thought you'd find it in the Add-Ins Manager dialog box, but it's not there. That's because you have to install the Application Wizard first, because the Application Wizard contains the Toolbar Wizard. Once you've installed the Application Wizard, you can access the Toolbar Wizard from the Add-Ins menu.
A reader recently asked me why he should program using object-oriented programming techniques. The main reason I use them is because I can map real-world objects, like customers and orders, to computer programming. Instead of thinking about creating records in tables to represent a customer, I simply use the CreateNew method of the Customer object. It's also easier to understand from a process or conceptual basis, since you don't have to get into the implementation details of how something is done. Finally, you can also change how a function works without affecting everything else in the system. In short, it makes my job as a programmer easier, even though there is often more code to write.
If you're building SQL statements within your Visual Basic code that have data that might have single quote characters in it, be sure to replace every single quote with two single quotes. This is really easy to do with the new Replace function. Simply run the Replace function on each data value you're appending to your SQL query, as shown in this example: strName = "Mrs. O'Leary" strSQL = "SELECT * FROM Emp WHERE Name = " & Replace(strName, "'", "''") This replaces each single quote in strName with two single quotes. Both Access and SQL Server will properly handle the single quote as long as it is marked in this manner. You can also create a shortcut function, called CleanString, that looks like this: Function CleanString(strInput As String) As String CleanString = Replace(strInput, "'", "''") End Function Be sure NOT to run this function on your entire SQL string, because the function will also replace the single quotes surrounding your data values. This will cause the SQL statement to raise an error when you use it.
Visual Basic, like most Microsoft products, is periodically updated through service packs. Subsequent service packs include all the fixes from the previous service packs, so if you need to update to Service Pack 3, you have to apply only Service Pack 3. Visual Basic 6's latest service pack is SP 3 and is available from the Microsoft Web site: With Visual Studio, Microsoft typically packs all the applications' service packs into a single service pack download, which makes it easy to update all your Visual Studio applications at the same time.
A reader wrote me asking about using a Data control with a large table (250 fields, in his case). He states that with a small number of rows, the control works fine. But with more rows, the control blows up. My initial guess would be that the database table is not properly designed. There are very few applications that require a single table with 250 fields in it. My guess is that there are lots of duplicated fields that should be separate tables. For instance, he might have two or three sets of address fields. One of the rules of database design is that repeated groups like this should be moved into separate tables. You can then join the tables using a primary and foreign key relationship. In addition, there may be fields that are irrelevant to the main entity. For instance, on a customer table, you wouldn't put in the price they paid on their last order. That is best left to either a table that contains the order totals for all orders in the system or a table where the total can be calculated dynamically. While these things don't solve the problem of the DAO Data control not being able to handle large amounts of data, they can be (and should be) used to prevent problems in the future. DAO is far from being a high-performance database access library. RDO and ADO are better optimized for this type of size and traffic. The ADO Data control is much more efficient when retrieving data from large row count and large field count tables, as well.
If you're building a long string, remember that you can't simply wrap your string onto multiple lines. You can, however, use the line continuation character to break a long line into multiple, smaller lines, as shown here: strLongString = "This is a long string that goes on and on without any end in sight." strShorterPieces = "This is a long string" _ & " that goes on and on" _ & " without any end in sight." Be sure to include spaces either at the end or the beginning of the sections--the continuation character won't automatically add them for you.
If you're using properties of objects, such as recordsets or custom COM/DCOM objects, be sure to store the value in a variable so it can be used multiple times. It is much faster to access a local variable than it is to read the property of an object. (This relates to how the object is stored in memory.) As a rule of thumb, if I'm using a property more than once, I create a variable for it.
If you're like me, you'll always start a project the same way: adding controls, adding components and libraries, and so on. By adding your project to the Visual Basic templates folder, you can create your own, custom project, which in turn can be used in the future instead of making you do the same steps over and over again. Just save your project file, once you've gotten all the components and controls in it, into the Template\Projects directory in your VB installation directory. You'll then see the project come up in the New Project dialog box. You can also do this with forms, as well as some other types of documents. Look in the Template directory to see all the types of objects you can use as templates.
One of the most overlooked features of the Visual Basic environment is its ability to let you customize your toolbars and menus. When you right-click on the menu bar, you'll see a pop-up menu with the Customize choice. Once you select that option, you can drag commands from the dialog box to any of your menus or toolbars. Since some helpful commands (like Comment Block and Uncomment Block in the Edit group) aren't added to the VB menus by default, this is a handy way to put them there.
Databases make up the majority of Visual Basic applications. If you're still coding to get the job done, stop! VB's Data control makes accessing a database almost code free. First, open VB and create a new form. Then, add a Data control to the form. (You'll find the Data Control icon on the toolbar.) Next, create the appropriate number of field controls. The number and type will depend on the fields you want to access and the types of data you're accessing. After positioning the field controls, select the Data control so you can set a few properties. Specifically, you need to identify the database you're working with in the DatabaseName property. Be sure to enter the entire path. The next step is to identify the table to which your form is linked. Do so by specifying that table in the Data control's RecordSource property. Once you've established the linked database and data object (table), you'll need to customize the field controls a bit. Just as you established a link between the VB form and the database, you'll need to link the field controls to the Data control. To do so, select each control and change its DataSource property to the name of your Data control. Next, in the DataField property, specify the field that you want that particular control to display. You'll probably want to update each control's label to reflect the field as well. You're done, and you didn't write one line of code. Run your program and watch the form move through the records in the linked table.
One of the most frequently asked questions about Visual Basic is how to allow a form to be resized and have all the controls adjust themselves automatically. Guess what? You can't do it . . . not automatically, anyway. You do, however, have a couple of options: The more interesting approach is to do it yourself. Using a simple methodology, this is not hard to do on forms where it makes sense. On a form designed for writing, such as a "Notes" or "Description" form, it makes sense to allow resizing. As the user enlarges the form, you change the height and width of the box to fit within the form borders. Here's a quick bit of code that changes the size of txtNotes to fit within the form edges, minus a small margin: txtNotes.Height = Me.ScaleHeight - (2 * txtNotes.Top) txtNotes.Width = Me.ScaleHeight - (2 * txtNotes.Left) In this code, Me refers to the form on which this control is located. This code uses the margin defined by the upper-left corner as the margin to use on the other side. This means that if your leftmost point of the text box is at 60 and the form is 1200 wide, the text will be 1080 wide (1200 - 2 * 60). Same thing goes for the height using the Top property as the top margin. While this version is pretty simple, it gets tricky if you have other controls, such as command buttons, on the form. I like to keep my command buttons centered in the bottom part of the form. This means that while the width of the text box can use the same formula, the height has to account for the height of the command buttons. Here's how you could change the code to handle this: txtNotes.Height = Me.ScaleHeight - (2 * txtNotes.Top) - cmdOK.Height - txtNotes.Top. This tells the text box that it has to account for three blank spaces: one above the box, one below the box, and one below the command buttons. The command buttons also have to be repositioned based on the bottom of the text box, like so: cmdOK.Top = txtNotes.Top + txtNotes.Height + txtNotes.Top The easier way to do all this is to look into a commercial resizer control. They're a little tricky to get set up initially, but once you're done, they handle all this work for you. Another option is to not allow resizing at all. Most windows, such as options dialog boxes and most other dialog boxes, don't need to be resized by the user. However, for the few that you need the feature available, one of these options should take care of the problem for you.
One of the nagging problems with Visual Basic is that there isn't a good way to make a completely packaged application. Every VB program you write has at least a few runtime DLLs that have to be registered, not just copied, to the client machine. You also have to make sure that when you copy a file to a client machine you're not overwriting a newer version. Most installation programs take care of this automatically, but your program might not do it. This means that you still can't create a VB application that runs on a CD-ROM, for instance. Based on the way VB applications are now written, you probably never will be able to do this. If you do need to build a self-contained application, you might want to look into another tool, such as one of the several that Macromedia produces. These programs are designed to create self-contained applications that work best for CD-ROM browsers like you might see in an installation program.
When writing a program that uses a database connection, be sure to minimize the number of database connections you use. Besides the ones you explicitly create using the ADO Connection object (or the Database object in DAO), remember that each DAO or ADO data control, by default, makes its own connection to the database. This is particularly critical if you're writing an application using a database that allows only a limited number of connections--each connection will typically count against the total available. This means that if you have 25 client licenses available and you're using five per instance of your application, you've reduced to five the number of people you can handle. To get around this limitation, create a global ADODB.Connection object in a code module external to any form. You'll then initialize and open your database when the program starts using the Sub Main routine and close it when the program exits. Everything in the application can share the same connection. It may mean that you have to do a bit of your own ADO code, but it will save time during execution since you won't have to create each connection every time.
Some of you may have on your Web servers Access databases that provide some sort of dynamic content or storage facilities for data. A reader asked me when you should convert to SQL Server. There are several reasons for converting to SQL Server. First, SQL Server can better handle large amounts of data. (Access databases tend to bog down when the files are getting over 50 to 100 MB, whereas SQL Server databases can easily handle that much data and far more.) Another reason to convert to SQL Server is performance. SQL Server operates in a different manner than Access and can handle requests much faster and more efficiently. SQL Server databases can also be backed up without having to take down the server, which means you'll get better uptime. SQL Server is a more reliable platform for critical applications like e-commerce because it has the capability to commit and roll back transactions at any point. If your system crashes, you can get all the transactions that had committed to that point without losing a great deal of data. Finally, SQL Server is a lot easier to administer remotely than is Access. Instead of having to download and upload the entire database every time, SQL Server lets you make all your changes via Enterprise Manager. For that reason, if nothing else, I prefer SQL Server to Access in almost all cases. If you can't afford SQL Server, you might want to look into Microsoft's Data Engine (MSDE). This is a good combination of SQL Server's reliability with a low cost (actually, free). For more information on MSDE, you can download this white paper from Microsoft's Web site. The paper discusses the differences between Access and MSDE: Note to all you Oracle fans: Oracle has all the same capabilities as SQL Server that I mentioned above. In fact, it is still the choice for most major e-commerce sites on the Web. SQL is catching up, but Oracle has always been able to deal with big databases better than SQL Server does.
This may be obvious to you old-timers, but one of the biggest newbie mistakes is to not declare variables. Visual Basic, by default, still allows you to simply use a variable without declaring it. A simple typo doesn't get flagged as an error; instead, it creates a new variable and becomes a bug that can be quite nasty to track down. If you choose Tools, Options, you can select the Require Variable Declaration option in the Options dialog box so that any new modules you add (forms, code modules, classes, etc.) automatically get Option Explicit added to them. Hopefully, the next version of VB will have this option turned on by default.
I've gotten this question a few times lately: How can I use a transparent GIF with Visual Basic? The short answer is that you can't . . . not with the controls that come with VB. While you can now use GIFs and JPEGs in VB, the controls aren't that smart and don't know how to handle transparency. If you need a control like this, you'll need to look at using a third-party product to do it.
One of the more useful sites that I visit periodically is This site consolidates all the press releases and announcements about Visual Basic and add-on products and components. If you're trying to keep updated on what is going on in this multimillion-dollar industry, you can sign up for their newsletter as well. In addition, if you have products or resources for the VB community, you can announce them for free using this service. For example, I use VBWire to announce new articles and features as I post them on my VB Techniques site. VBWire--Visual Basic News and Information
Most tips are from TipWorld - :The Internet's #1 Source for Computer Tips, News, and Gossip