Excel VBA - Verify A Named Range Exists


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Excel VBA - Verify A Named Range Exists

Hybrid View

  1. #1
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666

    Excel VBA - Verify A Named Range Exists

    I found this piece of code on the web to verify whether or not a named range exists.
    Code:
    Function NameExists(TheName As String) As Boolean
    On Error Resume Next
    NameExists = Len(ThisWorkbook.Names(TheName).Name) <> 0
    End Function
    I use it like this
    Code:
    If NameExists("PYAcuteBase") = True Then
           Wkb1.Sheets("HFR Setting").Range(pyactuebase).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
    Else
           Wkb1.Sheets("HFR Setting").Range("E16").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
    End If
    But, it always returns False even when the range does exist.

    I just need to know if a range name exists...if it does, then I want to use it. If it doesn't, not big deal, I will just hard code the cell location I want.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    I use a Function almost identical to yours, and it works fine.
    Code:
    Public Function IsRangeName(RangeName As String) As Boolean
      On Error Resume Next
      IsRangeName = Len(Names(RangeName).Name) <> 0
    End Function

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Ok...maybe it is how I'm calling it.

    What code do you use to call your IsRangeName function? Is it similiar to my If/Else statement?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    After much playing around I came up with this which seems to work just fine. Any of you Excel gurus see a potential problem that I don't see with using this?
    Code:
    Function NamedRangeExists(strName As String, Optional wkbName As String) As Boolean    
        Dim rngRangeNameToFind As Range
        Dim i As Long     
        On Error Resume Next 'because we will be going through all ranges, all non matches
                                      'will generate an error that we can avoid
    
        If wkbName = vbNullString Then wkbName = ActiveWorkbook.Name
         
        With Workbooks(wkbName)        
            For i = 1 To .Sheets.Count Step 1
                Set rngRangeNameToFind = .Sheets(i).Range(strName)
                Select Case Err.Number
                   Case 0
                    NamedRangeExists = True
                    Exit Function
                   Case 1004 '"Application defined or Object Defined error" - this is what
                                  'we will get if the range name does not exist so just
                                  'clear it out and move on
                    Err.Clear
                   Case Else
                    MsgBox Err.Number & " " & Err.Description
                End Select
            Next
        End With
        On Error GoTo 0
    End Function
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Jun 2012
    Posts
    2

    Corrected function

    Hi,

    You both had the same error in the function.
    It has to be "Len(.Range(RangeName).Name"

    And also make sure, the function has a false value first. If then an object error occurs because the name does not exist, it stays false; if the name exist, it gets true:
    Code:
    Public Function IsRangeName(mySh As Worksheet, RangeName As String) As Boolean
    On Error Resume Next
      
      IsRangeName = False
      IsRangeName = Len(mySh.Range(RangeName).Name) <> 0
    
    End Function

  6. #6
    Join Date
    Nov 2013
    Posts
    1

    A version wihout messages

    Quote Originally Posted by KerryXEX View Post
    Hi,

    You both had the same error in the function.
    It has to be "Len(.Range(RangeName).Name"

    And also make sure, the function has a false value first. If then an object error occurs because the name does not exist, it stays false; if the name exist, it gets true:
    Code:
    Public Function IsRangeName(mySh As Worksheet, RangeName As String) As Boolean
    On Error Resume Next
      
      IsRangeName = False
      IsRangeName = Len(mySh.Range(RangeName).Name) <> 0
    
    End Function
    Here's a simple version that excludes messages:
    Code:
    Function RangeNameExists(RangeName As String, Optional Wkbk As Workbook) As Boolean
    '   Returns TRUE if the range name exists.
    
        Dim i As Long, RangeToFind As Range
        Dim S As String
        RangeNameExists = False
        If Wkbk Is Nothing Then
            Set Wkbk = ActiveWorkbook
        End If
      
        Err.Clear
        On Error Resume Next
        With Wkbk
            For i = 1 To .Sheets.Count
    '            S = .Sheets(i).Name 'For test purposes.
                Set RangeToFind = .Sheets(i).Range(RangeName)
                If Err.Number = 0 Then
                    RangeNameExists = True
                    Exit For
                Else
                    Err.Clear
                End If
            Next i
        End With
        On Error GoTo 0
        Err.Clear
    End Function
    Last edited by Hack; 11-25-2013 at 07:53 AM. Reason: added code tags

  7. #7
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    Quote Originally Posted by KerryXEX View Post
    Hi,

    You both had the same error in the function.
    It has to be "Len(.Range(RangeName).Name"

    And also make sure, the function has a false value first. If then an object error occurs because the name does not exist, it stays false; if the name exist, it gets true:
    Code:
    Public Function IsRangeName(mySh As Worksheet, RangeName As String) As Boolean
    On Error Resume Next
      
      IsRangeName = False
      IsRangeName = Len(mySh.Range(RangeName).Name) <> 0
    
    End Function

    ALL VBA Variables are automatically initialized by VBA Including retrun values
    Therefore IsRangeName = False is redundant

    Then default RANGE object in EXCEL is the Workbook.Range, therefore only RANGE(RangeName).Name is required to return the name of the specified range.

    FYI: a Boolean data type internally is considered a numeric data type with the values of 0 or -1
    where 0 is false and -1 is true. Since all numeric variables initialize to zero, a boolean variable's
    initialization to 0 is automatically FALSE...

    The only error you will get with this is when the RangeName does not exsist
    So simply ignoring the error and allowing the function to return its's default value of zero
    which as a boolean data type means False, is the correct responce.

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Why the "On Error Resume Next" (basically I would rather have a double root canal than ever use that - if there is an error I want to correct it, or handle it, not ignore it.)
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  9. #9
    Join Date
    Jun 2012
    Posts
    2
    Quote Originally Posted by Hack View Post
    Why the "On Error Resume Next" (basically I would rather have a double root canal than ever use that - if there is an error I want to correct it, or handle it, not ignore it.)
    As there is no method to check for if the name exists, the ERROR raised when accessing a non-existing name is what we need to detect if it exists or not.

    So there is no complicated error-handling necessary because the method only does exactly this and we know that the error can only come from this event because that's what we're actually testing. Therefore the error itself can be ignored.

Similar Threads

  1. Excel VBA - Printing Out Named Ranges
    By Hack in forum VB Classic
    Replies: 4
    Last Post: 09-24-2008, 09:02 AM
  2. Copy/Paste VBA Excel
    By Tord in forum VB Classic
    Replies: 3
    Last Post: 09-15-2008, 01:26 PM
  3. Replies: 0
    Last Post: 05-15-2008, 04:24 PM
  4. Replies: 1
    Last Post: 01-02-2007, 08:58 AM
  5. Workbook not Closing Properly by Excel VBA
    By blayne in forum VB Classic
    Replies: 1
    Last Post: 11-17-2005, 06:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center