CODEWITHSUNDEEP
vba
Matteo NNZ
Matteo NNZ

Reputation: 12655

Comparing two Sheet objects (not contents)

In the context of an error handling code, I would like to verify if the user has given to the current sheet the same name of another one into the same workbook (action forbidden, of course). So the way I intuitively tried to verify this was simply to loop through all the sheets and comparing the names: 

For Each sh In ThisWorkbook.Sheets
    If sh.Name = ThisWorkbook.ActiveSheet.Name Then
        'error handling here
    End If
Next sh

However, this is a huge logic fall in the case when:

1) The user is editing, let's say, the sheet number 3; 2) The sheet with the same name is at the position number 5;

In that case, the condition sh.Name = ThisWorkbook.ActiveSheet.Name would be met for sure because the sheet is compared to itself.

So, I wonder: how to understand if sh is not ThisWorkbook.ActiveSheet?

I had thought the task it could have simply been solved with a simple object comparison: 

If (sh Is Not ThisWorkbook.ActiveSheet) And (sh.Name = ThisWorkbook.ActiveSheet.Name) Then

but this raises a compile error, namely Object does not support this property or method. Could anyone please help me finding the lack of logic in my code's structure?

OTHER INFORMATION

I have tried to manage the case through the Err.Description and the Err.Number, but the first is OS-language dependent and the second is the same for other types of error I need to handle differently.

Moreover, the sheets (names and contents) are contained into a .xlam add-in so the user can change the contents through custom user-forms but not through the Excel Application.

More in general, let's say that I would like to know how can I perform the comparison, even if a work-around in this specific case is possible, in order to use this method for future developments I already plan to do and that cannot be managed through the default VBA error handler.

Upvotes: 3

Views: 767

Answers (3)

Gene Skuratovsky
Gene Skuratovsky

Reputation: 581

You've got an incorrect syntax with "Not"; it should be this:

If (Not sh Is ThisWorkbook.ActiveSheet) And (sh.Name = ThisWorkbook.ActiveSheet.Name) Then

Upvotes: 4

Maciej Los
Maciej Los

Reputation: 8591

There's no reason to loop through the collection of sheets. Use this:

Function IsWshExists(ByVal wbk As Workbook, ByVal wshName As String) As Boolean
Dim wsh As Worksheet

On Error Resume Next
Set wsh = wbk.Worksheets(wshName)

IsWshExists = (Err.Number = 0)

Set wsh = Nothing
End Function

Usage:

If Not IsWshExists(ThisWorkbook, "Sheet2") Then 
    'you can add worksheet ;)
    'your logic here
End If

Upvotes: -1

RubberDuck
RubberDuck

Reputation: 12728

Just check the index of the worksheet along with the name. Only error (or whatever) if the name matches, but the index doesn't. 

Option Explicit

Public Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet

    Dim wsToCheck As Worksheet
    For Each wsToCheck In wb.Worksheets
        If ws.Name = wsToCheck.Name And ws.Index <> wsToCheck.Index Then
            'do something
        End If
    Next
End Sub

Of course, you could always just test for object equality using the Is operator too, or inequality in your specific case.

Public Sub test2()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet

    Dim wsToCheck As Worksheet
    For Each wsToCheck In wb.Worksheets
        If Not ws Is wsToCheck Then
            'do something
            Debug.Print ws.Name
        End If
    Next
End Sub

Upvotes: 4

Related Questions