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

Select Any of These

Visual Basic

LAST UPDATED: 08 March 2009 17:02:09 -0600

QUITTING AN APPLICATION

In our previous tip, we showed you how to initiate a new instance of an Office application from Visual Basic. Of course, opening another application consumes a lot of memory. That's why you should also follow any Automation task with the Quit method in the form

ObjApp.Application.Quit

This way, you'll free up the memory previously used by the application. Don't forget to also set the object variable to Nothing.

MORE ON AUTOMATION

All applications aren't equal in the eyes of Automation. There are subtle differences between the ways the applications respond when called. Today, we'll discuss a few of those differences.

Automation will launch Access as an icon. You must change the Visible property to True to restore the main window. Changing that property to False will minimize the window. Setting the object variable to Nothing will terminate the instance of Access; you can use Quit as well.

Excel will launch as a hidden window; you must set the visible property to True to unhide that window. You must use Quit to terminate Excel; setting the object variable to Nothing won't do the trick.

Like Excel, PowerPoint launches as a hidden window, and setting the Visible property to True will unhide that window. In addition, you must use Quit to terminate the application.

Word also launches as a hidden window with a Visible property of False, and you must use Quit to terminate the application.

Outlook launches as a hidden window, but you must use the Windows API to unhide the window. You must use the Quit method to terminate the application.

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

LEARN THE VALUE OF A VARIABLE--FAST

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

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

UNDERSTANDING THE PRINTER OBJECT

You can print simple reports without a third-party report-generating application. Simply use the Printer object. But there's a trick to it: This object allows you to select a paper bin, and your bins will most likely hold different-sized paper. Let's suppose your upper bin holds letter size (8.5 x 11) and the lower bin holds legal size (11 x 14). If you specify the upper bin for letter size, you must also specify the paper size in the form

PrinterPaperBin.vbPRBNUpper PrinterPaperSize.vbPRPSLetter

To specify the lower bin with legal size paper, you'd use the statements

PrinterPaperBin.vbPRBNLower PrinterPaperSzie.vbPRPSLegal

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

GOOD, FREE DOWNLOADS

If you're learning about VB and the Internet, you might try checking out some of the downloads from

http://www.vb-bootcamp.com/

Once you're at the home page, click Downloads and then click Free Area. Here you'll find sample code, demos, and documentation on VB and Internet features, DHTML, and ASP.

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

USING THE GLOSSARY

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

glossary

You can select the glossary at large, or you can go to a specific section of the glossary. Regardless of how you open the glossary, Help displays a list of words in alphabetical order. Simply locate the most appropriate word for the subject you're searching and click it. You might have to click a few words, but you're bound to find what you're looking for.

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

TESTING FOR A BLANK STRING VARIABLE

There's little doubt that empty variables can wreak havoc on your code. Fortunately, there are several easy ways to test a variable before using it. For instance, the expression

sVariable = vbNullString

will return True if sVariable equals "" or False if sVariable has value. The vbNullString constant represents an empty string (""). Similarly, the expression

sVariable = ""

will return True if sVariable is empty and False if sVariable contains a string. However, there's an even faster expression. Consider the simple expression

booResponse = Len(sVariable) = 0

If sVariable is blank, its length equals 0, and the expression returns True. When sVariable equals a data string, booResponse equals False.

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

PADDING NUMERICAL STRINGS WITH ZEROS

Adding leading zeros to a numerical string is a fairly common task and can be accomplished with formats. However, the procedure shown here is more flexible than a format, since you can change the number of zeros at any time:

Function ZeroPad(pad As String, places As Integer) While places > Len(pad) pad = "0" & pad Wend ZeroPad = pad End Function

You'll pass the string you want to pad with zeros and the number of places you want the finished string to contain. Then, as long as the number of places is larger than the string you want padded, the procedure adds a zero. For instance, if you want to pad the value 2 with enough zeros to make the final string three characters, you'd pass this function the values 2 and 3.

The procedure will cycle through the While loop twice, each time adding a 0 character to the value 2. The final value will be 002.

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

FINDING AN OBJECT'S DEFAULT PROPERTY

You probably know that you can omit the object's default reference. In fact, we've discussed this referencing shortcut in earlier tips. But you may be wondering how you know what an object's default property is. There's an easy way to find out. Just open the Object Browser and select the object in the Classes list. The Members list will update accordingly, and the default property will display a small blue circle right above it.

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

DO YOU REALLY NEED THAT PUBLIC FUNCTION?

You probably see constants declared in the General Declarations section from time to time. This is particularly true when your code contains API calls. However, sometimes we find these constants are misplaced because they're used by only one function. When this is the case, you should consider moving your constant(s) to the procedure that uses it (or them). That's because Access will automatically release the memory used to declare those constants when the procedure ends.

On the other hand, constants declared at the module level remain in memory for the lifetime of the application. As you can see, if the constant is used by only one function, you could be wasting resources.

If you must leave the constant in the General Declarations section, you can still free up those resources. Simply reset the constants in the last procedure that refers to the constant. For instance, if your constant is a string variable, you'd use the statement

strMyString = ""

to free up any resources previously assigned to strMyString.

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

SETTING PROPERTIES

VB allows you to set a control's properties via a property sheet or Properties window during design time. After locating the appropriate property field, you can type the setting. Some properties have predefined settings. When this is the case, you can choose the setting from a list of items. To do so, simply click the field's drop-down list and choose an item.

When a property has predefined settings, you can also toggle through those settings by clicking the property field label (the property name to the left of the property field). Click this label and VB will toggle through the different property settings. Stop toggling when you reach the setting you want.

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

A FEW DIR() TRICKS

VB users can use the Dir() function to check paths for valid files. This function uses the syntax

Dir("path")

where path is the file's entire path and filename, including the file's extension. If the file doesn't exist, the Dir() function returns a zero-length string (""). If the file does exist, the function returns the file's name.

You might not realize that there's a little more to Dir() than just checking for a valid file. The form

Dir()

will return the name of the first file in the current directory. Each time you repeat this function, it returns the next file in the current directory. This behavior can be a big help, or a nuisance, depending on what you want to accomplish.

You must have the VBA library referenced for this function to work.

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

VARIABLE DOESN'T WORK?

When a value doesn't display or consider a decimal component in a calculation--and you know it should--check that value's data type. The value must be a Single or Double data type to store a decimal component. This is a common mistake and it's easy to make. While it's obvious that the Integer and Long Integer data types both store only integers, we use them so often that it's easy to enter Integer or Long without realizing the repercussions. Another problem is that we don't always know during the design stage that a variable might need to accommodate a decimal value.

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

CHANGING DATA TYPES

A few tips ago we reminded you that any time you might need to store decimal values, you must remember to declare the field or variable's data type as Single or Double. If you forget, you can always change the data type later to accommodate decimal values. However, we would like to offer one word of caution about doing so: If you change a data type after you've entered data, you risk losing that data, and you can't undo the damage. Here's what happens: If an existing value is too large for the new data type, Access may truncate, round, or even delete that value. This caution should be applied any time you change a field property when data already exists.

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

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.

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

JUST FOR FUN

If you're considering a new diet or exercise regime, good for you. If you're not, you might want to check your Body Mass Index and then reevaluate. To check your Body Mass Index in VB, you can use the following procedure:

Function BMI(pounds As Integer, feet As Integer, inches As Integer) As Integer BMI = (pounds * 0.45) / (((feet * 12) + inches) * 0.0254) ^ 2 End Function

You can attach the procedure to a text box control or run it in the Immediate window:

?BMI(pounds, feet, inches)

where pounds, feet, and inches represent your information as integers. For example, if you're 5'5" and you weigh 130 pounds, you'd use the statement

?BMI(130,5,5)

which will return a BMI value of 21. If the result is 25 or under, your BMI is acceptable. If it's over 25, you need to reduce your body fat through diet and/or exercise.

The first part of our expression--pounds * 0.45--converts your weight into kilograms. The second component--(((feet * 12) + inches) * 0.0254)--converts your height into meters. Your height in meters is then squared. In a nutshell, your weight is divided by your height squared to return your BMI.

If you're wondering why our expression doesn't return decimal values, stay tuned for our next tip.

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

QUICKLY ROUNDING DECIMAL VALUES

In our last tip, we showed you how to return your Body Mass Index. Although the expression we used should, in most cases, return decimal values, our procedure doesn't.

If you're rounding decimal values to an integer, you don't really need special expressions for rounding. Simply assign the value of your procedure as an Integer data type. If you recall, the procedure used in our last tip was named

Function BMI(pounds As Integer, feet As Integer, inches As Integer) As Integer

By assigning the entire procedure to an Integer value, we limit our function to returning only Integer values. Therefore, any rounding is automatic. If the result of the expression is 21.45, the function will return 21; if the result is 22.55, the function will return 23.

The function doesn't care how many decimal values the expression returns. While this isn't a typical programming solution, it is a quick solution in the right situation.

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

MOVING A TOOLBAR

If you don't like where VB positions a toolbar by default, move it. To do so, simply click the move handle (the raised gray line at the left or top of a toolbar) and drag the toolbar to the desired location. You can use the move handle to slide the toolbar to the left or right of a docked position, or to undock the toolbar and create a floating toolbar. Once the toolbar is floating, you can click the title bar and drag the toolbar to a new position.

If you're unable to move a toolbar, choose Options from the Tools menu and click the Docking tab. Simply select the toolbar (by name) if you want a particular toolbar to be dockable. Or deselect it if you don't want the toolbar to be dockable.

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

AUTOMATING THE KNOWLEDGE BASE

You're probably aware of Microsoft's Knowledge Base--a huge online database of technical articles. Now, you can receive these articles automatically by simply sending an e-mail. Here's how it works. First, to receive an index of articles, send e-mail to

Mshelp@microsoft.com

and enter the word

Index

in the Subject line. Once you've received and reviewed your index, send another e-mail to

mshelp@microsoft.com

with an article's ID number in the Subject line. Microsoft will e-mail an electronic copy of the article to you. To request more than one article at a time, separate ID numbers with a comma.

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

DOCKING TOOLBARS THE EASY WAY

VB toolbars are easy to modify and position. For instance, you probably know that there are two positions--docked and floating. A docked toolbar is attached to the top, bottom, or one of the sides of the Access application window. Although you can move a docked toolbar, you must undock that toolbar first. A floating toolbar resides inside the Container window and moves easily.

You can alternate between a docked and floating toolbar by simply double-clicking the toolbar. If the toolbar is floating, double-click its title bar to dock it. To undock the toolbar, double-click the raised handles at the toolbar's left edge.

You can turn off the docking feature by choosing Options from the Tools menu, clicking the Docking tab, and selecting or deselecting the toolbar.

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

VIEWING THE OBJECT HIERARCHY

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

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

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

THE VALUE OF AN INTRINSIC CONSTANT

Visual Basic offers several intrinsic constants--predefined values that can't be changed. For instance, the Mousepointer property has several constants that represent the different mousepointer cursors. (We talked about this property in September.)

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

?vbArrowHourglass

and press Enter. In response, VB will return the value 13. You can use this technique with almost any constant, as long as you've referenced the appropriate library.

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

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. Not all windows are dockable, however. 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 is dockable. If the window is floating, just double-click the toolbar and VB will dock the window to the nearest border. Windows that have the Minimize and Maximize (or Restore) buttons in the title bar aren't dockable. If you double-click the title bar, VB will maximize or restore that window.

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

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, VB opens a table-type recordset (dbOpenTable) unless you specify a linked table or a query. In those cases, the default is the dynaset-type. Before you waste a lot of time trying to restate your criteria expression, check the recordset's type.

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

THE ADO FIND METHOD

DAO's Find method can evaluate multiple criteria 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.

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

OFFICE ERROR MESSAGES

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

http://www.microsoft.com/office/ork/2000/appndx/toolbox.htm#custalrt

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.

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

NEED A CALCULATOR?

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

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

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

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

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

SPEAKING OF CALCULATIONS

In our previous tip, we told you about a Web site with downloadable calculators for all kinds of tasks. However, most of the time, you'll have to rely on your own expressions to get the results you need. Occasionally your expressions return errors and there are several reasons why VB can't evaluate your expression. Here are a couple of quick checkpoints to review before you start pulling out your hair: - Make sure you've included the right number of parentheses (each opening parenthesis requires a closing parenthesis). - Make sure you've supplied all the required arguments for any functions or procedures. - Check all your object and variable references to make sure they're correct.

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

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

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

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

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

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

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

---------------------------------------------- DELETING A SPLIT SCREEN

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.

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

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.

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

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!

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

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

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

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

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

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

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

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

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.

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

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.

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

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.

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

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.

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

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

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

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.

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

PRINTING A FORM'S DESCRIPTION

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

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.

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

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

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.

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

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

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

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.

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

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.

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

UNIVERSAL CURRENCY

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

strAmount = "$999.99"

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

strAmount = CCur(amount)

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

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

USING THE FILE OBJECT

Did you know you could work with files as objects? You can, but you may need to reference the SCRRUN.DLL library (Windows\System) first. If you're running Windows 98 or NT with the Option Pack, or if you've installed VBA6, the library should already be installed. If you're working with VBA5 or Windows 95, you need to install the dll. If you can't find it on your system, you can download the file from

http://msdn.Microsoft.com/scripting/

Once you know you have the dll on your system, create a reference to it by choosing References from the Project menu and choosing Microsoft Scripting Runtime.

If you use the Scripting Runtime on a distributed product, make sure each system that runs your program has the dll installed.

---------------------------------------------- NEW RELEASE OF SCRIPT DEBUGGER

In our last tip, you learned how to install the Scripting Runtime so you can work with files as objects. If you're using Script Debugger, you should download the new release 1.0a, which repairs some known issues. Even if you're running Windows NT or Windows 2000 with Internet Explorer 5, you should still download version 1.0a. You can download both versions from

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

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

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

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

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

---------------------------------------------- PENTIUM SPEED

Are you lucky enough to have a Pentium Pro (P6) processor? If so, be sure to take advantage of VB's special compile option Favor Pentium Pro. To do so, choose Project Properties from the Project menu, click the Compile tab, select Favor Pentium Pro from the Compile To Native Code options, and click OK.

Fortunately, you can run code generated with this option on earlier processors--it just won't run as efficiently. So, if you plan to install your project on older machines, you probably ought to forego this compile option.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---------------------------------------------- ALWAYS PROVIDE A CASE ELSE

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

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

---------------------------------------------- ANNOYING HELP

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

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

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.

---------------------------------------------- CHECK FOR NULL VALUES

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:

<pre> strNonNull = strNullable & "" </pre>

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.

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

COMPARING FLOATING POINT VALUES

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.

---------------------------------------------- CONNECTING TO ACCESS 2000 DATABASES

If you're trying to connect to your Access 2000 databases, you have a few steps to complete first. You first have to have the Jet 4.0 engine installed on your machine. The best way to do this is to install at least Access 2000 on your machine. You then 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

The added bonus is that the Jet 4.0 provider can read and write to Access 97 databases. This will save you time because you don't have to convert the databases to take advantage of the performance improvements in the Jet 4.0 engine. If you want to make changes in an Access 97 database using Access 2000, you will have to convert it to the new format.

Good luck!

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

CONVERTING FROM HEXADECIMAL

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

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

FINDING AN EMPTY RECORDSET

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

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

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

---------------------------------------------- FOR...NEXT IN VBSCRIPT

If you're proficient at 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.

---------------------------------------------- GETSETTING AND SAVESETTING RESTRICTIONS

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.

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

GREAT POINTERS

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.

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

There are several solutions for adding leading zeros to a value, and most of them are more convoluted than they need to be. One of the simplest methods for adding leading zeros 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.

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

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

You don't have to use a powerful function to express a date. You can use a literal date string instead, and they're generally faster. Save the functions for those times when you really need all that power. If you just need a date, express it as a literal date--similar to the way you express a string. Simply enclose most any established date string in pound signs. For instance, all of the following strings represent valid dates, and 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#

---------------------------------------------- MANAGING IMAGES WITH A DATABASE

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.

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

MORE ON RESIZING AN ARRAY

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

Redim Preserve arrayname(x) As datatype

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

---------------------------------------------- MULTIPLE KEYWORD SEARCHES

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.

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

NEAT SQL TRICK

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

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

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

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

Next, add the 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

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

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.

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

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:

<pre> Dim a_intValues() As Integer </pre>

When you want to resize the last dimension of the array, you can use the Preserve keyword to preserve existing values, like so:

<pre> Redim Preserve a_intValues(10) As Integer </pre>

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.

---------------------------------------------- RESTRICTIONS ON MDI CHILD FORMS

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.

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

SELECT CASE WITHOUT CASE ELSE

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 FILES BETWEEN VB AND VBA

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.

---------------------------------------------- SPEEDIER OLE

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

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

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

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

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

