CODEWITHSUNDEEP
excelvba
Tiffany
Tiffany

Reputation: 1

VBA to ignore when worksheet is mentioned if it does not exist

I've been doing some pretty extensive research on trying to find this VBA code for excel, but I'm sure someone on here much smarter than I has an answer!

I have a pretty long code that is automating an invoicing process for someone in my company, and it references 6 different detail tabs. Sometimes, 3 of these tabs do not exist, because those clients did not invoice this week.

I'm wondering if there is a code that will ignore those worksheets if they do not exist, that I can put at the beginning of the code and have it apply to EVERY time the name is mentioned, rather than having to add code before every single mention of the sheetname throughout my 4-page code.

Upvotes: 0

Views: 2926

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

You could check if the worksheet exists first by trying to reference it: 

Sub Test()

    If WorkSheetExists("Sheet1") Then
        'do stuff
    End If

End Sub

Public Function WorkSheetExists(SheetName As String, Optional WrkBk As Workbook) As Boolean
    Dim wrkSht As Worksheet

    If WrkBk Is Nothing Then
        Set WrkBk = ThisWorkbook
    End If

    On Error Resume Next
        Set wrkSht = WrkBk.Worksheets(SheetName)
        WorkSheetExists = (Err.Number = 0)
        Set wrkSht = Nothing
    On Error GoTo 0
End Function

Another way of writing the code, taking into account what @Brandon posted: 

Sub Test()

    Dim wrkSht As Worksheet

    If WorkSheetExists("Sheet2") Then
        Set wrkSht = ThisWorkbook.Worksheets("Sheet2")
    End If

    If Not wrkSht Is Nothing Then
        'Do Stuff
    End If

End Sub

Upvotes: 3

Brandon Barney
Brandon Barney

Reputation: 2392

The best approach would be to structure your code in such a way that it allows you to properly and easily check for existence of objects, and then handle the logic as appropriate. Consider the following:

Sub Foo()
    If Not Bar Is Nothing Then
        Baz(Bar)
    End If
End Sub

This is my most common approach (simplified of course) where I check to ensure that the reverse of the object is nothing, and if so then it uses that object.

If you are unable to take this approach, then the more tedious approach is of course to wrap each reference in an If block. While tedious, it is better than using On Error Resume Next or On Error GoTo 0. Remember the quote "Slow is fast" which is important to remember when writing code. What may be quick fixes now will inevitably cost hours of debugging as your projects get more complicated. It is best not to develop bad habits too soon.

Upvotes: 0

Related Questions