Select Case


G

Guest

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?
 
Ad

Advertisements

B

Bob Phillips

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 said:
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?
 
G

Guest

Bob Phillips said:
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
 
B

Bob Phillips

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 said:
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"
 
G

Gary Clifford

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

Regards
Gary
 
Ad

Advertisements

G

Gary Clifford

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Case Select 10
Case Select 6
Select Case 0
Select Case 13
Select Case 6
Select Case 4
Select case or If then 3
Select Case 6

Top