---------------------------------------------- TRANSFERRING RECORDS BETWEEN DATABASES

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.

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

USE OPTION EXPLICIT

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.

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

USING CONTINUATION CHARACTERS

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.

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

USING DATE VALUES WITH SQL

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.

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

USING NAMED ARGUMENTS

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.

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

USING THE JET 4.0 PROVIDER

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.

---------------------------------------------- WHEN NOTHING ISN'T NOTHING

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.

---------------------------------------------- WHERE IS THAT WIZARD

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.

---------------------------------------------- ZIP CODE VALIDATION

I'm commonly asked questions dealing with ZIP code validation and how to match entered ZIP codes with the city and state entry. This is pretty common in many applications and allows for more reliable and consistent data entry. However, the database of ZIP codes and city/state pairs is not free. The best source of information about available vendors is the US Postal Service's National Customer Support Center.

US Postal Service's National Customer Support Center http://www.usps.gov/ncsc/

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

Y2K STATEMENTS

While this may seem out of date now, several users have asked me recently about Y2K statements from component vendors. Many component vendors that have controls dealing with dates put out so-called Y2K statements indicating how their components had been tested for Y2K compliance. The best source for these statements, if relevant, is still the component vendor. As far as I know, there isn't a "repository" of these statements anywhere online.

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

WRITING YOUR OWN MMC SNAP-INS

The Microsoft Management Console, used for managing SQL Server, IIS, and most other BackOffice applications, now has a designer available for it that lets you build your own snap-ins for this platform. There's a new book out from Microsoft Press about it, and there's an article at this DevX.com site on the same topic:

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

The book covers doing it in both VB and VC, and from the looks of it, they've done a great job with the MMC Snap-In Designer for VB. All of the commonly used functions are supported by the designer, and best of all, the designer is free with the Windows 2000 Platform SDK.

Microsoft Management Console Design and Development Kit Microsoft Press (editors), May 2000 http://www.amazon.com/exec/obidos/ASIN/073561038X/tipworld

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

WRITING SIMPLE CONDITIONS

One thing I like to do is to write simple conditions. A student in a class I taught recently put it best as "Don't code negatively." A perfect example is this statement:

Do While Not rs.EOF

I probably typed this a hundred times before I realized I was breaking my own rule. This code is more easily read as

Do Until rs.EOF

In general, I try to avoid using the Not keyword as much as possible. If you have a Not keyword and a condition, you can reverse the condition like this:

Not (x < 5) is the same as x >= 5 Not (x > 5) is the same as x <= 5

Conditions that don't use the Not keyword are typically easier to read and don't take as much work to explain to other programmers.

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

WRITING A SHORTCUT FORMATTING FUNCTION

In a previous tip, we discussed formatting and left-padding numeric data so that it will sort properly in a ListView control, which only sorts alphabetically. Here's a shortcut function that does all this in one fell swoop:

Private Function LVFormat(varExpression As Variant, strFormat As String) Dim strTemp As String

strTemp = Format(varExpression, strFormat) LVFormat = String(Len(strFormat) - Len(strTemp), " ") & strTemp End Function

Accordingly, to add data to a ListView control from the Northwind Traders database, you can use this code:

Private Sub Form_Load() Dim rsData As ADODB.Recordset Dim objItem As ListItem

Set m_dcnDB = New ADODB.Connection m_dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Visual Studio\VB98\NWind.MDB" m_dcnDB.Open Set rsData = m_dcnDB.Execute("SELECT * FROM PRODUCTS ORDER BY ProductName")

With lvwProducts.ColumnHeaders .Add , "ProductName", "Product Name", 2880 .Add , "UnitPrice", "Unit Price ($)", 1440, lvwColumnRight .Add , "UnitsInStock", "Units In Stock", 1440, lvwColumnRight End With

Do Until rsData.EOF Set objItem = lvwProducts.ListItems.Add(, "P" & rsData("ProductID"), _ rsData("ProductName")) objItem.SubItems(1) = LVFormat(rsData("UnitPrice"), "###,###,##0.00") objItem.SubItems(2) = LVFormat(rsData("UnitsInStock"), "###,###,##0")

rsData.MoveNext Loop rsData.Close m_dcnDB.Close

End Sub

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

WRAP LONG LITERALS

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 beginning of sections, because the continuation character won't add them for you automatically. (Breaking a long string like this makes it easier to read since the user doesn't have to scroll horizontally.)

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

WRAP LONG LINES OF CODE

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.

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

WORKING WITH STRINGS

In the following article at Dev-Center.com, you can learn more about string processing using Visual Basic. Here's the URL to visit:

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

In particular, you'll find some interesting techniques for encrypting text in your application.

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

WORKING WITH MP3 FILES

If you're like me, you probably have a few music files that are in the MP3 format. A number of people have asked me recently about using this format to write an encoder or decoder. The best source of information is the original source of the MP3 format: the Motion Picture Experts Group. I would also recommend the more unofficial MPEG.org site. Both sites offer lots of code listings that you can adapt for use within Visual Basic to read and write MP3 files.

Motion Picture Experts Group http://www.cselt.it/mpeg/

MPEG.org http://www.mpeg.org/

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

WORKING WITH APIS

Finding the correct declaration information for an API can be a challenge. The easiest way is to use the API Viewer that comes with the Professional version of Visual Basic. If you're working with VB5, you'll find the API viewer under VB's Basic Start Menu group. VB6 makes the API Viewer available as an add-in. Unfortunately, the declarations listed in the API Viewer aren't always correct, so you may still need an alternative source. However, many of the declarations are correct, and since the Viewer is so handy, it's the logical place to start.

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

WORKING WITH A MULTILINE TEXT BOX

Textbox controls don't always contain just one line of text. However, you must handle each line separately within your code. The first step is to make sure that you've set the control's MultiLine property to True. Then, add code that identifies each line as follows:

Private Sub AddLines(txtBox As TextBox, sFirstLine As String, sSecondLine As String) txtBox.SelText = sFirstLine & vbCrLf & sSecondLine & vbCrLf End Sub

The vbCrLf code inserts a return (line feed) into the text you're sending. That way, Visual Basic stores the text with the appropriate line breaks.

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

WITH STATEMENTS

The With statement is an easy way to save yourself some typing. In a previous tip, I used the With statement to set values in the ColumnHeaders collection of the ListView control. Here's a copy of that code:

Note that you can use With even with a nested object (an object that is part of another object). You can also do nested With statements. Here's the same code in a different format using two With statements:

With lvwProducts With .ColumnHeaders .Add , "ProductName", "Product Name", 2880 .Add , "UnitPrice", "Unit Price ($)", 1440, lvwColumnRight .Add , "UnitsInStock", "Units In Stock", 1440, lvwColumnRight End With End With

The downside of the second version is that the more you nest, the harder it is to read. For this particular code, the first version uses less coding and is easier to read.

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

WINDOWS 2000 SERVICE PACK 1 NOW AVAILABLE

It's not exactly Visual Basic, but if you're using any version of Windows 2000, the long-awaited service pack is now available. This service pack contains a whole bunch of fixes that are all documented on the Microsoft Service Pack Web site. The service pack is also available for download, or you can order it on CD.

Windows 2000 Service Pack 1 http://www.pcworld.com/r/pcw/1%2C2061%2Cpcw-vb8-3%2C00.html

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

WHY I USE OBJECT-ORIENTED PROGRAMMING

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.

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

WHICH ORACLE DRIVER TO USE

In a previous tip, I talked about the Oracle driver that had been created by Microsoft when ADO was first released. At the time, it had better performance than any driver that Oracle had created. However, this situation has since changed. According to several readers, the Oracle OLE DB provider has improved dramatically and is now the recommended driver for accessing Oracle databases through OLE DB/ADO. Some users estimate a 30 percent performance improvement over the Microsoft Oracle driver.

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

VISUAL STUDIO SERVICE PACK 4 AVAILABLE

If you're using any part of Visual Studio, be aware that Service Pack 4 is now available. You can see a list of fixes and features at

Microsoft Visual Studio http://msdn.microsoft.com/vstudio/sp/vs6sp4/default.asp

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

VISIT VBWIRE.COM

One of the more useful sites that I visit periodically is VBWire.com. 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 http://www.vbwire.com

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

VBSCRIPT OUT IN NEXT GENERATION WEB SERVICES

If you've not yet installed Microsoft's Next Generation Web Services, you'll be pleasantly surprised to learn that VBScript is no longer included or supported. Instead, ASP+ includes the Visual Basic 7.0 language, which includes all the new features previewed at the Web site listed below. In addition, you'll be able to use the new C# (C-Sharp) language within ASP+, as well as JavaScript. You can read more about the new technologies here:

Visual Studio Next Generation http://msdn.microsoft.com/vstudio/nextgen/default.asp

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

VBG FILES

A VBG file is a Visual Basic project group file. A project group is a way to have more than one project grouped together within the Visual Basic environment. This is especially handy when debugging ActiveX controls or components. You put the control or component in one project, and then add another project where you use the control or code. With this method, you can step into the code for your component or control just as if it were part of a single project.

To create a project group, use the Add Project option on the File menu. This will let you create or add another project to the environment. When you save, you will be prompted to save the VBG file. You can open the individual projects by themselves, or you can open the group file once you've saved it.

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

VARIABLE USAGE

In response to a question sent by a reader, here's a quick reminder about how you can create variables within Visual Basic. If you create a variable within a subroutine or function, it's available only from within that function. If you create a variable in a form's or class's declarations section, that variable is available throughout all the code in that file. If you make the variable in that same section Public, it can be accessed by code outside the file.

If you create a variable in the declarations section of a code module, it's available throughout the entire application--as long as you use the Public keyword in front of the variable's name.

In short, declare the variable to be as small in scope as possible. For example, if you need a counter for a single procedure only, don't declare it as global to the application.

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

USING VARIANTS IN COM COMPONENTS

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

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

USING TRANSPARENT GIFS IN VB

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.

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

USING THE VALIDATE EVENT

In our previous tip, I described the difference between the Change and LostFocus events and when to use the latter. Visual Basic 6.0 introduced an additional event to confuse the issue: the Validate event. This event works in conjunction with the CausesValidation property, also added in VB 6.0. Here's how it works:

Each control that needs to be validated has its CausesValidation property turned to True. This event, like QueryUnload, has a parameter called Cancel that can cancel whatever action caused the validation to start. In a command button, you can specify that validation is not to be done, such as for a Cancel button. When you click a button where CausesValidation is marked True, all the validation routines in the form are triggered. If none of them mark their Cancel flag, the action goes through. To try it out, draw a text box on a form and add two command buttons. Mark the second command button's CausesValidation property to False, and then add this code:

Option Explicit

Private Sub Command1_Click() Unload Me End Sub

Private Sub Text1_Validate(Cancel As Boolean) If Text1.Text = "" Then MsgBox "You must enter data." Cancel = True End If

End Sub

If you don't type data into the box before clicking the first button, the form won't unload and you'll get a message. If data is present, the form will unload.

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

USING THE RECORDSET SORT PROPERTY

Once you've created an ADO recordset, you have the option of re-sorting the data by any of the fields available to you in the recordset. For this to work, the CursorLocation property has to be set to adUseClient. This eliminates the ability to use this feature with ForwardOnly recordsets, as well.

To sort a recordset by a field, just set the Sort property, as shown here:

rsData.Sort = "CompanyName"

The sorting happens immediately. If you want to sort in reverse (descending) order, you can add the keyword

DESC

following the field name, as shown here:

rsData.Sort = "CompanyName DESC"

You can also list multiple columns for sorting, like so:

rsData.Sort = "CompanyName, ContactTitle DESC"

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

USING THE JET 4.0 PROVIDER WITH ACCESS 97

The Jet 4.0 OLE DB provider is able to read Access 97 and Access 2000 databases. However, a number of problems have been reported with this method. In certain cases, the driver will stop responding and will on occasion cause 100 percent CPU usage on servers. The solution to this problem is to upgrade your Access 97 database to Access 2000. Microsoft has promised a Knowledge Base article on this problem, but none exists as yet. Thanks to Seth Lipkin for this tip.

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

USING THE ADO RECORDSET FIND METHOD

Once you open a recordset (excluding a ForwardOnly recordset), you can use the Find method to search for records within that recordset. The Find method uses the same syntax as a SQL WHERE clause. If it finds a record matching your criteria, the record pointer will be left on the matching record. If the Find method doesn't match any records in the recordset, the recordset's EOF property will be True. This is a handy way to be able to manipulate more than one recordset at a time without having to requery the database each time. Just move through the recordset, and you can do some of the work yourself.

There are a number of additional options for use with the Find method--refer to MSDN for more information.

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

USING MULTISELECT LIST BOXES

VBExplorer.com has recently run an article about working with multiselect list boxes. Written by John Smiley, this article provides some good information to help you get the most from list boxes--and it is worth the visit.

MultiSelect ListBoxes http://www.vbexplorer.com/vb_feature/june2000/june2000.asp

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

USING DATA CONTROL WITH LARGE TABLE

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.

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

USING CONTINUATION CHARACTERS

Even though you can write extremely long lines of code, they're 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, for example:

MsgBox "http://www." _ & "microsoft" _ & ".com"

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

USING COM OBJECTS FROM VB

I often receive questions about using COM objects or components within Visual Basic. Any object you use within Visual Basic (leaving out DCOM and COM+ for now) is a COM object. COM is simply a specification for how objects talk to one another within the Windows environment. For instance, if you create a FileSystemObject, that is a COM object.

In more general terms, you have to first reference the object library in the References dialog box under the Project menu. For the FileSystemObject, for instance, you need the Microsoft Scripting Runtime library. The Object Browser (located on the View menu) will show you all the parts of that library. You can then declare instances of those objects as shown in the documentation provided with VB.

In short, don't let the buzzwords trip you up. COM objects are really easy to use, and believe it or not, you're already using them!

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

USING ADODB PREFIX IN DECLARATIONS

A user asked me when she might need to use the ADODB prefix for an object declaration. The short answer is that it's not necessarily necessary. If you're sure that you have only one object named Connection, you don't have to prefix it with ADODB. In addition, prioritizing libraries in the References window will cause one library to be read before another. However, I try to always specify the library name. This eliminates any possible confusion or difficulty in understanding which object is being used.

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

USING A VARIABLE WITH THE RECORDSET.SORT PROPERTY

In a previous tip, you learned about the recordset's sort property and how it can be used to automatically sort the data in the recordset. A user asked if it was possible to use a variable to specify the field name, as shown here:

Dim rsData As ADODB.Recordset Dim strField As String strField = "CompanyName" rsData.Sort = strField

As with anything else Visual Basic, you can use a variable wherever you might use a constant or literal piece of text.

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

USE OPTION EXPLICIT

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.

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

USE FORWARD-ONLY RECORDSETS WHENEVER POSSIBLE

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 I always like to explicitly close my objects, just to make sure they're closed.

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

USE DATE DATA TYPE FOR DATE/TIME DATA

One of the nice things about Visual Basic is that it doesn't have any internal issues dealing with Y2K issues. In fact, the built-in Date data type is able to hold year values from 100 to 9999. However, if you don't ask your users for four digit years, you do have a fallback position: Currently, Visual Basic will assume a 20 prefix for all two-digit years from 0 to 29 (2000 to 2029), and a 19 prefix for all other two-digit years (1930 to 1999). You can test this yourself with this code:

Dim i As Integer For i = 0 To 99 Debug.Print Format(CDate("1/1/" & i), "MM/DD/YYYY") Next i

The key to preventing these problems is to always ask your users for four-digit years in all date values. Don't assume your program won't exist in a few years--that was what got us into trouble in the first place.

---By Susan Harkins

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

UPDATING DATABASES

If you're writing applications to update your database with either new or modified records, you've got several options with ADO. For starters, you can create a dynamic recordset, which allows you to add new records or update existing ones. You can also create SQL statements to perform inserts, updates, and deletes and use the Execute method of the Connection object to fire them off. You can even create stored procedures with parameters and use them with the Command object.

The fastest way to run your SQL is through stored procedures. All of the SQL statements can benefit by being saved as stored procedures. Saving the stored procedure also saves the execution plan--which, for inserts and updates, includes how to update any indexes related to the tables.

If you don't have the ability to create stored procedures, executing SQL statements is the next fastest way to make this work. The system does have to create the execution plan on the fly; however, this doesn't take nearly as long as does opening a dynamic recordset and making updates.

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

UPDATING A DATE/TIME FIELD IN SQL SERVER

A user sent a question asking why, when he builds a SQL UPDATE statement to update a date/time field, he always gets a date and the time he wants to store. The user needed to store only a time value (such as 15:43) and not the date.

The reason this happens is that in SQL Server, as in other database systems, a date/time value is not stored as text. Instead, the value is stored, for example, as the number of seconds that has passed since some date, such as December 31, 1899. If you just want to store the time, you have a few options:

