Option Explicit 
Option Compare Text 
 
Function NamedRangeExists(strName As String, _ 
    Optional wbName As String) As Boolean 
     'Declare variables
    Dim rngTest As Range, i As Long 
     
     'Set workbook name if not set in function, as default/activebook
    If wbName = vbNullString Then wbName = ActiveWorkbook.Name 
     
    With Workbooks(wbName) 
        On Error Resume Next 
         
         'Loop through all sheets in workbook.  In VBA, you MUST specify
         ' the worksheet name which the named range is found on.  Using
         ' Named Ranges in worksheet functions DO work across sheets
         ' without explicit reference.
        For i = 1 To .Sheets.Count Step 1 
             
             'Try to set our variable as the named range.
            Set rngTest = .Sheets(i).Range(strName) 
             
             'If there is no error then the name exists.
            If Err = 0 Then 
                 
                 'Set the function to TRUE & exit
                NamedRangeExists = True 
                Exit Function 
            Else 
                 'Clear the error
                Err.Clear 
                 
            End If 
             
        Next i 
         
    End With 
     
End Function 
 
Sub NREtest1() 
    MsgBox NamedRangeExists("test") 
End Sub 
 
Sub NREtest2() 
    MsgBox NamedRangeExists("testing") 
End Sub 
 
 
 
Sub Test_CreateNamedRangeIfNotAlready() 
     'I want to create a Named Range if it doesn't exist already ..
    If NamedRangeExists("Foxtrot") Then 
        MsgBox "Named Range already exists!" 
    Else 
        ActiveWorkbook.Names.Add "Foxtrot", "=Sheet1!$A$1:$D$4" 
    End If 
End Sub