Using the Do Unti Lbop

The macro shown in Listing 7.5 enables a sales representative's age to be input and validated in the range 25 to 65. Any value outside this range is rejected by the macro, and the user is forced to re-input again. The macro continues to loop until the user has entered acceptable data.

Listing 7.5 Using Do...Until to control a loop

Sub ageDoUntil()

Dim strAge As String

Dim intAge As Integer

strAge = InputBox("How old are you?", "Age Ask")

'Get the age in a string variable

If (strAge = "") Then End

'Check for the Cancel command button, if so, terminate the program

intAge = Val(strAge)

'Cancel was not pressed, so convert Age to integer

Do Until ((intAge >= 25) And (intAge <= 65))

'Loop if the age is not in the correct range

MsgBox("Your age must be between 25 and 65", vbExclamation, "Error!")

'The salesman's age is out of range

strAge = InputBox("How old are you?", "Age Ask")

If (strAge = "") Then End

'Terminate the program when Cancel button pressed

intAge = Val(strAge)


End Sub

The Listing uses the built-in Val() function. Val() accepts a string argument and converts that string to a number (assuming that the string holds the correct digits for a number). The InputBox function returns a string so the value that the user enters into the input box must convert to an integer before you store the value in the integer variable named intAge.

The Do Until ((intAge >= 25) And (intAge <= 65)) loops until the user enters a value that's more than or equal to 25 and less than or equal to 65.

0 0

Post a comment