Variable as Name

Hi there,

The function is used to determine whether or not a specifically-named workbook-level Name has been defined in a workbook.

The following code shows an annotated English-language version of the routine, and also an alternative way of determining the result:

Option Explicit


'=========================================================================================
'=========================================================================================


Function mbNameExists__Version_1(sNameToFind As String) As Boolean

    Dim bNameExists As Boolean
    Dim nam         As Name

'   Assume that the Name does NOT exist
    bNameExists = False

'   Scan through each workbook-level defined Name
    For Each nam In ThisWorkbook.Names

'       Check whether or not the name of the current Name is the same as the name to find
        If nam.Name = sNameToFind Then

'           The name IS the same, so flag this
            bNameExists = True

'           The name has been located, so there's no need to check anything else
            Exit For

        End If

    Next nam

'   Return the appropriate value to the calling routine
    mbNameExists__Version_1 = bNameExists

End Function


'=========================================================================================
'=========================================================================================


Function mbNameExists__Version_2(sNameToFind As String) As Boolean

    Dim nam As Name

'   Temporarily disable error handling
    On Error Resume Next

'       Initialise the variable
        Set nam = Nothing

'       Assume that the Name DOES exist, and ignore any error if it does not
        Set nam = ThisWorkbook.Names(sNameToFind)

'   Re-enable error handling
    On Error GoTo 0

'   Return the appropriate value to the calling routine
    If Not nam Is Nothing Then
          mbNameExists__Version_2 = True
    Else: mbNameExists__Version_2 = False
    End If

End Function

Hope this helps.

Regards,

Greg M