Excel

Check if Named Range exists

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Zack Barresse

Description:

Check whether or not a Named Range specific name exists in a specified workbook or not. 

Discussion:

Use this function to test the existence of a specific named range. You can specify the workbook name if you'd like to. If you do not, the activeworkbook will be used. It will loop through all sheets. It does this because you MUST specify the sheet name whilst using VBA this way, in a worksheet function you do not. 

Code:

instructions for use

			

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

How to use:

  1. Copy all above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left.
  5. Choose Insert --> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
  9. Press Alt + F8, select 'NREtest1' or 'NREtest2', press Run.
 

Test the code:

  1. From an existing workbook, save first.
  2. Press Alt + F8.
  3. Choose 'Test_CreateNamedRangeIfNotAlready'.
  4. Press 'Run'.
  5. Press Alt + F8.
  6. Choose 'Test_CreateNamedRangeIfNotAlready'.
  7. Press 'Run'. (Yes, twice.)
 

Sample File:

NREex.zip 10.84KB 

Approved by mdmackillop


This entry has been viewed 232 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express