Formatter not able to process this code

[mauriciojxs]

Hello @hilkoc

The format function is not able to process the code below:

Sub SplitNotes( _
    ByVal NotesStr As String, _
    ByVal IDStr As String, _
    ByRef Notes() As String, _
    ByRef TaskLocal As TaskType)

    Dim tmpArray() As Variant
    Dim tmpID() As Variant
    Dim tmpNote() As String
    Dim a As Integer
    Dim b As Integer
    Dim arrayMax As Long
    Dim IDMax As Long
    Dim counter As Long
    Dim tblNotes As ListObject
    Dim clNotes As ListColumns
    Dim rowNote As ListRow
    Dim found As Boolean

    Set tblNotes = Sheets("InternalConfig").ListObjects("TblNotes")
    Set clNotes = tblNotes.ListColumns

    tmpArray = SplitBase1(NotesStr, "]")
    tmpID = SplitBase1(IDStr, Chr(10))

    If IsArrayAllocated(tmpArray) Then
        arrayMax = UBound(tmpArray)
        If tmpArray(arrayMax) = "" Then arrayMax = arrayMax - 1
    Else
        arrayMax = 0
    End If

    If IsArrayAllocated(tmpID) Then
        IDMax = UBound(tmpID)
    Else
        IDMax = 0
    End If

    If IDMax < arrayMax Then
        ReDim Preserve tmpID(1 To arrayMax)
        tmpID(arrayMax) = ""
    End If

    ReDim Notes(1 To 3, 1 To (arrayMax + tblNotes.ListRows.Count))

    'add note to TODOist and/or properly format
    For a = 1 To arrayMax
        tmpNote = FormatNote(tmpArray(a), tmpID(a), TaskLocal)
        Notes(1, a) = tmpNote(1) 'id
        Notes(2, a) = tmpNote(2) 'date
        Notes(3, a) = tmpNote(3) 'note
    Next

    'include notes in local table not yet in the array
    counter = arrayMax
    For Each rowNote In tblNotes.ListRows
        'check if note belongs to task
        If rowNote.Range(clNotes.Item("item_id").Index) = TaskLocal.ID Then
            found = False

            'check initial range if note was not already included
            a = 1
            Do While a <= arrayMax
                If rowNote.Range(clNotes.Item("id").Index) = Notes(1, a) Then
                    found = True
                    'check if note was deleted in TODOist
                    If rowNote.Range(clNotes.Item("is_deleted").Index) = "1" Or _
                        rowNote.Range(clNotes.Item("is_archived").Index) = "1" Then
                        'delete
                        If a < arrayMax Then 'not in the end of the array
                            For b = a + 1 To arrayMax
                                Notes(1, b - 1) = Notes(1, b)
                                Notes(2, b - 1) = Notes(2, b)
                                Notes(3, b - 1) = Notes(3, b)
                            Next
                        End If
                        arrayMax = arrayMax - 1
                        counter = counter - 1
                    End If
                    Exit Do
                End If
                a = a + 1
            Loop
            'if not found and not deleted
            If Not found And _
                rowNote.Range(clNotes.Item("is_deleted").Index) = "0" And _
                rowNote.Range(clNotes.Item("is_archived").Index) = "0" Then
                'add
                counter = counter + 1
                Notes(1, counter) = rowNote.Range(clNotes.Item("id").Index)
                Notes(2, counter) = Format(DateConv(rowNote.Range(clNotes.Item("posted").Index)), "yyyy-mm-dd")
                Notes(3, counter) = rowNote.Range(clNotes.Item("content").Index)
            End If
        End If
    Next

    If counter > 0 Then
        If counter <> UBound(Notes, 2) Then ReDim Preserve Notes(1 To 3, 1 To counter)
    Else
        ReDim Notes(0, 0)
    End If

End Sub

From what I see, it's trying to align the "Exit Do" at the same indent as the "Do While", making the "Loop" to have the indent reduced. It is throwing the error below:

Error while formatting Module1
9 Subscript out of range
 on line 105: End Sub
indentLevel: -1 , levelChange: -1

Best regards, Mauricio

[tommy9]

The issue is caused by having a comment at the end of an If ... Then statement. In particular, this line:

If a < arrayMax Then 'not in the end of the array

Removing the comment will mean that the module correctly indents.

The error is caused by the isOneLineIfStatemt function thinking the line is a "one line if statement" because it doesn't end with Then. I think any trailing comments need to be stripped off the line before checking it's syntax and then added back.

[mauriciojxs]

Nice catch! I moved the comment to the next line, and worked like a charm.

Thanks!