Run Time error 1004 Method 'Names' of object '_Global' failed

[pbishop]

We have this code below. For one user, it seems, she gets the 1004 Method 'Names' of object '_Global' failed the first time she opens this XLSM file. If she closes the XLSM file and comes back in she doesn't get it the second time. If we add Names to a watch it just gives use that same error. I'll attach a screen shot of that. Again though it seems to work for her the second time through and nobody else seems to have the error. Any idea what could be causing this? Also she was on 32bit Excel and got the error and has now been upgraded to 64bit and still gets it.

Dim nName As Name



For Each nName In Names

If InStr(1, nName.RefersTo, "#REF!") > 0 Then

nName.Delete

End If

Next nName



End Sub

 

[Micron]

My guess is that it's due to an unqualified range reference but I/we can't see that code or where the code is located. I've read that if you don't specifically refer to a range and the line raises an error, the _Global object determines what object you're referring to. If code is in a sheet module, it refers to that sheet. If in a standard module, it refers to the active sheet. Perhaps one user uses the wb in a fashion that no one else does (the active sheet is not the expected one), thus raises the error.

Would this not be the same test
For Each nName In Names
If nName.RefersTo = "#REF!" Then nName.Delete
Next

without invoking the Instr function?

 

[pbishop]

The first thing in 'ThisWorkbook' is this call 'Call LoadUpdatedModules' The first thing in that module is a call to this....

Sub DeleteDeadNames()

Dim nName As Name



For Each nName In Names

If InStr(1, nName.RefersTo, "#REF!") > 0 Then

nName.Delete

End If

Next nName



End Sub

Sorry didn't realize I chopped off he sub name in my first post.

Does that help?

 

[Micron]

Specifics in Excel vba is not really my thing - more so generalities, or specifics in Access vba. About all I can do at this point is reiterate - try making your references explicit. Your last post still doesn't clarify where that code is but I'm guessing it's in a standard module, not a worksheet module. So maybe when error is raised, one user has the wrong sheet active when the wb opens (if that's when the code runs), and that could be because the last user (or code) caused that. I'm not saying it's the 'problem' user's fault here. Sorry I can't be more helpful than what I read about _Global object. Part of the reason for trying here, besides trying to help you, is to be in the email loop when/if somebody comes up with a better idea.

 

[steefq]

Hi!

Try this

Change "Sheet1" to your sheet name

Sub DeleteDeadNames()

Dim nName As Name
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

For Each nName In ws.Names

If InStr(1, nName.RefersTo, "#REF!") > 0 Then

nName.Delete

End If

Next nName

End Sub

 

[Micron]

Set ws = ThisWorkbook.Sheets("Sheet1")

Now that's an explicit reference and that's what I've been saying - probably was too vague. Let's hope that's the issue.

 

[steefq]

Now that's an explicit reference and that's what I've been saying - probably was too vague. Let's hope that's the issue.

Thats true,

Also dont forget to always add Option explicit over your sub, that way you cant miss out to define ranges/variables etc

 

[Micron]

Also dont forget to always add Option explicit

Someone once wrote that if you fail to use Option Explicit, you deserve what you get. I couldn't agree more.
And what's true? I was too vague?

 

[steefq]

Someone once wrote that if you fail to use Option Explicit, you deserve what you get. I couldn't agree more.
And what's true? I was too vague?

I've heard that, but there was once a time where i didn't use it.

We have all been beginners

 

[pbishop]

I guess I'm still a little confused. We took over this XLS from someone who wrote it 15 plus years ago and converted it to XLSM and fixed it to work in 64bit Excel. Everything else is working and this only seems to be an issue for one user and she is using the same XLSM that others, who aren't having the issue, are using. I'm not even 100 percent for sure what this sub is doing. If you put Sheet1 or whatever it will only do that sheet correct? With sheet1 is it only doing one sheet regardless? I guess I'm asking with what I sent originally is that looping through one sheet(whatever it's called) or many sheets? I've never coded anything like that in VBA before. Again though it seems to only happen for one user and if she closes and reopens(without saving) it doesn't seem to happen the second time.