test "included in array"

[Limipl]

Hi,
Is it possible in VBA to test if a variable is included in an array ?
Thanks for answers.
Limipl
Don't forget, like everybody, you are nobody .

 

[Peter Hewett]

Hi Limipl

Do you mean the variables value or do you mean an array of objects where you want to know
if a specific object is in the array?

In either case there are no fancy array search functions as you find in .Net in VBA.
You'll need to optimise (if necessary) the array and search it yourself.

HTH + Cheers - Peter

 

[Tushar Mehta]

As Peter mentioned there are no VBA constructs. However, you may be
able to leverage the application's capabilities. For example, if your
code is based on an Excel platform, the following works with a 1D
array:
Sub testMatch()
Dim Arr()
ReDim Arr(1 To 3)
Arr(1) = 10: Arr(2) = 20: Arr(3) = 30
MsgBox Application.WorksheetFunction.Match(20, Arr, 0)
End Sub

You can extend the concept to searching any one dimension of a 2D array
(!) with
Sub testMatch()
Dim Arr()
ReDim Arr(1 To 3)
Arr(1) = 10: Arr(2) = 20: Arr(3) = 30
MsgBox Application.WorksheetFunction.Match(20, Arr, 0)
ReDim Arr(1 To 2, 1 To 3)
Arr(1, 1) = 1: Arr(1, 2) = 2: Arr(1, 3) = 3
Arr(2, 1) = 10: Arr(2, 2) = 20: Arr(2, 3) = 30
With Application.WorksheetFunction
MsgBox .Match(20, .Index(Arr, 2), 0)
MsgBox .Match(3, .Index(Arr, 1), 0)
End With
End Sub

And, you can even 'slice' the matrix the other way around (!) with:
MsgBox .Match(2, .Index(Arr, 0, 2), 0)

Ah, the power of leveraging the application's object model.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Martin Seelhofer]

Hey there

Additionally, the recent versions of Office (2000+) come with Split and Join
from which the latter can be used to simulate what the original poster asked
for:

Function IsInArray(arr, v) As Boolean
IsInArray = InStr(Join(arr,";"),v) > 0
End Function

Explanation:
Join concatenates all values of an array separating them by default with
a space. However, the separator can be supplied to the Join-function
as Parameter 2 which I've done in the example. InStr returns the position
where the given string (v) is found inside the concatenated string. It
returns 0 if v does not occur.

Note that there are some drawbacks of this solution:
1. it returns true even if only a part of an array value matches the given
one
2. it does not consider data types
3. ...

.... but it's still usable ;-)


Cheers,
Martin