1

I have this strange problem. I have a vba project, where the project explorer displays more than one workbook object. It is exactly the same as in this SO question, but I don't have any faulty references to uncheck.

However, I do know what's caused it and I'm sure you can all duplicate. What I did was use the codename for a sheet as a byref argument for a simple sub and at the end of the sub, nulled the worksheet object. So I nulled the whole sheet through the reference.

Something like:

Option Explicit

Sub test_1()
    test_2 sh:=Sheet2
End Sub

Sub test_2(ByRef sh As Worksheet)
    Set sh = Nothing
End Sub

If you run test_1, it will run without problems. But after that, the 'Sheet2' codename is invalid. And if you close and reopen the workbook, you'll see what I mean. Excel creates a new worksheet with the same name (but another CodeName). Any data stored in the cells of the sheet is not lost. The old CodeName references the Workbook Object.

project explorer

I have not found a way to restore or remove the old references so far (other then move all the objects to a new workbook). I am using Excel 2013.

Solution is of course not so null the worksheet, but does anyone have any idea how to restore?

Community
  • 1
  • 1
Jzz
  • 729
  • 4
  • 7
  • Do you have another Excel workbook open? They will both display in the same code editor. – rory.ap Dec 15 '15 at 14:17
  • @roryap Yes I do. And several global templates. But I tested this sample above also on an 'empty' excel instance. Both show the same. – Jzz Dec 15 '15 at 14:18
  • How about just deleting the sheet at the same time using: `Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True` – Luuklag Dec 15 '15 at 15:09
  • @Luuklag not sure what you mean by 'at the same time'? Deletion (right click I'm the explorer) is not possible. – Jzz Dec 15 '15 at 15:12
  • @Luuklag oh, like that. Not sure what that would do. Nulling the sheet is simply a mistake, it was not my intention. However deleting the sheets now the problem exists is not possible – Jzz Dec 15 '15 at 15:15
  • @Jzz does your code really require `sh` to be set to `Nothing` otherwise you could just do without the line in future cases, to avoid this as well. As VBA has its own garbage collection. – Luuklag Dec 15 '15 at 15:22
  • @Luuklag Although it is good practice to do so, and I make a point of always doing so, nulling a ByRef argument / parameter does not make much sense (perhaps there are exemptions). But as I said: it was a mistake and I removed the line already. – Jzz Dec 15 '15 at 16:36
  • no "solution" or at least an explanation of why does this happens? I'm having the same isuue and I'm baffled. – pablete Jan 04 '17 at 22:52
  • 1
    @pablete, no I never found a solution, other than moving all my code to a new workbook and deleting the old. – Jzz Jan 05 '17 at 05:05

1 Answers1

-1

Well, after suffering this weird behaviour of Excel I can finally say that I have tried man times and that I can confirm that the cause (at least for my experiments) was indeed using User Defined Functions (UDFs). There is no way of fixing a Workbook once the extrange sheets appear. My approach was to just create a new Workbook and copy all the Modules and all the sheets from the old (broken) one.

Just make sure to not use user defined functions if you are having this issue.

pablete
  • 1,030
  • 1
  • 12
  • 21
  • I'm wondering why was this answer downvoted but no alternative answer was provided. At least a reason for the downvote would be apreciated, we all could learn something from it. – pablete Jun 14 '17 at 14:19