|
|
Checking for Valid Choices
The advantages common to all the above methods is that the user does not need to type, which is faster and means no typos.
When to Apply Validation
A decision for the developer is whether to check a single value when exiting a control, or to check all the values when the user clicks a button to single completion. A hybrid method often works well, checking individual controls as they are completed, and then checking when the UserForm is complete that all mandatory controls are taken care of.
Sources of Invalid Data
Invalid data is encountered one of two ways. It can be provided by the workbook or by the user. If it is provided by the workbook, then it can be static (available before UserForm is started) or dynamic (available after UserForm is started).
Bad static data, usually in the form of a bad entry in a list, is the responsibility of the person maintaining the workbook. Bad static data could also result from a SpinButton with the wrong limits set.
Bad dynamic data, also usually in the form of a bad entry in a list, is the responsibility of the programmer. It often results from data validation code failing to detect invalid data provided by the user.
When a user’s input is checked and added to a list, it is still possible that it is not valid for the list. This is now a user problem. If additional checks can be identified by the user, then the developer can enhance the validation accordingly. Sometimes allowable data is so variable that it is virtually impossible to account for all possible invalid combinations.
Validating data requires an understanding of how it is to be used. If the data is to be a date or a number, VBA provides the IsDate and IsNumeric functions. Even though a number is needed, it is not always valid. For example, zip codes are a special case, best treated as text because zip codes can have leading zeroes, and numbers typically don’t. Zip codes are known length, makes sense to check when length is 5 and again when length is 9. not all zip codes exist, and it’s best to check against
If Not IsDate(var) Then
MsgBox "Your entry must be part of the list", vbCritical
Exit Sub
End If
If Not IsDate(var) Then
MsgBox "Your entry must be part of the list", vbCritical
Exit Sub
End If
Where free-form entry is necessary, the developer must decide whether to validate after every keystroke or only when the entry is complete.
Code Cautions for Testing Data
Checking data is not necessarily as easy as it seems. The method of checking itself may encounter an error, and these errors must be correctly by the developer so that the only errors that occur are the detecting of invalid data. For example, in UserForm_Lesson03a, the OptionButtonSeeW is a TripleState control and is initialized to Null. The following statement will fail even though the value is not true.
If OptionButtonSeeW.value = False then
In such a case the value must be specifically checked for Null as well as False and True
Const var As Integer = 1
If var = "abc" Then MsgBox "xx"
The above code will cause a Type Mismatch error because var is not a string. Likewise, the following code will cause a Type Mismatch error because var is not an integer.
Const var As String = "abc"
If var = 1 Then MsgBox "xx"
For this reason, variables to receive TextBox.Value and ComboBox.Value must be of type Variant, which can take any type of data. In the following code, the test of var will evaluate to False, but the code itself will not fail.
Dim var As Variant
var = 1
If var = "abc" Then MsgBox "xx"
var = "abc"
If var = 1 Then MsgBox "xx"
Examples of Verification by Code
When validating entries, be as pessimistic as possible. If a user can get it wrong, they will! Often it will not be possible to account for all possible errors. Sometimes the developer can only minimize the damage. In the end, responsibility falls on the user.
If the user has been allowed to type an entry, either in TextBox or in the TextBox part of ComboBox, then the entry must be checked for where it will be used. For example, it can be validated as a number, but furthermore, if it is to be a US ZIP code, then it can be checked to see in string form it is 5, 9, or 10 digits long. If 10 digits, then it should be of the form zzzzz-nnnn.
A string entry may be quite complex, with multiple parts, where parts may be dates, numbers, etc. Rather than devise a complex way to parse such a string, it is better to change the design so the various parts are supplied by different controls.
Many different kinds of data can at least have some checking done, as can be seen from the following examples.
Check if Number
If Not IsNumeric(TextBox1.Value) Then
MsgBox "Numbers only", vbCritical
Exit Sub
End If
Check if Text
If IsNumeric(TextBox1.Value) Then
MsgBox "Text only", vbCritical
Exit Sub
End If
Check if Date
Dim s$
s$ = """" & TextBox1.Value & """"
If IsDate(s$) Then
MsgBox " Date only", vbCritical
Exit Sub
End If
IsDate requires a text string. TextBox returns a Variant value, so it must be surrounded with two pair of double quotes. The code strips one pair of double quotes, so one pair remains when it is submitted to IsDate.
Lesson 10 will present the DTPicker control for selecting valid dates.
Check if Time
Dim dTime As Date
If TextBox1.Text <> "12:00:00 AM" Then
On Error Resume Next
dTime = WorksheetFunction.Sum(TextBox1.Value, 1)
On Error GoTo 0
If dTime > DateValue("1/1/1900") Or dTime = "12:00:00 AM" Then
MsgBox "Times only", vbCritical
Exit Sub
End If
End If
This is a rather obscure check. The default value of a Date variable is 12:00:00 AM. It is a number, so if text is added to it the result will still be 12:00:00 AM. A valid time is a number less than 1 (see http://excel.tips.net/Pages/T002176_How_Excel_Stores_Dates_and_Times.html), so if 1 is added to it and submitted to the DateValue function, it will return a result that is less than the date 1/1/1900.
The On Error statement is needed for the code to keep operating if an error occurs. It will be discussed more in the Handling Invalid Data section below.
Check if Cell Address
Dim rCheck As Range
On Error Resume Next
Set rCheck = Range(TextBox1.Value)
On Error GoTo 0
If rCheck Is Nothing Then
MsgBox "Cell address only", vbCritical
Exit Sub
End If
Set rCheck = Nothing
Lesson 10 will present the RefEdit control for selecting and validating addresses.
Check if Email
If InStr(1, TextBox1.Text, "@", vbTextCompare) = 0 Then
MsgBox "Not a valid email", vbCritical
Exit Sub
End If
The same principle can be used to check for a Web address (www or http or .com etc) see Excel VBE help on InStr Function!
In UserForm Control Demos.xls, click “Validate Data” on sheet Lesson 9 for implementation of the above six checks
Check if Exists Already
This checks if a value is already present in a range of cells. If the cells are the RowSource of a ComboBox or ListBox, then it checks whether the value is included in the control List.
If WorksheetFunction.CountIf(Sheet1.Range("A1:C200"), TextBox1.Text) <> 0
Then
MsgBox "Entry is already part of the Table", vbCritical
End If
The same principle can be used to check whether an entry doesn't exist already simply by changing the comparison operator to =
Check if File or Folder Exists
This check will not be needed if the folder or file name was obtained from the GetOpenFilename dialog box as described above. If a path is from a cell on a worksheet, then the check is useful to ensure the path is still valid.
With Application.FileSearch
.NewSearch
.LookIn = "C:/MyDocuments"
.SearchSubFolders = True
.Filename = TextBox1.Text
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute = 0 Then
MsgBox "File does not exist", vbCritical
Exit Sub
End If
End With
This check does not return any information about where the file or folder name was found, only that it exists. The FileSearch Property has many other possibilities. Read the Excel VBE help on the FileSearch Property for more information.
Handling Invalid Data
There three concerns when handling invalid data.
On Error
The On Error statement is provided for code to survive run-time errors. It comes in two forms, GoTo and Resume Next. On Error GoTo 0 is a special form which clears the error and resets the error handling.
The Resume Next form most often used just to detect errors
On Error Resume Next ' start error handling
isAddress = IsObject(Range(s$))
On Error GoTo 0 ' clear error handling
The GoTo form can be used when complex handling of the error is required. The first example has an inline error handler where code execution resumes after the handler. The second example has the error handler at the end of the procedure. It can be allowed to exit the procedure after handling the error. The third example is the second ending with a Resume Next statement, which has the same effect as Example 1 with inline error handling
EXAMPLE 1
On Error Resume Next ' start error handling
isAddress = IsObject(Range(s$))
If Err.Number = 0 Then GoTo noError
'< code if an error occurred>
noError:
'< code if no error occurred>
On Error GoTo 0 ' clear error handling
EXAMPLE 2
On Error GoTo errorHandler ' start error handling
isAddress = IsObject(Range(s$))
'< code if no error occurred>
Exit Sub
errorHandler:
'< code if an error occurred>
On Error GoTo 0 ' clear error handling
' focus is set to the failing control and code is allowed to exit sub
EXAMPLE 3
On Error GoTo errorHandler ' start error handling
isAddress = IsObject(Range(s$))
'< code if no error occurred>
Exit Sub
errorHandler:
'< code if an error occurred>
On Error GoTo 0 ' clear error handling
Resume Next ' return to normal execution
As far as choosing one form over another, Example 1 is good for short procedure, where the code flow is easily seen. The other two examples are more useful where there is a lot of code in the non-error flow. It moves the error handling to the bottom of the procedure where it doesn’t interfere with understanding the main code Flow. Example 2 is most often used with UserForms, because the focus can be set back to the failing control before the procedure ends.
MsgBox
MsgBox is the usual method of choice to notify the user of an error to be corrected. It has the power to insist the error be seen by the user. While its normal use is to post one error, a message can be accumulated in a checking loop and display just once after the loop with all the errors.
MsgBox can display one of four different icons, which can be used to indicate the severity of the error. The four icons are Critical, Question, Exclamation, and Information. In general, fatal errors will be posted with a Critical MsgBox,
MsgBox also has built in buttons for the user to select an action. There are six button choices
Display OK button only.
Display OK and Cancel buttons.
Display Abort, Retry, and Ignore buttons.
Display Yes, No, and Cancel buttons.
Display Yes and No buttons.
Display Retry and Cancel buttons.
A hierarchy can be defined for a project, where Critical errors must be fixed, Question errors may be ignored, and Exclamation errors may be retried, and Information conditions are only logged to the log file. Note that if decision buttons are included, then MsgBox must be used as a function, with an integer return value:
MsgBox "This is a Critical message box", vbCritical
returnValue% = MsgBox("This is a Question message box", _
vbQuestion + vbAbortRetryIgnore)
returnValue% = MsgBox("This is an Exclamation message box", _
vbExclamation + vbRetryCancel)
MsgBox "This is an Information message box", vbInformation
The message presented by the MsgBox should state what the problem is, and either suggest corrective action, or put the user back on the UserForm at the point of the error so it may be corrected. “Invalid Data” messages are useless. Cancel and Ignore buttons should lead to Exit Sub statements which ends the validation attempt. If multiple errors are being processed, then they should go on to deal with the next error in the list.
Log File
Some projects will have a need for the invalid data history. In those cases, the information can be written to a log file. By logging after the MsgBox, the log record can include what button on the MsgBox was clicked.
retValAry = Array("OK", "Cancel", "Abort", "Retry", "Ignore", "Yes", "No")
Print #1, "Invalid Data", control.Name, value, retValAry(returnVal%)
Writing to a log file was covered in Lesson 7.
Set Focus
If a value is invalid, the SetFocus method is used to put the cursor into the failing control so the user can correct the value. The Tag property is useful because it can be set at design time and save with the file.
EXERCISE
1. Insert a UserForm into VBAProject.
2. On it create six TextBox controls.
3. Format them with Centers aligned.
4. From top to bottom change their
a. Name properties to: HomeEmail, WorkEmail, Age, PayNumber, Telephone, and StAddress
b. Value properties to: *YourEmail@Home, *YourEmail@Work, *Your Age, *Your Pay Number, Telephone, and Street Address
c. Tag properties to “Check”
5. At the top of the TextBoxes place a Label control and change it's Caption Property to: Please note * denotes required entry
6. Finally add a CommandButton, rename to “OKButton” and change Caption “OK”
Code the OKButton_Click event to require the user to fill in all required entries.
' module level variables
Dim ctrlControl As Control
Dim bCancel As Boolean
Private Sub OKButton_Click()
For Each ctrlControl In Me.Controls
If ctrlControl.Tag = "Check" Then
ctrlControl.SetFocus
ValidationCheck ' Private Sub to check entries
End If
If bCancel = True Then Exit For
Next ctrlControl
If bCancel = True Then Exit Sub
'<Some carry on code here>
End Sub
OKButton_Click will loop through all controls on the UserForm and perform the validation check if so indicated by the Tag property. Each validation procedure will set bCancel to True if there is an error. If a validation error occurs, the validation procedure will return with bCancel set to True, and the OKButton_Click code will exit with focus set to the failing control.
Private Sub ValidationCheck()
Dim ValToCheck, msg$ As String
ValToCheck = Me.ActiveControl.Value
Select Case ActiveControl.Name
Case "HomeEmail", "WorkEmail"
If Left(ValToCheck, 11) = "*YourEmail@" Then
msg$ = "Please enter an email"
ElseIf InStr(1, ValToCheck, "@", vbTextCompare) = 0 Then
msg$ = "Please enter a valid email"
End If
Case "Age
Select Case ValToCheck
Case vbNullString, "*Your Age", Not IsNumeric(ValToCheck)
msg$ = "Please check your age"
Case ", "PayNumber""PayNumber"
Select Case ValToCheck
Case vbNullString, "*Your Pay Number", Not
IsNumeric(ValToCheck)
msg$ = "Please check your pay number"
End Select
If msg$ = "" Then
bCancel = False
Else
bCancel = True
MsgBox msg$, vbCritical
' optional log file step would go here
End If
End Sub
NOTES:
1. Procedure is a Private Sub as it is residing in the Private module of the UserForm.
2. The variable "ValToCheck" is declared as a Variant (default) as it may hold either text or numbers. "msg$" will be used in a message box.
3. Because the calling procedure set focus to this control, a Select Case Statement can used on the ActiveControl name.
4. The control names set at design time are used to determine which validation to perform.
5. Each validation must allow for the default value and for a “” value (NullString) caused by the user hitting the Delete key.
6. "HomeEmail" and "WorkEmail" uses a simple If statement to check whether the entered value an "@".
7. “Age” and “PayNumber” are checked with another Select Case Statement to find out 1) if it's blank, 2) if it still has its default value and 3) if it contains text.
8. If any of the above are True, the variable is set to relevant text.
9. After the original Select Case statement, if the string variable is not empty, the user has not correctly filled in the UserForm, so the string variable is displayed.
10. After the user clicks OK on MsgBox, the code returns to the loop.
The advantage of setting focus to the failing control is that the cursor will be flashing, leading the user to the error.
Accumulating Validation Errors
In the special case where errors are to be accumulated in a loop, the message string accumulates with linefeed characters between the messages.
msg$ = msg$ & IIf(msg$ = "", "", " & Chr(10) & ") & "this message"
The MsgBox will display all the errors at once. It will be closed before the user has control and can make the corrections. The user will have to find and correct the problems without benefit of the cursor blinking control with the problem. This situation is one where having a log file listing the errors will be very useful.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.