Excel VBA check if named range is set

Question

I'm trying to determine if a named range has been set via VBA. The named range is called LoadedToken and essentially is loaded when a user clicks a particular button. I use this as proof that initialisation has taken place.

I have a function to check if this has taken place:

Function ToolIsEnabled()

    ' We check if the R2A add-in has been loaded by detecting the named range
    If ActiveWorkbook.Names("LoadedToken") Is Nothing Then
        ToolIsEnabled = False
    Else
        ToolIsEnabled = True
    End If

End Function

and I get an application error. Of course, the VBA is incorrect. However how can I actually accomplish this?!

Answer 1

Sub Test()
    Debug.Print IsNamedRange("Bumsti")
End Sub

Function IsNamedRange(RName As String) As Boolean
Dim N As Name

    IsNamedRange = False
    For Each N In ActiveWorkbook.Names
        If N.Name = RName Then
            IsNamedRange = True
            Exit For
        End If
    Next
End Function

Usage in OP context could be

' ...
If IsNamedRange("LoadedToken") Then
    ' ...
End If
' ...

or - if a program specific Bool needs to be set

' ...
Dim IsTokenLoaded as Boolean
IsTokenLoaded = IsNamedRange("LoadedToken")
' ...

Both constructs make it pretty clear in the source code what you are aiming for.

Answer 2

You can achieve this by using error handling:

Function ToolIsEnabled() As Boolean
    Dim rng As Range

    On Error Resume Next
    Set rng = ActiveWorkbook.Range("LoadedToken")
    On Error GoTo 0

    ToolIsEnabled = Not rng is Nothing

End Function