Longest Common Subsequence in VBA Giving #VALUE! Error

Question

I've been putting together a UDF in Excel (365) to calculate the longest common subsequence between two strings (based on this implementation in python https://www.geeksforgeeks.org/printing-longest-common-subsequence/).

When I run the UDF I get a #Value! error on the worksheet. I've done some rudimentary debugging but I'm new to VBA and running into a wall. The message box statements in the code are simply for said crude debugging.

I believe the issue is in my manipulation of the L array. It seems to get to the first case in the first set of for loops then quit as it's evaluating L(i, j,) = 0. Any pointers on where I'm going wrong?

In the worksheet I'm using =ClosestMatch("aabbaaaa", "aaaabbaa") and getting #VALUE! as a result.

This is the VBA code for the UDF I'm attempting:

Function ClosestMatch(ByVal x As String, ByVal y As String, Optional ByVal return_String As Boolean = False) As Variant
    Dim xLen As Integer
    Dim yLen As Integer
    
    xLen = Len(x)
    yLen = Len(y)
    
    MsgBox "x = " & x & " y = " & y
    
    'Create Zeroed Array of xLen+1 x yLen+1 dimensions (intentional extra space).
    ReDim L((xLen + 1), (yLen + 1)) 'indexing starts at 0.
    For i = 0 To (xLen + 1)
        For j = 0 To (yLen + 1)
            L(i, j) = 0
        Next j
    Next i
    
    MsgBox "Created 0'ed array L"
    
    'Build dynamic programming table from the bottom up.
    'Note that L[xLen][yLen] will contain an integer equal to the length
    'of the complete LCS.
    'Note that L[i][j] contains the length of the lcs of x[0..i] and y[0..j]
    For i = 0 To (xLen + 1)
        For j = 0 To (yLen + 1)
            If i = 0 Or j = 0 Then
                L(i, j) = 0
            ElseIf Mid(x, i - 1, 1) = Mid(x, i - 1, 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
    
    'Length of LCS
    Dim LCSlen As Integer
    LCSlen = L(xLen, yLen)
    
    MsgBox "Length of the LCS is " & LCSlen
    
    'Start from the right-most-bottom-most corner and store chars
    'one by on in LCS
    Dim LCS As String
    
    LCS = ""
    i = xLen
    j = yLen
    
    While i > 0 And j > 0
            'If current character in x and y are same, then current char
            'is part of the LCS. The L[xLen][yLen] is the location of the
            'fist charachter we will PUSH onto the front of the LCS string
            If Mid(x, i - 1, 1) = Mid(x, i - 1, 1) Then
                LCS = Mid(x, i - 1, 1) & Right(LCS, Len(LCS))
            
            'If not same, then find the larger of the two lengths in L[][]
            'then go in the direction of the larger value
            ElseIf L(i - 1, j) > L(i, j - 1) Then
                i = i + 1
            Else
                j = j + 1
            End If
    Wend
    
    If return_String Then
        ClosestMatch = LCS
    Else
        ClosestMatch = LCSlen
    End If
    
End Function

score 3 · Answer 1 · answered Dec 06 '21 at 23:22

Excel "swallows" user-defined function errors and wraps them into a Variant/Error value, such that any function that throws a VBA run-time error would return a #VALUE! error to the calling worksheet.

The trick is to remove the wrapper and invoke the function directly yourself.

In the VBIDE, press Ctrl+G to bring up the immediate toolwindow, then type ? followed by the name of the function and its arguments:

?ClosestMatch("aabbaaaa", "aaaabbaa")

The ? is shorthand for PRINT so if all goes well, the function returns a value that gets printed right underneath:

?ClosestMatch("aabbaaaa", "aaaabbaa")
aa

But if anything goes wrong and the function throws an error, you'll get a VBA run-time error prompt and will be taken directly to the instruction responsible for the #VALUE! error the worksheet is seeing, and by using the debugger tools at your disposal you'll be able to:

Hover any variable to see its value
Bring up the locals toolwindow to see all variables and their values
Set the current statement (the yellow arrow) to any other statement in the function
Step-through (F8) the code and execute a single statement at a time
Place and remove breakpoints (F9) to halt execution at a specific statement
Resume execution (F5), even after modifying the code on-the-fly

Consider using Debug.Print statements instead of MsgBox, to print to the immediate toolwindow instead of popping a disruptive message box.

Then consider writing several test methods that call your function with various argument combinations, and assert on the result: if the function returns the expected output, the test passes, otherwise the test fails. When all tests pass, you know your function is going to work as intended for all the covered cases. Rubberduck (a free and open-source VBIDE add-in project I started a few years ago) gives you tooling to easily write and run such unit tests, and its static code analysis can help you avoid many pitfalls, beginner traps, and archaic code constructs (for example While...Wend can be written as a more standard Do While...Loop structure).

This is fantastic information! +1 (I can't vote until I reach 15 reputation apparently. Lol) — Seth Thomas, Dec 07 '21 at 14:28

Siddharth Rout · Answer 2 · 2021-12-06T05:57:26.793

The error is because the value of i= 0 on the line ElseIf Mid(x, i - 1, 1) = Mid(x, i - 1, 1) Then which makes Mid(x, i - 1, 1) fail and hence the function collapses.

My recommendation:

User proper error handling always.
Use Line Numbers to number your code and use ERL to get the offending line number.

Here is an example

Option Explicit

Function ClosestMatch(ByVal x As String, ByVal y As String, Optional ByVal return_String As Boolean = False) As Variant
          Dim xLen As Integer
          Dim yLen As Integer
          Dim i As Long, j As Long, k As Long
          
10        On Error GoTo Whoa
          
20        xLen = Len(x)
30        yLen = Len(y)
          
40        MsgBox "x = " & x & " y = " & y
          
          'Create Zeroed Array of xLen+1 x yLen+1 dimensions (intentional extra space).
50        ReDim L((xLen + 1), (yLen + 1)) 'indexing starts at 0.
60        For i = 0 To (xLen + 1)
70            For j = 0 To (yLen + 1)
80                L(i, j) = 0
90            Next j
100       Next i
          
110       MsgBox "Created 0'ed array L"
          
          'Build dynamic programming table from the bottom up.
          'Note that L[xLen][yLen] will contain an integer equal to the length
          'of the complete LCS.
          'Note that L[i][j] contains the length of the lcs of x[0..i] and y[0..j]
120       For i = 0 To (xLen + 1)
130           For j = 0 To (yLen + 1)
140               If i = 0 Or j = 0 Then
150                   L(i, j) = 0
160               ElseIf Mid(x, i - 1, 1) = Mid(x, i - 1, 1) Then
170                   L(i, j) = L(i - 1, j - 1) + 1
180               Else
190                   L(i, j) = WorksheetFunction.Max(L(i - 1, j), L(i, j - 1))
200               End If
210           Next j
220       Next i
          
          'Length of LCS
          Dim LCSlen As Integer
230       LCSlen = L(xLen, yLen)
          
240       MsgBox "Length of the LCS is " & LCSlen
          
          'Start from the right-most-bottom-most corner and store chars
          'one by on in LCS
          Dim LCS As String
          
250       LCS = ""
260       i = xLen
270       j = yLen
          
280       While i > 0 And j > 0
                  'If current character in x and y are same, then current char
                  'is part of the LCS. The L[xLen][yLen] is the location of the
                  'fist charachter we will PUSH onto the front of the LCS string
290               If Mid(x, i - 1, 1) = Mid(x, i - 1, 1) Then
300                   LCS = Mid(x, i - 1, 1) & Right(LCS, Len(LCS))
                  
                  'If not same, then find the larger of the two lengths in L[][]
                  'then go in the direction of the larger value
310               ElseIf L(i - 1, j) > L(i, j - 1) Then
320                   i = i + 1
330               Else
340                   j = j + 1
350               End If
360       Wend
          
370       If return_String Then
380           ClosestMatch = LCS
390       Else
400           ClosestMatch = LCSlen
410       End If
          
LetsContinue:
          
420       Exit Function
Whoa:
430       ClosestMatch = Err.Description & " on line " & Erl
End Function

In Action:

Other Tips:

Use Option Explicit.
I use MZ Tools for Excel. If you are a serious programmer, I definitely recommend using that.

Thank you very much!! You got me kicked in the right direction to get debugging more effectively. I didn't make it to the stage of adding in the error handling yet, but I've posted what I came up with to fix the logical errors I had in the code. — Seth Thomas, Dec 07 '21 at 01:40
@SethThomas: I would recommend that you incorporate the error handling. If not today, I am sure in the near future you will realize it's importance. :) — Siddharth Rout, Dec 07 '21 at 05:00

Seth Thomas · Accepted Answer · 2021-12-07T01:47:33.247

After entirely too much timing staring at the watch window...I had a LOT of errors of me copying x where it should've been y, and typing i where it should've been j.

As I haven't been able to find a VBA example of finding the Longest Common Subsequence, here it is...

Public Function LCSMatch(ByVal x As Range, ByVal y As Range, Optional ByVal return_String As Boolean = False) As Variant
    Dim xLen As Integer
    Dim yLen As Integer
    
    xLen = Len(x)
    yLen = Len(y)
    
    
    'Create Zeroed Array of xLen+1 x yLen+1 dimensions (intentional extra space).
    ReDim L((xLen), (yLen)) 'indexing starts at 0.
    For i = 0 To (xLen)
        For j = 0 To (yLen)
            L(i, j) = 0
        Next j
    Next i
    
    'Build dynamic programming table from the bottom up...
    'Note that L[xLen][yLen] will contain an integer equal to the length
    'of the complete LCS.
    'Note that L[i][j] contains the length of the lcs of x[0..i] and y[0..j]
    For j = 0 To (yLen)
        For i = 0 To (xLen)
            If i = 0 Or j = 0 Then
                L(i, j) = 0
            ElseIf 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 i
    Next j
    
    'Length of LCS
    Dim LCSlen As Integer
    LCSlen = L(xLen, yLen)

    
    'Start from the right-most-bottom-most corner and store chars
    'one by on in LCS
    Dim LCS As String
    
    LCS = ""
    i = xLen
    j = yLen
        
        
        
        While i > 0 And j > 0
            'If current character in x and y are same, then current char
            'is part of the LCS. The L[xLen][yLen] is the location of the
            'fist charachter we will PUSH onto the front of the LCS string
            If Mid$(x, i, 1) = Mid$(y, j, 1) Then
                LCSPart = Right$(LCS, Len(LCS))
                LCS = Mid$(x, i, 1) & LCSPart
                i = i - 1
                j = j - 1
                'GoTo Match
            'If not same, then find the larger of the two lengths in L[][]
            'then go in the direction of the larger value
            ElseIf L(i - 1, j) > L(i, j - 1) Then
                i = i - 1
            Else
                j = j - 1
            End If
'Match:
    Wend

    MsgBox "Length of the LCS is " & LCSlen
    MsgBox "LCS is " & LCS

    If return_String Then
        LCSMatch = LCS
    Else
        LCSMatch = LCSlen
    End If
    
End Function

Mathieu's answer is excellent but returning the favor by posting the final working code is equally so. Thanks to both :-) — ndemou, Dec 18 '21 at 07:29

Longest Common Subsequence in VBA Giving #VALUE! Error

3 Answers3