A VBA fuzzy finder
fzf-vba is an implementation of fzf a general-purpose fuzzy finder.
It contains both functions to be used by VBA for VBA as well as some functions
that can be used as functions directly in Excel.
The installation varies depending on which features you need, but basically it
all boils down to importing the files in fzf-vba in your VBA project.
Optionally if you have the Rubberduck VBE add-in you can also
import the unit tests in Tests.
There is an Excel-DNA/IntelliSense csv sheet in bin
for those who use that add-in.
Finally there is an example in the example folder (where the preview
is taken from), more information on the wiki.
FzfAlgorithm
Public Function FuzzyMatchV1( _
ByVal Text As String, _
ByVal Pattern As String, _
Optional ByVal WithPositions As Boolean = False, _
Optional ByVal WithNormalize As Boolean = True) As FzfResult
The base of the entire library, it does a fuzzy match on Text
using thePattern
and returns information about the match. If WithPositions
is true it
will also return the positions of the characters in the match. IfWithNormalize
is true it will run Text
through the Normalize
function
before matching.
The return value is basically a struct with the following items
Type FzfResult
Score As Long
StartIndex As Long
EndIndex As Long
Positions() As Long
End Type
If Score = 0
that means that there was no match, otherwise Score
will
generally be larger than 0. The algorithm is the V1 algorithm of the
fzf library which searches for the first match that has the shortest
substring. Due to various character position bonuses this is not guaranteed to
return the match that has the highest score. The V2 version which basically is a
Smith-Waterman algorithm might be implemented in the future.
The Positions
return variable is implemented as a Variant
and will beEmpty
if WithPositions
is false.
The function only accepts ASCII characters, or characters that are ASCII after a
pass through the Normalize
function. It is also only does a case insensitive
fuzzy match.
Future improvements:
Public Function Normalize(ByVal Value As String) As String
Returns a normalized version of the string where character accents etc have been
stripped from the letters.
Example: Normalize("Ḥɇƚɭø") => "Hello"
FzfUtilities
Public Function SortAndFilter( _
ByVal Texts As Variant, _
ByVal Pattern As String, _
Optional ByVal ScoreThreshold As Long = 0, _
Optional OnlyTopN As Long = -1) As String()
Takes a single string or an array of string and applies the fuzzy match on it.
Filter out all matches that have a score equal to or lower thanScoreThreshold
, finally sorts the result and returns an array of strings
sorted by score. If OnlyTopN
is specified (and not =-1
) it will only
include the N
results with the highest score. If there are fewer matches thanN
then all matches will be returned but the array will not be padded. The
result array is 0-indexed.
Public Function SortAndFilterStrict( _
ByRef Texts() As String, _
ByVal Pattern As String, _
Optional ByVal ScoreThreshold As Long = 0, _
Optional OnlyTopN As Long = -1) As String()
The same as the SortAndFilter
function however with stricter typing on the
arguments (specifically Texts
). If you already have a 0-indexed array of
strings then this is slightly cheaper to call.
FzfWorkbookMethods
Public Function Fzf( _
ByVal Value As Variant, _
ByVal Pattern As Variant, _
Optional ByVal OnlyTopN As Long = 1, _
Optional ByVal ScoreThreshold As Long = 0, _
Optional ByVal TransposeResult As Boolean = False) As Variant
Applies SortAndFilter
to Value
and returns the result. The return value can
be either a value (if there is only one result) or an array. The array is a
column unless TransposeResult
is true.
Value
can be either a value or a range that can contain multiple cells (but
not multiple areas). If it is a matrix (Rows>1
and Columns>1
) then it will
take all values (flatten the matrix).
Pattern can also be a cell or a value but has to be a single cell range.
Public Function FzfScore(ByVal Value As Variant, ByVal Pattern As Variant) As Variant
Returns the score of fuzzy matching Value
with Pattern
. Both have to
represent a single string value (either as value or a single cell range).
Public Function FzfStartIndex(ByVal Value As Variant, ByVal Pattern As Variant) As Variant
Returns the start index of fuzzy matching Value
with Pattern
. Both have to
represent a single string value (either as value or a single cell range).
Public Function FzfEndIndex(ByVal Value As Variant, ByVal Pattern As Variant) As Variant
Returns the end index of fuzzy matching Value
with Pattern
. Both have to
represent a single string value (either as value or a single cell range).
Public Function FzfNormalize(ByVal Value As Variant) As String
Returns the normalized text string after apllying FzfAlgorithm.Normalize
to
it. Value
has to represent a single string value (either as value or a single
cell range).
MIT
Copyright (c) 2020 Jonas R. Glesaaen
fzf copyright (c) 2013-2020 Junegunn Choi