none
Excel 2010 VBA-How to check to see if a Worksheet.Name already exists?

    Question

  • I'm trying to figure out, using VBA, to check to see if a Worksheet.Name exists before hiding columns.  strRangeName1 is a String variable representing a range name in the sht.Names collection.  Does anyone know why this code doesn't work?  Also if it did work, would it error out if the strRangeName1 = ""?

    My VBA Code:

    If Not sht.Names(strRangeName1) Is Nothing Then

      sht.Names(RangeName1)Columns.EntireColumn.Hidden = True

    Else

      'Do nothing.

    End IF

    Thanks,

    ....bob sutor


    • Edited by ConstPM Wednesday, March 20, 2013 5:21 AM correction
    Wednesday, March 20, 2013 5:20 AM

Answers

  • >>Can anyone tell me why this line of code doesn't work:

    >If Not sht.Names(strRangeName1) Is Nothing Then

    It errors because the range object doesn't exist.  There is a difference between a range object that exists but has not been assigned to a range (dimmed but not Set to anything for example) and a range object that doesn't exist.

    This is a function I use to test whether a local name exists on a sheet:

    ''Returns True if Nm is defined locally on sheet WS.
    Function LocalNameExists(WS As Worksheet, Nm As String) As Boolean
        Dim Temp As String
        On Error Resume Next
        Err.Clear
        Temp = WS.Names(Nm).Value
        LocalNameExists = (Err.Number = 0)
    End Function

    • Marked as answer by ConstPM Wednesday, March 20, 2013 6:08 PM
    Wednesday, March 20, 2013 1:47 PM

All replies

  • Hello Bob,

    I really can't work out if you are referring to variables created in VBA or named ranges as when you select a range on a worksheet and then select Formulas ribbon -> Define Name.

    If RangeName1 is a named range on the worksheet then I think your code should be something like the following.

    Dim sht As Worksheet
        Dim strShtName As String
        Dim strRangeName1 As String
       
        strShtName = "Sheet1"
        strRangeName1 = "RangeName1"   '"RangeName1" is named range on a worksheet
       
        Set sht = Worksheets(strShtName)
       
        If Not sht.Range(strRangeName1) Is Nothing Then
            sht.Range(strRangeName1).EntireColumn.Hidden = True
        Else
            'Do nothing.
        End If

    However, as a separate example to see if a specific worksheet exists then the following

        strShtName = "Sheet1"
       
        Set sht = Nothing   'Initialize to nothing before using in test otherwise retains previous setting (If any)
        On Error Resume Next
        Set sht = Worksheets(strShtName)
        On Error GoTo 0
       
        If sht Is Nothing Then
            MsgBox "Worksheet " & strShtName & " does not exist"
        Else
            'Code to do somehing here.
        End If


    Regards, OssieMac

    Wednesday, March 20, 2013 6:56 AM
  • My appologies for not being clear.  The code I gave you wasn't intended to be a full working routine.  I only included the part that I was concerned about regarding the test to see if the Worksheet.Name existed.  (I say Worksheet.Name to indicate I'm referring to a Name in the Worksheet.Names collection, doesn't really matter how it was created). 

    Can anyone tell me why this line of code doesn't work to verify that a range named "SampleRange" is actually in the sht.Names collection before proceeding with the code that follows the if statement???  If "SampleRange" doesn't exist I am getting an "Object Defined" error when the following line tries to hide the range name that doesn't exist.  The "Not.......Is Nothing" doesn't seem to work? I believe this only would work for an "uninitialized object", but not if the object doesn't even exist.  I assume I'm stuck with using "On Error Resume Next" since I don't see an "If Exists" type statement in VBA?

     If Not sht.Range("SampleRange") Is Nothing Then

       sht.Range("SampleRange").Columns.EntireColumn.Hidden = True

    ...bob sutor



    • Edited by ConstPM Wednesday, March 20, 2013 9:06 AM clarify
    Wednesday, March 20, 2013 8:04 AM
  • Like I said before "I really did not understand". If you are testing to see if the named range exists then the following.

    Added with edit after initial post: Basically attempt to use the named range with Errors turned off and see what it returns.

        Dim sht As Worksheet
        Dim strShtName As String
        Dim strRangeName1 As String
        Dim strAddress As String
       
        strShtName = "Sheet1"
        strRangeName1 = "RangeName1"   '"RangeName1" is named range on a worksheet
       
        Set sht = Worksheets(strShtName)
       
        strAddress = ""      'Initialize to zero length string
        On Error Resume Next
        strAddress = sht.Range(strRangeName1).Address
        On Error GoTo 0
       
        If Len(strAddress) = 0 Then
           MsgBox strRangeName1 & " does not exist"
        Else
           sht.Range(strRangeName1).EntireColumn.Hidden = True
        End If


    Regards, OssieMac


    • Edited by OssieMac Wednesday, March 20, 2013 8:38 AM
    Wednesday, March 20, 2013 8:33 AM
  • >>Can anyone tell me why this line of code doesn't work:

    >If Not sht.Names(strRangeName1) Is Nothing Then

    It errors because the range object doesn't exist.  There is a difference between a range object that exists but has not been assigned to a range (dimmed but not Set to anything for example) and a range object that doesn't exist.

    This is a function I use to test whether a local name exists on a sheet:

    ''Returns True if Nm is defined locally on sheet WS.
    Function LocalNameExists(WS As Worksheet, Nm As String) As Boolean
        Dim Temp As String
        On Error Resume Next
        Err.Clear
        Temp = WS.Names(Nm).Value
        LocalNameExists = (Err.Number = 0)
    End Function

    • Marked as answer by ConstPM Wednesday, March 20, 2013 6:08 PM
    Wednesday, March 20, 2013 1:47 PM
  • OK--I think what both Ossie and Jim are saying is that you need to use the "On Error Resume Next".

    I've already seen Jim's function code, it was actually submitted by Zack Barresse on vbaexpress.com.  That code uses the "On Error Resume Next" statement to identify a non-existent object.  Which makes sense, "Is Nothing" can't be used on a "non-existent" object.  Just wanted to make sure there wasn't an equivalent to Ifexists in SQL for VBA.

    Thanks to both of you for your efforts.

    Regards,

    ....bob

    Wednesday, March 20, 2013 6:08 PM
  • I think it's simplier to use a function like this :

    Function test_if_WSname_exists(ByVal WTF As String) As Boolean ' WS i "want to find"
    Dim i As Long
    Dim result As Boolean
    result = False
    For i = 1 To ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(i).Name = WTF Then
            result = True
            Exit For
        End If
    Next i
    test_if_WSname_exists = result
    End Function

    i used similar code in one of my vba projects.

    Sunday, October 27, 2013 5:44 PM