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

 

[MarkAtlanta]

Mr Walker - I apologize, your code will indeed save the line and comment it out, not just leave a blank comment line. Sorry for having stated otherwise. I blame it on being too tired. Kind of funny that there is method for .insert and for .delete but nothing for .commnet. At first quick glance, I like your first code best. If you are around, please what is the syntax of the line numbering? Like if I want to comment out lines 3-8, is the syntax something like 3,5 where the 3 is the line to start at, and 5 is the number of lines from that point? Either way, I will experiment with it and get it to work. thanks to you and everyone else on this board!

 

[Mark O'Brien]

Mr Walker - I apologize, your code will indeed save the line and comment it out, not just leave a blank comment line. Sorry for having stated otherwise. I blame it on being too tired.

A few lessons learned here, I think.

1. Don't be a ***** to people on this board when they're trying to help you.
2. Actually, try out the procedures provided to you, before compaining about them.
3. You need to get some more sleep.

Besides, the best place I've found for messing around with the VBE is here:

http://www.cpearson.com/excel/vbe.htm

If you've already seen this page then great. If you haven't then you might find it useful.

Truthfully though, you may have met resistance on your requests here because generally people do not like to alter their code at run-time. It's just not good practise. It still sounds like you should rethink your strategy here, but you are too focussed on this one solution. (which isn't always a bad thing)

If you only take two things from this thread, one should be the solution that Mr Walker gave you, the other thing is to not be such a *****. (I mean that in a nice way)

 

[MarkAtlanta]

If you are interested in my "final" solution

In ThisWorkbook:

Private Sub Workbook_Open()
    If Range("DataCount").Value = 0 Then
        Call Retrieve_Data  'which is one of many macros in module 1
        ThisWorkbook.Save
        application.Quit
    Else
        With ActiveWorkbook.VBProject
            .VBComponents.Remove .VBComponents("Module1")
        End With
        Call DeleteOut

    End If
End Sub

Private Sub workbook_activate()
    Call CustomToolBar
End Sub

Private Sub workbook_deactivate()
    Call CustomToolBar
End Sub

Private Sub DeleteOut()
    With ThisWorkbook.VBProject.VBComponents("Thisworkbook").CodeModule
        .DeleteLines 1, 13    'deletes the Workbook_Open macro
        .DeleteLines 10, 6    'deletes this DeleteOut macro, while it is still running!
    End With
End Sub

How it all works

1) The user of the java application presses a button to get their data on a spreadsheet.

2) The AppServer makes a copy of the base template, with a naming convention that is used in the data query.

3) The AppSever then opens that copied file and its workbook_open event fires. Workbook_open fires before workbook_activate.

4) In the workbook_open event, as this is before any data retrieve, the Range DataCount is equal to 0, so it follows the Then path.

5) The Retrieve_Data macro in Module 1 is run. (In earlier postings I had refered to this as 'Macro2'.)

6) Retrieve_Data runs a data query using the parsed out file name for needed information. There are other macros in Module1, but they are all called from within Retrieve_Data and they all only need to run once.

7) When Retrieve_Data is finished, it returns to Workbook_open, Saves the spreadsheet, and closes Excel.

8) The AppServer, when it senses that Excel has closed, then presents the spreadsheet for the user to download (thru the use of save target as - if the user displays thru a web interface, the macros are deleted. We haven't figure that one out yet.)

9) The user saves the file to their own PC.

10) When the user opens the file, the Workbook_open even fires.

11) The workbook_open event follows the Else path of the IF THEN ELSE statement, since DataCount is now not equal to zero.

12) This path, first deletes Module1, then it calls the DeleteOut macro.

13) The DeleteOut macro deletes the Workbook_open macro, and also deletes the DeleteOut macro. This last one was a surprise to me, because it deletes itself while it is running. I don't know if this is cool or scary that a macro can do this to itself. Like a snake swallowing its own tale until it disappears. I had to either deleteout or commentout the Call Retrieve_Data line of the macro, because, what with Module1 not existing any more, the workbook_open macro wouldn't compile because it contained and a call to a macro that didn't exist. Eventhough that macro call was in a conditional logic branch that the logic would not be going down. Apparently Excel, before it runs a macro each time, compiles the macro. And this workbook_open event was not compiling, which would blow up the macro. I had learned from you all, mainly Mr Walker, how to commentout VBA lines, but I figured if you can commentout or deleteout, then deleteout leaves zero footprint.

14) Now that the Workbook_open macro is finished running, the workbook_activate macro fires.

15) This creates a custom toolbar. I like this macro call in the workbook_activate instead of the workbook_open, because this way the user can bring up other Excel files (I call them spreadsheets) of theirs and the toolbar isn't showing, but when they click back on this file, the toolbar pops up again.

16) When the user closes down Excel or this file for the first time, they are prompted if they want to save their changes, even if they didn't do anything, because behind the scenes these macros were making changes.

17) After they answer Yes or No, the workbook_deactivate event fires and deletes the custom toolbar.


One of the less than perfect things about this approach

A) In Step 10, if the user holds down the shift key when opening their file for the first time, they could then go into the VBE and see the information on Module1. Sensitive information could be revealed. I would rather that Module1 gets deleted in the workbook_open event or as the last lines of the Data_Retrieve macro, but every attempt I tried ended up with a Do You Want to Save Changes message box when the code got to the application.Quit line, eventhough the preceeding line was ThisWorkbook.Save. And the AppServer can't answer that question.


Note
Among other things in the custom toolbar is a macro button for the printing of selected visibile sheets. I got the code from John Walkenbach's excellent website. But I did tweak it a smidgen so that when the macro is over, it returns back to the page from on which the user pressed this button, so that it handles correctly what happens if the user selects sheets but cancels out, so that it handles correctly what happens if the user doesn't select any sheets but presses 'ok', and fixed a DisplayAlerts problem. And I put some of my own tricks in the code such as hiding certain columns from being printed, such as light-yellow highlighting every other row only for printing and not for screen display, so that there is a very-light shading to help the reader read across the page from left-side descriptions to right-side columns of numbers. (they didn't want gridlines on). Fun stuff like that.


Why all these words
I know that this is long, but perhaps someone, someday will need such a solution as mine here, and I thought they might appreciate a full explanation. Before I got this to work all the way through, I tried LOTS and LOTS of iterations. So far only this one works.


Thank yous
If you all Gurus have any suggested improvements, I will ray: to you! You all gave me lots of great things to consider, and even if I didn't use all your suggestions, it just helped to have some people (more knowledgeable than me, that's for sure) to talk to. Thank you.