Link to home
Start Free TrialLog in
Avatar of JohnRobinAllen
JohnRobinAllenFlag for Canada

asked on

Delete a VBA module from a document using VBA

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
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JohnRobinAllen


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.

Will take a look at the time.

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 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.
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.
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.
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.
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?

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.

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

Solution found!!! Chris Bottomley's earlier suggestion to check
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
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

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.