Change parameters On error in vb6

Question

I am working with vb6 and automating some tasks to be performed across a large number of spreadsheets.

The issue is setting my worksheet. Most of the syntax on the workbooks I am working with is the same however there are some where the sheets have different names.

Currently using

for x = 2 to cellcount
    set worksheet = workbook.sheets("*typical sheet name*")
    *rest of code

I'd like to set up something like this

psuedo-code

for x = 2 to cellcount
    On error goto errorhandler
    set worksheet= workbook.sheets("*typical sheet name*")
    Errorhandler:
    set worksheet= workbook.sheets("*secondary sheet name*")
    *rest of code

How can this be done in vb6?

Answer 1

Don't use errors to control the flow of the logic, instead look at all the available names and select the one you want:

Function GetBestMatchingSheet() As Worksheet
    For Each GetBestMatchingSheet In ActiveWorkbook.Sheets
        Select Case LCase$(GetBestMatchingSheet.Name)
            Case "typical sheet name", "secondary sheet name", "third name"
                Exit Function
        End Select

        Set GetBestMatchingSheet = Nothing
    Next
End Function

...

dim foundSheet As Worksheet
set foundSheet = GetBestMatchingSheet()

If not foundSheet is Nothing then msgbox foundSheet.Name