Select Case
 

I have a function that prompts the user to enter a week number. Control then passes back to to another function that gets the appropriate info for that week. My problem is in checking the week number the user inputs is valid (whole number between 1 and 20 inclusive). I'm doing something wrong in the first Case (see below) Here is the function:

Function GetWeek() As Variant
Dim Answer As Variant
Begin:
Answer = Application.InputBox(prompt:="Enter Week # to change. Hit cancel" _
& " when done.", Type:=1)

If TypeName(Answer) = "boolean" Then
If ConfirmExit = True Then GetWeek = False
Else
Select Case Answer
Case Is Application.WorksheetFunction.Round(Answer, 0)
'problem is here
MsgBox "You must enter a whole number."
GoTo Begin
Case Is < 1, Is 20
MsgBox "You must choose a number between 1 and 20"
GoTo Begin
Case Else
GetWeek = Answer
End Select
End If
End Function

Is putting a function call inside a Case test legal? Do i need to do the check outside of the Case (assigning the result to a variable) then check 'answer' against that variable?

Select Case
 

Marcotte,

Nothing implicitly wrong, but round could round up and so will fail (e.g..
1.5 rounds up to 2), and then Answer is not greater. Changer the test to <

Function GetWeek() As Variant
Dim Answer As Variant
Begin:
Answer = Application.InputBox(prompt:="Enter Week # to change. Hit cancel"
_
& " when done.", Type:=1)

If TypeName(Answer) = "boolean" Then
If ConfirmExit = True Then GetWeek = False
Else
Select Case Answer
Case Is < Application.WorksheetFunction.Round(Answer, 0)
'problem is here
MsgBox "You must enter a whole number."
GoTo Begin
Case Is < 1, Is 20
MsgBox "You must choose a number between 1 and 20"
GoTo Begin
Case Else
GetWeek = Answer
End Select
End If
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Marcotte A" <Marcotte wrote in message
...
I have a function that prompts the user to enter a week number. Control
then passes back to to another function that gets the appropriate info for
that week. My problem is in checking the week number the user inputs is
valid (whole number between 1 and 20 inclusive). I'm doing something wrong
in the first Case (see below) Here is the function:

Function GetWeek() As Variant
Dim Answer As Variant
Begin:
Answer = Application.InputBox(prompt:="Enter Week # to change. Hit
cancel" _
& " when done.", Type:=1)

If TypeName(Answer) = "boolean" Then
If ConfirmExit = True Then GetWeek = False
Else
Select Case Answer
Case Is Application.WorksheetFunction.Round(Answer, 0)
'problem is here
MsgBox "You must enter a whole number."
GoTo Begin
Case Is < 1, Is 20
MsgBox "You must choose a number between 1 and 20"
GoTo Begin
Case Else
GetWeek = Answer
End Select
End If
End Function

Is putting a function call inside a Case test legal? Do i need to do the
check outside of the Case (assigning the result to a variable) then check
'answer' against that variable?

Select Case
 

"Bob Phillips" wrote:

Marcotte,

Nothing implicitly wrong, but round could round up and so will fail (e.g..
1.5 rounds up to 2), and then Answer is not greater. Changer the test to <

Function GetWeek() As Variant
Dim Answer As Variant
Begin:
Answer = Application.InputBox(prompt:="Enter Week # to change. Hit cancel"
_
& " when done.", Type:=1)

If TypeName(Answer) = "boolean" Then
If ConfirmExit = True Then GetWeek = False
Else
Select Case Answer
Case Is < Application.WorksheetFunction.Round(Answer, 0)
'problem is here
MsgBox "You must enter a whole number."
GoTo Begin
Case Is < 1, Is 20
MsgBox "You must choose a number between 1 and 20"
GoTo Begin
Case Else
GetWeek = Answer
End Select
End If
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)


Thanks Bob. I made your correction but was still getting the same error. Searched and searched for upwards of an hour before finding "boolean" (should be "Boolean"). arrgggghhh! Not only did I have a bug, I couldn't even intrepret the runtime results correctly (ie I was looking in the wrong place). Ironically, 5 minutes before I found it I had told a coworker "This is why I gave up CompSci in college. You search for a bug for hours only to find it is a misspelling or missing carriage return"

Thanks again!
Marcotte

Select Case
 

Well I don't understand that. I type in boolean and VBA accepts that and
changes it itself to Proper case.

Don't ignore my post, even if it hasn't given you a problem yet, it is a
bug.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Marcotte A" wrote in message
...
"Bob Phillips" wrote:

Marcotte,

Nothing implicitly wrong, but round could round up and so will fail
(e.g..
1.5 rounds up to 2), and then Answer is not greater. Changer the test to
<

Function GetWeek() As Variant
Dim Answer As Variant
Begin:
Answer = Application.InputBox(prompt:="Enter Week # to change. Hit
cancel"
_
& " when done.", Type:=1)

If TypeName(Answer) = "boolean" Then
If ConfirmExit = True Then GetWeek = False
Else
Select Case Answer
Case Is < Application.WorksheetFunction.Round(Answer, 0)
'problem is here
MsgBox "You must enter a whole number."
GoTo Begin
Case Is < 1, Is 20
MsgBox "You must choose a number between 1 and 20"
GoTo Begin
Case Else
GetWeek = Answer
End Select
End If
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)


Thanks Bob. I made your correction but was still getting the same error.
Searched and searched for upwards of an hour before finding "boolean"
(should be "Boolean"). arrgggghhh! Not only did I have a bug, I couldn't
even intrepret the runtime results correctly (ie I was looking in the wrong
place). Ironically, 5 minutes before I found it I had told a coworker "This
is why I gave up CompSci in college. You search for a bug for hours only to
find it is a misspelling or missing carriage return"

Thanks again!
Marcotte

Select Case
 

You might want to type this directly after your inputbox call:
MsgBox TypeName(Answer)
It will show that Answer is never boolean

Regards
Gary

"Bob Phillips" wrote in message ...
Well I don't understand that. I type in boolean and VBA accepts that and
changes it itself to Proper case.

Don't ignore my post, even if it hasn't given you a problem yet, it is a
bug.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Marcotte A" wrote in message
...
"Bob Phillips" wrote:

Marcotte,

Nothing implicitly wrong, but round could round up and so will fail
(e.g..
1.5 rounds up to 2), and then Answer is not greater. Changer the test to
<

Function GetWeek() As Variant
Dim Answer As Variant
Begin:
Answer = Application.InputBox(prompt:="Enter Week # to change. Hit
cancel"
_
& " when done.", Type:=1)

If TypeName(Answer) = "boolean" Then
If ConfirmExit = True Then GetWeek = False
Else
Select Case Answer
Case Is < Application.WorksheetFunction.Round(Answer, 0)
'problem is here
MsgBox "You must enter a whole number."
GoTo Begin
Case Is < 1, Is 20
MsgBox "You must choose a number between 1 and 20"
GoTo Begin
Case Else
GetWeek = Answer
End Select
End If
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)


Thanks Bob. I made your correction but was still getting the same error.
Searched and searched for upwards of an hour before finding "boolean"
(should be "Boolean"). arrgggghhh! Not only did I have a bug, I couldn't
even intrepret the runtime results correctly (ie I was looking in the wrong
place). Ironically, 5 minutes before I found it I had told a coworker "This
is why I gave up CompSci in college. You search for a bug for hours only to
find it is a misspelling or missing carriage return"

Thanks again!
Marcotte

Select Case
 

Ignore that last post - I've just realised that you are asking the
user to click the cancel button - in which case, Answer will not
contain any input the user makes.

Regards
Gary