I am trying to get the nth similar of a range compare to a text. The Similarity function is to return a percentage similarity between 2 texts.

Function SimilarText(ByVal CompareText As String, _
                    ByRef TargetCompare As Range, _
                    Optional ByVal RankSimilarity As Integer = 1) As Long

Dim compareResults() As Long
ReDim compareResults(RankSimilarity)
Dim simiResult As Single
Dim smallestIndex As Integer
Dim result As String
Debug.Print (CompareText)

For Each cell In TargetCompare
    simiResult = Similarity(cell.Value, CompareText)
    Debug.Print (simiResult)
    If simiResult > Application.Min(compareResults) Then
        smallestIndex = Application.Match(Application.Min(compareResults), compareResults, 0) - 1
        Debug.Print ("Index:" & smallestIndex)
        compareResults(smallestIndex) = CLng(simiResult)'//This doesnt seem to do anything. I tried without the conversion but still nothing.
        Debug.Print ("Smallest after update:" & compareResults(smallestIndex)) '//This always 0
    End If
Next cell

    SimilarText = Application.Min(compareResults) '//So this is also alway 0

End Function

I would expect the array element will be updated after each cell like '(0.22,0.44) but the result seems to always be 0.

1 Answers

Foxfire And Burns And Burns On Best Solutions

Your array compareResults() is defined as Long, and that's only for integer numbers.

Long data type (Visual Basic)

Change data type to something that admits decimal.

Also, in line compareResults(smallestIndex) = CLng(simiResult), you are forcing a number to become a long integer. Use somethin else, or it will save a long integer number.