none
[VB Editor] Is there the way to expand / collapse code block? RRS feed

  • Question

  • Hi all who are using Excel VBA (VB Editor),

    In advance, sorry my poor English and explanation.

    I would like to write VBA code with structured programming. As a result, my code is separated into several Sub(s).

    With Visual Studio code editor (e.g. VB.NET), code blocks (Region ~ End Region, Sub ~ End Sub, With ~ End With) can be collapsed and expanded. This is very helpful for me to grasp the structure of the whole code, and to focus on a certain code block. 

    Is there any ways to collapse and expand some code blocks in VB Editor?

    Best Regards,

    Ashidacchi -- http://hokusosha.com

    Sunday, November 24, 2019 1:06 PM

Answers

  • Unfortunately the VBA Editor cannot expand/collapse code as some other editors can. It is well overdue an update!

    If of interest we have an old treeview demo similar to 'Project Explorer' that also lists all routines and module level declarations in all loaded (unlocked) projects. As written it doesn't show any code (except declaration, routine names), but if you're familiar with the VBIDE object model it could be adapted to do something along the  lines you are looking for, quite a lot of work though!

    • Marked as answer by Ashidacchi Tuesday, November 26, 2019 5:20 AM
    Monday, November 25, 2019 9:02 PM
    Moderator
  • To export all code modules in a project that contain code -

    Option Explicit
    #Const EarlyBinding = 0
    
    Sub test()
    #If EarlyBinding Then
        'with a ref to MS VisualBasic for App's Extensibility
        Dim vbp As VBProject
        Dim cmp As VBComponent
    #Else
        Dim vbp As Object
        Dim cmp As Object
        Const vbext_ct_StdModule = 1
        Const vbext_ct_ClassModule As Long = 2
        Const vbext_ct_MSForm As Long = 3
        Const vbext_ct_Document = 100
    #End If
    
        Dim sExt As String
        Dim sModName As String
        Dim sPath As String
        
        sPath = CurDir & "\"
        Set vbp = ThisWorkbook.VBProject
        
        For Each cmp In vbp.VBComponents
            If cmp.CodeModule.CountOfLines Then
                Select Case cmp.Type
                    Case vbext_ct_StdModule: sExt = ".mod"
                    Case vbext_ct_ClassModule: sExt = ".cls"
                    Case vbext_ct_MSForm: sExt = ".frm"
                    Case vbext_ct_Document: sExt = ".cls"
                End Select
    
                sModName = cmp.Name
                cmp.Export sPath & sModName & sExt
    Debug.Print sPath & sModName & sExt
            End If
        Next
    
    End Sub

    The exported modules are simply text files (could name as *.txt), they will include some header details that are normally hidden in the VBE.

    Tuesday, November 26, 2019 7:33 PM
    Moderator

All replies

  • Unfortunately the VBA Editor cannot expand/collapse code as some other editors can. It is well overdue an update!

    If of interest we have an old treeview demo similar to 'Project Explorer' that also lists all routines and module level declarations in all loaded (unlocked) projects. As written it doesn't show any code (except declaration, routine names), but if you're familiar with the VBIDE object model it could be adapted to do something along the  lines you are looking for, quite a lot of work though!

    • Marked as answer by Ashidacchi Tuesday, November 26, 2019 5:20 AM
    Monday, November 25, 2019 9:02 PM
    Moderator
  • Hi,

    Thank you for your reply.

    If permitted, I'd like to ask one more question.
    Can we File Export the VBA code and File Import by VBA code, instead of manual operation?

    # If this is possible, we can use other editors, and exchange code/text between Excel and the editors.
       That would be very convenient to many VBA writers.

    Regards,  

    Ashidacchi -- http://hokusosha.com

    Tuesday, November 26, 2019 5:28 AM
  • To export all code modules in a project that contain code -

    Option Explicit
    #Const EarlyBinding = 0
    
    Sub test()
    #If EarlyBinding Then
        'with a ref to MS VisualBasic for App's Extensibility
        Dim vbp As VBProject
        Dim cmp As VBComponent
    #Else
        Dim vbp As Object
        Dim cmp As Object
        Const vbext_ct_StdModule = 1
        Const vbext_ct_ClassModule As Long = 2
        Const vbext_ct_MSForm As Long = 3
        Const vbext_ct_Document = 100
    #End If
    
        Dim sExt As String
        Dim sModName As String
        Dim sPath As String
        
        sPath = CurDir & "\"
        Set vbp = ThisWorkbook.VBProject
        
        For Each cmp In vbp.VBComponents
            If cmp.CodeModule.CountOfLines Then
                Select Case cmp.Type
                    Case vbext_ct_StdModule: sExt = ".mod"
                    Case vbext_ct_ClassModule: sExt = ".cls"
                    Case vbext_ct_MSForm: sExt = ".frm"
                    Case vbext_ct_Document: sExt = ".cls"
                End Select
    
                sModName = cmp.Name
                cmp.Export sPath & sModName & sExt
    Debug.Print sPath & sModName & sExt
            End If
        Next
    
    End Sub

    The exported modules are simply text files (could name as *.txt), they will include some header details that are normally hidden in the VBE.

    Tuesday, November 26, 2019 7:33 PM
    Moderator
  • Hi Peter Thornton,

    Thank you for providing excellent code.
    I've modified sPath = CurDir & "\" to sPath = ThisWorkbook.Path & "\"

    Anyway, I'll use your code and write VB.NET code to combine with Notepad++.
    Thank you very much. 

    Regards,

    Ashidacchi -- http://hokusosha.com

    Wednesday, November 27, 2019 7:51 AM