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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
Glad to help out :)
... jacko's solution is exactly what I posted in the first comment
... jacko's solution is exactly what I posted in the first comment
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(intLo
'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
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(intLo
'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
fyed
Does it work if you pass an actual array?
For example like this.
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
....
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.
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.
ASKER