Solved

Delete a VBA module from a document using VBA

Posted on 2012-12-20
17
308 Views
Last Modified: 2013-01-02
I think there is a one-word answer to my yes/no question, so I'm giving it only 250 points in case the answer "no." If the answer is "yes" and one gives the solution, the points would naturally go up.
    The question: With Word, can I use VBA in one module to delete a different module in the same document?

    Thanks for any advice.
    J.R.A. in Priddis, Alberta
0
Comment
Question by:JohnRobinAllen
  • 11
  • 4
  • 2
17 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38710633
Without experimenting on your specific request, the answer is probably 'Ja' (to add a third language to your repertoire).

If you want to use early binding (much easier), you need to set a reference to the 'Microsoft Visual Basic for Applications Extensibility' library.

Let us know if you need more help.
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 500 total points
ID: 38710787
Yes

See Deleting a module on Chops page here:

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

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38710788
Chips!
0
 

Author Comment

by:JohnRobinAllen
ID: 38722353
I am working on getting a working model of my program to post. It shows clearly the problem. I hope to post it here tomorrow (Thursday) afternoon.

I would be very grateful for any help. The value of this question if solved, would obviously be the full 500 points. I'm not so sure it is solvable when working in Word.

j.r.a.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38722824
Will take a look at the time.

Chris
0
 

Author Comment

by:JohnRobinAllen
ID: 38724045
Details on my query:
      Since my document contains macros, EE will not let me attach it to a message. I can send it to you if you give me your E-Mail address. If you are willing to look at it, please let me know. Mine is allen@davincibb.net. I can also try to put it into a public Drop Box.
      The document, “Lise Checker,” contains a VBA program that can check a separate Word document to find clichés and trite phrases such as “a total of” or “acquaint yourself with.” The program creates a copy of the original document with all such phrases underlined and in boldface, along with a brief explanation of what is wrong. The user can then use keyboard shortcuts to move back and forth around the boldface items that need revision. The user can also automatically hide the explanations or remove the boldface underlined markings after correcting (or rejecting) the items.
      The goal of the program is to produce a new document with all the corrections and with the keyboard commands described above. To do that, one would normally use a separate template containing the code to move around the document. Here, however, to avoid forcing the user to have an extra file to run with the program, I use the Lise Checker document itself as a template. Here, in more detail, is how I do that now.
      Lise Checker has two modules of VBA code. One named “jra” produces the target document with the highlighting and comments. The other module “lise” has the commands for manipulating the output.
      At present, to use the program one should have no other document loaded than the document to check. (I’ll fix that later so it will show a list of open documents from which the user will choose the document to analyse.) When the user has Lise Checker as the active document and presses Alt + L (for Lise), that triggers a procedure (StartLC) in the lise module that runs code (CheckDoc) in the first, jra, module. The code does its thing to produce the corrected copy of the document to be checked.
      (What it does is not important, but, in brief, it asks the user for permission to mark up a copy of the other document. If the user agrees, the code saves the Lise Checker program (for retrieval later) and then changes the document into a slightly different version of the target document. It photographs (i.e., selects and copies the text to be checked), replaces the text of Lise Checker with the text to examine, saves the document under a slightly different, then it goes through the process of adding the corrections and suggestions. When finished, it reloads the original Lise Checker and control passes back to the lise module.)
      At that point, if possible, I want to delete the no-longer-needed jra module in the newly-created document. That will make the output document easier to save and load, but more importantly, it will keep secret some passwords. The code in Lise Checker will be password protected and the jra module contains another password that I use for decrypting various vocabulary lists the program loads from document variables.
      I would like to keep both passwords private. However the output document that Lise Checker creates has all the original code visible, including the second password stored in the jra module. Since I apparently cannot use VBA to lock access to those modules in the output document, I could still protect myself by deleting the now-useless jra module, if that is possible. The lise module code is trivial and needs no protection.
      I hope someone can show me how to get rid of the jra module with VBA code stored in the lise module.
      Thanks!
      J.R.A.
0
 

Author Comment

by:JohnRobinAllen
ID: 38724109
Another possibility is that I have attached the file in question without the two VBA modules.
If anyone prefers to go this way, I  could send the VBA code for the two modules in a separate posting. You could then manually add them to the document to change it into a *.docm document.
     j.r.a.
Lise-checker.docx
0
 

Author Comment

by:JohnRobinAllen
ID: 38725115
To make Lise-Checker.docx work, one needs to add two modules. This is the first of the two.
My next post will be the second of the two modules.
   j.r.a.
jra.bas
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:JohnRobinAllen
ID: 38725120
0
 

Author Comment

by:JohnRobinAllen
ID: 38725156
If you have added those two modules to the Lise-Checker.docx file, you still need to add the shortcut sequences. The "ShortcutsAddChecker" sub in jra adds the shortcuts to the Lise-Checker. The rest should be automatic. Adding the modules should change the extension to .docm.
     Note that the two modules must be attached to the Lise-Checker.doc and not to a different file. Lise-Checker uses a number of document variables stored in Lise-Checker. For example, the module that adds shortcuts first checks the "ThisDoc" variable in the document. It will not run if "ThisDoc" is not "Lise checker".
     I can send the Lise Checker.docm to anyone reading this who wants to help, but it has those macros that prevent one from attaching the file to a posted comment.
     j.r.a.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 38726136
