Excel VBA: VBA Code That Writes VBA Code

What VBA code would I need that writes "Call FunctionOrganizeData" in the "Private Sub Workbook_Open()" subroutine if "Call FunctionOrganizeData" does not exist there yet.
ouestqueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Does Private Sub Workbook_Open() exist yet?
0
hamid tabCommented:
you must create the function before calling
0
MacroShadowCommented:
Paul Pearson has a nice article explaining how to do that.

In order to use the code on this page in your projects, you must change two settings.

    First, you need to set an reference to the VBA Extensibility library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you do not set this reference, you will receive a User-defined type not defined compiler error.


    Next, you need to enable programmatic access to the VBA Project. In Excel 2003 and earlier, go the Tools menu (in Excel, not in the VBA editor), choose Macros and then the Security item. In that dialog, click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

    In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.


Once you've done the above steps, use the following code:
Option Explicit

Sub AddProcedureToModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long

    If SubExists("Workbook_Open", "This_Workbook") = False Then
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("This_Workbook")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Private Sub Workbook_Open()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    Call FunctionOrganizeData"
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
    Else
        MsgBox "sub Workbook_Open already exisits"
    End If
    
End Sub

Function SubExists(StrSubName As String, strModuleName As String) As Boolean
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long
    Dim NumLines As Long
    Dim ProcName As String
    Dim ProcKind As VBIDE.vbext_ProcKind
    
    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(strModuleName)
    Set CodeMod = VBComp.CodeModule
    
    With CodeMod
        LineNum = .CountOfDeclarationLines + 1
        Do Until LineNum >= .CountOfLines
            ProcName = .ProcOfLine(LineNum, ProcKind)
            LineNum = .ProcStartLine(ProcName, ProcKind) + .ProcCountLines(ProcName, ProcKind) + 1
            If ProcName = StrSubName Then
                SubExists = True
                Exit Function
            End If
        Loop
    End With
    SubExists = False
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jkpieterseCommented:
It's Chip Pearson :-)
1
MacroShadowCommented:

It's Chip Pearson :-)
You're right, Paul Pearson is married to my first cousin, LOL.
1
ouestqueAuthor Commented:
Awesome answer!! Thank you!
0
MacroShadowCommented:
Glad to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.