CODEWITHSUNDEEP
vbaexcel
Selkie
Selkie

Reputation: 1255

Testing if a worksheet is undefined VBA

So at one point I'm testing if the worksheet number being called is greater than the total number of worksheets in a workbook.

If SourceWB.Sheets.Count < WorksheetNumber Then
                GoTo ErrorLog
                MsgBox "We're looking for a sheet number higher than the total number of worksheets. For example, we're looking for sheet 3 when there are only 2 sheets in the workbook. Please check your mapping and re-try this workbook."
                Exit Sub
            Else
       'We're good
       End If

Ok no problem. My error logging:

ErrorLog:


Set ErrorLogSh = wb.Sheets("Error Log")
errorRow = ErrorLogSh.Range("A" & ErrorLogSh.Rows.Count).End(xlUp).Row + 1

     ErrorLogSh.Cells(errorRow, 1) = Err.Number
     ErrorLogSh.Cells(errorRow, 2) = Err.Description
     ErrorLogSh.Cells(errorRow, 3) = SourceWB.Name


     ErrorLogSh.Cells(errorRow, 4) = SourceSheet.Name


    Resume Next

Issue: When there's no sourcesheet (As defined earlier, we're testing for it), we get a problem. I'm trying to figure out a test for the sourcesheet variable to see if it's blank or not.

Now, the easy, easy solution would be to mimic the same test as earlier. But I got thinking - how could I test if the variable is empty?

Isnull didn't work - it flags it the wrong way. A test of = Nothing didn't work - complained that the variable didn't exist

So I'm kind of stuck.

Note: This is a subroutine within a larger macro - could setting the variables to = nothing at the end do the trick?

Upvotes: 0

Views: 403

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

The correct syntax for testing is an object = Nothing is If Object Is Nothing Then

Also, your ErrorLog needs to account for that too

Something like this 

Sub Demo()
    Dim SourceWB As Workbook
    Dim SourceSheet As Worksheet
    Dim WorksheetNumber  As Long

    On Error GoTo ErrorLog
    WorksheetNumber = 1
    If SourceWB Is Nothing Then
        MsgBox "SourceWB is not set"
        Exit Sub
    ElseIf SourceWB.Sheets.Count < WorksheetNumber Then
        MsgBox "We're looking for a sheet number higher than the total number of worksheets. For example, we're looking for sheet 3 when there are only 2 sheets in the workbook. Please check your mapping and re-try this workbook."
        Exit Sub
    Else
        'We're good
    End If
Exit Sub

ErrorLog:
    Dim errorRow As Long
    With ThisWorkbook.Sheets("Error Log")
        errorRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        .Cells(errorRow, 1) = Err.Number
        .Cells(errorRow, 2) = Err.Description
        If SourceWB Is Nothing Then
            .Cells(errorRow, 3) = "Nothing"
        Else
            .Cells(errorRow, 3) = SourceWB.Name
            If SourceSheet Is Nothing Then
                .Cells(errorRow, 4) = "Nothing"
            Else
                .Cells(errorRow, 4) = SourceSheet.Name
            End If
        End If
    End With

    Resume Next
End Sub

Note that the error log won't be called if SourceWB is Nothing or WorksheetNumber is > number of Sheets. If you want to log those errors using the error handler, you need to let the code error, handle it and return

Sub Demo2()
    Dim SourceWB As Workbook
    Dim SourceSheet As Worksheet
    Dim WorksheetNumber  As Long

    On Error GoTo ErrorLog
    WorksheetNumber = 1
    Set SourceSheet = SourceWB.Sheets(WorksheetNumber) '<-- throws an error
    If SourceWB Is Nothing Then
        MsgBox "SourceWB is not set"
        Exit Sub
    ElseIf SourceSheet Is Nothing Then
        MsgBox "We're looking for a sheet number higher than the total number of worksheets. For example, we're looking for sheet 3 when there are only 2 sheets in the workbook. Please check your mapping and re-try this workbook."
        Exit Sub
    Else
        'We're good
    End If
Exit Sub

ErrorLog:
    Dim errorRow As Long
    With ThisWorkbook.Sheets("Error Log")
        errorRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        .Cells(errorRow, 1) = Err.Number
        .Cells(errorRow, 2) = Err.Description
        If SourceWB Is Nothing Then
            .Cells(errorRow, 3) = "Nothing"
        Else
            .Cells(errorRow, 3) = SourceWB.Name
            If SourceSheet Is Nothing Then
                .Cells(errorRow, 4) = "Nothing"
            Else
                .Cells(errorRow, 4) = SourceSheet.Name
            End If
        End If
    End With

    Resume Next
End Sub

Upvotes: 1

Related Questions