none
VBA cleaning tool RRS feed

  • Question

  • Hi,

    Does someone knows about a tool that removes all comment in a VB-project? I'm not saying that comments are not useful, just that it's not needed in an add-in. I know I had one years ago, speak about Excel < 1997.

    Just to keep the add-in as small as possible.

    Saturday, September 22, 2018 2:43 PM

Answers

  • Hi JP,

    For the "Remove all comment" as you mentioned, I didn't find any tools about this. However, you could try the following code:

    Sub RemoveComents()
        Dim iTotal As Integer
        Dim i As Integer
        Dim k As Integer
        Dim tmpStr As String
        Dim SearchChar, MyPos, SearchChar1, MyPos1
        SearchChar = "'"
        SearchChar1 = "''"
        For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count
            With ActiveWorkbook.VBProject.VBComponents.Item(i).CodeModule
                iTotal = .CountOfLines
                If iTotal > 0 Then
                    For k = iTotal To 1 Step -1
                        tmpStr = .Lines(k, 1)
                        MyPos = InStr(1, tmpStr, SearchChar, 1)
                        MyPos1 = InStr(1, tmpStr, SearchChar1, 1)
                        If Left(Trim(tmpStr), 1) = SearchChar And Left(Trim(tmpStr), 2) <> SearchChar1 Then
                            .DeleteLines k, 1
                        End If
                        If MyPos > 1 And MyPos1 = 0 And Left(Trim(tmpStr), 10) <> "SearchChar" Then
                            tmpStr = Left(tmpStr, MyPos - 1)
                            .DeleteLines k, 1
                            .InsertLines k, tmpStr
                        End If
                        If Len(Trim(tmpStr)) = 0 Then
                            .DeleteLines k, 1
                        End If
                    Next k
                End If
            End With
        Next i
    End Sub

    Hopefully it helps you.

    Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by JP Ronse Monday, October 1, 2018 5:36 PM
    Monday, September 24, 2018 1:51 AM
    Moderator

All replies

  • Hi JP,

    For the "Remove all comment" as you mentioned, I didn't find any tools about this. However, you could try the following code:

    Sub RemoveComents()
        Dim iTotal As Integer
        Dim i As Integer
        Dim k As Integer
        Dim tmpStr As String
        Dim SearchChar, MyPos, SearchChar1, MyPos1
        SearchChar = "'"
        SearchChar1 = "''"
        For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count
            With ActiveWorkbook.VBProject.VBComponents.Item(i).CodeModule
                iTotal = .CountOfLines
                If iTotal > 0 Then
                    For k = iTotal To 1 Step -1
                        tmpStr = .Lines(k, 1)
                        MyPos = InStr(1, tmpStr, SearchChar, 1)
                        MyPos1 = InStr(1, tmpStr, SearchChar1, 1)
                        If Left(Trim(tmpStr), 1) = SearchChar And Left(Trim(tmpStr), 2) <> SearchChar1 Then
                            .DeleteLines k, 1
                        End If
                        If MyPos > 1 And MyPos1 = 0 And Left(Trim(tmpStr), 10) <> "SearchChar" Then
                            tmpStr = Left(tmpStr, MyPos - 1)
                            .DeleteLines k, 1
                            .InsertLines k, tmpStr
                        End If
                        If Len(Trim(tmpStr)) = 0 Then
                            .DeleteLines k, 1
                        End If
                    Next k
                End If
            End With
        Next i
    End Sub

    Hopefully it helps you.

    Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by JP Ronse Monday, October 1, 2018 5:36 PM
    Monday, September 24, 2018 1:51 AM
    Moderator
  • Hi Simon,

    I've found it back, it is a tool of Rob Bovey.

    Monday, October 1, 2018 5:36 PM
  • It is glad to hear that you have found the solution and thanks for sharing it with us. If you have any question, or update, please feel free to let me know.

    Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 1, 2018 9:20 PM
    Moderator