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

 

[steefq]

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.

Do you know if she’s in the right sheet when the macro starts?

Is this the whole sub or there anything more?

 

[GWteB]

@pbishop, your issue is caused due to VBA's implicitness. When a parent object is not explicitly indicated in the code, VBA tries to fill in this omission (in the background).
Under certain but very rare circumstances, VBA makes the wrong choice or fails at all.
The Names collection object is a member of the Workbook object. In this case, VBA would/should have chosen the ActiveWorkbook object (being a member of the Excel.Application object), which apparently failed.

If the code has to run against the same workbook the code is residing, then it's most likely the code below resolves your issue.

Sub DeleteDeadNames()

    Dim nName As Excel.Name
    For Each nName In ThisWorkbook.Names
        If InStr(1, nName.RefersTo, "#REF!") > 0 Then
            nName.Delete
        End If
    Next nName
End Sub