Announcement

Collapse
No announcement yet.

Remove A Project From VBA/VBE Editor

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Remove A Project From VBA/VBE Editor



    Once again I'm at the end of my tether with a VBA problem.. Would anyone know if it is at all possible to remove a VBE project from the VBE editor? This is a project that is opened as a read-only workbook, and then converted into an addin (essentially just to hide the worksheets). It therefore shows in the VBE editor but is in neither the add-ins or workbooks collections..
    This file is basically used as a source for data for another charts workbook. I need to clear this out of the VBE editor so as to reload an updated version of this workbook every so often..so it helps to clear the existing one first.. otherwise I end up with multiple instances of the same project...
    Many thanks anyone for any ideas..

  • #2
    Re: Remove A Project From The Vb Editor

    do you want to remove the modules? in each module in vbeditor click file and click remove "modue ........: and in the ensuing window "do you want to export ..." click NO. The module is removed. save the file. next time when you open the file you will not get the option window for enable/disable macros.
    I am not an expert. better solutions may be available. [email protected]$$$gmail.com

    Comment


    • #3
      Re: Remove A Project From The Vb Editor

      another way is in the vbeditor click view-project explorer (or control+R)
      and if you click the + before the file name you get the list of modules and highlight each module and clck remove module ..... and then as said before.
      I am not an expert. better solutions may be available. [email protected]$$$gmail.com

      Comment


      • #4
        Re: Remove A Project From The Vb Editor

        DonMS, Project in the VBE is used to describe ALL Modules in the Workbook. Any Open Workbook will show in the VBE whether it houses code or not.

        Comment


        • #5
          Re: Remove A Project From VBA/VBE Editor

          Thanks for getting back on this and sorry for not making myself clear.. (I am increasingly convinced this is a quirk in Excel) Try opening a new Excel application, then opening and saving two workbooks, say wkA and wkB. Close wkB and using code in wkA do the following..

          1. Open wkB as a readonly.
          2. Turn wkB into an addin. (wkB.isaddin = true)

          Now I defy you to get wbB out of the VB Editor.. I have tried closing it directly, turning it back from an addin to a workbook and THEN closing it.. but all to no avail.. the project wbB remains in the VB Editor as some kind of ghost, and if I actually click on it, I get the message "Out of Memory".. "

          Comment


          • #6
            Re: Remove A Project From VBA/VBE Editor

            Sorry about this.. actually, I've gone through the above example a number of times and it does, in fact, work fine. I think that my workbook might be corrupt in some way, which is why it won't clear itself from the VB Editor.. I'll try moving all the data to a new workbook.. hopefully, this should do the trick..

            Comment


            • #7
              Re: Remove A Project From VBA/VBE Editor

              Got it! By trial and error I have worked out what is causing this.

              Basically, if you bring up two worksheets in a single Excel application and you create and asign a public variable in one workbook that references the other workbook, even if you close the referenced workbook, its VB project remains (ghost-like) in the VB editor.. you can even open its modules although you can't edit them.. (note that if you are testing this all in one procedure, you have to assign the public variable referencing the workbook in a different procedure - not sure why).. anyhow, even if your variable that is referencing the other workbook loses its value, because it has been set, there remains some kind of hidden link between the two workbooks that prevents the referenced workbook being removed from the VB Editor.

              Why is this an issue? Well, in the event that you open a workbook and turn it into an add-in, although it can be assigned directly to a variable through the workbooks collection (set wbA = workbooks(wkB) for example), oddly enough, it does not appear either in the workbooks collection or the add-ins collection. The only collection it appears is in the projects collection of the VB editor. I typically use VBA code to scroll through this collection to check if the file is there and therefore accessible.

              The solution is to remove the reference to the workbook (something like 'set wkB = nothing'). Once this hidden link between the workbooks has been broken, closure of the referenced workbook will remove it from the VB Editor.

              For anyone who has followed this (I hope I have made myself clear), i hope you find this useful..

              Comment


              • #8


                Re: Remove A Project From VBA/VBE Editor

                I also have this problem. Open many excel workbooks, close them and they all still appear in VBE. Then eventually I get Out Of Memory message.
                This solution above did not work. The opened work books do not all have links to another one.
                Is there another solution?
                Is there code to delete/remove/close any/all workbooks listed in the VBE to close when closing open workbook?
                Thank you if you can help, hope this thread is still open some way after all this time

                Comment

                Working...
                X