Store a dummy date with your time, such as January 1, 1900. When you retrieve the information, ignore the date and use the time information by itself. Store the time value as a text string and convert it to a time using a built-in VBScript or database function. This is not recommended, however, because it is more efficient to sort dates kept as datetime types. There are more options, of course, but these should get you started.

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

UNREGISTERING A COMPONENT

It's very easy to load your machine with lots of components and controls--but often not so easy to weed out the ones you don't need. When time comes to unregister a component, you can do it with the regsvr32 utility using the /u argument, as shown here:

regsvr32.exe /u d:\something.dll

This will remove the references to the DLL in the registry.

Note: All of the previous Visual Basic tips written by Eric Smith are now archived and searchable at the VB Techniques Web site. Be sure to visit soon!

VBTechniques http://vbtechniques.com/

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

TRANSFERRING RECORDS BETWEEN DATABASES

A user recently asked if there were good ways to move data from one database to another. Here are a couple of suggestions:

The first thing I'd try would be SQL Server Data Transformation Services (DTS). 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) technique 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.

A final way to do it is not to move the data at all. Instead, you link one database to another by referencing the other's tables. This can be done in Access, SQL Server, and other databases. In this way, you don't have duplicate data.

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

SWITCHING FROM ACCESS TO SQL SERVER

Some of you may have Access databases on your Web servers providing some sort of dynamic content or storage facilities for data. A question I received asked when you should convert to SQL Server. There are several reasons to convert to SQL Server: First, SQL Server can better handle large amounts of data. Access databases tend to bog down when the files get over 50-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, allowing requests to be handled 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. 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.

Note to all you Oracle fans: Oracle has all the same capabilities as SQL Server 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 could SQL Server.

In case you can't afford SQL Server, you might want to look into Microsoft's Data Engine (MSDE). This offers a combination of SQL Server's reliability with a low cost (actually, free). For more information on MSDE, you can download from Microsoft's site a white paper that discusses the differences between Access and MSDE:

http://www.microsoft.com/sql/productinfo/Access2000Jet&MSDE.doc

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

SWITCHING FROM ACCESS TO SQL SERVER

Some of you may have Access databases on your Web servers providing some sort of dynamic content or storage facilities for data. A reader me asked when you should convert to SQL Server. There are several reasons to convert to SQL Server: First, SQL Server can better handle large amounts of data. Access databases tend to bog down when the files get over 50-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, allowing requests to be handled 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.

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

SWITCHING FROM ACCESS TO SQL SERVER

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.

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:

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

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.

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

SUB MAIN NOT NEEDED FOR DLLS

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.

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

STORE CONTROL TYPE IN STRING

Many of you are aware of the TypeOf operator, which allows to you write a statement like this:

If TypeOf(ctlInput) Is TextBox Then ' do something End If

What you might not know is that you can get the datatype as a string using the TypeName function. For instance, you could do something like this:

Dim strControlType As String strControlType = TypeName(ctlInput) Select Case strControlType Case "TextBox", "ComboBox" ' do something Case Else ' do something else End Select

Using the TypeOf operator makes this sort of structure tedious to build.

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

STACK RECORDSET RETURNS

If you're getting a bunch of data for your application (such as for decodes for drop-down lists), you can retrieve all the data through a single stored procedure and through a single call. Here's what your stored procedure might look like:

CREATE PROCEDURE sp_GetAllTheData AS SELECT * FROM tblStates ORDER BY StateCode SELECT * FROM tblCountries ORDER BY CountryName SELECT * FROM tblTitles ORDER BY TitleName

When you retrieve this, you'll get three recordsets back at the same time. Simply open the query as a static recordset, and then use the NextRecordset method to assign each recordset to a different variable, as shown here:

Dim dcnDB As New ADODB.Connection Dim rsStates As ADODB.Recordset Dim rsCountries As ADODB.Recordset Dim rsTitles As ADODB.Recordset

dcnDB.Open "Some connection string" rsStates.Open "sp_GetAllTheData", dcnDB, adOpenStatic Set rsCountries = rsStates.NextRecordset Set rsTitles = rsCountries.NextRecordset

Because it's more time consuming to do another COM operation than it is to just pass more data, this is a more efficient way to pass back a large batch of data.

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

SORTING LISTVIEW DATA NUMERICALLY

The ListView control (part of the Windows Common Controls) has the ability to sort by a particular column in either ascending or descending order. While this works great for text, it doesn't work so well for numeric data. For instance, if you wanted to show the price of products in the Northwind Traders database, the system would sort the prices according to alphabetical order, not numeric. You can see another example of this when you look at Windows Explorer in Detail mode. The files in a directory can be sorted according to size, and they work properly.

A trick I came up with is to left-pad the numbers with spaces. Once you do that, they sort properly. Here's an example that has a ListView control, in Report View (View property), on a form called frmProducts. I'm going to show the product name (with product ID as the key), unit price, and units in stock:

Private Sub Form_Load() Dim rsData As ADODB.Recordset Dim objItem As ListItem Dim strTemp As String

Do Until rsData.EOF Set objItem = lvwProducts.ListItems.Add(, "P" & rsData("ProductID"), _ rsData("ProductName")) strTemp = Format(rsData("UnitPrice"), "###,###,##0.00") objItem.SubItems(1) = String(14 - Len(strTemp), " ") & strTemp

strTemp = Format(rsData("UnitsInStock"), "###,###,##0") objItem.SubItems(2) = String(11 - Len(strTemp), " ") & strTemp

rsData.MoveNext Loop rsData.Close m_dcnDB.Close

End Sub

The formatting for the price will handle a far bigger value than the database will allow. The length of the maximum format is 14 characters, so that's what we use to left-pad the result. A dollar value of 5.50 will get 10 spaces in front of it. The same thing happens for the quantity, which doesn't have a decimal component. You can now specify a sort on the unit price or units in stock columns and all the data sorts properly, based on the numeric values.

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

SORTING A LISTVIEW CONTROL

When using a ListView control in Report mode, users have the ability to click on a column header to designate the sort order in Windows Explorer. Here's the code you need to make this work:

Private Sub lvwProducts_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader) lvwProducts.SortKey = ColumnHeader.Index - 1 lvwProducts.SortOrder = lvwAscending End Sub

This code works on the assumption that the ListView control is named lvwProducts and that the Sorted property has been properly set. If this property isn't set, nothing will happen when you click a column header, even with this code in place.

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

SINGLE QUOTES AND SQL

In previous tips, you've learned that single quotes can wreak havoc for your SQL statements. Changing the single quotes into two single quotes normally will take care of the problem. Another way to eliminate the problem is to use parameters with a Command object. If you're creating parameters for your stored procedure, there's no need to change the data before submitting it to the server.

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

SHARE YOUR DATABASE CONNECTION

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.

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

SETTING WATCH EXPRESSION SCOPE

One of Visual Basic's overlooked debugging features is its ability to set watch expressions and to give those expressions scope. In some cases, you'll want to watch a global variable, which is valid at all times. However, if you're looking at a local variable to a subroutine, it will be valid only when VB is running that code.

When you add a watch expression (by selecting Debug, Add Watch), you're presented with a number of options for watching that variable: You can specify the subroutine in which to watch it, a form/module, or throughout the entire application. You can also specify a break when the value changes or is True. This is helpful if you're watching a flag or other accumulator and want to be able to trace how the value changed.

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

SERVICE PACK 4 NOW AVAILABLE

Microsoft has recently released Service Pack 4 for Visual Studio. This includes a number of fixes, as well as all the fixes included through Service Pack 3.

Visual Studio 6.0 Service Pack 4 http://msdn.microsoft.com/vstudio/sp/vs6sp4/default.asp

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

SEARCHVB.COM

The site formerly known as the VB Web Directory has been reborn as searchVB.com. This site has an excellent selection of resources and articles for Visual Basic and related technologies. Check it out:

searchVB http://www.searchvb.com/

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

SEARCHING LONG TEXT FIELDS IN SQL SERVER

