×

AutoCAD

Protect Yourself from Joe Unknown

14 Oct, 2004 By: Mike Tuersley Cadalyst

---

Data validation is one way to ensure users won’t break your application

The hardest part of designing and implementing a custom solution is not designing the architecture, or solving the problem, or the arduous task of assembling and typing all the code. The hardest part is planning for Joe Unknown -- that user who will break your program in record time doing something you never in your wildest dreams could anticipate.

My personal favorite is a project I did long before the advent of the remote desktop. I never saw the client -- everything was done via conference call, e-mail, and FedEx. I wrote an elaborate BOM (bill of materials) program in which I had accounted for every possible scenario that Joe Unknown could bring to the table. He generated a fatal error within an hour of installation that crashed AutoCAD. After a day and a half of testing and sifting through misinformation, Joe finally admitted he had tried to insert a BOM with no information, a completely blank form. Had he 'fessed up early on ... nope, I won't even go there.

What To Do About Joe?
How can you plan for Joe? For starters, remember that Joe will always be Joe, and there's no way to plan for him completely. Remember that and you can sleep at night. However, you can minimize Joe's effect on your application. The primary way, in my case, should have been to ensure all the required information was filled in before the program executed the next phase. The next, and sometimes the hardest, part is validating the data that Joe enters. This month, we'll look at three techniques to accomplish this task.

Assume that I have a form with a textbox and I want Joe to type in a number. I can use any of three approaches:

• IsNumeric function
• key checking
• regular expressions

Isnumeric Function

Figure 1. IsNumeric example.

This, the simplest approach, uses the Visual Basic IsNumeric function. Simply test the value of the textbox to see if it's numerical based on the IsNumeric Boolean return value: TRUE means it's numerical; FALSE, it's not.

Create a new project and add a Userform with a textbox, a label, and a CommandButton (figure 1), keeping the default names for the controls.

To test the value of Textbox1.TEXT, I need to add code to one of its events. The three best events are AfterUpdate, BeforeUpdate, and Exit (unless you're using full Visual Basic 6, in which case I recommend Validate). Don't use Change, because it fires every time you add or remove a character from the textbox. My personal preference is the Exit event. So, using Textbox1_Exit, here's what the code should look like:

Option Explicit


Private Const DefaultNumber = "0.00"


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  If Not IsNumeric(TextBox1) Then TextBox1.Text = DefaultNumber

End Sub

Here I'm passing the textbox to IsNumeric and testing for a FALSE return value, in which case I push a default value into the control. I could just as easily pop in a message box to inform Joe that he needs to enter a number.

To run this, enter a value in the textbox and tab to the CommandButton. The tab causes the textbox's Exit event to fire and the code to run so Joe doesn't need to click the CommandButton.

This accomplishes my task. Joe has to enter a number. If he chooses not to, the program fills in a default number, which he can go back and change if he wants.

Key Checking

Figure 2. Key checking example.

The next method is to limit the keys Joe can use in the textbox. To demonstrate this approach, let's add another label and textbox to the Userform (figure 2). Instead of checking for a numerical value when Joe leaves the control, the program checks as he's typing to make sure he types only the characters allowed as dictated in my constant, Numbers. It's important to remember that Joe may decide not to type anything at all, so you need to establish a default value in the textbox at startup or revise the Exit event code to check for a null value. Here's the code I'm going to use this time:

Private Const Numbers = "0123456789."


Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  If IsNull(TextBox2) Then TextBox2.Text = DefaultNumber

End Sub


Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

  If InStr(Numbers, Chr(KeyAscii)) = 0 Then KeyAscii = 0

End Sub

Added is a constant, Numbers, that represents all the valid keys Joe can use. In the KeyPress event, I check to make sure Joe pressed one of the keys allowed. If not, the program ignores the key Joe pressed. Lastly, I added code to the textbox2's Exit event to ensure that a value is present and, if not, that it's populated with a default value.

Regular Expressions
These first two methods fulfill the task at hand, but suppose I add to the task. What if I wanted Joe to enter a number to the nearest hundredth, even if it's a whole number? Neither method accommodates this, so I'd need to implement some sort of string

Figure 3. An example of a Regular Expression.

searching/comparing method to see if he had entered the decimal place, and then check for two following characters. Or I could use a Regular Expression.

A Regular Expression is a pattern-matching language that functions like a wildcard (such as * or ?). The Regular Expression language, though, is many times more complex and powerful. In fact, these expressions are behind most search engines.

To access the Regular Expression language, you need to add to the project a reference to Microsoft VBScript Regular Expressions 5.5. To demonstrate, you'll also need to add another label and textbox to the Userform (figure 3).

Private oReg As RegExp


Private Sub UserForm_Initialize()

  Set oReg = New RegExp

  With oReg

    .IgnoreCase = True

    .Pattern = "^\d*.\d{2}$"

  End With

End Sub


Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  If Not oReg.Test(TextBox3) Then TextBox3 = DefaultNumber

End Sub

In this example, I've added:

• a global RegExp object;
• code to the Userform's Initialize event, in which I set my Regular Expression object, oRegExp, to an instance of RegExp; and
• code to the textbox3's Exit event, where the value of the textbox is validated by the Regular Expression object.

The pattern I'm using is ^\d*.\d{2}$, where:

• ^ marks the beginning of the pattern
• \d* matches any number of digits (0-9)
• . matches the literal decimal point
• \d{2} matches 2 digits (0-9 and 0-9) after the literal decimal point
• $ marks the end of the pattern

Finally, the task is complete. Now I know for sure that Joe has to enter the numbers exactly as I want them, or else the program will place values for him. Now you, too, can tame your Joe Unknowns and sleep at night knowing that the information you expect will actually be there when Joe clicks the Execute button.

Granted, I've only skimmed the surface of what you can do using Regular Expressions. To gain more insight, see Microsoft's "How To Use Regular Expressions in Microsoft Visual Basic 6.0."

On Microsoft's Web site, you can also find articles on how to use Regular Expressions in VB.NET and C#.NET.

Kick It Up a Notch
Suppose the textbox contains a date. Dates can be one of the most frustrating pieces of information to request, especially in VBA. Unlike full VB, VBA has no calendar or date control. How many different ways can a user enter a valid date? Some of the most common are: 8/28/04, 8-28-04, 8.28.04, 28/08/04, and 28-08-04 -- the list goes on. Try using Regular Expressions to limit Joe to entering dates in MM/DD/YY format, and then check out this month's sample code to see how I did it.