CODEWITHSUNDEEP
excelvba
noobmaster69
noobmaster69

Reputation: 3115

Excel VBA check if named range is set

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?!

Upvotes: 4

Views: 4913

Answers (5)

Vasko
Vasko

Reputation: 39

As per Tom's answer these 2 line should do the trick:

On Error Resume Next
Set TestRange = ActiveWorkbook.Range("LoadedToken")  'if it does **not** exist this line will be ERROR

Upvotes: 0

Tom
Tom

Reputation: 9878

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

Upvotes: 2

Rory
Rory

Reputation: 34045

For the activeworkbook, you could also call the old XLM NAMES() function:

Function IsNameInActiveWorkbook(sName As String) As Boolean
    IsNameInActiveWorkbook = Not IsError(Application.ExecuteExcel4Macro("MATCH(""" & sName & """,NAMES(),0)"))
End Function

Upvotes: 1

MikeD
MikeD

Reputation: 8941

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.

Upvotes: 6

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

This will check either in ThisWorkbook or a named workbook and return TRUE/FALSE.

Sub Test()

    MsgBox NamedRangeExists("SomeName")
    MsgBox NamedRangeExists("SomeOtherName", Workbooks("Book1.xls"))

End Sub

Public Function NamedRangeExists(sName As String, Optional Book As Workbook) As Boolean

    On Error Resume Next

        If Book Is Nothing Then
            Set Book = ThisWorkbook
        End If

        NamedRangeExists = Book.Names(sName).Index <> (Err.Number = 0)

    On Error GoTo 0

End Function

Edit: A shorter version if it's only going to look in ThisWorkbook:

Public Function NamedRangeExists(sName As String) As Boolean

    On Error Resume Next

        NamedRangeExists = ThisWorkbook.Names(sName).Index <> (Err.Number = 0)

    On Error GoTo 0

End Function

Upvotes: 0

Related Questions