Excel-VBA - Is there anything like Javas Set container in VBA?

Question

Is there anything like Java's Set container in VBA? I can't find anything and Google doesn't seem to helpful since set is a reserved work in VBA.

Any ideas would be great. Right now my only options are Dictionary or Array.

Thanks.

Answer 1

VBA has a 'Collection' object built in, and many people consider the 'Dictionary' object from the MS Scripting Runtime to be sufficiently standard that it's essentially part of VBA.

However, neither will enforce uniqueness for general objects. Whether or not you can make them enforce uniqueness for your application depends on the details.

For example, if you want a set of strings, it's easy. Just use a 'Dictionary', and use its keys as your "set". 'Dictionary' has an 'Exists' method, so it would be pretty easy to write your own limited 'Set' class where all of the real work is done by a contained 'Dictionary'. (If you're using Excel, this will work with any simple Variant value - strings, numbers, booleans, errors.)

If you have object instances, things can get more complicated. The 'Dictionary' object can use objects as keys, but it uses object identity as its equality test. For example, I just typed this into the VBA Immediate window:

set d=new Dictionary
set c1=new Collection
set c2=c1
d(c1) = 42
fv d
<Dictionary: keys: #V(0..0){<Collection: 0 elems: >}, items: #V(0..0){42%}>
d(c2)=99
fv d
<Dictionary: keys: #V(0..0){<Collection: 0 elems: >}, items: #V(0..0){99%}>
set c3=new Collection
d(c3)=101
fv d
<Dictionary: keys: #V(0..1){<Collection: 0 elems: >,<Collection: 0 elems: >}, items: #V(0..1){99%,101%}>

('fv' is a VBA routine I use for debugging - it just prints out a string representation of stuff.) You can see that identical objects are treated as equal, but identically valued objects aren't.

If you need a set that has a custom equality test, you'll need to write non-trivial code. However, if you can at least write a mapping from object instance to a value that can be used as a Dictionary key, you can still avoid having to write your own "is this thing already a member of the set?" code.

Some relevane links:

Is there a way to write an equality test for a VBA class with private members without exposing knowledge of the existence of those private members?

Hash Table/Associative Array in VBA