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:

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

Remove and export the newly created class.
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

Save and close the file.
Import this file into the VBA project.
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).