VBA - Multiple OR In a statement.

This may seem like a very basic programming question in VBA but for some reason I can not get this statement to work.  I'm fairly new to VBA.

Here is the statement.

If producttype <> "ABC" Or producttype <> "DEF" Or producttype <> "GHI" Or producttype <> "JKL" Then
     MsgBox ExportRequest, vbInformation, "No Excel Form for Product Type"
    Exit Sub

If I limit it to one <> then it works for that one product type.  

How should this be written in VBA?
LVL 2
CompTech810Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
I think you need ANDS:

If producttype <> "ABC" AND producttype <> "DEF" AND producttype <> "GHI" AND producttype <> "JKL" Then

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
Alternatively Using OR:

If  NOT (producttype = "ABC" Or producttype = "DEF" OR producttype = "GHI" Or producttype = "JKL") Then
Paul JacksonSoftware EngineerCommented:
You need to use ANDs instead

If producttype <> "ABC" And producttype <> "DEF" And producttype <> "GHI" And producttype <> "JKL" Then
     MsgBox ExportRequest, vbInformation, "No Excel Form for Product Type"
    Exit Sub

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

CompTech810Author Commented:
I feel like such an airhead!!  That worked.  Between all the different programming languages I've used I have again confused myself.  Thank You!
NorieAnalyst Assistant Commented:
You could use a Select Case statement.

This will only show the message box if producttype is not equal to ABC, DEF etc

If producttype is ABC or DEF or GHI then it will do nothing but that can be changed if required.
Select Case producttype

      Case  "ABC",  "DEF", "GHI","JKL"
              ' do nothing
      Case Else
                MsgBox ExportRequest, vbInformation, "No Excel Form for Product Type"
                Exit Sub
End Select

Open in new window

mbizupCommented:
Glad to help out :)

... jacko's solution is exactly what I posted in the first comment
Dale FyeOwner, Developing Solutions LLCCommented:
I use a function I wrote a several years ago for these types of checks.  It allows me to test to see whether a value is listed among an array of other values passed to the function.  The function looks like:

Public Function IsIn(TestValue As Variant, ParamArray ArrayOfValues() As Variant) As Boolean

    'Tests to see whether a value is in a list of other values.
    'Similar to both the SQL IN ( ) clause and SELECT Case statements
    'Accepts a value of Variant data type, followed by an array of values.
    'Checks to see if the first argument is in the array
    'Ignors NULL values passed in the array

    Dim intLoop As Integer
   
    IsIn = False
   
    If IsNull(TestValue) Then Exit Function
   
    For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
        isnull(ArrayOfValues(intLoop)) Then
            'ignore that value
        ElseIf TestValue = ArrayOfValues(intLoop) Then
            IsIn = True
            Exit For
        End If
    Next
   
End Function


To use this function in your case, you would simply use the syntax:

If IsIn(ProductType, "ABC", "DEF", "GHI", "JKL") = False Then
    'do something here
Else
    'do somthing else
endif
NorieAnalyst Assistant Commented:
fyed

Does it work if you pass an actual array?

For example like this.
    arr = Array(1, 2, 3, 4, 5)

    If IsIn(1, arr)  Then
   ....

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
No, I so rarely use arrays that I didn't think of that initially.

However, over time, I have made several slightly modified versions to handle

1.  arrays
2.  comma separated strings (like you might get from an inputbox)

but don't have either of those versions handy.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.