Set "xxxxx" = Nothing not working

[rpinxt]

Dear experts,
I have some code setting a variable workbook as a variable:

Please Login or Register  to view this content.

problems was that when I used VBA to close the workbook after it was not needed anymore it still would remain visible in the VBAProject tree (Alt+F11).
Not a really big problem but still would be better if that was cleaned up.
So I read that one then needs to use Set "name" = Nothing and that you need to do it before you close the workbook

So I tried with this:

Please Login or Register  to view this content.

But unfortunately without succes.
Anybody that sees where I am going wrong?
Thanks!

[TMS]

Not tested, but maybe:

Please Login or Register  to view this content.

Regards, TMS

[ragulduy]

Can you describe, "without success" in more detail? What doesn't happen that should? Do you get an error message, for what it's worth, it's not really neccessary to clean up variables in VBA, it does a good job of it automatically (other languages sometimes do not though).
Anyway, I'm pretty sure you just need:

Please Login or Register  to view this content.

[rpinxt]

Hi TMShucks thanks for the suggestion but unfortunately not working

[rpinxt]

Hi yudlugar, no there are no error messages. But in the VBAProject tree still the workbook is visible which I closed.
On the internet I read that this was caused by the Set statement you use.

[ragulduy]

Sounds like an issue with your application not your code. There is nothing wrong with using activeworkbook.close as far as I know.

If you haven't already, I'd try restarting excel/your computer.

Also, have you recently installed any new add-ins? Do you have any add-ins for excel at all?

[rpinxt]

No I think the application is ok. When you open with VBA code a workbook this workbook also shows in the Projec-VBAProject in you VBA editor. Then when you close that workbook again it also gets removed from the VBA editors VBAProject pane (most left pane).
But for me it did not. And using google I saw it was more common problem which gets caused by the statement Set one uses in his VBA.

I used

Please Login or Register  to view this content.

Now appaerently if you use this then when you close the workbook it will remain in the VBA editor.
And then you should use something like:

Please Login or Register  to view this content.

to remove it (clean it up in the VBA editor)

[ragulduy]

Yes, but that doesn't work for you...

You are using the set bbwb = nothing statement correctly, although it should probably come after you close the workbook, as suggested by TMShucks.

[ragulduy]

For what it's worth, I can replicate this if I step through my code, so before setting bbwb = nothing at the end of the code and after closing the workbook it is visible in my project tree, and using set bbwb = nothing will remove it.

However, when the vba procedure finishes the variable is automatically set to nothing and the workbook dissappears from the project tree. So it is only noticeable if I step through my code.

It might be helpful if you can upload the workbook with all the code in (take out any sensitive data, probably not needed) and I can see if I get the same issue running on my machine.

[rpinxt]

Well I tried the code of TMShuck. So also with set nothing behind the closing of the book but then still the variable workbook remains open in my excel vba editor

[ragulduy]

Which is why I think it is not your code that is the problem. The whole not using set = nothing thing being the cause of the problem sounds like rubbish to me anyway but whatever.

Do you have any add-ins installed?

[rpinxt]

Well ok, I will upload 2 books. One main with the code and the other workbook which will be openend.
You then will have to adjust the paths were it picks up everything but you probably know that better than me
Thanks!

[ragulduy]

It works ok for me, the file dissappears on the = nothing statement if I have it after the .close or on the .close statement if I already set the variable to nothing. Without the =nothing it dissappears once the procedure has finished.

I am using excel 2003.

[rpinxt]

Hmm Ok. Well I am using excel 2010. Not sure if that matters. The macro work fine but I just was annoyed by the fact that it remained in the VBA editor.
And because I am testing the code the list with appearances became longer and longer
You need to close excel entirely to get it empty again.

Oh well, not a very big issue as said.
Thanks for your time!

[TMS]

If it's any consolation, I have seen this phenomena ... and nothing to do with your code/experience.

You can close a workbook and its code pages still appear in the VBE. You can click in them and copy code, etc.

I have no idea what causes it or what the solution is.

Whatever, protect your project with a password and no-one will ever see it

Regards, TMS

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Andy Pope]

http://www.excelforum.com/excel-prog...dow-popup.html

ghosting entries can be caused by certain 3rd addins.

[rpinxt]

Ok Andy. Well I'm on a corporate excel and yes there are addins. So I will just accept that it is how it is for me.
Thanks

[rpinxt]

Ok...I found the problem. Still wanted to share it because maybe someone else also can benefit.
I found a COM Add-In which when i switched it off made that my problem described in earlier posts has been solved.

The COM Add-In I had to disable is "Oracle SOP Excel Control for Excel"

[ragulduy]

Glad you got it sorted and thanks for posting the update.