Named ranges disappearing in Excel 2007

[Mike]

I have some named ranges (no more than 15) in a workbook and I am assigning
their values to string variables. For some reason my named ranges
are randomly disappearing after my code runs.

The syntax in VBA looks like this:

strDate = Worksheets("SheetName").Range("NamedRange1").Value

Any ideas?????

 

[Bill Renaud]

Are you deleting the cells of the named range somewhere in your code? The
named range will refer to 'Worksheet Name'!#REF! after the delete, and will
be invalid the next time you try to use it.

You will have to step through all of your code to figure out where this is
happening.

You might consider including an error hander and doing it like this:

Public Sub Test()
Dim rngNamedRange As Range

On Error Resume Next

Set rngNamedRange = ThisWorkbook.Names("NamedRange").RefersToRange

If rngNamedRange Is Nothing _
Then
MsgBox "'NamedRange' has disappeared!", vbCritical + vbOKOnly
Else
'Continue processing.
End If
End Sub

 

[David J Richardson]

I have some named ranges (no more than 15) in a workbook and I am
assigning their values to string variables. For some reason my named
ranges are randomly disappearing after my code runs.

I have seen this happen recently, but only once. I don't know whether
the disappearance was related to code running or not.