Create Iterator Function in Excel VBA

Question

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

@ScottCraner In that case, is there any way to replicate the functionality? — In Hoc Signo
– In Hoc Signo, Commented Jun 25 at 1:15
Please don’t post code as images. It’s much better to paste your code directly into the question using proper formatting. That way, others can easily copy, test, and suggest improvements. Code images aren’t searchable, can’t be read by screen readers, and often don’t display properly on all devices. — Siddharth Rout
– Siddharth Rout, Commented Jun 25 at 5:53
Regarding your question: VBA doesn’t have a built-in “iterator” object like some other languages, but you can simulate iteration by looping through a collection, range, or array and performing actions on each item. You can even create a more flexible iterator function that calls another function for each value, or write an iterator specifically for arrays. That said, could you clarify what exactly you’re trying to achieve? If you can explain your goal more clearly, we’ll be better able to help. — Siddharth Rout
– Siddharth Rout, Commented Jun 25 at 5:53

rotabor · Accepted Answer · 2025-06-27 16:57:11Z

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

At which point see stackoverflow.com/q/4796613/11683 for how to implement a VBA-ForEachable object in .NET.

Collectives™ on Stack Overflow

Create Iterator Function in Excel VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related