excel - vba to create and save a function in another file

Question

i have this excel macro in a file that calls a text file and converts it into excel then saves the new excel file to a specific location. each time I run the macro a new excel file will be created. however, I want this macro to add some functions inside this file in a specific sheet before saving it to the new directory and next time I open the new excel file the function should be running normally and if i go to the vba window i should see my function there.

1- i run the macro from my macro file 2- it opens my new excel and adds a function inside sheet1 and saves the excel file in it's new directory

any ideas on how to do that ?

thanks and best regards

Answer 1

Please, test the next code. It creates a new workbook (you may use the one you obtained opening the text file), inserts a standard module (named "TestModule"), creates a Sub ("WrittenFromAnotherWorbook") and then creates the same test Sub in the "Sheet1" module. The new saved workbook ("TestWorkbookWithCode.xlsm") can be found in the same path with the workbook keeping this code:

Sub CreateNewWorkbookAddMacro()
   'It needs a reference to 'Microsoft Visual Basic for Applications Extensibility 5.3'
   Dim wb As Workbook, project As VBProject, component As VBComponent
   Dim code(1 To 3) As String, i As Long, boolFound As Boolean
   Const moduleName As String = "TestModule"
   
   Set wb = Workbooks.Add
   Set project = wb.VBProject
   code(1) = "Sub WrittenFromAnotherWorbook()"
   code(2) = "    MsgBox ""Hello from the new workbook!"""
   code(3) = "End Sub"
   
    For Each component In project.VBComponents
        'if the module has already been created, exit the code:
        If component.Name = moduleName Then boolFound = True: Exit For
    Next
    If Not boolFound Then
        Set component = project.VBComponents.Add(vbext_ct_StdModule)
        component.Name = moduleName
        'Put the code in place:
        For i = LBound(code) To UBound(code)
            component.CodeModule.InsertLines i, code(i)
        Next
    End If
    Set component = project.VBComponents(wb.Worksheets(1).CodeName)
    'Put the (same) code in place:
    For i = LBound(code) To UBound(code)
        component.CodeModule.InsertLines i, code(i)
    Next
    wb.SaveAs ThisWorkbook.Path & "TestWorkbookWithCode.xlsm", xlOpenXMLWorkbookMacroEnabled
End Sub

If you are not familiar with references adding, please use the next code to programmatically add the needed one:

Sub addExtenssibilityReference()
   'Add a reference to 'Microsoft Visual Basic for Applications Extensibilty 5.3':
   ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
End Sub

You must firstly run this code, save your workbook keeping the code and then run the one able to do what I explained above.

Please, test it and send some feedback.