# More imitation of Enumerable in VBA

I was inspired by Me How's question to see how far I could push an imitation of .Net's Enumerable Class.

The new functions can obviously handle Collections, but can also handle any collection-type object whose items have a default value. If a collectionObject's items don't have a default value, Runtime Error 438 "Object does not support property or method" is raised. So, things like Cells and Range work, but Worksheets doesn't.

Min and Max only differ by one operator, so there's some duplication there, but I don't know how to refactor it out. Intersect also seems inefficient to me, but I couldn't imagine a better algorithm. So, those are particular areas of interest to me. Suggestions for which features to add next would also be appreciated.

### Enumerable.cls

VERSION 1.0 CLASS
BEGIN
MultiUse = -1  'True
END
Attribute VB_Name = "Enumerable"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Private c As Collection ' used for Range

Public Function Range(ByVal startValue As Long, ByVal endValue As Long) As Collection
Attribute Range.VB_Description = "Returns a collection of longs."
Set c = New Collection
Dim i As Long
For i = startValue To endValue
Next
Set Range = c
End Function

Public Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
Set NewEnum = c.[_NewEnum]
End Property

' All of these functions work only on collectionObjects whose items have a default value.
' If the items do not have a default value,
'   Runtime Error 438 "Object doesn't support this property or method" is raised.

Public Function Contains(collectionObject As Variant, itemToSearchFor As Variant) As Boolean
Attribute Contains.VB_Description = "Checks if an item exists in a Collection. Matches on the default property."
Dim item As Variant

For Each item In collectionObject
If item = itemToSearchFor Then
Contains = True
Exit Function
End If
Next item

Contains = False
End Function

Public Function Min(collectionObject As Variant) As Variant
Attribute Min.VB_Description = "Returns the item with the minimum value in a Collection. Uses the default property."
Dim item As Variant
Dim result As Variant
Dim isFirstTry As Boolean: isFirstTry = True

For Each item In collectionObject
If isFirstTry Then
result = item
isFirstTry = False
Else
If item < result Then
result = item
End If
End If
Next item

Min = result
End Function

Public Function Max(collectionObject As Variant) As Variant
Attribute Max.VB_Description = "Returns the item with the minimum value in a Collection. Uses the default property."
Dim item As Variant
Dim result As Variant
Dim isFirstTry As Boolean: isFirstTry = True

For Each item In collectionObject
If isFirstTry Then
result = item
isFirstTry = False
Else
If item > result Then
result = item
End If
End If
Next item

Max = result
End Function

Public Function Intersect(collection1 As Variant, collection2 As Variant) As Collection
Attribute Intersect.VB_Description = "Returns a new collection containing the items that are common to both collection parameters. Returns Nothing if either parameter IsNothing."

If collection1 Is Nothing Or collection2 Is Nothing Then
Exit Function
End If

Set Intersect = New Collection
Dim item As Variant
Dim innerItem As Variant

For Each item In collection1
For Each innerItem In collection2
If item = innerItem And Not IsEmpty(item) Then
End If
Next innerItem
Next item
End Function


For anyone interested, the reason you can call these functions without creating a new instance is setting VB_PredeclaredID = True creates a default instance of the class.

I see a potential problem with your Intersect method. It only works as expected when there are no repeated items in either collection. I'm not sure what the desired behavior is, but if you have 3 copies in collection1 and 2 copies in collection2 then the returned collection has 6 copies. Now I know that in most cases Intersect only makes sense with sets, that is, one of each item, so this may not be relevant.

• Collections can have duplicate items, so this is indeed a bug. Thank you. Aug 11 '14 at 21:31

I don't like that you're relying on default properties, especially in the Contains method: in many cases your Contains method will return false positives for objects.

Simply because an object has value XYZ for its default property doesn't mean another object with value XYZ can be considered equal - in fact, with your code two objects of different types could be considered equal under some unfortunate circumstances.

Fortunately that's an easily avoidable bug.

A collection of objects will hold a bunch of references - to know if a given object exists in a collection you can iterate the collection and if you find that reference, you can safely return True and be confident that the specified object does exist in the collection.

But before you can check for a reference, you need to be sure you're dealing with an Object reference, because ObjPtr(1234) (or any other non-object value) will raise a "Type Mismatch" runtime error.

gives us the IsObject function to do that. Hence, first thing I'd do is try to know whether I'm dealing with an object or not.

This splits the function in two distinct parts: the Else block can deal with what I like referring to as value types - basically anything that's not an Object. Your code works well in these cases:

