VBA实现最长公共子序列UDF运行返回#VALUE!错误如何修复？

核心错误点 #

• 字符比较逻辑错误：两次对比都是取x字符串的字符，未对比x和y的对应位置字符
• 数组越界：循环遍历范围超过了声明的数组下标上限，触发VBA运行时错误
• 回溯逻辑错误：索引增减逻辑完全颠倒，导致程序进入死循环

修复后完整代码 #

Function ClosestMatch(ByVal x As String, ByVal y As String, Optional ByVal return_String As Boolean = False) As Variant
    Dim xLen As Integer, yLen As Integer
    Dim i As Integer, j As Integer
    Dim L As Variant
    Dim LCSlen As Integer
    Dim LCS As String
    
    xLen = Len(x)
    yLen = Len(y)
    
    ' 声明DP数组，索引范围0~xLen、0~yLen
    ReDim L(xLen, yLen)
    
    ' 从下到上填充动态规划表
    For i = 1 To xLen
        For j = 1 To yLen
            If Mid(x, i, 1) = Mid(y, j, 1) Then
                L(i, j) = L(i - 1, j - 1) + 1
            Else
                L(i, j) = WorksheetFunction.Max(L(i - 1, j), L(i, j - 1))
            End If
        Next j
    Next i
    
    LCSlen = L(xLen, yLen)
    ' 仅返回长度时直接输出
    If Not return_String Then
        ClosestMatch = LCSlen
        Exit Function
    End If
    
    ' 回溯拼接最长公共子序列
    LCS = ""
    i = xLen
    j = yLen
    Do While i > 0 And j > 0
        If Mid(x, i, 1) = Mid(y, j, 1) Then
            LCS = Mid(x, i, 1) & LCS
            i = i - 1
            j = j - 1
        ElseIf L(i - 1, j) > L(i, j - 1) Then
            i = i - 1
        Else
            j = j - 1
        End If
    Loop
    
    ClosestMatch = LCS
End Function

调用说明 #

• 调用=ClosestMatch("aabbaaaa", "aaaabbaa")返回最长公共子序列长度6
• 调用=ClosestMatch("aabbaaaa", "aaaabbaa",TRUE)返回最长公共子序列aaabbaa

内容的提问来源于stack exchange，提问作者Seth Thomas