Select Case syntax

[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

Thanks,
Alan Beban

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Chip Pearson]

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)

 

[Dave Peterson]

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.

 

[Dana DeLouis]

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

 

[Alan Beban]

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

 

[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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Alan Beban]

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\)]

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

 

[Dave Peterson]

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]

Which is apparently what was the case for me. I don't have any idea
what the Missing Array Addin.xls is, but I can't imagine it having
anything to do with the Mid or Right or Len functions that were throwing
the error until I unchecked it.

Although it's no longer relevant to the problem that caused me to post,
do you, Dave, or anyone else who might be interested, have any
additional insight with respect to Chip's comment about the
undesirability of the use of the Select Case True construct?

Alan

 

[Dave Peterson]

I don't have any insight one way or another on the "select case true".

It's never failed for me when I've used it (although that's on the seldom
side). And I've seen a few posts in the newsgroups. I don't recall reading any
posts that follow up describing problems with it.

 

[Chip Pearson]

additional insight with respect to Chip's comment about the undesirability

of the use of the Select Case True construct?

I picked that up in a thread in one of the VB6 newsgroups some time ago. My
guess is that it is undesirable because you have to evaluate all the
expressions in the Case statements until you get to one that evaluates to
True. Evaluating the expressions carries more overhead than a simple test
against a constant or simple variable. It all may have been just a debate
about coding style. Maybe Rick has some better insight.


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

 

[Rick Rothstein \(MVP - VB\)]

additional insight with respect to Chip's comment about the

I picked that up in a thread in one of the VB6 newsgroups some time ago.
My guess is that it is undesirable because you have to evaluate all the
expressions in the Case statements until you get to one that evaluates to
True. Evaluating the expressions carries more overhead than a simple test
against a constant or simple variable. It all may have been just a debate
about coding style. Maybe Rick has some better insight.

That is pretty much it. The problem is basically that you are bastardizing
the structure in order to use it in a way it was not really designed for. If
you use Select Case True, then you might as well use If-Then-ElseIf blocks
(which I think are more efficient then Select Case True). There were lots of
discussions on this issue across the years, but I can't find any of them now
using Google. Actually, Google seems to not have the entire archive it used
to have. My posting history goes back to 1998 or 1999, but the oldest post I
can find is dated November 20, 2003.

Rick