Programming Techniques

Programming Techniques

Numerical Input

This introductory example displays a form for input of a number between 0 and 100 (Figure 7-26). The form exhibits two features of interest:

• Validity control of the input when OK is pressed.
• Reciprocal updating of the editing field and scolling field when one of these controls is changed.

Figure 7-26: Form for the input of a number between 0 and 100

The transfer of the result is accomplished, as with most examples of this chapter, with the module variable result. New in this example is the situation that with this variable the initial state of the form can be set with this variable. To ensure that the required initialization tasks are carried out in spite of the unreliable UserForm_Activate event, the form is equipped with a new method, ShowMe, which takes over both the initialization and display of the form.

' Userform.xls, class module »dlgNumber«

Option Explicit

Public result As Variant

Public Sub ShowMe()

Dim nmb As Variant

nmb = result

If nmb < 0 Or Not IsNumeric(nmb) Then nmb = 0

If nmb > 100 Then nmb = 100

txtNumber = nmb: scrSlider = nmb

Show

End Sub

The synchronization between a text box (txtNumber) and scroll bar (scrSlider) is accomplished with the event procedures of these two controls.

Private Sub scrSlider_Change()

txtNumber = scrSlider

End Sub

Private Sub scrSlider_Scroll()

scrSlider_Change

End Sub

Private Sub txtNumber_Change()

Dim nmb As Variant

nmb = Val(txtNumber)

If nmb >= 0 And nmb <= 100 And IsNumeric(txtNumber) Then

scrSlider = nmb

End If

End Sub

If the form is terminated with OK, then btnOK_Click checks the contents of the input field. If the value is outside of the valid range, then the user is requested by an alert to mend his ways and input a valid number. At the same time the input focus is placed in the text box (Focus method). If the input is correct, the form is terminated (Hide method).

Private Sub btnOK_Click()

Dim nmb As Variant

nmb = Val(txtNumber)

If nmb < 0 Or nmb > 100 Or Not IsNumeric(txtNumber) Then

MsgBox "Please choose a number between 0 and 100"

txtNumber.SetFocus

Else

result = nmb

Hide

End If

End Sub

Reciprocal Calling of Forms

There are numerous possible applications of reciprocal calling of forms.

• If the user makes an input error, he can be sent to an error-handling form that explains the error and offers the possibility of fixing it.
• A subdialog can be available for little-used options, summoned by an Options button. After input of the options the original form would reappear.
• If the input data are so complex that they cannot be conveniently handled in a single form, then an entire chain of forms (such as in the case of the office assistant) can be displayed that take the user step by step through the process of data entry.

Cascades of Forms

Programming cascades of forms presents few difficulties. Essentially, the event procedure for the relevant button must be programmed so that the next form is launched with Show. Forms that are active are not influenced by this process, and when the last form is terminated, they become active again. (For Visual Basic pros, the forms are displayed modally, that is, previously launched forms are blocked until the termination of the last form.)

Userform.xls contains a three-stage cascade of form pages, "Cascade1" through "Cascade 3" (Figure 7-27), which demonstrates the basic pattern (though without offering the possibility of setting any options). Here as an example is the code for the second form; the event procedures for the first and third forms are similar.

Figure 7-27: A three-step cascade of forms (all three forms can be made visible at one time)

' Userform.xls, UserForm »dlgCascade2«

Private Sub btnOK_Click()

Unload Me

End Sub

Private Sub btnCancel_Click()

Unload Me

End Sub

Private Sub btnOption_Click()

dlgCascade3.Show

End Sub

Chain of Forms (Creating Your Own Assistants)

In a chain of forms there are several forms of which only one is visible at a time (Figure 7-28). The main distinction between a chain and a cascade is that the user must follow the chain to the end except to cancel, while the cascade can be terminated with OK at any stage. With Next and Back buttons you can jump from one form to the following or previous one in the chain, until all input has been taken care of the chain ended in the last form.

Figure 7-28: A three-stage chain of forms (only one form at a time is visible)

The only particular issue in this example is the manual positioning of the forms: When form 1 is moved to another position by the user, forms 2 and 3 appear in the same new position. To enable this feature the property StartupPosition must be set manually to (0) in the development environment. Now the position of the forms can be influenced by the properties Left and Top before the form is displayed via Show.

Now to the example: It is merely a chain of forms, without doing anything useful. The event procedures for the second form are as follows:

' Userform.xls, UserForm "dlgChain2"

Private Sub btnNext_Click()

dlgChain3.Left = Left

dlgChain3.Top = Top

Hide

dlgChain3.Show

End Sub

Private Sub btnPrevious_Click()

dlgChain1.Left = Left

dlgChain1.Top = Top

Hide

dlgChain1.Show

End Sub

Private Sub btnCancel_Click()

Unload Me

End Sub

Editing Forms Dynamically

By the dynamic editing of a form we mean that the appearance of the form can be changed while it is being displayed. An example of a predefined dynamic form is the one for finding and replacing. When you execute Edit|Find and then click on Replace, the Find form is extended to include a "replace" field.

Dynamic forms are always used when a form is to be used for several similar cases or when the user might become confused by a large selection of seldomused setting options.

The simplest way to set dynamic forms is to take into consideration during the development of the form all the controls that you are going to need. In the form's program code you can then make individual controls visible or invisible as the need arises. You can also place several controls one on top of the other, provided that you take care in the code to make only one of these controls visible at a time.

