Microsoft Add-in Causes VBE Ghost Project Issues

Recently I was introduced to this really cool add-in by a vendor: SQL Server 2005 Data Mining Add-in for Office 2007. It's a pretty neat tool that you can use to analyze relationships within data.

Unfortunately, as cool as this add-in is, it has wee little issue with it. It creates ghost projects in the Visual Basic Editor. What I mean is this…

When you open Excel, a new file called Book1. Go into the VBE (press Alt+F11) and you'll see VBAProject(Book1) in the list. What should happen is that when you close Book1 in Excel, it should go away in the VBE as well. With this add-in installed, it doesn't. The workbook closes in Excel, but it hangs around in the VBE. You can't do anything with it, because it doesn't really exist, but it just loiters there.

You should also never be able to see the same file name multiple times in the VBE, as you can't open the same file more than once in Excel. The picture below is a mock-up of what I saw, as I've fixed the issue, but this is what it looked like.

(Note: My issues were not with Book2, but rather with one of my own files that was listed several times.)

I've been having issues with a certain procedure I've been writing all day, and I'm not sure if this was affecting it or not. I suspect that it was a bit, although I can't prove it, as I was opening and closing the same file repeatedly in testing, and seeing some weird results… almost like it was pointing to a former copy or something. Again, I can't prove it, I just suspect it is all.

I also don't know if it would affect a normal, non-code hungry user at all. If you're a developer though, it may be something you want to know about.

7 thoughts on “Microsoft Add-in Causes VBE Ghost Project Issues

  1. This problem cropped up frequently a few years ago. One of the side effects was that you could apparently modify the code in the phantom projects, but such changes could not be saved, leading to hours of lost work. Closing Excel and restarting it will temporarily stop the problem, but it will recur.

    I don't recall the details, but it was related to a .Net or VSTO apps interacting with Excel or with the desktop, and not properly releasing all of the resources it had accessed.

    I suspect that this data mining add-in has this problem. Uninstall it and the problem will vanish. Then put pressure on the developer to correct this bug.

  2. Interesting... I wasn't able to modify the code, although I was able to open the phantom VBE projects. Trying to activate the worksheets yielded an "ActiveX Can't Create Object" (or something quite like it) error.
    I'm going to see if I can find out who to send it to at MS. I don't think it's an Excel problem, so...

  3. Awesome, thanks Jamie!

    FYI, for others... Jamie has a blog post on one of the features that I thought was really cool. Using the tool he describes here, I was able to look at the relationship between golfing sales. Based on the data I was using, the most likely thing that a golfer is to buy when they rent a cart (after green fee) is beer. (Balls, tees, gloves all come later.) Pretty neat tool. 🙂

  4. Pingback: The Ken Puls Blog » Blog Archive » Breaking Links in Excel

  5. This is a very common problem. The easiest way to replicate it is to build a query to another sheet in an active
    file.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *