Closed workbooks still appear in VBE?

[robotman]

I'm still having a problem of when I close a workbook (with or without
macros), a phantom copy of the workbook remains in the VBE.

If I open and close the same file 10 times, I get 10 phantom workbooks
in the VBE. The dangerous thing is that you can actually look at any
code and edit it even though there's no way to save the actual image.

The only way to get rid of these phantoms is to exit Excel and come
back in (which I'm forced to do constantly).

I know this has been discussed before, but I haven't really seen any
solutions or explanations. Has anyone figured out what causes this
phantom problem?!

Thanks!

John

 

[robotman]

What do you mean "google desktop version"? What is the "google
desktop application"?

I'm talking about Excel workbooks in the Visual Basic Editor ... are
they affected by something by Google?

Thanks.

John

 

[Dave Peterson]

I think Tskogstrom meant Google Desktop Search.
http://desktop.google.com/

An early version of this program was accused of causing the problem you're
seeing. Newer versions haven't been accused (as far as I've seen).

I don't have a reason why these things occur. I do know that if they bother me,
I can close excel and restart it. But the few times I've seen them, they
haven't bother me enough to do that. It sounds like they bother you more than
they bother me.

 

[robotman]

A lot of my macros open and close several files so the phantoms build
up quickly in the VBE when I'm testing my macros. It basically forces
me to close and re-open Excel with every test.

It would be nice to know understand what causes this phantom problem.
I don't use the Google program so that's not the culprit.

John

 

[Jon Peltier]

Dave -

This can in fact be a real bother. As RobotMan says, you don't know which VB
project you should be editing, and you can lose hours of work if you guess
wrong or don't even notice the problem in time.

John -

Do you have any COM add-ins installed? I have heard of this problem arising
when add-ins written in dot net or VSTO don't properly reference and release
resources. Two early versions of commercial sparkline chart tools both
experienced these issues, though I've been assured that both have been
corrected. Remove the add-ins one by one, and check whether the problem
disappears. When it does, you've found the culprit.

- Jon

 

[Dave Peterson]

Hi Jon.

