自用Excel VBA函数整理 part3

'http://en.wikipedia.org/wiki/Levenshtein_distance
Public Function EditDistance(s, t, Optional costIns As Integer = 1, Optional costDel As Integer = 1, Optional costSub As Integer = 1) As Integer
    'declaration, d(i,j) will hold the Levenshtein distance between
    'the first i chars of s and the first j chars ot t
    Static d(0 To EDIT_DISTANCE_CACHE, 0 To EDIT_DISTANCE_CACHE) As Integer
    
    'initialization, note that (m+1)*(n+1) distances is used
    m = Len(s): n = Len(t)
    For x = 0 To m
        For y = 0 To n
            d(x, y) = 0
        Next y
    Next x
    
    'source prefixes can be transformed into empty string by dropping all chars
    For i = 1 To m
        d(i, 0) = i * costDel
    Next i
    'target prefixes can be reached from empty source prefix by inserting every chars
    For j = 1 To n
        d(0, j) = j * costIns
    Next j
    
    For j = 1 To n
        For i = 1 To m
            If Mid(s, i, 1) = Mid(t, j, 1) Then
                d(i, j) = d(i - 1, j - 1)
            Else
                d(i, j) = WorksheetFunction.Min( _
                    d(i - 1, j) + costDel, _
                    d(i, j - 1) + costIns, _
                    d(i - 1, j - 1) + costSub)
            End If
        Next i
    Next j
    EditDistance = d(m, n)
End Function

 

Example of workbook already enabled:
1. JSON
2. XmlHttp (via usage of API for Geocoding) 
3. Geo Distance Calculation From Lng & Lat
[Download Here]