Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Colorado Springs, Colorado, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB/VBA Language Feature (VBA Office 2003)

    Strangely enough, yesterday for the first time in a very long time, I needed to stop by the Lounge to ask a question and it wasn't coming up. I was a little bummed that this great resource was gone. Glad to see it was just a DNS issue.

    Anyway, is there a language feature in VBA that allows you to check for membership in an array, collection, or dictionary?

    In Python you can do

    dwarves = ['grumpy', 'dopey', 'sleepy', 'happy']
    dwarf = 'grumpy'
    if dwarf in dwarves

    Perl has something similar like
    if grep $dwarf @dwaves

    Is there some sort of construct in VBA that you can do this? I was in Excel and I only need to process a set of sheets in a workbook. I couldn't find anything to do something like

    if sheetname in sheetnames

    I ended up writing a function that uses a Select Case statement with each case being a sheetname. I could have done a big if statment with a pile of "or"s.

    Am I missing something?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    No, VBA doesn't have a specific operator for this. You can create a custom function, for example

    Function SheetExists(SheetName As String) As Boolean
    Dim sht As Worksheet
    For Each sht In Sheets
    If sht.Name = SheetName Then
    SheetExists = True
    Exit For
    End If
    Next sht
    End Function

    Use like this:

    If SheetExists("Sheet37") Then

    And to annoy Chris Greaves, here is another version:

    Function SheetExists(SheetName As String) As Boolean
    Dim sht As Worksheet
    On Error Resume Next
    Set sht = Worksheets(SheetName)
    SheetExists = (Err = 0)
    End Function

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    In an array of strings, rather than check every value, you could try the Join() function:

    <code>If InStr(1, Join(myArray, vbCrLf), myString, vbTextCompare) > 0 Then</code>


    In a Collection, you can use error trapping to test for an item by key, but if you want to check the data item assigned to that key, I think you have to loop through.

    For the VBScript Dictionary object, I don't use it often enough to recall...

    Added: I didn't read the part about Excel sheet names. Never mind!

  4. #4
    New Lounger
    Join Date
    Jan 2003
    Location
    Colorado Springs, Colorado, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    Thanks everyone.

    The Instr solution looks like it will work. I'll need to give it a good comment to explain what it's doing.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    I'm kinda rusty, but aren't you going to have to loop the Sheets Collection to get it into an array, therefore defeating the purpose of avoiding a loop? Or is a Collection redim-able as an array?

    (You can get selected sheets into an array by macro-recording it, but I didn't think that either the Sheets Collection or the ActiveWindow.SelectedSheets Collection is an Array.)

    I feel a learning experience may be imminent.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    If it's checking for the existence of a Worksheet name in the Workbook, here's another take on it, without the need for setting an error trap:
    <pre>Option Explicit

    Public Function GetRef(strSheetName As String) As Worksheet

    <font color=448800>'Returns a reference to a named worksheet.
    'Returns Nothing if the worksheet doesn't exist.</font color=448800>

    Dim wks As Worksheet
    Dim idx As Long

    With Application.ThisWorkbook

    For Each wks In .Worksheets
    If wks.Name = strSheetName Then idx = wks.Index: Exit For
    Next wks

    If idx = 0 Then
    Set GetRef = Nothing
    Else
    Set GetRef = .Worksheets(idx)
    End If

    End With

    End Function
    </pre>


    Alan

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    Alan,
    That won't always work unfortunately - the Index property of a worksheet returns the index in the Sheets collection, not the Worksheets collection. You would need to change it to:
    <pre>Option Explicit

    Public Function GetRef(strSheetName As String) As Worksheet

    'Returns a reference to a named worksheet.
    'Returns Nothing if the worksheet doesn't exist.

    Dim wks As Worksheet
    Dim idx As Long

    With Application.ThisWorkbook

    For Each wks In .Worksheets
    If wks.Name = strSheetName Then idx = wks.Index: Exit For
    Next wks

    If idx = 0 Then
    Set GetRef = Nothing
    Else
    Set GetRef = .Sheets(idx)
    End If

    End With

    End Function
    </pre>

    in case there are any chart sheets. But you could shorten it to this anyway:

    <pre>Public Function GetRef(strSheetName As String) As Worksheet

    'Returns a reference to a named worksheet.
    'Returns Nothing if the worksheet doesn't exist.

    Dim wks As Worksheet
    Dim idx As Long

    With Application.ThisWorkbook

    For Each wks In .Worksheets
    If wks.Name = strSheetName Then Set GetRef = wks: Exit For
    Next wks

    End With

    End Function
    </pre>


    FWIW
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    The Dictionary object has an Exists method - it's a real shame the Collection doesn't!
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB/VBA Language Feature (VBA Office 2003)

    Yep <img src=/S/yep.gif border=0 alt=yep width=15 height=15>, I adapted this (quickly and carelessly) from a general collection routine I wrote to avoid errors/error handling when adding or deleting items. I can see your abbreviated version is much better, for the case of simply retuning a reference.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •