Check if a variable belongs to a set of values Solved

Question

Hello,I just want to check with a condition if my variable belongs to a set of values.Of course, I can do:If Var = 1 or Var = 5 or Var = 12 or..... thenbut it's a bit tedious when there are a lot of tests.There should be something likeif Var = (or(1, 5, 12...)) thenorif Var = {1; 5; 12...} thenI'm sure there is something like that and the answer will be obvious, but I can't find it. I specify that this is VBA for Access.Configuration: Windows XP / Firefox 8.0.1

panda · Answer

I was trying to use the array function, but without success:

firstarray = Array(8, 9, 10, 11, 12, 13, 14, 15)
myarray = Filter(firstarray, Var)
If UBound(myarray) > 0 Then

it gets stuck at "myarray = Filter(firstarray, nomod)". I tried "myarray = Filter(firstarray, Var, true)" but in all cases it tells me that I do not have the right number of arguments.

any ideas?

panda · Answer

I have another lead with this code found on a site:

Sub essai()
montableau = Array("A", "B", "C", "D")
x = "C"
p = Application.Match(x, montableau, 0)
If IsError(p) Then
MsgBox x & " Unknown"
Else
MsgBox p
End If
End Sub

but my VBA (for Access) does not seem to recognize the Match method.

Yoda · Answer

Hello,

You need to loop through the entire array to check if an element exists.

Sub test() myArray = Array("A", "B", "C", "D") x = "C" found=False For i = LBound(myArray) To UBound(myArray) if x=myArray(i) then found=True next if found then MsgBox x & " Known" else MsgBox x & " Unknown" endif End Sub

The most practical thing would be to create a custom function.

See you later.

Patrice33740 · Answer

Hello,  To avoid the loop, you can use a dictionary: Option Explicit Sub Test1() Dim d As Object Dim x As String 'Variable to find x = "C" 'Creating a dictionary Set d = CreateObject("Scripting.Dictionary") 'Adding keys, values d.Add "A", "" d.Add "B", "" d.Add "C", "" d.Add "D", "" 'Test If d.Exists(x) Then MsgBox x & " exists" Else MsgBox x & " unknown" End If End Sub The same with a reference to Microsoft Scripting Runtime: Sub Test2() ' Add a reference to Microsoft Scripting Runtime Dim d As Scripting.Dictionary Dim x As String 'Variable to find x = "C" 'Creating a dictionary Set d = New Dictionary 'Adding keys, values d.Add "A", "" d.Add "B", "" d.Add "C", "" d.Add "D", "" 'Test If d.Exists(x) Then MsgBox x & " exists" Else MsgBox x & " unknown" End If End Sub    -- Best regards Patrice

panda · Answer

Thank you for your response.
That should work indeed.
What's unfortunate is that I wanted the shortest possible instruction to do a simple

if Var belongs to {x,y,z} then

and make the code as readable as possible.
But this will do in the absence of better.
I'm still surprised that there isn't a more concise way.

panda · Answer

not bad the idea of the dictionary, I didn't know that. it's a bit the idea I had, testing the existence of my variable in a set, here it's in a dictionary.

telliak · Answer

Hi, Have you tried something like Select Case Var Case 1, 5, 12, ... Instructions Case else End select? it's hard to make it simpler than that.

Yoda · Answer

As I was saying, we can program a custom function, making it very simple and readable, for example:

The function

Function Exists(table, elem) As Boolean Exists = False For i = LBound(table) To UBound(table) If table(i) = elem Then Exists = True Next End Function

Calling the function

Sub trial() A1 = Array(8, 9, 10, 11, 12, 13, 14, 15) var=10 If Exists(A1, var) then MsgBox var & " exists" Else MsgBox var & " unknown" End If var="X" S1 = Array("A", "B", "C", "D", "E") If Exists(S1, var) then MsgBox var & " exists" Else MsgBox var & " unknown" End If End Sub

panda · Answer

Well, it would look like this:

Function testchoice(Tabl As String, quoi As Integer) As Boolean

montab = Array(Tabl)
found = False
For i = LBound(montab) To UBound(montab)
If quoi = montab(i) Then found = True
Next

End Function

And to test:

result = testchoice("(1,3,5,7,9,11,13,15)", Var)
If result = True Then
.....

But it doesn't seem to work, my if condition is always false.

Patrice33740 · Answer

Re,
“what is unfortunate is that I wanted the shortest possible instruction to make a simple
if Var belongs to {x,y,z} then
and make the code as readable as possible”

Here it is:

Sub test() Dim var As Integer var = 12 If Belongs(var, "8, 9, 10, 12, 14, 15") Then MsgBox var & " exists" Else MsgBox var & " unknown" End If End Sub Private Function Belongs(var As Variant, list As String) As Boolean Select Case CStr(var) Case list Belongs = True Case Else Belongs = False End Select End Function

--
Best regards
Patrice

Check if a variable belongs to a set of values

10 answers

Reinstallation of w11 impossible after pc crash...

Nonexistent sent items folder

Transfer mp3 from pc to mobile

Site unreachable

Increase the capacity of free's zimbra webmail to 10gb

Unrefreshed usb drive on android interface

Tencent impossible to remove

Is there enough storage space on my vps?

Car gps tracker

Erreur de captcha