Basic Imitation of C# Enumerable in VBA - or any other static class

Question

Since implementing static classes in VBA actually seems possible I came up with an idea to try to imitate C#' Enumerable class (well, to some extent...). This is just an idea but it should work with any sort of a class serving the purpose of a static one, ie. Math, Factory, etc.

quick note: the original Enumerable uses Linq which is not an option in VBA ( forget this ) therefore the functionality as of now will be very limited.

The point here is to be able to kind of replace the syntax of

Dim i as Long
for i = 1 to 100
    'do something...
next

with

Dim number as Variant
for each number in Enumerable.Range(1,100)
    'do something...
next

So you can iterate almost any Range of numbers "on-fly".

---

So far I have been able to come up with a very simple working version (but! -> try to see the potential) I would like to demonstrate here and ask for your opinion of possibly improving it and things to consider within the current implementation.

You need Notepad or another text editor and copy-paste the following

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

Public Function Range(param1, param2) as Collection
    Set c = new Collection
    Dim i as Long
    for i = param1 to param2
        c.add i
    next
    set Range = c
End Function

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

Save this file to your desktop as Enumerable.cls (make sure it's not an Enumerable.cls.txt)

Start Excel and go right into the VBE (alt+F11)

Right click the VBA Project and select Import File and select the Enumerable.cls.

Right click the VBA Project again and Insert a Module (Module1)

Now, copy-paste the below code into the Module1

Sub Main()

    Dim number As Variant

    For Each number In Enumerable.Range(1, 100)
        Debug.Print number
    Next

End Sub

and run w/ F5. Numbers from 1 to 100 should be printed to the Immediate Window at this point.

Notice - You did not have to instantiate the Enumerable class and you are able to iterate via any range of numbers - as long as they don't cause an overflow error on the Long data type

Notice2 - The current very basic version has not been tested with negative numbers - would it be worth fully supporting it and possibly combining it with a possibility of reverse iterating the collection?

Please, share your opinions :)

For fun: replace the code in Module1 with

Sub Main()

    Dim number As Variant

    For Each number In Enumerable.Range(1, 100)
        Debug.Print FizzBuzz(number)
    Next

End Sub

Public Function FizzBuzz(ByVal value As Long) As String
    If (value Mod 15 = 0) Then
        FizzBuzz = "FizzBuzz"
        Exit Function
    End If
    If (value Mod 3 = 0) Then
        FizzBuzz = "Fizz"
        Exit Function
    End If
    If (value Mod 5 = 0) Then
        FizzBuzz = "Buzz"
        Exit Function
    End If
    FizzBuzz = value
End Function

Answer 1

For each requires that number be declared as a Variant, but For..To requires a number. I like that you explicitly declared Dim number As Variant. It makes it clear that that you intended on it and didn't simply forget to specify the type. However, you've not specified what type param1 and param2 should be. By declaring them as longs you can stop several lines of code from executing if some unwary dev passes a string or object into .Range(). Runtime error 13 "Datatype mismatch" gets thrown either way, but it happens sooner and there's a tool tip showing what datatype is to be expected.

The params also being passed ByRef. While there are no side effects on the parameters, it would be nice and in good faith to let anyone using this class know that there are not any side effects by passing them ByVal.

Public Function Range(ByVal param1 As Long, ByVal param2 As Long) As Collection
    Set c = New Collection
    Dim i As Long
    For i = param1 To param2
        c.add i
    Next
    Set Range = c
End Function

Another thing to note is that using this method will be exactly twice as slow as just using a For loop. This is because the code loops once to build the collection and then again to enumerate over the collection. When performance is absolutely critical you might want to skip the syntactic sugar.

I tested against negative longs and your code works just fine.

There are a number of missing features that I'd like to see, but I suspect you'll have some amount of difficulty getting them to "feel right". This has to do with VBA's lack of proper inheritance and method overloading. I've attempted to do something similar recently without much luck. You've already gotten farther than I did though, so I'll be keeping an eye on vba4All to see what you come up with. =)

Answer 2

This is a possible implementation of allowing reverse iteration, expecting that your desired usage is merely Enumerable.Range(upper, lower).

Public Function Range(param1, param2) as Collection
    Set c = New Collection
    Dim d as Integer
    d = IIf(param1 < param2, 1, -1)
    Dim i As Long
    For i = param1 To param2 Step d
        c.add i
    Next i
    Set Range = c
End Function

Testing Forward,

for each x in Enumerable.Range(1, 10): ? x;: next x
> 1  2  3  4  5  6  7  8  9  10  

And Reverse,

for each x in Enumerable.Range(10, 1): ? x;: next x
> 10  9  8  7  6  5  4  3  2  1