excelvbaiteration

Create Iterator Function in Excel VBA


I'm working on implementing a fuzzy-matching function in VBA for a project I'm working on. Ideally, I'd like it to be iterable, since that way I don't have to deal with the headache of holding its output in a variable that I have to constantly ReDim.

However, when I modify the function's declaration in accordance with the docs, Excel's IDE marks the Iterator Function line red, which I believe means there's some sort of error (although it won't tell me what the error is):

Iterator Function FindFuzzy(sInp as String) as System.Collections.Generic.IEnumerable(Of String)
    Dim toreturn(40) As String
    Dim i As Long, j As Long, matches As Long, cell As Range, legngthError As Long, charsFound As Long, C ...
    Dim index as Integer

When I try running the macro anyway, it errors out the moment I get to the For Each loop, throwing the compile error "Sub or Function not defined".

What am I doing wrong here?

For reference, here is the function declaration without the iteration syntax:

Function FindFuzy(sInp as String) As String()
    Dim toreturn(40) As String

Solution

  • Here (en) is the step-by-step manual to create the iterator property in a class:

    1. Create a new class (Class1 here):
    Option Explicit
    
    Private internal As New Collection
    
    Private Sub Class_Initialize()
      With internal
        .Add "First Item"
        .Add "Second Item"
        .Add "Third Item"
      End With
    End Sub
    
    Public Property Get NewEnum() As IUnknown
      Set NewEnum = internal.[_NewEnum]
    End Property
    
    1. Remove and export the newly created class.
    2. Open the "Class1.cls" in a text editor and add the "Attribute NewEnum.VB_UserMemId = -4" line:
    VERSION 1.0 CLASS
    BEGIN
      MultiUse = -1  'True
    END
    Attribute VB_Name = "Class1"
    Attribute VB_GlobalNameSpace = False
    Attribute VB_Creatable = False
    Attribute VB_PredeclaredId = False
    Attribute VB_Exposed = False
    Option Explicit
    
    Private internal As New Collection
    
    Private Sub Class_Initialize()
      With internal
        .Add "First Item"
        .Add "Second Item"
        .Add "Third Item"
      End With
    End Sub
    
    Public Property Get NewEnum() As IUnknown
      Attribute NewEnum.VB_Description = "Gets an enumerator that iterates through the List."
      Attribute NewEnum.VB_UserMemId = -4
      Set NewEnum = internal.[_NewEnum]
    End Property
    
    1. Save and close the file.
    2. Import this file into the VBA project.
    3. Test the class with the code:
    Sub safasef()
      Dim Element
      Dim MyCustomCollection As New Class1
      For Each Element In MyCustomCollection
        MsgBox Element
      Next
    End Sub
    

    It's allowed to create a single iterator per class.


    Another option for you is to extend the standard functionality with custom functions within Excel Add-in.

    One more option is xll Add-in which can be written on C# or VB.NET using the ExcelDna extension. It needs to add the "[DispId(-4)]" attribute to the GetEnumerator method (credits to @GSerg who's found this post).