VBA Syntax: IF variable is NOT in list

[sonny123]

im a bit green with VBA

This what im trying to do

If MyVar NOT IN ( String1, String2, String3, etc) Then Test = True

but no matter what i try I just get Compile error..expected this or expected that

Can someone clue me as to how this should be coded

 

[Taruz]

Hi..

in operator, would not be this way I think vba..

Use select case..

select case MyVar
    case Not String1, String2, String3, etc
    Test = True
    case else
    [COLOR="YellowGreen"]'else code[/COLOR]
end select

 

[PleasantB]

I realize this thread is many years old but I am replying for anyone that comes across this in the future.
The solution posted by Taruz will not actually work as "Not" cannot be used in this way.

The workaround is as follows:

Select Case MyVar
    Case String1, String2, String3, etc
        Test = False
    Case Else
        Test = True
End Select

 

[isladogs]

Welcome to the forum PleasantB

thanks for spotting that error that nobody else noticed in the past 7 years!
For info, this would also SOMETIMES work....BUT NOT RELIABLY - see posts 6 & 7

Select Case MyVar
    Case Is <>String1, String2, String3
        Test = True
    Case else
        Test = False
End select

 

[PleasantB]

This is so much better! I really didn't like having an empty case when only taking action if desired the condition is met.

 

[pbaldy]

Select Case MyVar
    Case Is <>String1, String2, String3
        Test = True
    Case else
        Test = False
End select

This didn't look like it would work to me so in a brief test:

  Dim strOne As String, strTwo As String, strTest As String

  strOne = "one"
  strTwo = "two"
  strTest = "two"

  Select Case strTest
    Case Is <> strOne, strTwo
      MsgBox "True"
    Case Else
      MsgBox "false"
  End Select

returns True, should be False.

Case Is <> strOne, Is <> strTwo

also returns True. In essence, the comma is an "OR", and from techonthenet: "Once a condition is found to be true, the Case statement will execute the corresponding code and not evaluate the conditions any further." Thus as soon as the test value wasn't equal to the first variable, Access returns true and moves on.

 

[isladogs]

hi Paul
That's interesting.
Before I posted, I had tested with this:

Select Case Me.cboArea
    
    Case "GI", "BD"
        MsgBox "This postcode area is not used for geographical addresses", vbCritical, "Not available"
    
   [B] Case Else[/B]
        ShowControls True, "D"
        Me.cboDistrict.Requery
        Me.cboDistrict = ""
        Me.lblDistrict.Caption = ""
        
        Me.cboDistrict.SetFocus
        Me.cboDistrict.Dropdown
    
    End Select

I changed the Case Else to Case Is <>"GI", "BD"
It still worked exactly as before

So I ran yours and sure enough it 'wrongly' gave True
Now what I think is happening is indeed explained by what you wrote.
In your example the Case Is statement is looking for <> strOne OR equal to strTwo which is indeed true

I modified it to:

Select Case strTest
    Case strOne, strTwo
      MsgBox "false"
    Case Is <> strOne, strTwo
      MsgBox "True"
    
  End Select

It gave false for reasons that should be obvious

So I'm going to withdraw my alternative suggestion as its too far too easy to give incorrect output

In some ways this is a bit like doing Dim X, Y, Z as String which of course makes X & Y both variants