Intersecting/Unioning Collections

  • Thread starter Thread starter Robbie
  • Start date Start date
R

Robbie

Is there a one-liner (I'll consider a two-liner for half
price) in VBA that takes two or more collections and forms
their intersection or union?

I know about the Intersect and Union methods, but those
only take ranges as arguments; I'm interested in forming
intersections and unions of collections in general. E.g.,
if X, Y, and Z have been declared as New Collection, I've
added objects to both X and Y, and now I want to quickly
set Z equal to the collection that consists of those
objects that are members of both X and Y.

Thanks,

Robbie
 
There's nothing built in, but I could send you a sorted list class that
supports set operations. It will handle the basic data types in VBA
(e.g. integers, longs, singles, doubles, strings and variants) but not
more complicated or user-defined object. Let me know if you want to try it.

Dave Ring
 
Don't know if this will help. A "Collection" can't help much with 1-Liners.
However a Dictionary often can.
Here is a quick idea. This has a vba reference set to Microsoft Scripting.

Sub Demo()
'//Dana DeLouis

Dim v1 As New Dictionary
Dim v2 As New Dictionary
Dim rng As Range

Set rng = [B1:C10]
v1.Add rng.Address, rng

Set rng = [E1:F10]
v1.Add rng.Address, rng

Set rng = [A5:J6]
v2.Add rng.Address, rng

Set rng = [A8:J9]
v2.Add rng.Address, rng

'// Here is your 1-Liner :>)

Intersect(Range(Join(v1.Keys, ",")), Range(Join(v2.Keys, ","))).Select
End Sub

HTH
 
Back
Top