Find invalid names via VBA

[efree]

Using VBA, how do I identify invalid dynamic range names?

For Example, I would like to flag the following:

"TargetRng" is defined as OFFSET(StartPoint, 0, 0, 1, 100) where
"StartPoint" is undefined.

Thank you for your help

 

[Dave Peterson]

If your names always refer to ranges, then maybe something like this:

Option Explicit
Sub testme()

Dim testRng As Range
Dim myName As Name

For Each myName In ActiveWorkbook.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = myName.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'doesn't refer to a real range
MsgBox myName.Name
End If
Next myName
End Sub

But this breaks down if you have names that refer to constants (for example).

 

[efree]

If your names always refer to ranges, then maybe something like this:

Option Explicit
Sub testme()

Dim testRng As Range
Dim myName As Name

For Each myName In ActiveWorkbook.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = myName.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'doesn't refer to a real range
MsgBox myName.Name
End If
Next myName
End Sub

But this breaks down if you have names that refer to constants (for example).

Thank you for your help.

As it were, my workbook does, indeed contains named ranges, constants,
and formulas. How do I extend the methodology to accomodate those?

 

[Dave Peterson]

I don't have a good answer.

If the range that StartPoint pointed at was deleted, then that reference becomes
an error. But the formula that used StartPoint still looked valid.

Maybe you could dump all the possible errors to a worksheet and just eyeball
them?

 

[Dave Peterson]

I see you have another thread going. I'll let you resolve it in that other
thread.

 

[efree]

I see you have another thread going. I'll let you resolve it in that other
thread.

Thank you for your help. Definitely hight value!

Best Regards.

Eduard