CODEWITHSUNDEEP
excelvbarangeoffset
Colten J Nye
Colten J Nye

Reputation: 175

Test if range exists in VBA

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."

Upvotes: 14

Views: 80221

Answers (5)

pgSystemTester
pgSystemTester

Reputation: 9932

This is an old post, but none of the rated answers has a dynamic solution to test if a name exists in a workbook or worksheet. This function below will accomplish that:

Function pg_Any_Name(thename As String) As Boolean
Dim n As Name, t As String
   
   For Each n In ThisWorkbook.Names
      t = Mid(n.Name, InStr(1, n.Name, "!", vbTextCompare) + 1, 999)

      If UCase(thename) = UCase(t) Then
         pg_Any_Name = True
         Exit Function
      End If
   Next n

End Function

Worth noting that this would not have worked for this specific question because OP had a dynamic defined range. This question would have been more accurately titled Test if Name is a Valid Range because the name always existed as a formula, the issue was if it was a valid RANGE. To address this question with a solution that checks both workbook and sheets... this function would work:

Function PG_Range_Name(thename As String) As Boolean
Dim n As Name, t As String
  
   For Each n In ThisWorkbook.Names
      t = Mid(n.Name, InStr(1, n.Name, "!", vbTextCompare) + 1, 999)
      
      If UCase(thename) = UCase(t) Then
         On Error Resume Next
         PG_Range_Name = n.RefersToRange.Columns.Count > 0
         Exit Function
      End If
   Next n

End Function

Upvotes: 0

FCastro
FCastro

Reputation: 631

Depending on the application you're doing, it's good to consider using a Dictionary. They're especially useful when you wanna check whether something exists. Take this example:

Dim dictNames as Scripting.Dictionary

Sub CheckRangeWithDictionary()

    Dim nm As Name

    'Initially, check whether names dictionary has already been created
    If Not dictNames Is Nothing Then
        'if so, dictNames is set to nothing
        Set dictNames = Nothing
    End If

    'Set to new dictionary and set compare mode to text
    Set dictNames = New Scripting.Dictionary
    dictNames.CompareMode = TextCompare

    'For each Named Range
    For Each nm In ThisWorkbook.Names
        'Check if it refers to an existing cell (bad references point to "#REF!" errors)
        If Not (Strings.Right(nm.RefersTo, 5) = "#REF!") Then
            'Only in that case, create a Dictionary entry
            'The key will be the name of the range and the item will be the address, worksheet included
            dictNames(nm.Name) = nm.RefersTo
        End If
    Next

    'You now have a dictionary of valid named ranges that can be checked

End Sub

Within your main procedure, all you need to do is do an existence check before using the range

Sub CopyRange_MyRange()

    CheckRangeWithDictionary

    If dictNames.exists("MyRange") then
        Sheets(1).Range("MyRange").Copy
    end if

End Sub

While loading the dictionary may look a little longer, it's extremely fast to process and search. It also becomes much simpler to check whether any named range referring to a valid address exists, without using error handlers in this simple application.

Please note that when using names at sheet level rather than workbook level, it is necessary to use more elaborate keys to guarantee uniqueness. From the way the dictionary was created, if a key is repeated, the item value is overwritten. That can be avoided by using the same Exists method as a check in the key creation statement. If you need a good reference on how to use dictionaries, use this one.

Good luck!

Upvotes: 1

codea
codea

Reputation: 1561

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

Upvotes: 3

Steztric
Steztric

Reputation: 2942

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

Upvotes: 40

nutsch
nutsch

Reputation: 5962

You can replicate the match in your VBA to count before using the range how many rows you would have, or you can use error handling:

On Error Resume Next

Debug.Print range("DateRangeData").Rows.Count

If Err = 1004 Then
    MsgBox "Range Empty"
    Exit Sub
Else
    MsgBox "Range full"
End If

Err.Clear
On Error GoTo 0

Upvotes: 19

Related Questions