No named ranges are working anymore

[CH64]

I have a large workbook with several sheets, and a hundred or so single cells defined as named ranges so that I can easily reference them from VBA. In the beginning this worked fine, but for some reason it stopped working. Suddenly I'm not able to reference the named ranges anymore, the result is just empty.

If I open a new blank workbook and use the following simple example it works as supposed, the value of the single cell with the range name "testrange" is displayed in a message box. But if I do the same thing in my existing workbook the message box is empty.

Sub Test()
Dim var As String
var = Range("testrange")
MsgBox (var)
End Sub

To me it seems as if I've somehow managed to break all named ranges in my existing workbook. I will be very grateful for all ideas about what might have caused this.

 

[VoG]

If you adjust the zoom setting on a sheet to less than 40% all of the named ranges should be displayed on the sheet. Do they show up if you do that?

 

[CH64]

Yes, the names of the named ranges show up when I zoom out to 39%. Is that an indication of some special issue, or does it just mean that the named ranges are defined as they should?

Thank you very much for your help.

 

[VoG]

So clearly the named ranges are still there. Does this work?

Sub Test()
Dim var As String
var = Range("testrange").Text
MsgBox var
End Sub

 

[CH64]

No, it does not, but now I actually get an error message. However, I think that is because this time I created a new Sub test instead of trying the same code in my current Sub that contains "On Error Resume Next".

The error message I get is just a dialog box that says "400" and nothing else if I run the Sub Test from Excel.

If I run it from VB instead I get the following message:

"
Run-time error '1004':
Application-defined or object-defined error
"

 

[VoG]

Very odd. Assuming that your named ranges each refer to single cells, try running this

Sub List_Names()
Dim Nm As Name, I As Integer, ListSh As Worksheet
I = 1
Sheets.Add after:=Sheets(Sheets.Count)
Set ListSh = ActiveSheet
ListSh.Cells(1, 1).Value = "Name"
ListSh.Cells(1, 2).Value = "Refers to"
ListSh.Cells(1, 3).Value = "Value"
ListSh.Range("A1:C1").Font.Bold = True
For Each Nm In ThisWorkbook.Names
    I = I + 1
    ListSh.Cells(I, 1).Value = Nm.Name
    ListSh.Cells(I, 2).Value = Right(Nm.RefersTo, Len(Nm.RefersTo) - 1)
    ListSh.Cells(I, 3).Value = Nm.Value
Next Nm
ListSh.Columns("A:C").AutoFit
End Sub

 

[CH64]

That seems to work as supposed, it makes a list of all the named ranges, the cells they refer to and the content of each.

Very strange, but this is the first VB code I've seen since the problem appeared that is actually able to use named ranges so I think you have found something here. I very much appreciate your help.

 

[VoG]

You are welcome but I do not understand what the underlying problem is with your workbook, and why your code generates an error and mine doesn't.

Can you post the full code for the sub that gave you a 1004 error.

 

[CH64]

It was the small test Sub that you wrote that gave me the 1004 error:

Sub Test()
Dim var As String
var = Range("testrange").Text
MsgBox var
End Sub

 

[VoG]

I cannot generate a 1004 error with that. Does this give the same error?

Sub Test()
Dim var As String
var = Range("testrange").Value
MsgBox var
End Sub