2

This is a topic that (to my knowledge) is not touched on from CPearson's resource site (which is a very good resource for those looking to use a macro in Excel to programmatically insert code into a new worksheet/workbook, or existing worksheet/workbook).

I currently have Sheet1 containing a series of four to five separate sub macros that perform functions that do not currently affect each other. I have written a macro that, when activated by a user button press, creates a new worksheet and inserts a few lines of code onto that sheet (this task is already complete and functioning properly).

My goal is to add a line to this procedure so that, once the above action has been performed, "line of code xyz #1" will be added to a specific procedure in this CodeModule, namely: "Sub MacroMain()".

I am currently able to (nearly) achieve this result using the following, where "AddCode" is a text String:

ActiveWorkbook.VBProject.VBComponents(Sheets("Sheet1").CodeName).CodeModule.AddFromString AddCode

The issue with this solution however is that it only adds the new line of code to the uppermost portion of Sheet1's CodeModule, and not the specific sub macro Sub MacroMain. Attempts to reference SubMacroMain in the above line unfortunately produce an "Expected Function or variable" error. I assume this is user formatting error on my part.

I was wondering if perhaps there is a certain syntax I should be following with the line of code listed above to make it so the code being inserted in this procedure is placed into a specific macro in Sheet1's CodeModule.

Thank you.

Improve this question

1 Answer 1

Reset to default
3

You have a few options. If you know the exact line number, you can use InsertLines to add a line at a specific line number. For example:

Sub AddLineToModule(LineNum as Long, StrLineText as String)
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Const DQUOTE = """" ' one " character

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    Set CodeMod = VBComp.CodeModule

    CodeMod.InsertLines LineNum, StrLineText

    Set VBProj = Nothing
    Set VBComp = Nothing
    Set CodeMod = Nothing
End Sub

Original solution italicized: If you do not know the exact line number, you could read the entire module to a string array, then loop through it and add a line after the line you want. I don't have an example on hand but can add one later if you need it. EDIT: Thanks to Mikegrann, a potentially better solution is to use Module.Find; however, there is some debate in the comments below as to whether this is an advisable solution (see the update from Mat's Mug). Depending on OP's specific issue, this could be a viable method. EDIT 2: Per comments, Module.Find should not be used in most (if not all) scenarios. Use the VBIDE API or a custom array-search function to reliably find a line of code.

Finally, if you don't know the exact line number in the module but know the exact line number relative to the start of the specific procedure, you can use a combination of my first example above and ProcStarLine. 

Sub AddLineToProcedure(StrProcName as String, LineNum as Long, StrLineText as String)
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Const DQUOTE = """" ' one " character

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        LineNum = LineNum + .ProcStartLine(ProcName, vbext_pk_Proc)
        .InsertLines LineNum, StrLineText
    End With

    Set VBProj = Nothing
    Set VBComp = Nothing
    Set CodeMod = Nothing
End Sub


    ProcName = "DeleteThisProc"
    With CodeMod
        StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
        NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
        .DeleteLines StartLine:=StartLine, Count:=NumLines
    End With
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

In situation 2, instead of writing everything yourself to build a robust searching tool to find the desired line, why not use Module.Find? (msdn.microsoft.com/en-us/library/office/ff195471.aspx)
Ooh, that's an infinitely better solution! I am not very familiar with working with VBProject objects and was surprised I couldn't find a "Find" function or something similar. I will update my answer - thanks!!
@Mikegrann because Module.Find works off plain text - and blind string search is outright dangerous. Using the VBIDE API to retrieve the line a specific procedure starts at is the only safe way to locate a procedure, short of actually parsing the code with a formal grammar. Module.Find is not able to tell a variable from a procedure or a keyword: it will eventually break, even with the "whole word only" flag set. I know, I spent the better part of the last two years programmatically parsing VBA code.
Basically all it takes to break a text search, is a tiny little innocuous comment at the top of the module that says 'Sub MacroMain will be modified by external code, do not rename!, and there you go, the actual search result you wanted is now the 2nd result and you have no programmatic way of knowing. If you're searching "manually" with the API, that comment can't break anything.
Whereas, by reading the module text to an array, you can validate that the line matches exactly with what you expect. I will update option 2 accordingly - I learned something new today, thank you!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.