Test if range exists in VBA

Question

I have a dynamically defined named range in my excel ss that grabs data out of a table based on a start date and an end date like this

=OFFSET(Time!$A$1,IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),1,MATCH(Date_Range_End,AllDates)-IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),4)

But if the date range has no data in the table, the range doesn't exists (or something, idk). How can I write code in VBA to test if this range exists or not?

I have tried something like

If Not Range("DateRangeData") Is Nothing Then

but I get "Runtime error 1004, method 'Range' of object '_Global' failed."

Answer 1

Here is a function I knocked up to return whether a named range exists. It might help you out.

Function RangeExists(R As String) As Boolean
    Dim Test As Range
    On Error Resume Next
    Set Test = ActiveSheet.Range(R)
    RangeExists = Err.Number = 0
End Function

Answer 2

This is another approach. It has the advantage to take the container and the name you want to test. That means you can test either Sheets Names or Workbook Names for example.

Like this:

If NamedRangeExists(ActiveSheet.Names, "Date") Then
    ...
Else
...
End If

or

If NamedRangeExists(ActiveWorkbook.Names, "Date") Then
   ...
Else
   ...
End If

---

Public Function NamedRangeExists(ByRef Container As Object, item As String) As Boolean


Dim obj As Object
Dim value As Variant

On Error GoTo NamedRangeExistsError:

    value = Container(item)
    If Not InStr(1, CStr(value), "#REF!") > 0 Then
        NamedRangeExists = True
    End If
    Exit Function

Exit Function

NamedRangeExistsError:
    NamedRangeExists = False
End Function