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

• RubberDuck: Very interesting indeed.
• Malachi: The FizzBuzz part should be posted as a separate question, otherwise this is a well constructed question!
• Malachi: do you have a subscription process set up for you blog, @Mehow?
• Malachi: entries on the right side gave me an RSS feed, I think that will work for me! I want to learn how to use VBA for EXCEL and other Microsoft applications, I already know a bit of VBScript and VB.NET
• RubberDuck: I added a couple of features to this class.

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

Tool tip

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

• user28366: Thanks a lot for your feedback. I was aware of the "cosmetic" changes but I am glad you have pointed it out. Performance is what I am considering crucial but it shouldn't be a huge concern for a dev who knows what he's doing :) I have actually originally tested it with class properties, ie. tried to implement a default collection that allows to iterate class properties but had not much luck :) Although you are correct ByVal makes more sense + the intelli-sense
• RubberDuck: Actually thank you. Your blog and involvement on SO have taught me a lot. It was the least I could do, even if it felt like giving layup tips to Kobe Bryant.

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