Самая длинная общая подпоследовательность в VBA, дающая #ЗНАЧЕНИЕ! Ошибка

ProgramBox

Самая длинная общая подпоследовательность в VBA, дающая #ЗНАЧЕНИЕ! Ошибка

Post author:admin
Запись опубликована:13 июля, 2022
Post category:Вопросы по программированию

#arrays #excel #vba #debugging #lcs

#массивы #excel #vba #отладка #lcs

Вопрос:

Я собирал UDF в Excel (365) для вычисления самой длинной общей подпоследовательности между двумя строками (на основе этой реализации в python https://www.geeksforgeeks.org/printing-longest-common-subsequence /).

Когда я запускаю UDF, я получаю #Value! ошибка на рабочем листе. Я выполнил некоторую элементарную отладку, но я новичок в VBA и натыкаюсь на стену. Инструкции окна сообщения в коде предназначены просто для указанной грубой отладки.

Я считаю, что проблема заключается в моих манипуляциях с массивом L. Кажется, что он доходит до первого случая в первом наборе циклов for, а затем завершается, поскольку он вычисляет L(i, j,) = 0. Есть какие-либо указания на то, где я ошибаюсь?

На рабочем листе, который я использую =ClosestMatch("aabbaaaa", "aaaabbaa") и получаю #VALUE! в результате.

Это код VBA для UDF, который я пытаюсь:

 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 = " amp; x amp; " y = " amp; 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 " amp; 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) amp; 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

Ответ №1:

Excel «проглатывает» ошибки пользовательских функций и преобразует их в Variant/Error значение, так что любая функция, которая выдает ошибку во время выполнения VBA #VALUE! , вернет ошибку на вызывающий рабочий лист.

Хитрость заключается в том, чтобы удалить оболочку и вызвать функцию непосредственно самостоятельно.

В VBIDE нажмите Ctrl G, чтобы вызвать немедленное окно инструментов, затем введите?, за которым следует имя функции и ее аргументы:

 ?ClosestMatch("aabbaaaa", "aaaabbaa")

Это ?сокращение для PRINT so, если все идет хорошо, функция возвращает значение, которое печатается прямо под ним:

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

Но если что-то пойдет не так, и функция выдаст ошибку, вы получите сообщение об ошибке во время выполнения VBA и перейдете непосредственно к инструкции, ответственной за #VALUE! ошибку, которую видит рабочий лист, и, используя имеющиеся в вашем распоряжении инструменты отладчика, вы сможете:

Наведите курсор на любую переменную, чтобы увидеть ее значение
Откройте окно инструментов locals, чтобы увидеть все переменные и их значения
Установите текущий оператор (желтая стрелка) на любой другой оператор в функции
Пошаговый (F8) код и выполнение одного оператора за раз
Поместите и удалите контрольные точки (F9), чтобы остановить выполнение в определенном операторе
Возобновите выполнение (F5) даже после изменения кода на лету

Рассмотрите возможность использования Debug.Print операторов вместо MsgBox , для печати в немедленном окне инструментов вместо появления окна прерывающего сообщения.

Затем рассмотрите возможность написания нескольких методов тестирования, которые вызывают вашу функцию с различными комбинациями аргументов, и утверждают результат: если функция возвращает ожидаемый результат, тест пройден, в противном случае тест завершается неудачей. Когда все тесты пройдены, вы знаете, что ваша функция будет работать так, как задумано для всех рассмотренных случаев. Rubberduck (бесплатный проект надстройки VBIDE с открытым исходным кодом, который я начал несколько лет назад) предоставляет вам инструменты для простого написания и запуска таких модульных тестов, а его статический анализ кода может помочь вам избежать многих ошибок, ловушек для начинающих и архаичных конструкций кода (например While...Wend , может быть записан как болеестандартная Do While...Loop структура).

Ответ №2:

Ошибка заключается в том, что значение i= 0 в строке ElseIf Mid(x, i - 1, 1) = Mid(x, i - 1, 1) Then , которое вызывает Mid(x, i - 1, 1) сбой, и, следовательно, функция сворачивается.

Моя рекомендация:

Пользователь всегда обрабатывает правильные ошибки.
Используется Line Numbers для нумерования вашего кода и использования ERL для получения номера строки, нарушающего.

Вот пример

 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 = " amp; x amp; " y = " amp; 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 " amp; 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) amp; 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 amp; " on line " amp; Erl
End Function

В действии:

Другие советы:

Используйте параметр явно.
Я использую MZ Tools для Excel. Если вы серьезный программист, я определенно рекомендую использовать это.

1. Большое вам спасибо!! Вы заставили меня двигаться в правильном направлении, чтобы более эффективно выполнять отладку. Я еще не дошел до стадии добавления обработки ошибок, но я опубликовал то, что придумал, чтобы исправить логические ошибки, которые у меня были в коде.

2. @SethThomas: я бы рекомендовал вам включить обработку ошибок. Если не сегодня, я уверен, что в ближайшем будущем вы поймете, насколько это важно. 🙂

Ответ №3:

После слишком долгого времени, глядя в окно просмотра…У меня было МНОГО ошибок, когда я копировал x там, где должно было быть y, и вводил i там, где должно было быть j.

Поскольку я не смог найти в VBA пример нахождения самой длинной общей подпоследовательности, вот он…

 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) amp; 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 " amp; LCSlen
    MsgBox "LCS is " amp; LCS

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