You've shared this with me before <bg>. I guess I continue to be lucky. I
can't remember the last time I saw one of those phantom projects -- especially
in xl2003 (if there's a difference????).

Here's hoping your tips help John (with an H).

 

[Gord Dibben]

I can replicate this by having two windows with the same workbook.

Hide one instance and close the other.

The workbook still appears in VBE because a hidden window is present.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I think that this isn't a problem. The project will still appear in the VBE
with any hidden workbook/addin.

These phantom projects actually appear when the workbook is closed (not just a
window to the workbook).

 

[robotman]

Yes... the problem is when you actually CLOSE the workbook, not when
you just have only hidden sheets in a still open workbook . The VBE
actually shows the same phantom workbook name over and over in the VBE
(which shouldn't even be possible). And as mentioned, you can edit
any of the phantoms and completely waste your time since there's no
way to save the phantoms. It is some sort of resource release problem
as Jon mentions.

Jon, what is a "COM" add-in? I don't have any special add-ins from
the Tools -> Add-ins menu or in the Add-in Manager in the VBE. Is
there somewhere else to check? How do I uninstall a COM add-in?

I have one unique add-in called "XLFit", but I don't see where that is
being loaded. It checks for certain formulas in a spreadsheet, and if
detected, it then loads the main mother add-in to provide
functionality. The main add-in is an extremely complex charting macro
which sounds like may be similar to the Sparkline chart tools (??).

Thanks for your help! This problem is a big hassle when testing and
would be great to figure out.

John

 

[Dave Peterson]

In xl2003, you can add an option to the tools menu (or whereever you want).

Tools|Customize|Commands tab
Scroll down until you see "COM Addins..."
Drag it over the Tools item until the dropdown opens up.

Then drag it under the Addins option (that's where I put mine).

Then close the dialog and click on that new option.

 

[robotman]

I'm using XL2003.

I'm not seeing "COM Add-ins" as one of the choices under the Command
tab. Any ideas?

What is a "COM Add-in" versus a normal add-in?

Thanks.

John

 

[Dave Peterson]

It's a compiled addin. I guess the code is much more secure (since it's
compiled). And quicker than the equivalent functions in VBA.

But if you don't have any, then this isn't your problem.

If you want to see one in action, you can install Chip Pearson's Symbolizer
addin:
http://cpearson.com/excel/whatsnew.htm

Be aware that this isn't required in xl2003 since there's an Insert|Symbol built
into excel. But you'll see how they work from a user standpoint.

 

[robotman]

I meant that I don't even see the option under Tools -> Customize ->
Command Tab to add a "COM Add-in" function (button).

Is there any other way to see if I have any COM Add-in's without
customizing the menus?

Thanks.

John.

 

[Dave Peterson]

Did you look under the Tools section.

Just a bit more than half way down for me.

 

[robotman]

Hi Dave,

I didn't realize I had to choose the "Tools" category under the
Command Tab. I now see where the "COM Add-ins" is.

I added the button and there are no COM add-in's available. So I'm
back to square one.

Any other ideas what can be causing the phantom workbooks? It is a
strange problem. I'm still suspicious of this other complex charting
macro that I run. Even though it's not loaded when I start Excel, is
there any way it left behind some residual code or setting that is
causing the problem?

Thanks.

John

 

[Jon Peltier]

I'm not sure the COM add-in needs to be installed in or visible to Excel, or
even that it has to be a COM add-in. It was that way for my computer, and
removing the add-in made the problem go away, so I surmise the add-in caused
the problem. That may not even be true.

- Jon

 

[Dave Peterson]

I don't have any guesses.

I've never seen anything but guesses (like Jon posted). And many of those are
what Jon explained: I removed xxx and the problem went away.

But maybe that was just coincidence.

 

[NickHK]

FWIW, this is (some of) the output from Process Explorer for Excel 2002 on
Win2K. Never had the phantom problem.
http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx

Maybe see how it compares to yours. Note I have Acrobat and Winfax installed
I have no idea what a .box file is though. Anyone ?

NickHK

Process: EXCEL.EXE Pid: 796

File C:\Documents and Settings\Nick\Desktop
File C:\Documents and Settings\Nick\Application
Data\Microsoft\Excel\XLSTART\PERSONAL.XLS
File C:\Documents and Settings\Nick\Local Settings\Temp\~DFA732.tmp
File C:\Program Files\Microsoft Office\Office XP\Office10\EXCEL.EXE
File C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
File C:\WINNT\system32\FM20.DLL
File C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL
File C:\WINNT\system32\STDOLE2.TLB
File C:\Documents and Settings\Nick\Local Settings\Temp\568.tmp
File C:\Documents and Settings\Nick\Local Settings\Temp\~DFA4F8.tmp
File C:\Documents and Settings\Nick\Application
Data\Microsoft\Excel\XLSTART\PDFMaker.xla
File C:\Documents and Settings\Nick\Local Settings\Temp\~DFA94D.tmp
File C:\Program Files\Adobe\Acrobat 6.0\PDFMaker\Common\AdobePDFMakerX.tlb
File C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
File C:\Documents and Settings\Nick\Local Settings\Temp\VBE\MSForms.exd
File C:\Program Files\Microsoft Office\Office
XP\Office10\XLStart\WFXL2000.XLS
File C:\Documents and Settings\Nick\Local Settings\Temp\~DFB806.tmp
File \Device\KsecDD
File C:\Documents and Settings\Nick\Local Settings\Temporary Internet
Files\Content.MSO\8F41B51E.emf
File C:\Program Files\Common Files\Microsoft Shared\Smart Tag\FNAME.DLL
File C:\Program Files\Common Files\Microsoft Shared\Smart
Tag\Lists\STOCKS.DAT
File C:\Program Files\Common Files\Microsoft Shared\Smart Tag\MOFL.DLL
File C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
File C:\Documents and Settings\Nick\Application
Data\Microsoft\Forms\EXCEL.box
File C:\Documents and Settings\Nick\Local Settings\Temp\~DFD533.tmp
File C:\Documents and Settings\Nick\Local Settings\Temp\~DFD53E.tmp
File C:\WINNT\system32\FM20.DLL
File C:\Documents and Settings\Nick\Application
Data\Microsoft\Forms\MSComctlLib.exd
File C:\Documents and Settings\Nick\Local Settings\Temporary Internet
Files\Content.MSO\246E2687.emf
File C:\Program Files\MZTools\MZTools3VBA\
File C:\Documents and Settings\Nick\Local Settings\Temp\~DFC03C.tmp
File C:\WINNT\system32\MSVBVM60.DLL
File C:\WINNT\system32\MSCOMCTL.OCX
File C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL