VBA: Select Case for String in variable

[bukimi]

Hello!

I have specific problem with my code.
SkillName is my Variable taken from list (item selected in list, works fine)
I know I can add all possible answers one by one like that:

Select Case SkillName
    Case "Good Excel", "Very good Excel", "Excellent Excel"
    -stuff to do-
End Select

But I need something to work with all variants of possible answers. Something that would check if word "Excel" is inside of SkillName anywhere or not and if yes proceed with that Select Case. Of course there are many other words that will be checked that way in different "Case" after "Excel".

I tired "InStr" and "Like" in many ways, but no matter where I put it, it doesn't work properly.

Please help. Thank you in advance.

 

[ScottR]

This is why I never use select case...

You can either write it as

Select Case InStr(1,SkillName,"Excel")

Case Is>0  

'Do Stuff

and do different statements based on different requirements

or

If InStr(1,SkillName,"Excel")>0 then

'Do Stuff

Then you can use alternate conditions within the same If branch.

Hope that makes sense.

 

[bukimi]

Thank you for your reply, but it's not what exactly what I needed.
With your code I would need to make separate "If" or "Select case" section for every word checked (there will be ~12 of them). Then, code would be not very clear.

I want to make something like

Select Case SkillName (contains)
 "Excel" -do sth-
 "Word" -do sth else-
 "Access" -another code-

etc.

I don't know if it's possible to make that word check in one section list. That would make my code almost 12x shorter.

EDIT: Of course if there's something that would check contents of SkillName variable other than "Select case" I'm open to suggestions.

 

[ScottR]

That's exactly why I suggested using an If statement instead

If InStr(1,SkillName,"Excel")>0 then
'Do stuff
elseif InStr(1,SkillName,"Word")>0 then
'Do different stuff
elseif InStr(1,SkillName,"Access")>0 then
'Different again
elseif InStr(1,SkillName,"Foo")>0 and InStr(1,SkillName,"Baa")=0 then
'For skillnames containing the string "Foo" but not "Baa"
endif

etc

 

[bukimi]

Thank you very much for that. I forgot about existence of "elseif", because I got used to "Select case" so much. Shame on me.
Thank you again. I can't tell you if that works now, because I need some code writing to check it properly, but I'm sure it'll be fine now.

 

[ScottR]

You're welcome. Also don't forget to consider that you can improve readability (the thing people say Select Case is better for) by taking care of the evaluation before the if statement bit. Consider the following snippet

Dim sN1 As Integer, sN2 As Integer, sL1 As Integer, SL2 As Integer
Dim rText As String
rText = "The line of text with the attainment string you are searching through"


sN1 = InStr(1, rText, "Excel")
sN2 = InStr(1, rText, "Access")
sL1 = InStr(1, rText, "novice")
SL2 = InStr(1, rText, "advanced")


If sN1 > 0 And (sL1 > 0 Or SL2 > 0) Then
    'words Excel and either novice or advanced (or both)
ElseIf sN1 > 0 And sN2 > 0 And sL1 = 0 Then
    'Any Excel or Access any skill above novice
End If

You're doing the string position testing assigned to nice short variables then feeding those into the if.
You can also nest if statements inside each other like a logical flow.

If bread = "Fresh" Then
    If cheese = "Stinky" And butter = "Yellow" Then
        result = "Full Meal!"
    ElseIf cheese = "Stinky" Or butter = "Yellow" Then 
        result = "snack"
    Else
        result="dry snack"
    End If
Else
    If cheese = "Stinky" And butter = "Yellow" Then
        result = "small snack"
    Else
        result = "Hunger"
    End If
End If

 

[Rick Rothstein]

You could also put your list into and array and then iterate the items one at a time, something like this...

Dim X As Long, Phrases As Variant
....
....
Phrases = Array([B][COLOR="#FF0000"]"Good Excel", "Very good Excel", "Excellent Excel[/COLOR][/B]")
For X = LBound(Phrases) To UBound(Phrases)
  If InStr(skillname, Phrases) Then
[B][COLOR="#008000"]    '
    '  Do stuff here
    '
[/COLOR][/B]    [B][COLOR="#0000FF"]Exit For[/COLOR][/B]
  End If
Next

You can extend the red list as far as you like. Also, if there is nothing left to do in your program once an item has been identified and the "Do stuff here" has finished, you can use Exit Sub or Exit Function (depending on if your code is in a Sub or a Function) to end the code rather than Exit For which takes the code to the code line immediately following the Next statement.

 

[bukimi]

Thank you everyone for your suggestions. I try to learn something new everyday, so every post was very nice.

I tried Elseif with InStr, as ScottR suggested first, because it's a solution which is easiest to comprehend. I already tested it and it works fine. It's also very clear: I can see which section of code refers to which word, which is more than enough Thank you!
Fortunately my code doesn't need to evaluate cases where more than one word appears or one word appears and another one doesn't, so it's easier to write.

@Rick Rothstein:
I want to understand your suggestion better. I know how to use arrays, but I don't know how it does different things for different words:

Phrases = Array([B][COLOR=#FF0000]"Good Excel", "Very good Excel", "Excellent Excel[/COLOR][/B]")
 For X = LBound(Phrases) To UBound(Phrases)
  If InStr(skillname, Phrases) Then
[B][COLOR=#008000]    '
    '  Do stuff here
    '
[/COLOR][/B]    [B][COLOR=#0000FF]Exit For[/COLOR][/B]
  End If
Next

Could you elaborate on this example with two different things VBA will do for two different words from array?
I can see "do stuff here" section when word from Array is found in a Variable, but I can't see "do other stuff". Woudn't this code do the same "stuff" if ANY of words in array is found in a variable?

PS. Today I also learned about LBound and UBound. That will help me with some of my arrays!

 

[Sektor]

You can also create array in one line:

Sub K()
    Dim varItem
    For Each varItem In [{"Good Excel", "Very good Excel", "Excellent Excel"}]
        MsgBox varItem
    Next
End Sub

 

[Tetra201]

See if this works for you:

Private Function KeyWord(s As String) As String
    Dim wrd
    For Each wrd In Array("Access", "Excel", "Word")
        If s Like "*" & wrd & "*" Then KeyWord = wrd: Exit Function
    Next wrd
End Function
    ...
    Select Case KeyWord(SkillName)
        Case "Access": ...
        Case "Excel": ... 
        Case "Word": ...
        Case Else: ...
    End Select
...