VB/VBA Language Feature (VBA Office 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

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

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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

The Dictionary object has an Exists method – it’s a real shame the Collection doesn’t!

Reply | Quote

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:

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 = .Worksheets(idx)
        End If
        
    End With

End Function

Alan

Reply | Quote

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:

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

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

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

FWIW

Reply | Quote

Yep , 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

Reply | Quote