One thing I currently do on the ASP Techniques Web site (http://www.asptechniques.com) is store all the content in a SQL Server database. Because I have some long text articles, I store these in ntext fields. The problem is that in SQL Server, ntext fields are not searchable using the LIKE keyword. This makes performing keyword searches basically impossible.

However, I've found that I am able to support keyword searches using the Full-Text Indexing feature of SQL Server. I simply specify the table(s) and field(s) to index, and SQL Server automatically creates a full-text index of those fields. The best part is that the SQL query still uses the same LIKE keyword. It's basically transparent at the SQL level, and since the indexing runs automatically when the system isn't busy, it doesn't put extra work on the server.

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

SEARCHING FOR TEXT IN FILES

A user asked me how to search the files in a directory for a piece of text. The most basic way to do this manually is to loop through the files in a directory, open each one, and search the text using a function like InStr. Here's a quick example:

Dim strTemp As String Dim objStream As Scripting.TextStream Dim objFSO As New Scripting.FileSystemObject

Set objFolder = objFSO.GetFolder("C:\") For Each objFile in objFolder.Files Set objStream = objFile.OpenAsTextStream(ForReading) strTemp = objStream.ReadAll If InStr(strTemp, "texttofind") > 0 Then Debug.Print "Found text in " & objFile.Path strTemp = objFile Next objFile

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

SEARCHING FOR APOSTROPHES

This question comes up a lot: How do you search within SQL for a name like O'Brien? The problem is that the single quote character is used to delimit strings within SQL. The solution is to search for two single quotes. If you put this in your query, your database will translate those two single quotes into the single quote for which you're searching. This is a common problem in SQL Server, Oracle, and many other databases.

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

SAVING A RECORDSET TO DISK

One of the new features with ADO is the ability to save a recordset to a disk file. For data that doesn't change frequently (ZIP codes, states, and so on), you can write your program to retrieve the data from the database on a periodic basis (monthly, weekly, etc.) or based on a table's value. Once you retrieve the data, you save it to disk using the recordset's Save method. To open it again, you create a recordset and give it the filename (instead of a SQL statement) as the source of data. This is a nice way to cut some database traffic back and forth across the network.

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

SAVING A FILE

The reverse of our previous tip (reading a file) is to write content to a different file. Here's how you do it:

Dim objFSO As New Scripting.FileSystemObject Dim objStream As Scripting.TextStream Set objStream = objFSO.OpenTextFile("C:\TextFile.txt", _ ForWriting, True) objStream.Write txtData.Text objStream.Close

This writes all the contents of the text box into the file opened earlier in the code. Again, it's that simple...

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

RUNNING FROM THE CD-ROM

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.

http://www.macromedia.com

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

REVERSE SORTING A LISTVIEW CONTROL

A handy feature of Windows Explorer is the ability to click twice on a column header to sort that column's data in reverse order. If you used our previous tip on handling a simple sort, changing the code to handle a reverse sort is easy. The key is to check the column by which the data is currently sorted: If it's sorted by the same column that the user just clicked on, you simply reverse the sort. Here's the code to use:

Private Sub lvwProducts_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

If lvwProducts.SortKey = ColumnHeader.Index - 1 Then If lvwProducts.SortOrder = lvwAscending Then lvwProducts.SortOrder = lvwDescending Else lvwProducts.SortOrder = lvwAscending End If Else lvwProducts.SortKey = ColumnHeader.Index - 1 lvwProducts.SortOrder = lvwAscending End If

End Sub

Since users are accustomed to having this feature in Windows Explorer, it's a good idea to add it to your applications (those that use the TreeView control, anyway) as well.

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

RESPONDING TO THE COMMAND LINE

One of the less-used features in Visual Basic these days is the ability to read the command-line arguments. Using command-line arguments was almost a necessity in DOS days; however, it can still be used today with "Windowed" applications.

Any arguments you put on the command line are available at runtime through the Command$ function. The name of the application isn't provided, unlike applications written in languages like C or various shell scripting languages. However, anything else you put on the command line is put exactly as is into the Command$ function.

If you're testing, you can go to the Make tab of the Project Properties dialog box and enter whatever arguments you want to use for testing. These will be put into the Command$ function for you to use within the VB environment.

Once you have the arguments in the Command$ function, you have to deal with them. Normally, DOS options use this format: /option value /option2 value. There are other variations--use just about any DOS command with a /? option and you'll get a dump of the available options, either through text output or a message box popup. You can then use the Split function to break the string into multiple array records based on the location of the slash. You'll need to check to make sure the arguments are valid, based on your flag scheme. Just make sure you do support the /? option to display the available options, since of course, no one ever uses the online help.

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

RESIZING FORMS AND REPOSITIONING CONTROLS

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.

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

REMOVING COMPONENTS

If you need to remove a DLL from your computer, there are two steps to the process. First, you have to unregister the component. This is done using the regsvr32.exe utility, via the /u switch. To unregister foo.dll, for example, you might type this line:

regsvr32.exe /u C:\WinNT\System32\foo.dll

The second step would be to remove the actual file. I'd also suggest removing all copies of the DLL so that users aren't confused as to whether the component is available. (This will also stop users from trying to re-register an older copy of the library.)

Eric Smith is a consultant, writer, and trainer who specializes in Visual Basic, Active Server Pages, and SQL Server. He has written a number of books, including the Visual Basic 6 Bible and the Active Server Pages Bible. He also maintains the Web sites asptechniques.com and vbtechniques.com. You can contact Eric via his Web sites or directly at eric@northcomp.com.

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

REMEMBER TO SET COMMANDTYPE

Recently, I ran into a problem involving an ADO Command object. I was trying to use a stored procedure with SQL Server, but after setting everything--including the parameters--I was getting no results. Of course, the error messages didn't make any sense, either. After reviewing the code, I realized I hadn't set the CommandType property of the Command object. Once I set that, things worked properly. Here's the complete code:

Set cmdQuery = New ADODB.Command With cmdQuery Set .ActiveConnection = dcnDB .CommandType = adCmdStoredProc .CommandText = "sp_KeywordSearch" Set parItem = .CreateParameter("Keyword", adVarChar, _ adParamInput, 255, txtKeywords.Text) .Parameters.Append parItem End With Set rsData = New ADODB.Recordset rsData.Open cmdQuery, , adOpenStatic

In this case, the stored procedure is named sp_KeywordSearch and takes a parameter called Keyword. The parameter is created and then appended to the Command object. When you open the recordset object, instead of specifying a SQL string you supply a Command object.

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

REMEMBER TO CALL THE UPDATE METHOD

A user wrote me asking about some ADO code that she had in her application. She called the AddNew method, added all the data, and then closed the recordset. When she then opened another recordset to find the record, it wasn't there.

The problem was that while she had a dynamic recordset with an appropriate lock on it, she forgot to call the Update method to save the new record. The same goes for making changes to a record, but in that case, the AddNew method is not necessary.

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

RELEASING MEMORY WHEN YOU QUIT YOUR APPLICATION

If you're using the End method to close your application, you may be unnecessarily tying up resources. That's because the End method doesn't remove all forms from memory. To free up that memory, you'll need to reboot your system . . . or you can regain those resources by unloading each form using the Unload method before you end your application. To do so, use this procedure:

Public Function UnloadForms() Dim frm As Form For Each frm in Forms Unload frm Set frm = Nothing Next frm End Function

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

REGISTERING MTS COMPONENTS

In a previous tip, I said that MTS components have to be registered before use. As several readers pointed out, this is not entirely necessary: You can drag a new, unregistered component into an MTS package and the component will be registered for you. If you want to add a component to the package, you can select it from a list if it's registered. Dragging and dropping is often quicker since you don't have to figure out which component is yours.

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

READING A FILE

Previous tips have mentioned the use of the FileSystemObject component. However, we haven't discussed a simple version of opening a file and putting it into a control. Here's how you do it:

Dim objFSO As New Scripting.FileSystemObject Dim objStream As Scripting.TextStream Set objStream = objFSO.OpenTextFile("C:\TextFile.txt", _ ForReading, False) txtData.Text = objStream.ReadAll objStream.Close

This opens the text file named C:\TextFile.txt and puts it into a box called txtData. It's that simple...

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

READ PROPERTIES SPARINGLY

If you're looking at property values of objects, such as COM or ADO components, store the values in variables so that you don't have to read them more than once. Each property value read is expensive, in terms of computing resources. If you're doing it frequently, you're eating CPU cycles that could be better used elsewhere. A temporary variable allows you to reuse the property values without having to reread the object.

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

PROVIDING INSTANT FEEDBACK

One of the best things you can do for your users is provide lots of feedback about tasks during the run of the application. For instance, one simple thing you can do is change the cursor to an hourglass when the application is busy doing something. You can do this with a single line of code:

Screen.MousePointer = vbHourglass

Make sure you switch the pointer back if you display an error message or otherwise interrupt the procedure. (If not, the cursor won't click properly.) Here's the code to reset the pointer:

Screen.MousePointer = vbDefault

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

PROCEDURE TOO LARGE MESSAGE

Well, this is a new one for me: A user said that he had a procedure with approximately 4,000 ListBox AddItem lines in it and was getting an error from Visual Basic about the procedure being too long.

I personally don't remember VB having a limit on the number of lines in a procedure, but this guy found it. The first thing I would suggest is that the data (entered manually) be put into a database. The developer could then query the database and return as many rows as necessary for the application.

The point of this is that if you're manually entering more than a few data values, that data should be put in some sort of permanent storage mechanism OUTSIDE the application. This includes files, databases, XML files, or whatever you want--just don't put it directly in the application.

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

PRINT GENERATED SQL

When I'm dynamically building a long SQL statement, I often find it handy to use Debug.Print to print the SQL to the Immediate window. This makes it easier to find errors as things are running. At runtime, however, it's often helpful to write the text to a text file for logging and debugging later.

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

PASSING PARAMETERS

There's always some confusion about passing parameters to subroutines and functions in Visual Basic. Whenever you pass a parameter to a function, it is (by default) passed by reference. This means that if you change the value of the parameter within the subroutine or function, it changes the original variable as well. This has good and not-so-good uses: If you have a function that needs to update more than one parameter (you can use a function to return one value), you want to pass the variables by reference. However, if you want to prevent this from happening, define the parameter as ByVal, as shown here:

Private Sub Test(ByVal testvar As String)

You don't have to use ByVal when you call the function--just put it in the definition. That will prevent any inadvertent omissions of this critical keyword.

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

PARSING A FILE

A reader asked about easy ways to parse files that are delimited. In a delimited file, a particular character, such as a comma or tab, separates each field. If you're using Visual Basic 6.0, there's an easy way to do this in one step: Use the Split function. This function will break a line into an array of fields, based on the location of a particular character. Add this function to the FileSystemObject and you have an easy-to-use parser. Here's an example:

File Contents:

Eric#Smith#123 Main St#Anytown#VA#12345 Erica#Smith#234 Main St#Anytown#VA#12345 Erick#Smith#345 Main St#Anytown#VA#12345

Each field is separated using a pound sign. This could just as easily be a tab character. The character really doesn't matter. Here's the code that will read the file line by line and break each line into a number of fields:

Sub Main() Dim objFSO As New Scripting.FileSystemObject Dim objStream As Scripting.TextStream Dim strLine As String Dim a_strFields() As String

Set objStream = objFSO.OpenTextFile("C:\MyFile.txt", ForReading) Do Until objStream.AtEndOfStream strLine = objStream.ReadLine a_strFields = Split(strLine, "#") Loop objStream.Close End Sub

Note that the Split function is called right after populating the a_strFields array. The Lbound and Ubound functions can tell you how many fields are in the array.

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

PACKAGING AND DEPLOYMENT INSTALLATION ERRORS

A number of people have reported problems with the Packaging and Deployment Wizard under Windows 2000. The good news is that some of these errors have been corrected via the Visual Studio Service Pack. The current service pack for VS is SP 4, and it's available at this URL:

Visual Studio Service Pack 4 http://msdn.microsoft.com/vbasic

I'd recommend at least checking out what SP4 fixes--it might take care of any problems you're having.

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

OWNERSHIP OF CODE

Here's a non-technical tip for today... A user asked about ownership of code that he wrote for a client. The basic answer is: It all depends what you negotiated. Typically, if you work full-time for someone as an employee, your employer owns anything you create. If you're an independent consultant working for a client, it's up to you to ensure you both agree to ownership--and get it in writing.

Typically, I will give ownership to the client but include provisions that allow me to use the code for other clients, as long as they don't compete directly with the first client. This lets me reuse code I develop but still protects the original client's investment. Whatever you negotiate, make sure it's on paper. Also, it's a good idea to get in touch with an intellectual property lawyer if you're really concerned about ownership. They are the only ones qualified to give legal advice.

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

ORACLE AND MTS

If you're doing development using Oracle, OLE DB, and MTS, you'll be interested in the "Oracle OLE DB and MTS" article at the VB2TheMax.com site. Written by Bruce Sanderson, the article shows how to integrate ADO, OLE DB, Oracle, and VB components into a middleware solution.

VB2TheMax: Oracle OLE DB and MTS http://www.vb2themax.com/HtmlDoc.asp?Table=Articles&ID=220

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

ODBC and ADO

A couple of readers have asked how the ODBC Control Panel applet figures into using ADO. The short answer is that for most databases, it isn't necessary. ADO can talk directly to many types of databases, including Oracle, SQL Server, Access, and more, without requiring any changes to ODBC on each machine. ODBC can be used if you have an ODBC driver for your database but don't have an OLE DB provider. In this case, you'll use the OLE DB provider for ODBC, which in turn can talk to your ODBC database. You'll need to make the appropriate ODBC entries in this case, but not under normal circumstances.

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

NO RECORDCOUNT IN RECORDSET

A user sent me a snippet of code showing that the RecordCount property for his ADO recordset was coming back as -1. The solution to this was to use a type of recordset different from the ForwardOnly recordset he had used. The forward-only recordset doesn't include the navigation and positioning information that would provide a correct RecordCount value.

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

NO NEED TO WRITE CODE WHEN YOU'VE GOT THE DATA CONTROL

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.

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

NESTING FORMS

If you want to build an application that has several panes (as in Microsoft Outlook), you can use a component just released from Data Dynamics called Active Sizer. This product has a subform control that allows you to nest one form within another. Without a control like this, you can't easily nest one form inside another. Typically, the alternative is to use a Splitter or another similar control, available from several different vendors. However, those controls still use just a single form with one or more Splitters on it.

Data Dynamics Active Sizer http://www.datadynamics.com/products/

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

MULTIPLE KEYWORD SEARCHES

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:

Dim strWord1 As String Dim strWord2 As String Dim strSQL As String

strSQL = "SELECT * FROM tblCustomers " _ & "WHERE LastName LIKE '%" & strWord1 _ & "%' OR LastName LIKE '%" & strWord2 & "%'"

As you can see, you simply OR the conditions together, which will give you the union of the results from both parts of the query. Be sure to get all the single and double quotes in the right place, or you'll have a SQL statement that isn't correct.

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

MULTIPLE CLASSES PER DLL

A user recently asked about combining classes into a DLL. He had previously set up separate projects for each class file, which really wasn't necessary. As long as the amount of code is manageable, a single DLL would be more efficient and easier to manage. I try to break up my DLLs by function or application group, but you can divide them as you see fit. As usual, the fewer libraries and executables, the better. It's easier to update a single file than it is to change multiple files in multiple locations. However, this topic wades into personal opinion pretty quickly, so use your best judgment.

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

MULTIDIMENSIONAL CONTROL ARRAY

A user creating a game program asked about creating a multidimensional control array for his input controls (CommandButtons, I believe). The short answer is that control arrays in Visual Basic currently support only a single dimension. For his game, he needed a 10x10 grid. Using a simple coordinate system of (row, column), it's easy to map to a one-dimensional array of controls. For instance, row 2, column 6 could calculate to the second row of controls, which would be controls 11-20, if you're using one as the first control index. If you're using zero, the controls would be 10-19. The calculation would be ((row - 1)

· 10) + column. If you start at zero, you would need to subtract one from the column value. Once you have the control index number, you could store your data in the control's Tag property or separately in another array.

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

MOVING CONTROLS INTO A FRAME

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.

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

MCSD RECERTIFICATION REMINDER

If you are an MCSD who certified under the old track, which included two Windows Architecture tests, you need to get recertified using the new track. The new track requires two developer tools, a test (70-100) about creating solution architectures, and then an elective. As of this writing, the deadline for recertification was fast approaching... but it had already been extended once, so maybe it will be extended again.

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

MANUALLY CREATING A RECORDSET

One of the features of the ADO recordset is that it can be created manually. Instead of creating a recordset by way of a database, you can create a recordset, add fields of various types to it, and then add data. You can then manipulate it just like a database-created recordset, save it to disk, or pass it back from a component for use in an application.

After you create the recordset object, you can use the Add method of the Fields collection to create new fields for your recordset. When you create these fields, you can specify the data type, the field length, and so on. Once you have the fields added, you can use the Open method to open the recordset for modification. You then use the AddNew method to create a new record, followed by the Update method to commit the record to the recordset. Refer to the ADO Fields collection for more information about this feature.

Alan Silver mentioned another use for this feature: He was retrieving filenames from disk and needed to sort them. Instead of writing his own sort routine, he created an ADO recordset, loaded the filenames (along with the other data about them), and used the Sort property to automatically sort the data. This requires far less effort than any other sort routine I've used in the past. Thanks for the tip, Alan.

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

LIST BOX VS. DROP-DOWN LIST

A common question I get is when to use a drop-down list box versus when to use a list box. There are a few considerations to take into account. If you have to pick more than one item from the list, a list box control is required. If you have more than 50 items, scrolling through them in a drop-down list can get tedious and should be avoided. If you have a small number of items, it really comes down to how much screen real estate you have available. You can create a drop-down list and save space, or show a list box and display more items. It's really up to you, and there isn't any particular guideline other than those already mentioned.

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

LEARNING EVENT HANDLING

I get questions every so often about how to learn when various events are triggered during the run of a Visual Basic program. One of the easiest ways to find out when events occur is to add Debug.Print statements to your event handlers. These messages will be printed to the Immediate window as the events are triggered. Don't make the mistake of using the MsgBox function. The MsgBox function will interrupt the normal event flow and give you inaccurate results regarding when events happen.

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

KEY PROPERTY CANNOT BE NUMERIC

When adding ListItem objects to a ListView or Node objects to a TreeView, remember that the Key property cannot be just numeric. The value must begin with one letter, at a minimum. When I build applications using either of these controls, I typically use the primary key of the database table as part of the key. I then prefix that value with a letter (or two letters), indicating the table from which the data comes. I do this because the key has to be unique throughout the control's data collection (whichever collection is being used).

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

JULIAN DATES

In a previous tip, you learned how to get the "Julian" date for a given date using the Format statement. The date we're talking about is the day number starting with January 1 of the current year. However, several readers have reminded me that this isn't really the Julian date. By definition, a Julian date is the number of days that have elapsed since noon on January 1 of the year 4713 BC. Thanks to both Mike Shaffer and Sid Hollander for this tip.

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

JOIN THE VB DESIGN TEAM

Want to help build a real Visual Basic application on the Web? Visit the VB Design Team site to read about this interesting project--and to find out how to join the project. This is a good way to get some experience working on a distributed, worldwide team. The team is working on a number of different applications that encompass various features not commonly seen in VB applications.

VB Design Team http://vbdesignteam.com/

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

INTRODUCING ADO+

Microsoft continues to let out sneak peeks of the next version of Visual Studio and related technologies. The latest one is ADO+, which uses XML for moving data back and forth. You can read more about ADO+ on Microsoft's Web site.

Introducing ADO+ http://msdn.microsoft.com/vstudio/nextgen/technology/adoplus.asp

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

INT VS. CINT

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

Int() returns the same data type as the value it's passed. CInt() always returns an Integer data type. In addition, CInt rounds the value given to it, while Int truncates the decimal portion.

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

INHERITANCE IN VISUAL BASIC

A user recently asked about how Visual Basic implements inheritance. For starters, inheritance is used when discussing objects and classes. To take a simple example, let's say you have three classes: Animal, Dog, and Cat. There are some characteristics that are common between cats and dogs, and some that aren't. In this case, you would put the common characteristics (color, weight, height, etc.) in the Animal class and put things that are specific to each animal type in the appropriate class. Dog and Cat would inherit characteristics from the Animal class and add their own characteristics to that list to create a composite list of characteristics.

In the current version of Visual Basic (6.0), there isn't support for this type of inheritance. Languages like C++ and Smalltalk support inheritance. However, the next version of Visual Basic is going to include this and other object-oriented features. For more information, you can visit Microsoft's Visual Basic site:

http://www.microsoft.com/vbasic/

Here, you'll find an article about the next generation of Visual Studio that includes inheritance, as well as other cool features.

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

IMPROVING TREEVIEW PERFORMANCE

If you're loading a TreeView control with a large amount of data, the default behavior of the TreeView control is to refresh frequently, which slows down the data-loading process. Here's a tip provided by Rick Fleming as to how to make things go faster by disabling the refresh process:

"I recently ran into a similar situation, with TreeViews, ListViews, and Listboxes. The reason it is so slow is a WM_PAINT message gets thrown every time you add an item. You can speed this up significantly, and do something else cool, and still load everything at once. The trick is to do something like the following:

TreeView1.Nodes.Clear ' I am doing this from memory, can't remember if this exists or not. TreeView1.Nodes.Add , , "TempRoot", "Loading Data..."

SendMessage TreeView1.hWnd, WM_SETREDRAW, False, 0& ' This forces the control NOT to proceed on WM_PAINT

TreeView1.Nodes.Clear ' The "Loading Data..." will still be visible

{Load your tree}

SendMessage TreeView1.hWnd, WM_SETREDRAW, True, 0& TreeView1.Refresh Form1.Refresh ' Sometimes you might have to do a InvalidateRect to force a redraw...

Now your tree is loaded... one screen I did loaded ~12,000 records from an Access97 database in 20 seconds with WM_SETREDRAW on, and .12 seconds (notice the decimal) with WM_SETREDRAW off."

Thanks for the tip, Rick.

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

HIGHLIGHTING TEXTBOX DATA

If you have an application that has a search routine, it's nice to highlight the text for which the user was looking. Using the SelStart and SelLength properties of the TextBox control, this is easy to do. You set SelStart to be the position to start highlighting, remembering that SelStart uses 0 as the first character in the TextBox. SelLength specifies the number of characters to highlight.

Here's a quick example, using txtData as the TextBox on the form:

Private Sub mnuSearchFind_Click() Dim lngPos As Long

m_strSearch = InputBox("Enter the text to find.", "Find Text") lngPos = InStr(1, txtData.Text, m_strSearch, vbTextCompare) If lngPos > 0 Then txtData.SelStart = lngPos - 1 txtData.SelLength = Len(m_strSearch) Else MsgBox "Search text was not found.", vbExclamation m_strSearch = "" End If End Sub

m_strSearch is a string defined at the module level so that we can perform a "search again". The key code sets the SelStart and SelLength properties when InStr returns a non-zero result. InStr works with 1 as the first character, so we have to subtract 1 from it to get the correct starting location in the TextBox control.

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

HELP WITH HTML HELP

If you're like most help developers, HTML Help is giving you trouble. If you need help with creating it, you can visit the HTML Help Center at this URL:

http://mvps.org/htmlhelpcenter/

There are lots of tips and some examples of how to build HTML Help format files for Visual Basic. While I'm not a big fan of this format, Microsoft is putting its weight behind the format, which means it will likely be around for a while.

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

HANDLING DUPLICATE FIELDS IN A TABLE

A user asked me how best to handle the following database table structure that he had created:

Table: Invoices PartNumber - 1 to 15 Quantity - 1 to 15 Description - 1 to 15

The user wasn't sure whether he should use a few arrays or some other structure to maintain the data.

The quick answer is that a table like this is not the best design for a relational database table. The idea of a relational database is to eliminate duplicate fields from a table. Instead of holding space open for 15 possible rows of data, it's better to break the data into two tables: The first contains the "singular" information about the invoice, such as the number, date, shipper, etc.; the second contains each of the line items for the invoice. That table might look like this:

Table: InvoiceLines PartNumber Quantity Description

This structure has the benefit of not wasting space for invoices that have less than 15 rows and gives the ability to have more than 15 rows for invoices that require the extra space.

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

GREAT SITE FOR API CODE

If you're looking for examples that use the Windows API to do cool stuff in Visual Basic, take a look at Karl Peterson's One Stop Source Shop:

http://www.mvps.org/vb/

You'll find lots of samples for questions that I get often, such as how to get the user's login ID, use the common dialog API calls, and so on.

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

GETTING DATA FROM TWO DATABASES

A user recently asked if it was possible to select data from two separate databases. The short answer is no. However, you can reference one database's table from another database. Once you've linked the tables, you can select from both of them. This may vary if you're using databases other than SQL Server or Access, but most databases operate in this way.

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

GETTING A USER'S NETWORK NAME

If you're building an application that uses usernames, it's a nice, simple feature to prompt the user for a password after first determining the user's network login ID. The code shown here will do this for you. Add the Declare statement to the top of the module or into a separate code module: Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" _ (ByVal lpName As String, ByVal lpUserName As String, _ lpnLength As Long) As Long

'Your code goes here

Dim strUserName As String

strUserName = Space(255) WNetGetUser "", strUserName, 255 txtUserName = Trim(strUserName)

'Your other code goes here

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

GETTING A FORM'S HANDLE

Many API calls require the use of a window handle, or hWnd for short. If you need to pass a handle to the current form, use the hWnd property of the form. That will link the API call to the current form.

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

GET THE LATEST SERVICE PACKS

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:

http://www.microsoft.com/vbasic

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.

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

GENERATING A JULIAN DATE

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.

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

FREE VB NEWSLETTER AT ABOUT.COM

You can sign up for a free Visual Basic newsletter at About.com. Specifically, you can expect to be kept abreast of (in its words) "news and views from Visual Basic."

Here's the URL:

http://visualbasic.about.com/compute/visualbasic/gi/pages/mmail.htm

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

FINDING OLE DB PROVIDERS

When you're using ADO, you have to use an OLE DB provider to access the database. If you're using a database that doesn't have an OLE DB provider, you can get one from several companies. Microsoft maintains a site with all of the third-party OLE DB providers that are available. Here's the URL:

http://www.microsoft.com/data/partners/products.htm

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

FILTERING A RECORDSET

If you have a recordset already created and want to filter it by some criteria, you can use the Filter property of the recordset to do so. Just change the recordset's Filter property to a WHERE clause and then set the recordset object to another recordset variable, like this:

Dim dcnDB As New ADODB.Connection Dim objRS As New ADODB.Recordset Dim objFilter As ADODB.Recordset

dcnDB.Open "Some connection string" Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT * FROM tblOrders", dcnDB, adOpenStatic objRS.Filter = "CustomerName Like %Smith%" Set objFilter = objRS

Depending on how big the recordset is, you may want to just do another query from the database. It will probably run faster, especially if you create a stored procedure to do the work for you. Also, make sure you have indexes on the fields that you're using in your filters.

Remember that you also have the option, with ADO recordsets, to use the Save method to save the recordset to disk. Using this makes filtering more useful since you can keep a somewhat static recordset on disk and use the filtering option to retrieve only the records you really need.

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

FILESYSTEMOBJECT IN VISUAL BASIC

Want to take advantage of FileSystemObject and its related objects? You can make use of them in Visual Basic simply by referencing the Microsoft Scripting Runtime. The file should be listed in your References dialog box and is named SCRRUN.DLL. These objects make file, folder, and disk manipulation far easier than previously implemented.

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

EXECUTING STORED PROCEDURES

If you have an Oracle or SQL Server stored procedure that you want to execute, the quickest way is to use the Execute method of the ADO Connection object. Here's some sample code:

Dim dcnDB As New ADODB.Connection dcnDB.ConnectionString = "your connection string goes here" dcnDB.Open dcnDB.Execute "sp_DoSomethingStoredProcedure"

This particular stored procedure does not return values; rather, it simply runs and exits.

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

ESTABLISHING A CONNECTION TO AN ISP

The Visual Basic Internet Programming site recently featured an article that shows how to use RAS to connect to an ISP. The API is fairly simple to use, and the article includes a sample download file:

WinInet API: Establishing Connection to ISP http://www.vbip.com/wininet/wininet_dialup.asp

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

DYNAMIC FORMATTING

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.

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

DOWNLOADING COMPONENTS

Cnet, "the computer network," has maintained a large component library for a number of years. At one point, it was located at www.activex.com; however, the direct link to the site is now

http://download.cnet.com/downloads/0-10081.html

This is a great site with a huge sampling of commercial, shareware, and free controls for use in Visual Basic.

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

DON'T USE RESERVED WORDS

A reader recently passed on a reminder to the rest of us to not use reserved words. While we know this in Visual Basic--since VB will generally catch that type of syntax error--the suggestion was to make sure you're not doing it in your database tables. The story he related was that one of his fields was named ALL, which is a reserved word in SQL. There are some less-used keywords in SQL that can trip you up like this. If SQL starts complaining about a table or query, first check to make sure your fields and table names aren't using keywords. The syntax highlighting provided by Enterprise Manager, for instance, can help you detect these bugs.

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

DOING STRING COMPARISONS

In a previous tip, I talked about comparing strings and how an uppercase string is not normally equal to its lowercase equivalent. I suggested that you use either the Lcase function or the UCase function to switch the case of both strings. However, there's an easier way to do this that I had forgotten about: the StrComp function. This function allows you to compare two strings in either binary (exact case-sensitive) or text (case-insensitive) mode. Here's an example:

Debug.Print StrComp("test", "TEST", vbTextCompare)

This will return True, since text comparisons are case-insensitive. You can also use vbBinaryCompare if you want an exact comparison.

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

DISABLING THE WINDOWS CLOSE BUTTON

If you're like many developers, you want to control how and when a user exits a form. Unfortunately, Visual Basic forms include the Windows Close button (X), and there's no property that disables it. Don't let that limit your developing talents, however. Use the GetSystemMenu and RemoveMenu API functions to disable the Close button. First, open a module and enter the following declarations:

Declare Function GetSystemMenu Lib "User32" (ByVal hWnd As Integer, ByVal bRevert As Integer) As Integer

Declare Function RemoveMenu Lib "User32" (ByVal hMenu As Integer, ByVal nPosition As Integer, ByVal wFlags As Integer) As Integer

Global Const MF_BYPOSITION = &H400

Then, open your form and enter the following code in the form's Load event:

Private Sub Form_Load() SystemMenu% = GetSystemMenu(hWnd, 0) Res% = RemoveMenu(SystemMenu%, 6, MF_BYPOSITION) End Sub

It's that simple--when you run the form, the Close button will be missing.

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

DETERMINING HOW MANY RECORDS ARE AFFECTED

Using the ADO Connection object, you can use the Execute method to run queries. However, it's often helpful to know how many new records were created, updated, or deleted. Fortunately, you can do this by simply passing a variable into the Execute method as the second parameter, in which case ADO will fill that variable with the number of records that were touched. Here's an example:

Dim cnDB As New ADODB.Connection Dim lngRecords As Long cnDB.ConnectionString = "some connection string" cnDB.Open cnDB.Execute "UPDATE Products SET UnitPrice = UnitPrice * 1.05", lngRecords

The variable lngRecords will hold the number of records that were updated.

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

DECOMPILING VISUAL BASIC

A common question I 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, it appears that there no longer is a VB decompiler available for recent versions of VB. At one point, a company called AshSoft had one; however, the company doesn't seem to exist any longer.

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

DECOMPILING VISUAL BASIC

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.

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

DATA ACCESS LIBRARY CONFLICTS

In a previous tip, I mentioned that you must be careful about using data access objects since some of them have the same name. However, you can eliminate the problem by always specifying the library name before the object name, such as

Dim objDB As DAO.Database Dim rsData As DAO.Recordset

As long as you specify the library name, there won't be a question as to which library is in use.

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

DAT FILES, REVISITED

Previously, we've discussed DAT files and how they're often used to hide the source of the application that created them. Since this concept was misunderstood by several people, I figured I'd better clarify:

The idea here is that if you're storing data files locally, you don't necessarily want people opening them outside your application. For instance, if you're storing data in an Access database from your application, saving the data file as an MDB file gives this away. However, if you rename the file DAT or something else, you disguise the format of the data, making it tougher to figure out which application to use. Of course, you can always password-protect your Access databases and hard-code the password within your application, but that's a tip for another day.

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

CUSTOMIZE YOUR MENUS AND TOOLBARS

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.

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

CUSTOM ERROR NUMBERS

When defining your own error numbers in Visual Basic applications or COM components, remember to add the constant vbObjectError. Adding this value to your error values will ensure you stay clear of the reserved numbers that Microsoft products are already using. You can either use the built-in messages for errors lower than this number or create your own messages for errors higher than this value.

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

CRYPTOGRAPHY IN VISUAL BASIC

One of the more important topics in application security is the handling of encryption. There are a group of API calls known as the Cryptography API built into the Windows operating system. Along with a new book by Richard Bondi, there is an open source set of COM wrappers for these API calls. These objects are much easier to use than the actual API sets. To download this code, go to

http://www.geocities.com/richardbondi/wcco1.0.zip

For the documentation, visit

http://www.geocities.com/richardbondi/wcco_manual.zip

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

CREATING PROJECT TEMPLATES

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.

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

CREATING GAMES WITH VISUAL BASIC

While creating games in Visual Basic (and Windows) used to be a bit of a joke, DirectX has changed all that. In fact, the next version of VB is rumored to have DirectX support. For now, however, you can use the DirectX SDK by downloading it from the Microsoft DirectX Developer Center:

http://msdn.microsoft.com/directx/

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

CREATING DESIGN-EFFICIENT FORMS

Your users will spend most of their time entering data on forms and clicking buttons, so it's important that you create forms that not only pull them through the task, but also reduce eyestrain. A busy screen not only confuses users, it slows them down. These simple-to-apply guidelines will make your forms easy to view and easy to use:

Use white space effectively by separating information from the surrounding objects using margins and gutters, and by using blank lines between subgroups to show relationships. Align fields vertically and set the tab order accordingly. Avoid horizontal movement from control to control. Group all your related fields together. Use borders or white space to separate these groups from other groups. Left-justify your label text.

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

CREATING AND USING STORED PROCEDURES

One of the easiest ways to improve your application's performance is to take advantage of precompiled queries, also called stored procedures in databases like SQL Server and Oracle. ADO provides a mechanism to use these stored procedures with parameters, which allows you to create a stored procedure with a hole for a value you supply at runtime. To do this, use the code below, which invokes a simple stored procedure with a single parameter.

Note: Be sure you add the Active Data Objects 2.0 library to your Visual Basic project before using this code. All values prefixed with

are predefined constants.

Dim cmdSP As ADODB.Command Dim parCustomerID As ADODB.Parameter Dim rsData As ADODB.Recordset

Set cmdSP = New ADODB.Command

' dcnDatabase is the current database connection ' and is an ADODB.Connection object opened elsewhere

Set cmdSP.ActiveConnection = dcnDatabase cmdSP.CommandText = "sp_GetCustomerByID" cmdSP.CommandType = adCmdStoredProc

' Create the parameter and set its data type ' and parameter type

Set parCustomerID = cmdSP.CreateParameter("CustomerID") parCustomerID.Type = adInteger parCustomerID.Direction = adParamInput parCustomerID.Value = varID

cmdSP.Parameters.Append parCustomerID

Set rsData = cmdSP.Open

' At this point, you have an open recordset ' that has data that can be read by your code.

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

CREATING ALTERNATE CURRENCY FORMATS

A user from India asked me about creating a currency value using the Indian format, which looks like this: 99,99,99,999.99. This is easy to do using the Format statement. Remember that a pound sign in the format represents a digit that will be shown if it is significant--that is, not a leading or trailing zero. A zero in the formatting expression represents a digit that must always be shown. For his particular format, here's how it can be done:

Debug.Print Format(99999123123.123123, "##,##,##,##,##,##,##,##0.00")

This statement prints out the value: 99,99,91,23,123.12. If you're using standard US notation, you'd use this statement:

Debug.Print Format(99999123123.123123, "###,###,###,###,##0.00")

If you have larger numbers, be sure to add format characters to handle the length of the value.

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

CREATING A REGISTRATION SCHEME

Most software that I use frequently starts out as shareware but requires registration after a certain number of uses. There are lots of fancy ways to do registration, but the easiest doesn't require any software at all. A dialog box for registration will typically ask people for their name and registration number. What you can do is use the person's name to generate the registration number. The only real requirement is that you can generate the number again in a reliable fashion, which means random numbers can't easily be used. However, there are lots of other things you can do. For instance:

Dim strName As String Dim strRegCode As String Dim strLetter1 As String Dim strLetter2 As String

strName = "Eric Smith" strName = UCase(Left(strName, 2)) strLetter1 = Left(strName, 1) strLetter2 = Right(strName, 1)

strRegCode = Asc(strLetter1) & (Asc(strLetter1) ^ 3) _ & Int(Asc(strLetter1) ^ 0.5) & (Asc(strLetter2) ^ 3) strRegCode = Left(strRegCode, 4) & "-" & Mid(strRegCode, 5)

What I did here was take the name and use only the first two letters to build the code. I build up the code by using calculations of the ASCII value of characters and then put in a dash just for fun. For instance, "Eric Smith" generates a code of 6932-85098551368, which basically looks like a random sequence of numbers.

To use this, you create a program at your end where you type in the person's name and it spits out the registration code. In your software, you use the same function to verify that the person's name and the code the person enters are what you calculate they should be. You do this because breaking the registration code after it has been created would be a bit of a pain using this particular sequence.

Once you verify that the software is registered, you store the name and code in the registry and check it when the software loads. As long as the code and name match, your software can function normally.

If you're worried about the code being too easy to break, just change the way you build the code. If you want to use five letters to build the code, just make sure that you require a name that's at least five characters long.

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

CREATING A REGISTRATION CODE

There are lots of shareware packages that allow you to register electronically, and this is one feature customers really appreciate. The key (so to speak) to online registration is the ability to generate a key based on a static piece of information. For instance, WinZip asks for your name and your key. That indicates to me that they are generating the key based, at least partially, on the name. For instance, the application might add up the character values and multiply them by some value. To force the length to a particular size, you can Mod the value or simply use the first x number of characters. The key is to make the calculation repeatable and reliable.

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

CREATING A REALISTIC PROGRESS BAR

In most installation routines, you'll see a progress bar that supposedly represents the degree to which the installation is complete. The funny thing is that the installation progress bar always seems to sit on 95 or 98 percent forever. If you're building one of these for your application, I'd suggest looking at your process realistically to determine approximately how long it's going to take to do each task. If you're creating a database, for instance, each table creation will take approximately the same amount of time. If you have ten tables, each time you complete a table, you could increment your progress bar by 10 percent. If you have additional tasks, time how long they take and adjust your timing appropriately. I'd also suggest running the timing on multiple machines so that you get as accurate a timing as possible.

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

COPYING PROJECT REFERENCES

A reader recently wrote to me with problems in the libraries that his project was referencing. He couldn't find the library that another developer was using and was wondering if there was a way to copy the project references from one project to another.

The easy answer to this is to share the project file, but if that isn't an option, open the project (VBP) file in Notepad and copy the lines at the top that begin with the word Reference. The lines will look something like this:

Reference=*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\WINNT\System32\stdole2.tlb#OLE Automation Reference=*\G{00000201-0000-0010-8000-00AA006D2EA4}#2.1#0#C:\Program Files\Common Files\system\ado\msado21.tlb#Microsoft ActiveX Data Objects 2.1 Library Reference=*\G{74C08640-CEDB-11CF-8B49-00AA00B8A790}#1.0#0#C:\WINNT\System32\COMSVCS.DLL#Microsoft Transaction Server Type Library

Drop these into the other project file, and assuming the libraries are installed on the other machine, things will work fine. Again, however, the best approach is to copy the project file itself, even if you have to remove all the modules from it.

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

CONVERTING A .DAT FILE

A user sent me a question about how to convert a .DAT file into something he could use in Visual Basic. The problem is that a .DAT file isn't a particular format, as far as I know. The solution to the problem would be to get the file format (if possible) and use that to interpret the file. Alternatively, if the file is in a database format, you might be able to find an ODBC driver or OLE DB provider for it.

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

CONNECTING TO ACCESS 2000 DATABASES

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

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

CONNECTING TO ACCESS 2000

This question keeps coming up, so I'll answer it again: To connect to an Access 2000 database, you need to use the Jet 4.0 provider with ADO/OLE DB, like so:

Dim dcnDB As New ADODB.Connection dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=D:\Mydatabase.mdb;" dcnDB.Open

I've seen some users using syntax involving DBQ and curly braces, which seems to work okay. However, MSDN uses the method and syntax shown here, so that's what I stick with.

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

CONCATENATING TEXT

When concatenating strings, be sure to use the ampersand instead of the plus sign. While the plus sign works, it has some nasty side effects when dealing with strings that contain numeric data. If you need to do math on your strings, use one of the various numeric conversion functions--such as CDbl, CLng, or CInt, among others--first.

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

COMPONENT REGISTRATION SHORTCUT

Since I build a lot of COM DLLs on my systems, I've created a shortcut to register the components on the system. If you double-click a DLL file and don't have a default application already registered for it, a dialog box will appear asking which application to use to open the file. Navigate to your Windows or Windows\System directory and find RegSvr32.exe. If you use this application to open your DLL, it has the effect of registering the component. This double-click method is easier than having to type in the full command line each time.

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

COMPARING STRINGS

I'm commonly asked questions dealing with checking passwords against each other (in a confirmation mode, for instance) or against a stored password. One key thing you have to decide when doing this is whether case is important. Some systems I've used are case sensitive; others aren't. If you have decided to not check case when checking a password, be sure to indicate either lowercase values (LCase) or uppercase (UCase). This way, you're comparing "apples to apples," so to speak. Otherwise, a mix of capital letters will cause the values to never compare properly.

Keep in mind, too, that you should let users know whether case is factored as they enter their password.

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

COMPARING DATE VALUES IN SQL SERVER

If you are comparing dates within SQL Server, you may get some unexpected results if you don't remember that every date value contains both date and time components. For instance,

6-19-2000

is not the same as

6-19-2000 7:00 PM

because any date without a time is given a time of 00:00:00. If you do need to compare dates, you can use any number of functions to compare month, day, and year. You can also convert both dates to text using the CONVERT function and then compare just the date portion.

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

COMBINING TEXT FILES

A user asked if it was possible to combine multiple text files into a single file using Visual Basic. The short answer is yes. There are several ways to do this. My favorite involves the use of FileSystemObject. For instance, let's say that you wanted to merge all the files with the extension .log into a new file named master.txt.

Sub Main() Dim objFSO As New Scripting.FileSystemObject Dim strText As String Dim objSource As Scripting.TextStream Dim objDest As Scripting.TextStream Dim objFile As Scripting.File Dim objFolder As Scripting.Folder

Set objFolder = objFSO.GetFolder("D:\LogFiles") Set objDest = objFSO.OpenTextFile("D:\master.txt", ForAppending, True)

For Each objFile In objFolder.Files If Right(objFile.Path, 4) = ".log" Then Set objSource = objFile.OpenAsTextStream(ForReading) objDest.Write objSource.ReadAll objSource.Close End If Next objFile objDest.Close

End Sub

This code will loop through all the files in the specified directory looking for files with the .log extension. It reads them and then writes them to the file specified by the objDest object.

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

CLEANING YOUR SQL DATA

One big problem many people encounter when writing SQL within Visual Basic is dealing with data supplied by users. For example, you may have some code that looks like this:

Dim strSQL strSQL = "SELECT * FROM Customers WHERE CompanyName = '" _ & Request.Form("Keywords") & "'"

This code will return all the customers where the CompanyName field is equal to whatever was input via the Keywords field on a form. Simple enough, you'd think. But a problem arises when you have a name with a single quote in it, such as the name O'Reilly. The single quote character breaks the SQL statement.

In a situation like this, the easiest thing to do is "clean" the input data before putting it into the SQL statement. Here's a quick function you can use, which in turn employs the SQ constant that holds a single quote.

Const SQ = "'" Function Clean(strData As String) As String Clean = Replace(strData, SQ, SQ & SQ) End Function

The original chunk of code looks like this with the Clean function in place:

Dim strSQL As String strSQL = "SELECT * FROM Customers WHERE CompanyName = '" _ & Clean(Request.Form("Keywords")) & "'"

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

CHECKING TEXT BOXES FOR CHANGES

If you've ever needed to do validation on a text box, you've probably run into the fact that any change, no matter how small, causes a Change event to be triggered on a TextBox control. A better way to check the value when the user is done is to use the LostFocus event. This event will be triggered only when the user leaves the field. However, don't make the mistake of putting the cursor back into the same box using the SetFocus event. Doing so will create an infinite loop, out of which the user can't escape.

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

CHECKING AN OBJECT FOR NOTHING

If you need to check an object variable to determine if it is Nothing, don't use the equal sign, like this:

If objVariable = Nothing Then ... more code End If

Instead, you need to use the Is operator, like so:

If objVariable Is Nothing Then ... more code End If

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

CHANGING LISTBOX STYLES

The Visual Basic 6.0 ListBox control has a new property: Style. This property allows you to create a ListBox that uses check boxes for each item. A user recently asked how to change the Style property at runtime. The answer is that you can't do it. The documentation for the Style property indicates that this property is read-only at runtime, which means you have to stick with one style or the other while your program is running.

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

CHANGING FORM TITLE BAR COLOR

A user asked if there was a way to programmatically change the color of a form's title bar. The short answer to this is no. The color of the title bar is controlled by the user in Control Panel, which means you don't have access to it within Visual Basic. Several books are available that can teach you how to manipulate Control Panel, but this is not a simple task. Dan Appleman's VB API book has some of this information, as do several books from Wrox Press that cover the Windows API.

Dan Appleman's Visual Basic Programmer's Guide to the Win32 API http://www.amazon.com/exec/obidos/ASIN/0672315904/tipworld

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

CAN'T PUT A LABEL ON ANOTHER CONTROL

One problem you might run into when using Label controls is that you can't move a Label control on top of a "windowed" control. Most controls are considered to be windowed controls. The best solution is to use a control like a PictureBox, put the label inside it, and then layer the PictureBox as necessary.

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

CALLING EVENT HANDLERS AS SUBROUTINES

When you're writing a Visual Basic program, you create event handlers to deal with events such as a form loading or a user clicking a button. These event handlers are simply additional subroutines in your forms, which means they can be called like other subroutines. Therefore, if you have code in a button's Click event, you can invoke that code from elsewhere in the form by calling Button1_Click, for instance.

However, I prefer to put this type of reused code in a subroutine within the form and call it from both places. This technique makes the code easier to explain to other programmers, and makes it a bit easier to move or change the code at a later time.

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

BUILDING SQL STATEMENTS

If you are building, within your Visual Basic code, SQL statements that have data that may contain single quote characters, be sure to replace every single quote within 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, "'", "''") & "'"

Both Access and SQL Server will properly handle the single quote as long as it is marked in this manner. Remember that single quotes have to surround the text literal you're using in your SQL statement, so be sure not to just replace all the single quotes in your SQL statement with two single quotes--you'll end up with a mess.

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

BUILDING SQL STATEMENTS

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.

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

BUILDING A TEXT EDITOR

I'm currently writing an article series at the Ask the VB Pro site. These articles show you how to build a text editor and add all the common features found in many other applications. As of this writing, the first two parts are available, and four more are under way. If you're looking for instruction on how to work with basic file operations, menus, and so on, take a look at these articles:

Building a Text Editor, Part I http://www.inquiry.com/techtips/thevbpro/10_minute_solutions/10min0400es.asp

Building a Text Editor, Part II http://www.inquiry.com/techtips/thevbpro/10_minute_solutions/10min0500es.asp

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

BUILDING A PERCENTAGE BAR

A user asked me recently if it was possible to build a bar graph, such as the ones used in most installation packages to show how quickly the procedure progresses. This is actually a control included in the Windows Common Controls component and is called the ProgressBar control. It's really easy to use: You tell it the beginning and end values, and then set that value each time you move through your process.

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

BUILDING A MOST RECENTLY USED FILE LIST

One common feature in applications like Word and Excel is the most recently used (MRU) list at the bottom of the File menu. This list shows the last four (typically) files that had been opened in the application. Fortunately, this list is easy to build using the standard Visual Basic menu editor and a bit of code.

In a typical File menu, there is a separator bar between the Exit choice at the bottom and those choices preceding it. If you have an MRU list, there is another separator before those items. In your File menu, add a choice called mnuFileMRU with an index of zero. The Caption for this menu item should be a single dash, which will create a separator bar. Since we don't want to show the separator if there are no files in the list, mark this choice as invisible at startup.

Once you've done that, you can add this code to your form:

Private Sub AddToMRUList(strFilename As String) Dim i As Integer

mnuFileMRU(0).Visible = True If m_intMRU < 4 Then Load mnuFileMRU(m_intMRU + 1) m_intMRU = m_intMRU + 1 End If For i = m_intMRU - 1 To 1 Step -1 mnuFileMRU(i + 1).Caption = "&" & (i + 1) & " " _ & Mid(mnuFileMRU(i).Caption, InStr(mnuFileMRU(i).Caption, " ") + 1) Next i With mnuFileMRU(1) .Caption = "&1 " & strFilename .Visible = True End With End Sub

You'll also need to add the following variable declaration to the declarations section of the form (the m prefix indicates a module-level variable):

Private m_intMRU As Integer

Since we know we will either be adding an item or changing an item in the MRU list, we show the separator bar, currently named mnuFileMRU(0). The code first determines how many items are in the MRU list. Since we can't use the UBound function on a control array, we keep a separate variable (m_intMRU) with the current number of files. If that number is less than four, we use the Load statement to create a new menu choice with a new index value, which is automatically added after the choice with index zero.

We then have to shuffle all the names down--that is, #1 becomes #2, #2 becomes #3, #3 becomes #4, and #4 is dropped if we already have four. Each menu item will look like this:

&1 Filenamegoeshere.txt

The ampersand causes the 1 to be underlined in the menu choice. When we shuffle the choices down, we have to remove the ampersand and number before putting on the new number. Once the old choices are shuffled down, we store the new one in spot #1.

If you want to add an extra feature, have the code check to see if the file you selected is already in the list. If so, move it to the top and shuffle the rest down to fill the empty spot.

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

AVOID THE ADO ADDNEW AND DELETE METHODS

If you're looking to coax some performance from your application, be sure that you're using stored procedures or SQL statements to handle all your record additions and deletions. While you can use the AddNew and Delete methods of the ADO Recordset, they're quite a bit slower than the alternate versions that just use plain SQL or stored procedures. Stored procedures that do INSERT statements get additional performance since they have to update indexes when the new records are created. Accordingly, the execution plan includes this as part of the required work.

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

ARRAYS START AT INDEX ZERO

In case you didn't know it, the default index for an array starts at zero. That is, if you create an array like Dim a_intValues(10)

you actually have index values from zero to ten, inclusive. You can verify this by looking at the LBound and UBound functions, which retrieve the lower and upper bounds of the array.

You can configure this using the Option Base statement, which lets you specify whether the first index should be zero or one. You can also provide your own indexes, such as this:

Dim a_intValues(5 To 10)

In this case, the indices will go from 5 to 10, inclusive.

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

ALWAYS INCLUDE A CASE ELSE

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

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

ADO RECORDSETS ARE FORWARD-ONLY BY DEFAULT

If you're using ADO recordsets in your applications, remember that using the Connection.Execute method creates a forward-only recordset. This type of recordset can be used only with the MoveNext statement. To move back and forth through the recordset, you'll need to create a recordset and use its Open method to retrieve the data. When you do this, be sure to specify a recordset type other than forward-only, or you'll end up with the same problem. Forward-only recordsets are the most efficient type for rapidly moving through data (for example, to load into another control), but if you need more ability to move around, choose another option.

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

ADO 2.5

ADO 2.5 is included with Windows 2000 and is also available as a separate download from the Microsoft Data Access site. If you go into your Program Files\Common Files\System\ADO directory, you'll find a bunch of files, some of which have version numbers in the filenames. However, there aren't any with the version number 2.5. In fact, ADO 2.5 is stored in MSADO15.DLL. If you bring up the properties for this library (or any other DLL file), you'll be able to see the version number stored in the library file itself.

Microsoft Universal Data Access http://www.microsoft.com/data/

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

ADDING CARRIAGE RETURNS

When writing code that exports to a plain text file, it's often necessary to embed special characters, such as carriage returns, line feeds, and tab characters. Visual Basic has special constants defined for many of these characters:

VbTab--Tab character VbLf--Line feed VbCr--Carriage Return VbCrLf--Carriage return/line feed combination

These constants are available in VB 6 and, I believe they are also available in VB 5. Prior to that, I'm not sure. However, you can easily replace the control code using the Chr (or Chr$) function:

vbTab = Chr(9) vbLf = Chr(10) vbCr = Chr(13) vbCrLf = Chr(13) & Chr(10)

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

ACCESSING OBJECT PROPERTIES

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.

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

USING THE END STATEMENT

One statement that you need to avoid using frequently is the End statement. The End statement stops your program immediately, which means that any memory associated with your application is not properly released. The best way to stop an application is to unload all your forms, and then in the Form_Unload event of the last form to be unloaded, use the End statement. That will prevent "phantom" programs that have no visible portions but are still running.

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

RESIZING FORMS AND REPOSITIONING CONTROLS

One of the most frequently asked questions about Visual Basic is how you 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 interesting approach is to do it yourself. Using a simple methodology, it's not hard to do on forms where this approach makes sense. (For example, 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-hand corner as the margin to use on the other side, too. 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 technique is pretty simple, it gets tricky if you have other controls, such as command buttons, on the form. For example, I like to keep my command buttons centered in the bottom part of the form, which 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 scenario:

txtNotes.Height = Me.ScaleHeight - (2 * txtNotes.Top) - cmdOK.Height - txtNotes.Top.

This code 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 need this capability, one of these options should take care of the problem.

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

FILE FORMAT WEB SITE

Ever wonder what the format of an MP3 file looks like? What about an AVI? At the MyFileFormats.com site, you can find this out, as well as explore hundreds of other file formats that you may have forgotten. I personally liked the old Commodore 64 file formats shown on this site... brings back some good memories.

My File Formats http://www.MyFileFormats.com/

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

EXPLICIT RETURNS

As a rule, you'll want to explicitly assign a value to be returned by your functions. If you don't, Visual Basic will return the default value for the function's type:

Variant functions return Empty. Integer functions return 0. Boolean functions return False. You can use this behavior to your advantage, but if you're unaware of what's going on, you might get unexpected results down the line.

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

BUILDING A DATE VARIABLE

One thing I do frequently is build up date values using string concatenation, like so:

Dim dteNew As Date

dteNew = CDate(Month(Now) & "/1/" & Year(Now))

This creates an entry for the first day of the month and converts it to a date. An easier way to do this uses the DateSerial function. Here's how it works:

dteNew = DateSerial(Year(Now), Month(Now), 1)

You give it a year, month, and day, and this function puts it all together into a date variable. It's a bit easier to read and prevents errors. A similar function exists for creating time values: TimeSerial. It takes hours, minutes, and seconds and creates a time value in Visual Basic's internal format.

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

WORKING WITH THE OBJECT BROWSER

By default, the Object Browser displays properties, methods, and events in alphabetical order. Sometimes, this isn't convenient--especially if you're looking for something by type rather than by name. Fortunately, you can group both the Class and the Members lists by type. Simply right-click the appropriate window and choose Group Members. To return the grouping option to alphabetical order, repeat this process and deselect Group Members.

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

SPECIFYING DEFAULTS WITH INPUTBOX

You've probably used the InputBox function to solicit users for data. However, did you realize that you can specify a default value? When possible, you'll want to do so, for several reasons:

You'll save your users a little data entry time. You'll cut down on data entry errors. You'll avoid run-time errors that might occur due to missing data. To specify a default value, just take advantage of the function's third argument--the Default argument--in the form

InputBox("prompt","title",default,xPos, yPos,HelpFile,Context As String)

For instance, the following statement would offer an input box with a default value of "I'll take the really big one":

InputBox("Please enter the jewel of your choice", "Gems To Go","I'll take the really big one")

If the default text is a value, omit the quotes.

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

SPEAKING THE ERROR CODE LANGUAGE

We all--well, nearly all--receive error messages when coding, but few of us have the entire list of error codes memorized. A quick way to learn more about an error is to run, in the window immediately after you encounter the error, this statement:

?Error(Err)

The Err object contains information that identifies the most recent error, and the Err statement will provide that information. However, you must use this simple technique right after the error occurs, because the next error will overwrite the existing information with the new error's information. If this happens and you know the prior error's code number, you can run this statement in the Immediate window:

?Error(codevalue)

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

CENTERING A FORM

To display a form in the center of your screen, add the following to your form's Load procedure:

Private Sub Form_Load() Left = (Screen.Width - Width) \ 2 Top = (Screen.Height - Height) \ 2 End Sub

This procedure simply subtracts the width and height of the form from the width and height of the screen, and then divides that result by two.

If you're using version 5.0 or 6.0, simply set the form's StartUpPosition property to CenterScreen or CenterOwner.

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

READ ONLY TEXT BOX CONTROL

You can quickly and easily make a text box control read only by setting its Lock property to True. If you should need an alternative to the property setting, you can enter the statement

KeyAscii = 0

as the control's KeyDown event.

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

QUICKLY LOOPING THROUGH A RECORDSET

A common database task is to loop through all the records in a recordset. Typical code for completing such a task might resemble the following:

Do While Not rst.EOF ...task rst.MoveNext Loop

However, this structure can be slow because the code checks for the end of the file at the beginning of each loop. You can speed things up by eliminating this check using the following setup:

rst.MoveLast iTotal = rst.RecordCount rst.MoveFirst

For iCounter = 1 To iTotal ...task rst.MoveNext Next iCounter

Instead of checking to see if you've reached the end of the file, the For loop simply ticks off the appropriate number of cycles. This structure will speed up your search a great deal--as much as 30 percent.

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

NESTING FUNCTIONS

Normally, you probably assign the results of a MsgBox function to a variable and then use that variable in a Select Case statement. However, you can display and process the results of a message box in one step instead of two by nesting a MsgBox function in a Select Case statement. For example, try the following form:

Private Sub Form_Click()

Select Case MsgBox("Please choose Yes, No, or Cancel to continue.", vbYesNoCancel) Case vbYes Print "Yes" Case vbNo Print "No" Case vbCancel Print "Cancel" End Select

End Sub

This nested arrangement omits the need for separate structures--you get the same result by nesting the functions. However, if you need to refer to the result of the MsgBox function later in your application, you can't. So, if that's the case, you'll need to stick to using two separate functions.

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

INFORMATION ABOUT ADO

If you're still waiting for the ADO (ActiveX Data Objects) train, you may find yourself left at the station! Office 2000 includes an enhanced version of ADO, and ADO is fast becoming the model of choice (among serious developers, it already is).

ADO was designed as a client-server system and should seriously increase usage of Microsoft products in Internet applications, because ADO can access many types of data--not just relationship database data. Now you can use ADO to access Web pages, spreadsheets, and documents. Eventually, ADO will replace DAO and RDO, which access only relational databases.

You can learn more about ADO and how it fits into your developing needs by visiting these Web sites:

Microsoft ActiveX Data Objects http://www.microsoft.com/data/ado/

OLE DB Technology http://www.oledb.com/ole-db/guide.html

Using ActiveX Data Objects (ADO) via Visual Basic http://support.microsoft.com/support/kb/articles/Q168/3/35.asp

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

DISABLE SYNTAX ERROR DIALOG BOX

When you're entering code, Visual Basic will warn you when the statement syntax is incorrect. By default, VB displays the syntax error dialog box and then displays the offending statement in red. If you find the dialog box annoying and unnecessary, you can turn it off. Select Tools, Options; click the Editor tab; and then deselect Auto Syntax Check under Coding Options. VB will still display the statement in red until you correct it, but you won't have to dismiss the syntax error dialog box first.

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

CDBL VERSUS VAL

The Val function converts digits stored as text to their numeric value. However, you need to be careful when using this function with formatted text (which contains decimal and thousand separators, for example). For instance, the function

Val("1234")

will return the value 1234. However, the function

Val("1,234")

will return the value 1.

The Val function truncates all the digits following the thousands separator (the comma character). That's because the function grabs only those values that fall to the left of the first text character in the string. The Val function interprets the comma as a text character--totally ignoring its formatting potential as a thousands separator.

If the text you need to convert may contain formatting, you should use the CDbl function instead of the Val function. CDbl recognizes the different separators and responds accordingly. Accordingly, both these functions

CDbl("1234)" CDbl("1,234")

return the value 1234.

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

WORKING WITH DEFAULTS IN A COMBO BOX

When you add items to a combo box, Visual Basic doesn't automatically display one of those items in the control's text box as the control's default. You can, however, specify a default by adding the statement

comboboxname.ListIndex = x

where x represents the list item you want displayed as the control's default. (Remember that the index values begin at 0, not 1.)

Using this method has one drawback--specifying the default in this manner will fire the control's Click event. So, if you want to use the control's Click event elsewhere, you might want to bypass this method.

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

SOLVE DLL REGISTRATION PROBLEMS

Normally, registering a DLL is automatic--Windows searches the registry database for the DLL's identification number; if it doesn't find the number, Windows executes the DLLSelfRegister function. If, however, the DLL has been registered incorrectly, Windows returns an error and doesn't load the DLL. At this point, you can try to register the DLL manually.

To register a DLL manually, you need regsvr32.exe, which is available on the MSDN Library disk that comes with Visual Basic 6.0. Once you have the program, choose Start, Run and type

path\regsvr32.exe nameofdll

If this doesn't work, you may need to remove the old registration details by typing the following (using Run):

path\regsvr32.exe /u nameofdll

Once you've unregistered the incorrectly registered DLL, you should be able to reregister it by repeating the instructions above. If all else fails, try locating a newer version of the DLL.

By the way, if you don't have regsvr32.exe, you can perform the tasks with a handful of similar utilities, such as DLL/OCX Register, available at your favorite shareware site.

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

HELPFUL ERROR MESSAGES

Almost every application passes information to the user--and we often use the MsgBox function to relay this information. Knowing how to pass the information is just part of the job. You should consider the message itself--especially when the information is the result of an error. In this case, you want to share the information without assigning blame. Here are a few guidelines you can adopt to make sure your messages are effective but not inflammatory:

Forget the geek words, such as invalid, aborted, failed, fatal, and illegal. These words aren't just overly technical--they're often negative. Phrases such as "doesn't work" and "isn't available" will get your point across without intimidating your user. Don't be overly dramatic and use exclamation points. They might cause unnecessary alarm. Use a normal tone of voice. Don't accuse the user of creating the error with messages such as "You have failed to" or "You tried to." Leave the user out of it. Instead, try phrases such as "The current operation" or "Please enter the data in the #### format." The last guideline is the most useful: Users don't need to know what they did wrong; they just need to know how to resolve the problem.

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

FORM ICONS

You can use an icon to represent a form when the form is minimized. To do so, simply set the form's Icon property to the icon file (ICO) of your choice. You can do the same with a project, and the specified icon will be displayed to represent the distributed EXE file.

If you find that the icon is small and looks bad, you're probably running in 256-color mode. If you must retain the 256 mode, then choose another icon--one that uses a 16 x 16 image.

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

RETURNING A COMPUTER'S NAME

You can return the name of the current system by calling the GetComputerName API function. This function requires two arguments: a buffer that holds the returned name and a value for the maximum size of the buffer. In addition, this function returns a Null-terminated string, so you must trim the extra characters from the name before you try to use it.

You can create an example by positioning a command button on a blank form and adding this code to the form's module:

Private Declare Function GetcomputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal _ lpBuffer As String, nSize As Long) As Long

Function GetComputer() As String Dim temp As Long Dim Name As String * 255 Name = Space(255) temp = GetcomputerName(Name, 255&) GetComputer = Left$(Name, InStr(Name, vbNullChar) - 1) End Function

Private Sub Command1_Click() Dim Name As String Name = GetComputer MsgBox "The computer name is " & Name End Sub

If you're new to APIs, the Declare statement goes in the form's General Declarations area. Once you've added the code, simply run the form and click the command button to return the computer's name.

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

OBJECT VS. COMPONENT

In a previous tip, we told you how to find the class name for an ActiveX control. We mentioned that ActiveX controls are COM components. You might think that the terms COM object and COM component are interchangeable. On the contrary: A component is typically an ActiveX control, a DLL, or an EXE. A COM object is an instance of a COM component. It's a nit-picky difference, but technically, they aren't the same.

In regards to the components: ActiveX controls expose themselves to numerous applications. DLLs are COM-enabled files that contain COM components. That leaves us with EXE files. They are similar to DLLs, but they don't run in the same memory space that calls them--as do DLLs.

By now, you're probably wondering why you should care about COM. If you want to succeed as a Visual Basic developer, you need to know what's going on beneath all that code, and COM is the key that will unlock that door for you. If you aren't proficient with COM technology, it's worth learning about. For a list of white papers on the subject, visit this site:

Microsoft COM White Papers http://www.microsoft.com/com/wpaper/default.asp

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

JUMPING TO FUNCTIONS

It's common to find function calls in your code. When Visual Basic encounters a function call, it routes the flow to that function and then returns to the calling function once the task is complete. If you want to view the called function quickly, simply right-click the function name in your code. Then, select Definition from the context menu. VB will give focus to the selected function--even if it's in a different module. You can also choose View, Definition or press Shift-F2. Pressing Ctrl-Shift-F2 will return you to the calling procedure.

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

FINDING REGISTERED ACTIVEX CONTROLS

ActiveX controls (OCX) are one type of COM component--one that has a user interface associated with it. (DLLs and EXEs are two others.) If you'd like to know which ActiveX controls are registered on your local system, choose Project, Component (or press Ctrl-T). Visual Basic will display the Components dialog box. Here, you can find and register any ActiveX control on your system.

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

A COMMON DATE MISTAKE

When working with date functions such as DateAdd, DateDiff, and DatePart, you need to pay close attention to the time arguments. Even though "h" and "s" represent hours and seconds, respectively, you can't use "m" to represent minutes. That's because Visual Basic assigns the "m" setting to months. When working with time, be sure to use the appropriate minute setting, which is "n." This is an easy mistake to make and a hard one to find, because the "m" seems like such a natural and correct setting for the minute component.

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

VARIANT $TRINGS

If you've ever wondered what the $ sign means in string functions such as Trim$, Str$, and InStr$, it probably doesn't help much to know that these have seemingly identical functions--Trim, Str, and InStr. How do you know which to use? The difference is the data type of the returned value. The plain versions (the functions without the $) return a variant data type. If you've declared the string, then Visual Basic must convert the returned variant to a string data type. However, the $ version will return a string without first having to convert from the variant. The time you save is miniscule, but if you're dealing with lots of strings, that time can add up. In addition, the variant data type requires more memory than does the string data type.

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

SELECT TEXT IN A TEXTBOX

You may want a control to select the contents of a TextBox control automatically once Visual Basic gives that control focus. To do so, attach the following procedure to the control's Got Focus event:

Private Sub Text1_GotFocus() SendKeys "{Home}+{End}" End Sub

When VB selects the control, the GotFocus event will select the control's text value.

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

SAVING TIME WITH AUTOREDRAW

Visual Basic handles graphics in two ways: permanent and temporary. For the most part, all controls and pictures are considered permanent. Lines and circles are temporary, which means VB redraws them as necessary.

The AutoDraw property is False by default. If you set it to True, everything becomes permanent, which means that VB won't redraw anything on the fly. Instead, VB will keep a copy of the entire object (form) in memory.

When AutoDraw is False, a form will redraw twice as fast as a form with an AutoDraw property set to True. However, you'll need to add code that redraws the erased components in the form's Paint event.

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

REFERENCING FIELDS IN RECORDSETS

When working with recordsets, you can access your data in several ways. First, you can use the recordset object's Field property using the form

Field = DataControl.Recordset.Fields("fieldname")

where Field represents your field variable, DataControl is the name of your Data Control control, and fieldname is the name of the underlying field you're accessing.

Fortunately, you don't have to know the names of the fields because you can access them by order, using the form

Field = DataControl.Recordset.Fields(0)

to access the first field in the table. If you want to access the second field, specify the value 1; to access the third table, use the value 2, and so on.

You can also take advantage of a default shortcut: Since the Fields property is the recordset object's default, you can omit the Fields component using the form

Field = DataControl.Recordset("fieldname")

Field = DataControl.Recordset(0)

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

QUICKLY ROUNDING DECIMAL VALUES

There are many approaches to rounding. Perhaps the easiest way to round decimal values is to assign them to an Integer value:

iOne As Integer, iTwo As Integer

iOne = 1.6 iTwo = 7.3

Print iOne, iTwo

The results are 2 and 7. Of course, if you need to retain rounded decimal values, this quick solution won't work.

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

KEEP IT SIMPLE

When designing applications, you may be tempted to add lots of bells and whistles--but you're well advised to carefully consider each option. For instance, if you create a data entry form, you'll most likely insert Add, Edit, Delete, and Exit command buttons. You might consider adding the usual OK and Cancel buttons. You might even want to add a button to confirm the other action tasks--adding, deleting, and so on--but are these extra options necessary?

First, let's consider the OK and Cancel buttons: Just when will the user use them? Probably never, so why include them? In addition, a Confirm button may prove more of an annoyance to your user than a help. It presents an extra click--or interruption--each time your user tries to do something. Besides, if you make the Add button the default, the user can avoid stopping for a click until he or she needs to edit or delete a record. This setup is more efficient since it doesn't call for two clicks for every record.

Your best bet is to include only the functionality you really need.

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

JUSTIFYING TEXT USING FORMAT()

Did you know you can use the Format() function to justify text? You can, by specifying the number of characters, the underlying field, and the ! character. For instance, if the field has a width of seven characters, the function

Format$("abc", "@@@@@@@")

would right-justify the string abc. If you want to left-justify a string, simply add the ! character to your format code in the form

Format$("abc", "!@@@@@@@")

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

DISPLAY A PICTURE ON THE STATUS BAR

Did you know that you can display a picture in the status bar? Simply right-click the status bar and select the Properties command. Then, click the Panels tab and add a new panel. Next, click the panel's Picture area, choose Browse, and locate the picture you want to display. It couldn't be simpler!

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

DATA INTEGRITY VERSUS RESOURCES

Visual Basic offers two ways to pass data between routines. You can pass the actual data, or you can pass a pointer to the data. The first method--ByVal--passes a parameter by value. The second--ByRef--passes a parameter by reference. Each has its advantages and disadvantages.

ByRef saves on resources, but you'll find that some developers consider the use of ByRef bad programming because ByRef passes only a pointer to the variable. While this option uses less memory, it doesn't protect the original variable, which can be altered. If you want the option to change the original value, then ByRef is an adequate choice, especially since it saves on resources.

On the other hand, ByVal passes the actual value. VB makes a copy of the variable for temporary use, and maintains and uses this copy as long as the routine is running. So ByVal, while requiring more memory, protects the integrity of your data.

You must also conform to what the API function expects--so if you're working with API functions, stick with the declared reference.

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

ADD INFORMATION WITH COLOR

Typically, the more information you can give your users, the better. One easy way to quickly convey information is with color. For instance, if you want users to know a particular field is required--meaning they must enter data in that field before they can go to the next record--you can create a label control and enter the message

This is a required field, you must enter data.

But that solution requires that your user stop to read the message, which can waste time and be a nuisance.

Instead, use the same color consistently throughout your application to send a signal to your users that they must enter data in that field to continue. (Red works great.) Your users will learn and adjust quickly, and at a glance they will know that a field requires an entry. On the other hand, if most of your fields are required, it might be just as effective to apply special coloring to the fields that aren't required.

You can use color to denote all kinds of information. Just be sure to use it consistently and sparingly.

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

WORKING WITH TRUE-FALSE DATA

If you're new to Visual Basic, you might not realize that you can assign a number, or even a string, to a Boolean variable. Let's take a look at a quick and easy example:

Private Sub Form_Click() Dim bytOne As Byte Dim intTwo As Integer

Print bytOne = True Print intTwo = True Print bytOne = intTwo

End Sub

In this case, the True Boolean value is assigned to both a Byte and Integer variable, which is quite legal.

Furthermore, you might expect this short exercise to print True, True, and True, since if bytOne equals True and intTwo equals True, then bytOne must equal intTwo, right? Well, they don't. That's because bytOne and intTwo are different data types, so they aren't equal in the eyes of VB, even if you assign the same value to both.

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

WORKING AROUND VERSION NAME CONFLICTS

You can't use restricted keywords as the name of a form or control. Well, you can, but it isn't advisable. For those rare occasions when you do, you must first enclose the name in a set of square brackets in order for Visual Basic to accept that name.

Although we don't recommend you use this workaround in your normal coding, this tip does have its place. Sometimes you'll find that an accepted name in an earlier version is in conflict with a newer version because that name is now a reserved word. When this happens, simply enclose the name in square brackets, and the new version will accept and use the name.

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

WHY A VARIABLE'S DATA TYPE MATTERS

If you don't declare a variable's data type, Visual Basic will do so for you and assign the Variant data type to your variable. Now, you may not care, or you may not think it matters--and you'd be right, to a degree. However, assigning the most appropriate data type to your variable gives you control over that variable. What do we mean by control?

First, a data type restricts a variable to certain types of data. If you're storing values to use in a later calculation, you certainly wouldn't want VB to accept a string value, for example. In this case, you'd declare your variable as one of the many value data types, such as Byte, Integer, Long Integer, Single, Double, and so on. When you do so, VB will return an error if you attempt to enter a string value instead of an appropriate number value. This quality extends to dates as well--if you declare a variable as a date, it won't accept anything other than a date. So, declaring the appropriate data type is one (very easy) way to validate data.

Second, try to assign the data type that meets the needs of your variable but uses the least amount of resources, because each data type requires a certain number of bytes--and of course, the fewer you expend, the more efficient your application. A Variant data type requires 16 bytes while an Integer data type requires only 2. If the Integer data type is adequate but you use the Variant data type, you've wasted 14 bytes. This may not seem like a big deal, but if your application is large and your resources are limited, these unnecessary expenditures can add up quickly and affect the performance of your application.

Don't go overboard and be too stingy about your declarations. After all, sometimes more is better. For instance, if you're using a function to store a value between 0 and 255, you might want to use the Byte data type--but only if you are first sure you won't ever need to store a negative value. Also, consider the way your function will use these Byte values. If the result of calculating a Byte value might return a value other than one that falls between 0 and 255, you can't use the Byte data type to store the results just because the Byte data type was appropriate for the numeric elements you used in the calculation.

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

THE FOR...LOOP VARIABLE

The For Each...Next loop provides a convenient method for looping through each member of a collection or a normal array. Before the For Each...Next loop, the process was a bit more complex. Just remember this one detail--you must reference each element as an Object or Variant variable when working with collections. If you're looping through an array, however, you must use a Variant.

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

SHORTCUT KEYS FOR THE IMMEDIATE WINDOW

There are several shortcut keys you can use in the Immediate window. Some, such as pressing the Enter key, you already know. (Pressing Enter runs a line of code.) In addition, Ctrl-C copies selected text to the Clipboard, and Ctrl-V pastes the contents of the Clipboard into the Immediate window. You can use Ctrl-X to cut the selected text (to the Clipboard). More useful, but less obvious, shortcut keys include the following:

Ctrl-L displays the Call Stack dialog box. Ctrl-Enter inserts a carriage return. Ctrl-Home moves the cursor to the top of the window. F2 displays the Object Browser. F5 continues to run an application. Shift-F5 restarts an application. Alt-F5 runs the error handler code or returns the error. F6 switches between the Immediate and Watch windows. F8 initiates Single Step mode (executes code one line at a time). Shift-F10 displays the shortcut menu.

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

REPEAT i IN NEXT LOOP

When using the For statement, you can omit specifying the loop's variable name in the Next statement. For instance,

For i = 1 To 10 ... Next

is a legitimate loop. However, the code

For I = 1 To 10 ... Next i

is more obvious--it is easy to see where the For loop ends. This may not seem important in such a simple example, but in a complex loop with many lines of code, it can make spotting the end much easier.

If you want the loop to be even more meaningful, use a descriptive name--instead of I--for the looping variable. Simply name the variable as you would any other--by describing its purpose or origin.

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

IF...THEN VERSUS IIF()

If you routinely assign the result of an Iif() statement to a variable, you should consider replacing those statements with an If...Then construct. Why? First, the Iif() statement is a little harder to decipher because of its one line structure. Second, the If...Then construct is faster than the Iif() statement. That's right--an If...Then is more than twice as fast as an Iif() statement that performs the same task.

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

FIXED-LENGTH VERSUS VARIABLE-LENGTH

We're often asked what the difference is between a fixed-length string and a variable-length string. The difference is just as the name suggests. A fixed-length string is fixed in size--which means it can handle only so many characters and no more. For instance, the statement

Dim strFixed As String * 10

limits strFixed to ten characters or fewer. On the other hand, the statement

Dim strVariable As String

allows strVariable to grow (or shrink) as necessary.

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

DECLARING DLL FUNCTIONS

If you're new to Visual Basic, you may also be new to working with DLLs. There are three main types of API libraries: GDI, User, and Kernel. GDI functions are related to line, text, and bitmap output. User functions cover messaging, dialog boxes, and controls. The last library, Kernel, applies to system-related information, such as memory allocation. Each library contains many functions.

Once you've located the appropriate function in one of the libraries, you must declare that function. In doing so, you'll need to know the name of the function, the number of arguments the function expects, their data types, how the function will return the result, and the result's data type. The general syntax for declaring an API function is

Declare Function functionname Lib "libraryname" [Alias aliasname] [(argument list)] As resultdatatype

It's also important to note that 32-bit calls are case-sensitive.

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

AVOIDING GOTO

Visual Basic's GoTo statement allows you to control the flow of your code. However, you'll want to use this statement as little as possible. In fact, GoTo is easily the black sheep of VB statements, since some developers refuse to use the statement except when referring to an error handler.

Now, we're not trying to pass judgment on those who use the GoTo statement. If you use it with no problems, then continue with what works. However, you need to be aware that this statement can cause problems. First, it can be difficult to debug a module that jumps around a lot. Second, the statement requires no conditions; it simply jumps without looking first, so there's no control.

If you're using GoTo a lot, you might want to review some of the conditional structures such as If...Then, Select Case, Do...Loop, and so on. Then, try to replace some of your GoTo statements with one of these more stable statements.

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

USING ROUND()

The Round() function was introduced in version 6.0 and, as you might expect, this function returns a numeric expression rounded to a specified number of decimal places. You use the function in the form

Round(nExpression, nDecimalPlaces)

where nExpression is the value (or result of an expression) that you want to round and nDecimalPlaces specifies the number of decimal places to which nExpression is rounded.

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

ELIMINATING A LITTLE CODE

There are several ways you can optimize code by eliminating unnecessary statements. My favorite is to delete declaration statements for unnecessary integers. For instance, the following procedure is fairly common--it declares a variable, assigns a value to it, and then returns it:

Function NumberTest(number As String) As Boolean Dim boo As Boolean boo = IsNumeric(number) NumberTest = boo End Function

This procedure declares the variable boo as a Boolean data type, assigns the result of a function to boo, and then returns the value of boo as the function results. There's nothing wrong with this code--it works fine. But we can eliminate two lines. The next procedure accomplishes the same end:

Function NumberTest(number As String) As Boolean NumberTest = IsNumeric(number) End Function

The difference here is that we've cut out an unnecessary variable. The function is already declared as a Boolean data type, so we really don't need boo at all.

You'll find many developers who insist that such shortcuts are "bad" programming... and you might not want to eliminate boo from your procedure for several reasons. First, if you need to refer to boo more than once, you should retain the variable. Second, the variables make the procedure more readable. However, if your procedure is short and sweet, eliminating these few extra lines shouldn't be a problem.

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

DECLARATION SHORTCUT

Most of the time, we declare variables explicitly by identifying the data type. For instance, to declare a variable as an Integer, you'd use the statement

Dim i As Integer

However, there is a shortcut method when declaring a String, Integer, Long, Single, Double, or Currency data type: You can use the data type's character. The Integer data type's character is %. That means the statement

Dim I%

accomplishes the same task as our previous statement. Other data type characters are

String--$ Integer--% Long--& Single--! Double--# Currency--@

For the most part, we don't recommend you use these characters. We just want you to be aware of them in case you run into them.

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

WHERE TO USE NOTHING

You often see code that sets all the object and DAO (or ADO) variables to Nothing at the end of a procedure, using the syntax

objVariable = Nothing

However, perhaps the best place to reset a variable to Nothing is when the function is done with the variable--not at the end of the procedure. There's really no reason to wait until the end of the procedure to free up those resources. Instead, free them as soon as the variables are no longer needed.

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

USING THE GLOSSARY

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

glossary

You can then select the glossary at large, or you can go to a specific section of the glossary. Regardless of how you open the glossary, Help displays a list of keywords in alphabetical order. Simply locate the most appropriate word for the subject you're searching and click it. You might have to click through a few words to find the appropriate information, but you're bound to find it sooner or later.

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

STICKING WITH NATIVE CONTROLS

There are a ton of third-party controls on the market, and most are dependable and make short work of complicated tasks. However, any time you include a third-party control in an application, you take a risk if you plan to upgrade. That's because there's no way to guarantee that the control will work in the next version--and chances are it will not. If the company that wrote your control doesn't upgrade it, you're stuck between a rock and a hard place. Either you don't upgrade or you upgrade and revamp the application. Neither choice paints a pretty picture.

Now, we can hear third-party representatives growling, and we don't mean to imply that you should never use a third-party control. We simply recommend that you consider all the possibilities and weigh your options before going that route. Here are a few questions to ask yourself:

How much time will the control save me? How long has the third-party company been around? Does that company have a consistent history of upgrading controls? If you stand to save a lot of time, and if the company is well established and has been consistently reliable in the customer and product support arena, then your risk is low. If the application is a single-user application and you doubt the application will ever be upgraded, by all means save yourself as much time as possible.

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

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. Before you try to reinvent the wheel, do a quick search in Visual Basic'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, 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. You'll probably have to revamp the code a bit, but that's easier than starting from scratch.

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

SPLITTING YOUR SCREEN

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

SORTING ITEMS IN A LIST BOX

Did you know you can sort the items in a list box by simply changing a control property? The list box control's Sorted property is set to False by default. That means the control will display items in the order they are added by code. If you set the property to True, the control will display the items in sorted order.

For the most part, this means that you can enter items in any order and still display them in sorted order in the control. The following procedure shows a simple example:

Private Sub Form_Load() lstSort.AddItem "dog" lstSort.AddItem "cat" lstSort.AddItem "zebra" lstSort.AddItem "ant" End Sub

This procedure will display the four items (dog, cat, zebra, and ant) in that order--that is, unless you set the control's Sorted property to True. Then, the list box will display the same items in alphabetical order--ant, cat, dog, and zebra.

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

REPEAT i IN NEXT LOOP

When using the For...Next statement, you can omit specifying the loop's variable in the Next statement. For instance, the code

For i = 1 To 10 ... Next

is a legitimate use of the Next statement. However, you will probably find the code

For i = 1 To 10 ... Next i

more readable: It's easy to see where that particular For loop ends. I know this doesn't seem too obvious in such a simple example. But if your code includes a complex loop with many lines--especially if the code contains more than one For statement--you'll find it easier to locate the end of your loop if you include the variable in the Next statement.

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

PASSING OPTIONAL ARGUMENTS

Passing arguments from one procedure to another is how functions talk to one another. Passing an argument is simple--you specify the argument(s) in the main function using the syntax

Function DoMyWork(argument As datatype)

Then, you pass the necessary data (via the argument variable) to the function using the syntax

=DoMyWork(data)

Once you've declared a function's argument, you must pass a value or your procedure will return an error. In other words, the call

=DoMyWork()

won't work because Visual Basic expects an argument.

It stands to reason that we don't always know if an argument's going to exist--no data may be just as important to the procedure as a passed argument. When this is the case, you can declare an optional argument using the syntax

Function DoMyWork(Optional argument As datatype)

Visual Basic will execute this function with or without the passed argument. This means that either of the following calls will work:

=DoMyWork(data) =DoMyWork()

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

NAME THAT LOOP

In our previous tip, we told you that it's okay to omit the For loop's variable in the Next statement. However, including the variable makes the loop a little easier to read and certainly makes it easier to find the end of that particular loop.

If you'd like to make your code even more readable, give your loop variable a descriptive name instead of using the old i variable. The name should reveal the loop's purpose or origin. For instance, the variable iForCounterAdd indicates that the loop is adding values.

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

MORE ON CALCULATIONS

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.

LEARN THE VALUE OF A VARIABLE--FAST

When you run code for debugging purposes, you can quickly learn the value of a variable in the Debug or Immediate window--or you can position the cursor over any variable (that's been evaluated) in the actual code, and Visual Basic will display that variable's value in a Tip control. This feature can be extremely helpful when a variable changes its value or if a procedure is returning erroneous data. You can set a breakpoint right after the statement that contains the variable so you can check the variable's value.

HIGH-PERFORMANCE DATABASES

No matter what the application's task, performance is always an issue. If you're working with databases, there are a few things you can do to speed things up a bit:

Avoid opening recordsets as a table type--there are other options, such as snapshot. If you're searching in SQL tables, use indexes. Avoid including functions in SQL statements. Rely on stored procedures as often as possible. Adding these guidelines to your design should help improve performance in any database application.

FINDING AN OBJECT'S DEFAULT PROPERTY

You probably know that you can omit an object's default reference. In fact, we've discussed this referencing shortcut in previous tips. But you may be wondering how you learn an object's default property. There's an easy way to find out: Just open the Object Browser and select the object in the Classes list. The Members list will update accordingly, and the default property will display a small blue circle right above it.

DISPLAYING THE HOURGLASS MOUSE POINTER

As you know, processing can tie up any application for a while. When this happens, you can alert your users by displaying the hourglass mouse pointer. To do so, simply include the statement

Screen.Mousepointer = vbHourglass

in your code--right before the lengthy task begins. Your users are probably familiar with the hourglass icon and will know that they must stop trying to enter data or otherwise interact with the application. Of course, you'll want to let your users know when the task is complete so they can return to work. To do so, include a second statement:

Screen.Mousepointer = vbDefault

This returns the mouse pointer to the default after completion of the code that performs the time-consuming task.

Several mouse pointer constants are available. For more information, search Help for Mouse Pointer Constants.

DIFFERENT MOUSE POINTERS

In past tips, we showed you how to display the hourglass mouse pointer by using the statement

Screen.Mousepointer = vbHourglass

The Screen object isn't limited to the hourglass mouse pointer. Here, then, is a list of all the different mouse pointers you can display using the Screen object's Mousepointer property:

*Pointer*--*Constant* Arrow--vbArrow Cross--vbCrosshair I Beam--vbIbeam Icon--vbIconPointer Size--vbSizePointer Size NE, SW--vbSizeNESW Size N, S--vbSizeNS Size NW, SE--vbSizeNWSE Size W, E--vbSizeWE Up arrow--vbUpArrow Hourglass--vbHourglass No drop--vbNoDrop Arrow and Hourglass--vbArrowHourglass Arrow and Question mark--vbArrowQuestion Size all--vbSizeAll

DELETING ITEMS FROM A LIST BOX

After selecting items in a list box, you can delete those items with one short procedure, which relies on the control's Select property:

Private Sub cmdDelete_Click() Dim i As Integer For i = lstDelete.ListCount - 1 To 0 Step -1 If lstDelete.Selected(i) Then lstDelete.RemoveItem i Next i End Sub

When an item has been selected, the lstDelete.Selected(i) property returns True. When this property is True, the If statement deletes that item using the statement:

lstDelete.RemoveItem i

The For loop does its trick by looping backward through the selected items. You can't delete the items in numeric order, because doing so constantly resets the index values.

Keep in mind that this procedure doesn't delete the items permanently. It only deletes the items until you reload the form. In addition, this procedure will work with a multiselect list box, but you'll want to use a different event to trigger it as the Click event will react as soon as you click the first item.

DELETING A SPLIT SCREEN

In our previous 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 this the hard way, drag the split bar back to its originating split box. The easier way to eliminate a split module is to simply double-click the split bar.

CONTROL CONFUSION

Ever have trouble deciding whether you need checkboxes or option buttons? They may appear similar, but their behavior is very different and they aren't interchangeable. The two sets of controls act the same in that they offer a number of items, but they differ in the way they process your choice. Once you understand their purpose, you should have no trouble deciding between the two.

Checkboxes allow you to flag an option as True or False, Yes or No, and so on. You can select more than one checkbox. On the other hand, option buttons offer choices, and you can click only one. Your choice will determine the value of the option group (group of buttons).

If you need to offer several options and the user can choose any number of them, use checkboxes. If you need to limit the choice to just one item, use an option group.

COMPARING DATES

Ever need to know if two dates fall within the same month? Well, there's an easy way to compare two dates for this purpose. This simple date task can be accomplished with the following procedure:

Function MonthCheck(date1 As Date, date2 As Date) As Integer MonthCheck = DateDiff("m", date1, date2) End Function

If MonthCheck returns 0, the two dates are in the same month; any result other than 0 means the dates are not in the same month. At first glance, this function appears to return a Boolean result, but that's not the case. The result equals the number of months between the two dates.

CHANGING DATA TYPES

Previously, we reminded you that any time you might need to store decimal values, you must remember to declare the field or variable's data type as Single or Double. If you forget, you can always change the data type later. However, we would like to offer one word of caution about doing so: If you change a data type after you've entered data, you risk losing that data, and you can't undo the damage.

Here's what happens: If an existing value is too large for the new data type, Access may truncate, round, or even delete that value. This caution should be applied any time you change a field property when data already exists.

CALCULATORS ONLINE

Normally, we like to share expressions and formulas that you can use in Visual Basic. However, we've found a Web site that may make some of your work unnecessary. The Calculators On-Line Center 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.

Calculators On-Line Center

http://www.martindalecenter.com/

AUTOMATION

All applications aren't equal in the eyes of Automation. There are subtle differences between the ways applications respond when called. Today, we'll discuss a few of those differences.

Excel will launch as a hidden window; you must set the visible property to True to unhide that window. You must use Quit to terminate Excel; setting the object variable to Nothing won't do the trick.

Like Excel, PowerPoint launches as a hidden window, and setting the Visible property to True will unhide that window. In addition, you must use Quit to terminate the application.

Word also launches as a hidden window with a Visible property of False, and you must use Quit to terminate the application.

Outlook launches as a hidden window, but you must use the Windows API to unhide the window. You must use the Quit method to terminate the application.

ACCOMMODATING DECIMAL VALUES

When a value doesn't display or consider a decimal component in a calculation--and you know it should--check that value's data type. The value must be a Single or Double data type to store a decimal component. This is a common mistake and it's easy to make. While it's obvious that the Integer and Long Integer data types both store only integers, we use them so often that it's easy to enter Integer or Long when the variable really should be a Single or Double. Another problem is that we don't always know during the design stage that a variable might need to accommodate a decimal value.

In either case, simply changing the data type should solve the problem. However, we suggest you make sure that the data type is the actual problem before doing so--changing a data type can have far-reaching consequences.

Questions?

Just Check out some of our sponsors

Shop at BestPrices.Com!

[ Home ] [ Up ]

Visual Basic

QUITTING AN APPLICATION

MORE ON AUTOMATION

Send mail to CompanyWebmaster with questions or comments about this web site. Copyright © 2009 HALO Computer Technology Last modified: 03/11/09

Send mail to CompanyWebmaster with questions or comments about this web site.
Copyright © 2009 HALO Computer Technology
Last modified: 03/11/09