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

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 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. =)

StackExchange Code Review Q#59215, answer score: 4