Cacher is the code snippet organizer for pro developers

'需先引用“Microsoft Visual Basic for Applications Extensibility 5.3”，和“信任对vba工程的访问”
Public Sub exportAndImport()
    Dim wkBook As Excel.Workbook
    Dim wkComp As VBIDE.VBComponent
    Dim macroPath As String
    Set wkBook = ThisWorkbook

    On Error Resume Next
    macroPath = ThisWorkbook.Path & "\" & "export\"
    '需添加，如果不存在，就创建文件夹
    For Each wkComp In wkBook.VBProject.VBComponents 'export
        If wkComp.Type = vbext_ct_StdModule Then '如果是模块就导出并删除
            wkComp.Export macroPath & wkComp.Name & ".bas"
            'wkBook.VBProject.VBComponents.Remove wkComp
        End If
    Next
End Sub

Public Sub improt()
    Dim wkBook As Excel.Workbook
    Dim wkComp As VBIDE.VBComponent
    Dim macroPath As String
    Set wkBook = ThisWorkbook
    
    macroPath = ThisWorkbook.Path & "\" & "export\"
    '需添加，如果文件夹不存在，就报错并停止
    tempfile = Dir(macroPath & "*.bas")
    While tempfile <> ""
        Set wkComp = wkBook.VBProject.VBComponents.Import(macroPath & tempfile) '导入代码
        wkComp.Name = Left(tempfile, Len(tempfile) - 4)
        tempfile = Dir
    Wend
    Debug.Print "in export and import"
End Sub


''（运行前:
''1、引用Microsoft Visual Basic for Application Extensibility 5.3，
''2、并且选择信任对VBA工程访问）
Sub 批量导出VBE模块()
    Dim ExportPath As String, ExtendName As String
    Dim vbc As VBComponent
    Dim i%
    ExportPath = ThisWorkbook.Path
    For Each vbc In Application.VBE.ActiveVBProject.VBComponents
        '取得模块内代码行数
        i = ThisWorkbook.VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines
        
        ''判断模块是否为空，也就是代码行数是否>=1。
        If i >= 1 Then
            Select Case vbc.Type
            Case vbext_ct_ClassModule, vbext_ct_Document    '组件属性为类模块、EXCEL对象
                ExtendName = ".Cls"    '设置导出文件的扩展名
            Case vbext_ct_MSForm    '组件属性为窗体
                ExtendName = ".frm"
            Case vbext_ct_StdModule    '组件属性为模块时
                ExtendName = ".Bas"
            End Select
            If ExtendName <> "" Then
                vbc.Export ExportPath & "\" & vbc.Name & ExtendName
            End If
        End If
    Next

End Sub