If I understand correctly you want to delete the code modules from the file that is running the automation and this should still be possible with some judicious use of structure BUT in this way it would not be possible to delete all macros since the executing macro needs to complete.

If the code was all saved to a template that each user had access to ... and the VBA was protected in that file, Not abolutely perfect for security but in most cases acceptable then everyone could use the macro and not have all this hassle.

If more security were required I know the code can be placed into VB though I personally haven't gone this route.

What do you think?

Chris
0
 

Author Comment

by:JohnRobinAllen
ID: 38726728
Thanks for looking at the problem again.
      In re the suggestion that I use a template, the point of my query is to be able to skip the use of a template. If I had to choose between showing my code or using a template, I would choose showing code. There is no big reason for hiding it except that it is a messy "solution" that adds clutter to the output document.
     Of more importance is your first paragraph and the "judicious use of structure." I set up the structure to answer that question. All the stuff that can be deleted when its work is done is in the first module except for the macro that calls that code. That macro is in the second module.
     Working from the second module, that procedure starts all the work in the first module and when the work is done and the module is no longer needed, then control passes back to the original procedure in the second module. There is where I want to put the code that will kill the first module.
     I hope that is of some help. In an earlier post you suggested I look at the site devoted to "Programming in the VBA Editor" when working with Excel. I'll go through that to see if they have any suggestions that could be used when working with Word. Perhaps you already know the answer to that.

J.R.A.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38726819
I agree with Chris that there are probably better ways of doing it, but here is a simple macro to delete all the code lines from a module:

Sub DeleteModuleCode()
    Dim doc As Document
    Dim VBEnv As VBE
    Dim proj As VBProject
    Dim vbComp As VBComponent
    Dim iCount As Integer
    
    Set VBEnv = Application.VBE
    
    Set doc = Application.ActiveDocument
    Set proj = doc.VBProject
    Set vbComp = proj.VBComponents("Module2")
    iCount = vbComp.CodeModule.CountOfLines
    
    proj.VBComponents("Module2").CodeModule.DeleteLines 1, iCount
End Sub

Open in new window

0
 

Author Comment

by:JohnRobinAllen
ID: 38726913
Solution found!!! Chris Bottomley's earlier suggestion to check
     http://www.cpearson.com/excel/vbe.aspx
provided a solution with some tweaking.
     I'll post it here in a couple of hours, after I do some other things and then write out all the changes you have to do to make the Excel solution work with Word.
     Credit goes to Chris Bottomley and the code involved is only five lines long:

            Dim VBProj As VBIDE.VBProject
            Dim VBComp As VBIDE.VBComponent
            Set VBProj = ActiveDocument.VBProject
            Set VBComp = VBProj.VBComponents("jra")
            VBProj.VBComponents.Remove VBComp
0
 

Accepted Solution

by:
JohnRobinAllen earned 0 total points
ID: 38727042
Solution found!!! Chris Bottomley's earlier suggestion to check
     http://www.cpearson.com/excel/vbe.aspx
provided a solution with some tweaking to make it apply to a Word document.
     Here are the directions tweaked and abridged from that site to make it apply to Word documents:

     First, 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.
     Second, go back to Word, not the VBA editor. You must have the Developer tab available. If it is not visible, click on Files, Options, Customize Ribbon, in the Customize Ribbon Dropdown box choose Main Tabs, then put a check mark next to Developer. Click OK to close.
     Once you can see the developer tab, click on it and to the left, in the Code group click on Macro Security and under Macro settings put a check mark next to “Trust access to the VBA project object module.”
     You might want to read the caveats at the web site mentioned above. In particular they say
     “CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details.”
     The document from which you want to remove a module must be unlocked.
     The code to delete a module (other than the module where you put this this code) is
            Dim VBProj As VBIDE.VBProject
            Dim VBComp As VBIDE.VBComponent
            Set VBProj = ActiveDocument.VBProject
            Set VBComp = VBProj.VBComponents("jra")
            VBProj.VBComponents.Remove VBComp

     The above will delete a module named “jra”, so you would want to change that to the name of the module you want to delete.
0
 

Author Comment

by:JohnRobinAllen
ID: 38727339
I'm amazed at how closely Graham Skan's solution parallels the one I proposed. I did not see Graham's suggestion until after I had found the other one.
   His solution has
     "VBComponents.Remove DeleteLines 1, linecount"
   The one I accepted has
     ""VBComponents.Remove VBComp"

My apologies to Graham for not including him in the solutions. I'm glad he posted it for others to use as needs be.

     j.r.a.
0
 

Author Closing Comment

by:JohnRobinAllen
ID: 38736004
I shared the best solution between Chris Bottomley's earlier suggestion and my tweaked code from that suggestion. I'm trying to award full credit to Chris.
   The solution is brief, elegant, and has nothing to do with me other than minor editing.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
Introduction Authors who set out to write any sort of lengthy piece for online submission—be it a long question or comment on a technical form, an article, or a substantial blog entry—often find it useful to work up a draft in an editor other t…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now