5

In my code i want to use if condition. In which i want to use "OR" around 18 times. Like for e.g.

If a="something" or a="something" or a="something" or.........(up to 18 times)... then
  'do nothing
else
  'do action
end if

[Note : value of a is changing in For loop every time] so i just want to ask does there any limitation in IF for using OR in limited times. OR is there any other better way to do the same.

Thanks

Deanna
  • 23,876
  • 7
  • 71
  • 156
Pratik Gujarathi
  • 748
  • 10
  • 22
  • 40
  • I was having the same problem. After two "ands" I was getting the property or method error. After I used Case for my 10 <> conditions in my looping for loop and it worked. – user3553260 Jul 18 '18 at 14:09

2 Answers2

14

As far as I know, there is no limitation when using OR this way.

Yet, you may consider alternative ways of coding this.

Negating a condition using Not

First, if you do nothing in the first case, then consider using the Not statement:

If Not True Then
'do somethin
'no else
End If

Consider using Select Case

Second, if you are checking the very same variable, you could either consider using a Select Case but it doesn't seem appropriate in your case if you have only one case.

Try to use a search

Eventually, if you are checking strings, you could probably better use a search within an array (with Application.Match if you are within Excel or .Contains) or within a String using Instr.

Using a collection or a dictionary

[EDIT] Another very good way to handle this would be to use the Dictionary Structure of VBA and check if a exists (see MSDN for some information).

Community
  • 1
  • 1
JMax
  • 26,109
  • 12
  • 69
  • 88
  • 1
    Looks like a canidate for Select Case to me...`a` is the variable getting tested against over and over again. – tcarvin Feb 06 '12 at 13:21
  • @tcarvin: That's why I pointed out it existed. Yet, `Select Case` is very useful when you have many `Else If` (i.e. several cases) whereas you have a single outcome here so I would consider using other solutions (array, dictionary...). – JMax Feb 06 '12 at 13:28
  • 2
    And for String comparisons a Like operator or even regex matching might be a cleaner option depending on what is being tested for. – Bob77 Feb 06 '12 at 14:26
  • In general: if you have to ask for the limitation of a system, then probably there is a better way to do it. – AutomatedChaos Feb 06 '12 at 15:55
6

This answer is just an elaboration on my comments to JMay, full credit to him. I think the original poster meant to the "Something" in his question differ, with a being the loop variable.

For each a in MyList

   Select Case a
   Case "something", "something2", "something3", "something4", "something5", _
        "something6", "something7", "something8", "something9", "something10", _
        "something11", "something12", "something13", "something14", "something15", _
        "something16", "something17", "something18"

       'DO NOTHING

   Case Else

       'do-something code goes here
       ' and here
       ' and here
       ' and here
   End Select

Next a
tcarvin
  • 10,715
  • 3
  • 31
  • 52
  • nicely done. I wasn't familiar enough to the `Case` syntax and I didn't remember you could use the comma as an `OR` operator :) – JMax Feb 06 '12 at 14:40
  • @JMax Well, then you'll appreciate the [Select Case True](http://911-need-code-help.blogspot.com/2009/07/vbscript-select-case-statement-complete.html) construct even more. It can also be used for [short circuit evalution](http://en.wikipedia.org/wiki/Short-circuit_evaluation). – AutomatedChaos Feb 08 '12 at 14:29