Results 1 to 3 of 3

Thread: From VB program programmatically insert event procedure into Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    5

    From VB program programmatically insert event procedure into Excel

    From VB program programmatically insert event procedure into Excel
    I have a group of Excel worksheets that contain many Visual Basic event procedures and modules. I would like to be able to update the code in these worksheet procedures and modules directly from an external Visual Basic program. In the external Visual Basic program I have a routine that defines the Excel worksheet I want to change and have had no problem in extracting or changing information in the Excel worksheet cells. In the external Visual Basic program the following is what I use to access information contained in my Excel worksheets.

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open("C:\Excel\MyWorksheet.xls", , False)
    Set xlSheet = xlBook.Sheets(3)

    How can I Import, Remove, or Change modules or event procedures to the Excel worksheet from my external Visual Basic program? If anybody knows how to do this would you please provide me with a sample.

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: From VB program programmatically insert event procedure into Excel

    Hi

    Defining Objects

    VB Code:
    1. 'VBProject                
    2. Dim VBProj As VBProject
    3. Set VBProj = ThisWorkbook.VBProject
    4.  
    5. 'VBComponent                
    6. Dim VBComp As VBComponent
    7. Set VBComp = ThisWorkbook.VBProject.VBComponents("Module1")
    8.  
    9. 'CodeModule                
    10. Dim VBCodeMod As CodeModule
    11. Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

    Importing A Module To A Workbook

    There isn't a single method to copy modules from one VBProject to another. Instead, you have to export the module from one project, and then import it into another. The following procedure will copy Module1 from Book2 to Book1.

    VB Code:
    1. Sub CopyOneModule()
    2.  
    3. Dim FName As String
    4. With Workbooks("Book2")
    5.     FName = .Path & "\code.txt"
    6.     .VBProject.VBComponents("Module1").Export FName
    7. End With
    8. Workbooks("book1").VBProject.VBComponents.Import FName
    9.  
    10. End Sub

    Just change "Module1" to the name of the module you want to copy. If you want to copy all modules (except the ThisWorkbook and Sheet modules), you can use the following code.

    VB Code:
    1. Sub CopyAllModules()
    2.  
    3. Dim FName As String
    4. Dim VBComp As VBIDE.VBComponent
    5.  
    6. With Workbooks("Book2")
    7.     FName = .Path & "\code.txt"
    8.     If Dir(FName) <> "" Then
    9.         Kill FName
    10.     End If
    11.     For Each VBComp In .VBProject.VBComponents
    12.         If VBComp.Type <> vbext_ct_Document Then
    13.            VBComp.Export FName
    14.            Workbooks("book1").VBProject.VBComponents.Import FName
    15.            Kill FName
    16.         End If
    17.     Next VBComp
    18. End With
    19. End Sub

    Removing/Deleting A Procedure From A Module

    The procedure below will delete the procedure called "MyNewProcedure" from the module named "NewModule" in ThisWorkbook.

    VB Code:
    1. Sub DeleteProcedure()
    2.  
    3. Dim VBCodeMod As CodeModule
    4. Dim StartLine As Long
    5. Dim HowManyLines As Long
    6.  
    7. Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
    8. With VBCodeMod
    9.     StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc)
    10.     HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc)
    11.     .DeleteLines StartLine, HowManyLines
    12. End With
    13.  
    14. End Sub


    Removing/Deleting All Code From A Module

    The procedure below will delete all code from a module name "NewModule".

    VB Code:
    1. Sub DeleteAllCodeInModule()
    2. Dim VBCodeMod As CodeModule
    3. Dim StartLine As Long
    4. Dim HowManyLines As Long
    5.  
    6. Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
    7. With VBCodeMod
    8.     StartLine = 1
    9.     HowManyLines = .CountOfLines
    10.    .DeleteLines StartLine, HowManyLines
    11. End With
    12. End Sub


    Hope this helps....
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    5

    Re: From VB program programmatically insert event procedure into Excel

    Thank you for your response. This looks like just what I was looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width