How to check in VBA if a named range exists?

[JenniferMurphy]

A macro I am working on needs to access one or more auxilliary tables. The table names are formed by prefixing "tbl" to a data field. So if the data field contains "Body Type", the table name will be "tblBodyType".

I want to add code to check that (a) the table name exists and (b) it is a table.

I checked several "solutions", but they all seemed overly complicated. Probably the simplest is this function.

Function NameExists(rName As String) As Boolean
Dim errTest As String
On Error Resume Next
errTest = ThisWorkbook.Names(rName).Value
NameExist = CBool(Err.Number = 0)
On Error GoTo 0
End Function

It probably works, but I didn't test it.

But isn't there a simpler way, something like this?

If range(rName).exists ...

There are dozens of properties accessible after the ".". Surely a way to test if it exists is one of them.

And then if it exists, I need to check that it is a table.

Thanks

 

[Zot]

Will this okay?

VBA check if a name exists

Hi I want to open a file and then pick up a value from a named range but sometimes the range name might not exist. Is there any way to check if the name exists?

www.mrexcel.com

 

[JenniferMurphy]

Will this okay?

VBA check if a name exists

Hi I want to open a file and then pick up a value from a named range but sometimes the range name might not exist. Is there any way to check if the name exists?

www.mrexcel.com

That is a bit simpler that the other function, but it is still a separate function. So I take it that VBA has no simple test for range names. (sigh)

 

[Zot]

That is a bit simpler that the other function, but it is still a separate function. So I take it that VBA has no simple test for range names. (sigh)

The reason to create a function is when you need to use it multiple time. If you just need to use it one in your code flow then no need to create a function. That example is just a sample subroutine. You can use the code as part of your program,, right?

Example

Sub Test()

Dim FoundRangeName As Boolean

'.... your program here
On Error Resume Next
FoundRangeName = Len(ThisWorkbook.Names("RangeName").Name) <> 0
On Error GoTo 0

If FoundRangeName Then
    MsgBox "Found Name"
    ' Run this
Else
    MsgBox "Name not found"
    ' Run this
End If

' your program here

End Sub

 

[JenniferMurphy]

The reason to create a function is when you need to use it multiple time.

The real reason to create a function is because of a fundamental and decades old deficiency in VBA. If VBA has a simple range(name).exists, a function would not be needed.

If you just need to use it one in your code flow then no need to create a function. That example is just a sample subroutine. You can use the code as part of your program,, right?

Right & right. The function is the way to go. Thanks.

 

[JenniferMurphy]

PS: As long as I am writing a function to check if the name exists, maybe I can include a test that the name is a table. Do you have a test for that?

 

[RoryA]

The Range has a Listobject property, so assign the name's referstorange to a variable (within your error handler). If that is not Nothing then test if its Listobject property is also Not Nothing. You could combine the two tests but I would keep them separate.

 

[Alex Blakenburg]

You can do the if Range Names exists like this:
(does not rely on "On Error Resume Next")

Sub RangeNameExists()
    Dim NameExist As Boolean
    Dim rngName

    rngName = "tblBodyType"
    NameExist = Evaluate("IsRef(" & rngName & ")")
    
    Debug.Print NameExist

End Sub

 

[Alex Blakenburg]

You can use the same principle to check if the Name is a table.
If you don't care whether its a Named Range and only whether it is a table you only need the second part.

Sub RangeNameExists_IsTable()

    Dim rngName
    rngName = "tblBodyType"
    
    Dim NameExist As Boolean
    NameExist = Evaluate("IsRef(" & rngName & ")")
        Debug.Print "Name is Range Name: " & NameExist
    
    Dim IsTable As Boolean
    IsTable = Not IsError(Evaluate("IsRef(" & rngName & "[#Headers])"))
        Debug.Print "Name is Table Name: " & IsTable

End Sub

 

[JenniferMurphy]

You can use the same principle to check if the Name is a table.
If you don't care whether its a Named Range and only whether it is a table you only need the second part.

That worked perfectly. Thank you.

Here's my final code.

Function IsATable(pTblName As String, pMyName As String) As Boolean

If Not Evaluate("IsRef(" & pTblName & ")") Then
  Call ErrMsg("'" & pTblName & "' is not a valid range name", pMyName)
  IsATable = False: Exit Function: End If

If IsError(Evaluate("IsRef(" & pTblName & "[#Headers])")) Then
  Call ErrMsg("'" & pTblName & "' is not a valid table name", pMyName)
  IsATable = False: Exit Function: End If

IsATable = True
End Function

I'm marking this as the solution. It's more complete.