What is the Select Case syntax, if any, for

m = "a2"
If m Like "*1" Then j = 1
If m Like "*2" Then j = 2
If m Like "*3" Then j = 3

Thanks,
Alan Beban

What is the Select Case syntax, if any, for

m = "a2"
If m Like "*1" Then j = 1
If m Like "*2" Then j = 2
If m Like "*3" Then j = 3

Do you absolutely need to use a Select Case (that is, are you doing more
than assigning a value to j for each ending number? If not, you can do what
you posted with this one line of code...

j = Right$(m, 1)

Although I would encase that in a Cxxx function call to explicitly convert
it to the data type j was declared as. For example, if j was Dim'med As
Long, then I would use

j = CLng(Right$(m, 1))

However, if you need to use the Select Case structure, it would look like
this...

Dim m As String
m = "a2"
Select Case Right(m, 1)
Case 1
' m is equal to "a1"
Case 2
' m is equal to "a2"
Case 3
' m is equal to "a3"
End Select

where you would place your code for each possible Case where indicated.

Rick

Thanks Rick and Dave. Your responses raise my original problem that I
don't really see an expeditious way of addressing through the newsgroup,
but let's give it a try.

I have an Excel VBA program for tracking a company's stock option
issuances that worked several years ago and I have been trying to
rejuvenate it in xl2002. It has a UserForm (uFormGrantEntry) that
requires the selection of one from about 40 option buttons for the type
of vesting; e.g., Option5yrm1yrc for an option that vests ratably by
month over 5 years, with no vesting for the 1st yr (i.e., a 1-year
"cliff"), or Option4yrq6mc for an option that vests ratably by quarter
over 4 years with a 6-month cliff, etc. Except for the option buttons
named "OptionImmediate" and "OptionCustom, all the option buttons have
names that begin with "Option" followed by a number from 1 to 5. The
code is supposed to extract the "5" (or "4", or "3", or whatever)
portion of the option button name and deposit it in the appropriate cell
on a data form worksheet, so at one point the code for this UserForm
includes

For Each opButton In uFormGrantEntry.Frame2.Controls
If TypeName(opButton) = "OptionButton" Then
If opButton Then
m = opButton.Name

'If the selected type of vesting is not "immediate" or
"custom", then
'initialize a variable to refer to the number of years
(1,2,3,4 or 5)
'in the Vesting Type
If Not m = "OptionImmediate" And Not m = "OptionCustom" Then
j = Mid(m, 7, 1) * 1
End If
Exit For
End If
End If
Next

For some reason inexplicable to me, the line j = Mid(m, 7, 1)*1 throws
an error "Cant find project or library". So I substituted
j = Right(m, Len(m) - 6) (to extract the "5yrm1yrc" portion) and got the
same error. Rather than try to solve that problem, which I don't
understand, I am considering the If . . . Then or Select Case approach.
I suppose I could go back and rename the option buttons to eliminate the
"Option" part, but there are a lot of them, and I haven't even
ascertained whether an option button name can begin with a number.

Anyway, thanks for any help. I might consider Chip's suggestion if I
can't make any progress on the underlying error message problem.

Thanks.
Alan Beban

Rick Rothstein (MVP - VB) wrote:
What is the Select Case syntax, if any, for

m = "a2"
If m Like "*1" Then j = 1
If m Like "*2" Then j = 2
If m Like "*3" Then j = 3

Do you absolutely need to use a Select Case (that is, are you doing more
than assigning a value to j for each ending number? If not, you can do
what you posted with this one line of code...

j = Right$(m, 1)

Although I would encase that in a Cxxx function call to explicitly
convert it to the data type j was declared as. For example, if j was
Dim'med As Long, then I would use

j = CLng(Right$(m, 1))

However, if you need to use the Select Case structure, it would look
like this...

Dim m As String
m = "a2"
Select Case Right(m, 1)
Case 1
' m is equal to "a1"
Case 2
' m is equal to "a2"
Case 3
' m is equal to "a3"
End Select

where you would place your code for each possible Case where indicated.

Rick

For some reason inexplicable to me, the line j = Mid(m, 7, 1)*1 throws an
error "Cant find project or library".

In the VBA editor, click on Tools/References and see if anything is marked
as "Missing". If so, either check the item or items (if it is not checked)
or uncheck the item or items (if it is checked) and try the code again.

Rick

When I clicked on Tools/References there was a checked item "Missing
ArrayAdd-in"; I unchecked it and the code then worked.

What was that about? And thanks much.

Alan Beban

Rick Rothstein (MVP - VB) wrote:
For some reason inexplicable to me, the line j = Mid(m, 7, 1)*1 throws
an error "Cant find project or library".

In the VBA editor, click on Tools/References and see if anything is
marked as "Missing". If so, either check the item or items (if it is not
checked) or uncheck the item or items (if it is checked) and try the
code again.

Rick

When I clicked on Tools/References there was a checked item "Missing
ArrayAdd-in"; I unchecked it and the code then worked.

What was that about? And thanks much.

You are welcome. I don't know what the cause of a lost or extra reference is
(not sure anyone really does), but it happens in the compiled world of VB
also.

Rick

And the part that makes it really confusing is that if you're missing a
reference, then the line that is marked with an error may not have anything to
do with anything in that reference.



Alan Beban wrote:

When I clicked on Tools/References there was a checked item "Missing
ArrayAdd-in"; I unchecked it and the code then worked.

What was that about? And thanks much.

Alan Beban

Rick Rothstein (MVP - VB) wrote:
For some reason inexplicable to me, the line j = Mid(m, 7, 1)*1 throws
an error "Cant find project or library".

In the VBA editor, click on Tools/References and see if anything is
marked as "Missing". If so, either check the item or items (if it is not
checked) or uncheck the item or items (if it is checked) and try the
code again.

Rick

--

Dave Peterson

Alan,

You might try something like the following:

Sub AAA()
Dim M, J
M = "321"
Select Case True
Case M Like "*1"
J = 1
Case M Like "*2"
J = 2
Case Else
' whatever
End Select

End Sub

In other newsgroups, I've read that a "Select Case True" construct is to be
frowned upon, but I never got a good reason for this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Alan Beban" <unavailable wrote in message
...
What is the Select Case syntax, if any, for

m = "a2"
If m Like "*1" Then j = 1
If m Like "*2" Then j = 2
If m Like "*3" Then j = 3

Thanks,
Alan Beban

Thanks Chip. I may give it a try. The "real" problem is set out in my
post responding to Rick Rothstein and Dave Peterson in this thread.

Thanks
Alan

Chip Pearson wrote:
Alan,

You might try something like the following:

Sub AAA()
Dim M, J
M = "321"
Select Case True
Case M Like "*1"
J = 1
Case M Like "*2"
J = 2
Case Else
' whatever
End Select

End Sub

In other newsgroups, I've read that a "Select Case True" construct is to
be frowned upon, but I never got a good reason for this.

According to xl2003's VBA's help, you can't use Like as the comparison operator.

But you could do this:

m = "a2"
Select Case Right(m, 1)
Case Is = "1": j = 1
Case Is = "2": j = 2
Case Is = "3": j = 3
End Select

It fits the simple example. I'm not sure it can be used for your real code.



Alan Beban wrote:

What is the Select Case syntax, if any, for

m = "a2"
If m Like "*1" Then j = 1
If m Like "*2" Then j = 2
If m Like "*3" Then j = 3

Thanks,
Alan Beban

--

Dave Peterson

Just another option:

Sub Demo()
Dim m, j, s

m = "a2"
s = Right$(m, 1)
Select Case s
Case "1" To "3": j = CLng(s)
Case "a" To "z": MsgBox "Small letter"
Case Else: MsgBox "Something else"
End Select
End Sub

--
Dana DeLouis


"Alan Beban" <unavailable wrote in message
...
What is the Select Case syntax, if any, for

m = "a2"
If m Like "*1" Then j = 1
If m Like "*2" Then j = 2
If m Like "*3" Then j = 3

Thanks,
Alan Beban