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

 

[Tom Urtis]

Well that's basically the same solution I gave in the original thread. This is basically a duplicate thread of
http://216.92.17.166/board2/viewtopic.php?t=65735
where the solution was given earlier today.

 

[MarkAtlanta]

Kind of like

This is more kind of what I want...

sub workbook_activate
    If Range("A1").value <> 123 Then     'used on first iteration only
       'I could use a if Module1.Exists type test
       Call Macro2  'which is in Module1
       Activeworkbook.Save
       Application.Close 'which closes Excel
    End if
       Call customtoolbar   'used on subsequent iterations
end sub


'Module1
sub Macro2()
     Range("A1").value = 123  'in reality the data returned
     'by my query table creates a value I can test against
     Macro1.lines1-5.commentout  'that would comment out the IF statement lines in 
     'workbook_activate or that would delete those lines
     'other code for formatting is here
     'other code for data manipulating is here
     'needed worksheets get inserted by code here
     'calculations are made and converted into values by code here
     'etc
end sub

 

[Tom Urtis]

So back to my question here for you Excel MVPS - how would I use VBA to comment out a line of VBA code?

Did you see my solution earlier on your duplicate post? If it did not do the job, why not?

 

[Mr Walker]

Oops. Sorry Tom. I didn't read the duplicate thread.



Mark Atlanta, I have edited my original post, and changed the code a bit. HTH.

 

[MarkAtlanta]

Tom I saw it but I am too tired right now to fully consider/test it. Rest assured I will, and I very much appreciate your (and everyone else's) efforts. My first thoughts are that it won't work. But until I test it, I won't know. You changed the workbook_activate to a workbook_open, and I wanted _activate so that when they click on another spreadsheet, the customtoolbar goes away and when they click back it reappers. So I could probably have a workbook_activate just for the customtoolbar and a workbook_open for the call to Module1. I didn't want a workbook.save on the before_close event because once the user gets the workbook, they should have the choice of whether to save or not, and this forces a save. But as I am writing this, if only the call the Module1 is in the workbook_open macro, then can I have Macro2 delete the workbook_open macro? That way it fires once, and when opened then on, it can't fire Macro2. But how do I delete Module1 then? Tom, I will give your solution my full attention when I am rested.

Yogi, I mistyped. I understood your point. Sorry for causing confusion. Tired. I tried conditional goto for the code get around the line calling Macro2 after it fires the first time, but it won't compile on the next workbook_activate event because it is a reference to a Macro that doesn't exist.

Maybe since the thisworkbook_activate was still in the middle of the IF statement when it saved the workbook, when it exits the IF and finishes the sub, Excel thinks there were changes subsequent to the save.

Mr Walker, just went back and read your revised code and it looks good. When I am rested I will test it out. Thank you.

 

[Tom Urtis]

My first thoughts are that it won't work. But until I test it, I won't know. You changed the workbook_activate to a workbook_open, and I wanted _activate so that when they click on another spreadsheet, the customtoolbar goes away and when they click back it reappers.

Well just change the Open to an Activate event and it should be fine. It tested for me no problem. Duplicate posts cause well-meaning people to unnecessarily spend time on a problem that's been answered or is in the process of being answered by someone else. That does a disservice to the question answerers on this board, and to the other posters in the meantime who are not getting their unsolved questions attended to.

 

[Mr Walker]

I have to say that while I agree with you Tom re: duplicate posts, in this instance, having missed the original thread, I found it a bit intriguing, and enjoyed nutting out a solution.

 

[Tom Urtis]

Well that's fine for you that you enjoyed the experience, though you are free to do so with any post. Meanwhile, three other people and I were retracing each other's steps, which can get annoying. Anyone who answers questions are freely giving of their own time, as you did, to assist others in the good faith that the question at hand is not being cross posted. My point was that cross posting a question is an inconsiderate practice that wastes most people's time, and the real sufferers are other posters whose problems are not being solved because of the diversion to a duplicate question that has been solved, or at least is being worked on in another thread.

 

[MarkAtlanta]

Sorry if I you all think that I double-posted. I've been asking several questions here as I progress on my project. Yes the posts are of benefit to the one project I'm working on, but I thought that I had made sure that the questions I asked were not about my project but were generic and on different topics. Like this post/topic is about how to use VBA to comment out a line of VBA code. The other thread that is being referred to is about how come a Module.Remove and a Workbook.Save isn't removing the module and isn't saving. I only got into my project-specifics in answer to people's questions. To me, how to comment out VBA code via VBA code is a totally different question than how come a macro that looks like it worked, didn't. So please don't feel that I was trying to monoplize the experts-here's time. I don't need any more lumps on my head than I already get at work and at home.


As to why Save doesn't seem to work, I might have learned some go-arounds, but I haven't learned yet why it behaves as it does. I guess I was hoping that you all would say something like "Gee you left off the second-class variant, you didn't Dim the thingabob, and your Hansel-Gretle routine is lost in the woods."

As to how to comment out VBA code via VBA code, I don't think any answer here yet does that specifically. I think that the suggestions here would delete a line of code and replace it with a blank comment line. Maybe that is the closest I can get to commenting out a line.

But the best thing, is that the many suggestions and questions here are certainly helping move me further along in finishing my project - maybe not in the path I intended, but if the outcome works, that is 99% of the battle. And if I post another question, I hope you all won't chase after me with pitchforks.


If you all are interested, I might post here the code for what I decide to do. Maybe you all can ooh and ahh over its brillance, or you might want to offer further enhancements.

 

[Mr Walker]

Actually Mark, my solution will indeed comment out the line rather than deleting it.

It can be quite easily tweaked to scour every line of code and comment out every instance of "Call Macro2" if you so desired.