XL2000: Sample Macros to Create a Module from an Existing Module, Named Range, or Text File

You may want to programmatically insert stored Visual Basic for Applications (VBA) code into a new workbook that you create with the Workbooks.Add method. This may be necessary if you programmatically create new workbooks that you want to contain both data and macros.

This article describes automated methods that you can use to take VBA code that is stored in a module in another open workbook, a named range on a spreadsheet, or in a text file, and insert it into a new blank workbook.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The following methods require either an existing workbook with stored code in a module or named spreadsheet range, or a saved text file containing the stored code.

All examples below require a reference to Microsoft Visual Basic for Applications Extensibility. To create this reference, in the Visual Basic Editor, click References on the Tools menu, and then click to select the Microsoft Visual Basic for Application Extensibility 5.3 check box.

To Insert Code from an Existing Module

1. In an existing workbook, insert the following code:

   Sub ExportCodeMod()
   
    ' Dimension variables
      Dim strCode As String
      Dim vbCom As VBComponent
      Dim modObj As Object
   
    ' Set object to the module you want to export.
      Set modObj = _
         Application.VBE.ActiveVBProject.VBComponents.Item("modTest")
    ' Place code in a string.
      strCode = modObj.CodeModule.Lines(1, modObj.CodeModule.CountOfLines)
    ' Create new workbook.
      Application.Workbooks.Add
    ' Create a new module in workbook.
      Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
    ' Add code to new module from string variable.
      Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _
         .CodeModule.AddFromString (strCode)
   
   End Sub
   					

2. When you run ExportCodeMod, it creates a new workbook with a module named Module1, that contains the code from the existing module, named modTest.
   
   To test this sample code, you need to rename a module "modTest". To rename an existing module, in the Visual Basic Editor, select the module in the Project Explorer window; then, in the Properties Window, type a new name for the module. If you do not see the Project Explorer window or the Properties Window, click these window names on the View menu in the Visual Basic Editor.

To Insert Code from a Named Range

1. In the Visual Basic Editor, copy the existing macro (VBA) code that you want to insert into the new workbook.
2. Switch to Excel by pressing ALT+F11, or by clicking Microsoft Excel on the View menu, and then paste the code into a worksheet.
3. Select the entire range that contains the macro code.
4. On the Insert menu, point to Name, and then click Define.
5. Type a name for the range, such as MacroCode, and click OK.
6. Switch back to the Visual Basic Editor by pressing ALT+F11, or by pointing to Macro on the Tools menu, and then clicking Visual Basic Editor.
7. Type the following code into a module in the existing workbook:

   Sub ExportCodeNR()
   
    ' Dimension variable
      Dim strCode As String
   
    ' Read code from named range and store in variable
      For Each cl In Range("MacroCode")
         strCode = strCode & cl.Value & Chr$(10)
      Next
   
    ' Create new workbook.
      Application.Workbooks.Add
    ' Create a new module in workbook.
      Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
    ' Add code to new module from string variable.
      Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _
         .CodeModule.AddFromString (strCode)
   
   End Sub
   					

8. When you run the ExportCodeNR macro, it creates a new workbook containing a module called Module1, and then inserts the code located within the named range "MacroCode".

To Insert Code from a Text File

1. In a text file (this example uses C:\Code.txt), paste the macro (VBA) code that you want to insert in the new workbook. Then, save and close the text file.
2. Type the following code into a module in the existing workbook.
   
   To do this without programmatically reading through the text file, you can use the AddFromFile method, as follows:

   Sub ExportCodeTXT2()
   
    ' Create new workbook.
      Application.Workbooks.Add
    ' Create a new module in workbook.
      Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
    ' Add code to new module from string variable.
      Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _
         .CodeModule.AddFromFile ("C:\Code.txt")
   
   End Sub
   						

   A new workbook is created, containing a module named Module1, which contains the code from the text file. To achieve the same result by programmatically reading through the text file line-by-line, you can use the AddFromString method, as follows:

   Sub ExportCodeTXT()
   
    ' Dimension variables
      Dim strCode, strLine As String
      Dim FileNum As Integer
   
    ' Store the path and file name of text file.
      FileName = "C:\Code.txt"
    ' Get next available file handle number.
      FileNum = FreeFile()
    ' Open text file for input.
      Open FileName For Input As #FileNum
    ' Loop until the end of file is reached.
      Do While Seek(FileNum) <= LOF(FileNum)
       ' Store one line of text from file to variable.
         Line Input #FileNum, strLine
         strCode = strCode & strLine & Chr$(10)
      Loop
   
    ' Close the open text file.
      Close
   
    ' Create new workbook.
      Application.Workbooks.Add
    ' Create a new module in workbook.
      Application.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
    ' Add code to new module from string variable.
      Application.VBE.ActiveVBProject.VBComponents.Item("Module1") _
         .CodeModule.AddFromString (strCode)
   
   End Sub
   					

A new workbook is created, with a module named Module1, which contains the code from the text file.

For more information about the objects in the Visual Basic Editor, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type vbe property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.