How to use VBA to comment out a line of VBA?

[MarkAtlanta]

I've seen VBA code that deletes a module, and code that deletes VBA lines, but nothing that comments out a line of VBA code. How do I do it?

I would want something like:

Sub commentout()
     Macro1.line1.commentout
end sub

sub Macro1
    Call Macro2
     Range("A1").value = 123
end sub

Macro2 is in a module that I am going to delete via VBA, but when I run Macro1 again, it wouldn't compile because Macro2 isn't defined anywhere, so it would be great to commentout that line, that way it would compile because now I have

sub Macro1
    ' Call Macro2
    Range("A1").value = 123
end sub

Thanks

 

[Iridium]

What's your code and is there a condition associated with the comment out? I assume you're using a macro to create the code but for what purpose? Will the 'Comment Block' button in the VBE not suffice? A bit more info pls

 

[Mark O'Brien]

I am at a complete loss for what you are trying to do. Why are you trying to comment out code during run-time? More to the point I suppose, why are you deleting code at run time?

 

[Tom Urtis]

Does this thread have anything to do with this
http://216.92.17.166/board2/viewtopic.php?t=65735

 

[MarkAtlanta]

Tom, you are correct in that they are related. In one question I am asking why the Remove.Module1 and Save doesn't work in my Workbook_activate macro (which in my below example I called Macro1). Getting that to work would be my first choice. In this post, I am exploring a less than desireable alternative. That alternative would be to have some sort of commentout code in Macro2 (which is in Module1, which I want to delete for security purposes) to comment out the Call Macro2 line in my Macro1 example. Without commenting it out, after I delete Module1, and I have a workbook_activate event, the macro bombs. This is because the code won't compile because it can't find that macro. It must be doing runtime compiling each time it fires.

An another alternative, which I believe you Tom answered in my other thread, is to delete the line of code that calls the macro (the Call Macro2 line in my Macro1).

In that other post I explained more fully what I am trying to accomplish.

So back to my question here for you Excel MVPS - how would I use VBA to comment out a line of VBA code? Even if you can't comprehend exactly why I want to do it, what tricks can you all conjure up that will do this commenting out?

Quote: "Will the 'Comment Block' button in the VBE not suffice?Will the 'Comment Block' button in the VBE not suffice?" No, because that is what a programmer uses manually while writing code, whereas I want the code as it is running to comment another line out. Use once and disable.

 

[Yogi Anand]

Hi MarkAtlanta:

This may not be answering your question -- however why not consider using a conditional GoTo statement if you want to skip certain lines of code from being executed (instead of commenting those out at run_time) -- granted this will be encouraging one to use spagheti code, but if there is a good reason to do it, I don't think that should be undesirable.

 

[MarkAtlanta]

Yogi - I can't use conditional formatting. I've tried. What happens is that the Call Macro2 line still remains, and when an auto_activate event happens, it attempts to compile the code, and I get a can't compile error, which happens because Macro2 is now a call to something that Excel doesn't know anything about, which it doesn't allow. And if the code won't compile, it won't execute.

 

[MarkAtlanta]

I'm looking for something like this...

sub workbook_activate
    Call Macro2  'which is in Module1
    Call customtoolbar
    Activeworkbook.Save
end sub

'Module1
Range("A1").value = 123 'in reality, I run a data query here
Macro1.line.commentout
end sub[/code]

 

[Yogi Anand]

Hi MarkAtlanta:

I did not say Conditional Formating, I had said Conditional GoTo -- meaning that under a certain condition (for example when you do not want Macro2 to be executed), you use the GoTo statement to skip and bypass the relevant lines of code, and then the code starts executing after the skipped lines of code.

 

[Mr Walker]

This is what I came up with whilst mucking around.

This will comment out a line

Sub testcomment()

Dim Line$

Line$ = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.Lines(7, 1)

Sheet3.Select


With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
    .DeleteLines 7
    .InsertLines 7, "'" & Line$
End With


End Sub

This will comment out the "Sheet3.select line

I'm still not 100% sure why you'd want to do this really, but I hope this helps.

Edit:

Something a little more elegant and intuitive.

Sub test()

Dim Line$
Dim FirstLine As Long, LastLine As Long, x as long
Dim VBComp

Set VBComp = ThisWorkbook.VBProject.VBComponents("ThisWorkbook")

FirstLine = VBComp.CodeModule.ProcBodyLine("Workbook_Activate", 0)
LastLine = FirstLine + VBComp.CodeModule.ProcCountLines("Workbook_Activate", 0) - 1

For x = FirstLine To LastLine
    Line$ = VBComp.CodeModule.Lines(x, 1)
    If VBComp.CodeModule.Lines(x, 1) = "Call Macro2" Then
        With VBComp.CodeModule
            .DeleteLines x
            .InsertLines x, "'" & Line$
        End With
    End If
Next

End Sub

This will go through the THis Workbook module, find the Workbook_Activate event, go throught the lines of the code and comment out the line that contains "Call Macro2".

It should be entirely possible to scour through every single line of code in every module looking for "Call Macro2", but I have to run, so I haven't really got time to play around with it. I have done something a bit like this previously, but it's at work I'm afraid. HTH.