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
Here (en) is the step-by-step manual to create the iterator property in a class:
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
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
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).