Making VBA udf compatible with Array Formula

Question

I found the following great udf for fuzzy match a string but it doesnt work with Array formula, I am very basic in VBA and cant make it work (from reading different post it may have something to do with adding Lbound somewhere but cant figure it out).

Could I get some help ?

what I would like to do is something like

{=searchChars("yellow",if(list_of_product="productA",list_of_colors))}

.

    'Name function and arguments
Function SearchChars(lookup_value As String, tbl_array As Variant) As String
'Declare variables and types
Dim i As Integer, str As String, Value As String
Dim a As Integer, b As Integer, cell As Variant
'Iterste through each cell
For Each cell In tbl_array
  'Save cell value to variable
  str = cell
  'Iterate through characters
  For i = 1 To Len(lookup_value)
    'Same character?
    If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then
      'Add 1 to number in array
      a = a + 1
      'Remove evaluated character from cell and contine with remaning characters
      cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999)
    End If
  'Next character
  Next i

a = a - Len(cell)
'Save value if there are more matching characters than before
If a > b Then
  b = a
  Value = str
End If

a = 0
Next cell
'Return value with the most matching characters
SearchChars = Value
End Function

Option Explicit

Tim Williams · Accepted Answer · 2020-02-06 06:48:04Z

Working OK for me - does not need to be entered as an array formula:

A few "improvements":

Function SearchChars(lookup_value As String, tbl_array As Variant) As String
    Dim i As Long, str As String, Value As String, c As String
    Dim a As Long, b As Long, cell As Variant

    For Each cell In tbl_array
        If Len(cell) > 0 Then 'skip empty values
            str = cell
            a = 0
            For i = 1 To Len(lookup_value)
                c = Mid(lookup_value, i, 1) '<< do this once
                If InStr(cell, c) > 0 Then
                    a = a + 1
                    cell = Replace(cell, c, "", Count:=1) '<< simpler
                    If Len(cell) = 0 Then Exit For        '<< nothing left...
                End If
            Next i

            a = a - Len(cell)
            'Debug.Print str, a
            If a > b Then
                b = a
                Value = str
            End If
        End If
    Next cell

    SearchChars = Value
End Function

Collectives™ on Stack Overflow

Making VBA udf compatible with Array Formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related