Copy only written code (Text) in VBA components (Modules,ThisWorkBook)-CodePudding

I have an excel Macro-Enabled Report workbook with some codes in each of it's sheets (including "ThisWorkBook" component) which generates a Macro-Free version of it for my boss review. My boss almost always made some modifications on that ".xlsx" file and I want to transfer all of my written codes to this edited Macro-Free book and save it as ".xlsm" again.

How can I access and copy only the written codes inside those components?

CodePudding user response：

Copying VBA code is possible (see for example Copy all VBA codes from workbook to another), however it's not that easy.

I would suggest the following alternatives:

1) Send the file as XLSM-file for review. If you want to prevent that certain macros are executed, you can create a small function (make it a function instead of hardcode the username at every place so that you can easily change it).

Function UserMacrosAllowed() As Boolean
    UserMacrosAllowed = (Environ("UserName") = "milad")
End Function

Then, put the following statement at the top of a routine that shouldn't be executed by your boss, eg:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not UserMacrosAllowed Then Exit Sub
    (...)
End Sub

2) If that's not feasible, create a template file without any data, just with all the code, and copy the data of the xlsx-file into it. This should be much easier.

CodePudding user response：

Using The link provided by FunThomas I came up with my solution which is:

Sub ExportCodes()

Dim newWithMacro as workbook

For Each Component In ThisWorkBook.VBProject.VBComponents
    If Component.Type = 100 And Component.CodeModule.CountOfLines <> 0 Then
        Text = Component.CodeModule.Lines(1,Component.CodeModule.CountOfLines)
        newWithMacro.VBProject.VBComponents(Component.Name).CodeModule.AddFromString (Text)
    End If
Next Component

End sub