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.
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
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
Start Excel and go right into the VBE (alt+F11)
Right click the VBA Project and select Import File and select the
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
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