Furthermore, you can place controls outside of the form's boundaries and then enlarge the form as needed to include them (by changing the Height and Width properties). But be careful to ensure that controls that lie outside of the form are nonetheless active and can be accessed (Tab) via the keyboard. Regardless of the actual size of the form field, all controls that at the moment are not usable should have their Enabled property set to False.

The form shown in Figure 7-29 can be enlarged with the More Options button. The scroll bar is displayed according to the state of the control box. When the form is displayed, there must be in the code for running the form a clearly defined state that is independent of the settings the last time the form was called. For this reason the method ShowMe is defined, which must be used to call the form instead of Show:

Figure 7-29: A dynamic form; left: in its initial state; right: expanded

' Userform.xls, UserForm "dlgDynamic"

Public Sub ShowMe()

OptionButton1.Enabled = False

OptionButton2.Enabled = False

OptionButton3.Enabled = False

CheckBox1.Enabled = False

CheckBox2.Enabled = False

CheckBox3.Enabled = False

Show

End Sub

The event procedures for displaying the scroll bar as well as for enlarging the form are less spectacular:

Private Sub chkSlider_Click()

Scrollbar1.Visible = chkSlider

End Sub

Private Sub btnExpand_Click()

Height = 170

OptionButton1.Enabled = True

...

End Sub

Working with Spin Buttons

Spin buttons are a very practical item for the user. By simply clicking on the mouse certain data can be input, such as date and time, without the risk of a syntax error. Figure 7-30 shows a few possible applications.

Figure 7-30: Applications of spin buttons

For the programmer, on the other hand, things are not quite so simple. The main problem is in arranging the various options, dates, or times in order. The task is made easier by the fact that one is no longer restricted, as in Excel 7, to a range between 0 and 30000.

In addition to convenient settings with the mouse, it usually should be possible for direct text input: For this reason the contents of the text field and spin box must be synchronized (see also the introductory example below).

Setting the Year with a Spin Button

The procedures spnYear_Change and txtYear_Change show how text and spin fields can be synchronized for a simple numerical input. When the spin field is changed, the current value is transformed into a character string and written into the text field. Conversely, when the text field is altered an attempt is made to associate this value to those of the spin field. The upper and lower limits are set with the control options for minimum and maximum values, which are accessible in program code via the properties Min and Max.

' Userform.xls, UserForm "dlgSpin"

Private Sub spnYear_Change()

txtYear = spnYear

End Sub

Private Sub txtYear_Change()

Dim y As Integer

y = Val(txtYear)

If y >= spnYear.Min And y <= spnYear.Max Then

spnYear = y

End If

End Sub

Setting the Month with a Spin Box

The situation becomes somewhat more complicated when not only the year but also the month is to be set. For changing a number in the spin box to a date the formula n=year*12+month is used. The properties Min and Max for the spin box are set to 0 and 30000, so that dates between January 0000 and December 2499 can be represented. When the spin field is clicked on, the date is calculated and the resulting date transformed with Format into a character string, which then is displayed in the text field.

In the converse case, that is, where the date is input into the text field, an attempt is made to use CDate to transform the character string into a date. If this fails (on account of a syntax error in the input), the current value of the spin field is left unchanged.

Private Sub spnMonth_Change()

' n=year*12 + month

Dim y As Integer, m As Integer, dat As Date

y = Int(spnMonth / 12) 'year

m = spnMonth Mod 12 'month

dat = DateSerial(y, m, 1)

txtMonth = Format(dat, "mmm yyyy")

End Sub

Private Sub txtMonth_Change()

Dim dat As Date

On Error Resume Next

dat = CDate(txtMonth)

If Err > 0 Then Exit Sub 'input is not a valid date

spnMonth = Month(dat) + Year(dat) * 12

End Sub

Setting the Date in a Spin Box

There is no problem in converting the numbers in the spin box into consecutive dates. Excel's internal representation is used. The preset valid range from 0 to 109574 corresponds to dates between 12/31/1899 and 12/31/2199.

Using Like "*.*.??**" to make comparisons, a conversion to a date is attempted only if the input looks like a date. This measure prevents incomplete (and thus erroneous) input from being processed too early.

Private Sub spnDate_Change()

txtDate = Format(spnDate, "mm/dd/yyyy")

End Sub

Private Sub txtDate_Change()

Dim dat As Date

On Error Resume Next

dat = CDate(txtDate)

If Err > 0 Then Exit Sub 'invalid input

spnDate = CLng(dat)

End Sub

Setting the Time with a Spin Box

Like dates, times can also be set in a spin box. In the example below the time can be input precisely via the keyboard, or via the spin box in half-hour intervals. The valid range of values for n (in half hours) is from 0 to 47.

Private Sub spnTime_Change()

Dim t As Date

t = CDate(spnTime / 48)

txtTime = FormatDateTime(t, vbLongTime)

End Sub

Private Sub txtTime_Change()

Dim tim As Date

On Error Resume Next

tim = CDate(txtTime)

If Err > 0 Then Exit Sub

spnTime = Int(CDbl(tim) * 48 + 0.5)

End Sub

Initialization

UserForm_Initialize takes care that when the form is first launched, reasonable preset values are displayed.

' Initialization

Private Sub UserForm_Initialize()

txtYear = Year(Now)

txtYear_Change

txtMonth = Format(Now, "mmm yyyy")

txtMonth_Change

txtDate = FormatDateTime(Now, vbShortDate)

txtDate_Change

txtTime = FormatDateTime(Int(Now * 48 + 0.5) / 48, vbLongTime)

txtTime_Change

End Sub