Find invalid names via VBA

  • Thread starter Thread starter efree
  • Start date Start date
E

efree

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

For Example, I would like the following to be flagged:

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

Thank you for your help
 
Dim rng as Range
On Error resume Next
Set rng=Range("TargetRng")
On Error Goto 0
If rng is Nothing then
MsgBox "Error"
Else
Whatever
Endif

HTH
 
Ardus said:
Dim rng as Range
On Error resume Next
Set rng=Range("TargetRng")
On Error Goto 0
If rng is Nothing then
MsgBox "Error"
Else
Whatever
Endif

HTH
Click to expand...

Thanks for that. Almost there...

In adapting the methodology to my workbook at large, via:

For Each n In ActiveWorkbook.Names
....
Next

Along with invalid dynamic ranges, the subroutine flags all of the
names defined for formulas because, strictly speaking, those do not
refer to ranges. For example:

"oftenUsedFormula" - name defined as COUNTIF(A1:A500, "< 6")

Any thoughts?
 
Back
Top