Public Function Contains(collectionObject As Variant, itemToSearchFor As Variant) As Boolean
Dim item As Variant
For Each item In collectionObject

If IsObject(item) Then

Else

If item = itemToSearchFor Then
Contains = True
Exit Function
End If

End If

Next


What you're trying to do here, is to perform an equality comparison. When item is an Object the only way to reliably determine if the itemToSearchFor is the same object as your item, is to compare their references:

If ObjPtr(item) = ObjPtr(itemToSearchFor) Then
Contains = True
Exit Function
End If


Bottom line: don't mix apples and oranges - a Variant can be anything, and since a Collection can be a bunch of totally unrelated things of various types, you have no choice but to evaluate IsObject for each item.

Here's how I had implemented Contains in my List<T> implementation:

Public Function Contains(value As Variant) As Boolean
'Determines whether an element is in the List.

Contains = (IndexOf(value) <> -1)

End Function


Well ...yeah, so here's how I had implemented IndexOf in that class:

Public Function IndexOf(value As Variant) As Long
'Searches for the specified object and returns the 1-based index of the first occurrence within the entire List.

Dim found As Boolean
Dim isRef As Boolean
isRef = IsReferenceType

Dim i As Long

If Count = 0 Then IndexOf = -1: Exit Function
For i = 1 To Count

If isRef Then

found = EquateReferenceTypes(value, item(i))

Else

found = EquateValueTypes(value, item(i))

End If

If found Then IndexOf = i: Exit Function

Next

IndexOf = -1

End Function


Where IsReferenceType calls IsObject on the first item - in that case I could safely only check the first item in the list, because that collection could only ever have items of a single type, so if the first item was an object, they're all objects.

For completeness, the interesting part:

Private Function EquateValueTypes(value As Variant, other As Variant) As Boolean

EquateValueTypes = (value = other)

End Function

Private Function EquateReferenceTypes(value As Variant, other As Variant) As Boolean

Dim equatable As IEquatable
If IsEquatable Then

Set equatable = value
EquateReferenceTypes = equatable.Equals(other)

Else

'object doesn't implement IEquatable - use reference equality:
EquateReferenceTypes = (ObjPtr(value) = ObjPtr(other))

End If

End Function


Custom classes could implement a simple IEquatable interface if reference equality wasn't desirable:

Public Function Equals(ByVal other As Variant) As Boolean
'return True if [other] can be considered equal to this instance.
End Function


And has a TypeOf keyword that can be used for checking types, so you could have a little function like this, to call before attempting a "type cast":

Private Function IsEquatable() As Boolean
If IsReferenceType Then
IsEquatable = TypeOf First Is IEquatable
End If
End Function


(again, that one only checks the first item in the List - it's just food for thought ;)

• You've got a really good point about object equality, but does ObjPtr offer any advantage over the Is Operator? Is seems much less verbose. Aug 11 '14 at 0:56
• Not really, Is looks like a shorthand for ObjPtr comparisons. It's just, like the = operator, it has too many meanings to my taste - notice its semantics are different in TypeOf x Is ISomeInterface. Aug 11 '14 at 0:58

I explained in another answer my concerns about how your implementation of Contains deals with object variables, and that had to do with what's considered equal when talking about objects.

For Min and Max, it's not about equality - it's about comparability. Comparing objects by their default property value actually makes sense.

When objects don't have a default property, you're basically stumped here - you're left with no reliable way of comparing two objects. The .net framework solved that with the IComparable interface, which is pretty easy to imitate in VBA:

Public Function CompareTo(ByVal other As Variant) As Integer
'return  1 if [other] is greater than this instance;
'return -1 if [other] is smaller than this instance;
'return  0 if [other] is equivalent to this instance.
End Function


Similar to the EquateReferenceTypes function in my other answer, this would fit the bill here:

Private Function CompareReferenceTypes(value As Variant, other As Variant) As Integer

Dim comparable As IComparable

If IsComparable Then

Set comparable = value
CompareReferenceTypes = comparable.CompareTo(other)

Else

'try to compare default property values, or let it blow up!

End If

End Function


And then you can write custom classes that implement IComparable to specify how instances should be compared to each others.

Looking at your Min implementation, I'd get rid of the isFirstTry Boolean, and replace it with a IsEmpty(result) check within the loop:

For Each item In collectionObject

If IsEmpty(result) Then result = item


I really, really like this Enumerable "static" class. So much I'm seriously considering moving a lot of the List logic into that Enumerable class. With IEquatable and IComparable interfaces in the mix, I find it adds quite a bit of useful tooling to the language.