dealing with NullReferenceException

[dethredic]

Basically I am checking several cells in different spreadsheets to see if they contain the correct value. On one sheet I get an error saying: "NullReferenceException was unhandled"

I checked the sheet and it seems ok (it would make check valid return false, but it seemed normal), maybe it has something do to with merged cells, but I didn't see any.

Here is my code:

For Each WS In WB.Worksheets
                CheckValid = True
                If WS.Range("B4").Value.ToString <> "Employee Name:" Then
                    CheckValid = False
                End If
                If WS.Range("H4").Value.ToString <> "Week No.:" Then
                    CheckValid = False
                End If
                If WS.Range("F8").Value.ToString <> "Cost" Then
                    CheckValid = False
                End If
 
                ' Add the sheet to the list
                If CheckValid = True Then
                    CheckedListBox1.Items.Add(WS.Name)
                End If
            Next

Just wondering how I would skip to the next sheet if this occurred.

[Pradeep1210]

Put whatever is inside the For...Next block inside a Try... Catch block. You can catch NullReferenceException in one of the catch blocks, or log error to file etc.
This would allow you to move to next sheet incase there is some error on any one of them.

[Megalith]

you could use a try...catch statement

'loop with worksheets
Try
   'your code
Catch ex as NullReferenceException

Catch ex as exception
    messagebox(ex.tostring) ' any other errors will be caught here
End Try
Next ' so that we move to the next sheet

[techgnome]

Fortunate for you I haven't had my coffee yet, so I'm not alert enough for a snarky comment.

I'm assuming that the error happens on the CheckedListBox1.Items.Add(WS.Name) line? Or does it happen on one of the other lines.?

-tg

[techgnome]

pradeep, megalith - you guys posted while I was typing.... while that may work, I don't necessarily agree that just throwing error handling around something like that is the best idea. That's an example of defensive programming. Instead, it would be better to use offensive programming, determine WHY the error happens, then take precautions to prevent the error from happening. Having exceptions thrown is an expensive operation in .NET, so wouldn't it be better to prevent the exception from happening in the first place?

-tg

[Pradeep1210]

pradeep, megalith - you guys posted while I was typing.... while that may work, I don't necessarily agree that just throwing error handling around something like that is the best idea. That's an example of defensive programming. Instead, it would be better to use offensive programming, determine WHY the error happens, then take precautions to prevent the error from happening. Having exceptions thrown is an expensive operation in .NET, so wouldn't it be better to prevent the exception from happening in the first place?

-tg

Yes! you are right 100%.
But what I did is just what the OP asked for.

[dethredic]

@ techgnome the error comes on the if xxx cell = yyyy lines

thanks to pradeep, megalith

[Megalith]

@ techgnome, my reason was the same as pradeep and yes i 100% agree too, the reason for the error is what matters not how to ignore it in the 'On error Goto Next' sense

[techgnome]

tisk, tisk, tisk.... the two of you should know better than that...

dethredic - then what that tells me is that your range is off... you're probably hitting a worksheet with no data. You can't .ToString something that doesn't exist...

If WS.Range("B4") IsNot Nothing AndAlso WS.Range("B4").Value isNot Nothing AndAlso WS.Range("B4").Value.ToString...

-tg

[dethredic]

tisk, tisk, tisk.... the two of you should know better than that...

dethredic - then what that tells me is that your range is off... you're probably hitting a worksheet with no data. You can't .ToString something that doesn't exist...

If WS.Range("B4") IsNot Nothing AndAlso WS.Range("B4").Value isNot Nothing AndAlso WS.Range("B4").Value.ToString...

-tg

You code doesn't work, I get the same error.

Also I don't think the range is off because the cell B4 is clearly there and says "Employee", and that is where the error occurs. If I comment out the "B4" check, then the "H4" check give me the error, although H4 is merged with G4 so I could understand receiving an error with that cell.
The final check "F8" does not give me an error...

EDIT:
I wrote a quick VBA macro for the spreadsheet:

Sub Test()
    MsgBox (ActiveSheet.Range("b4"))
End Sub

That returned "Employee"

[techgnome]

Well, something in those lines is null. time to do some debugging. Check your values, use breakpoints and the Quick watch option to see what's what. Make sure things are what they are supposed to be.

Or use the code in post 3 and ignore it.

-tg

[Megalith]

sorry i can't help more, using vb with spreadsheets is not something i have needed to use yet, my reasoning is my end user may or may not have excel. however a null reference exception usually means you haven't declared the type, you say it works on other worksheets than the one which errors, maybe you could post the workbook as an attachment?

[dethredic]

Ok, it turns out the problem is merged cells. B4 was just a sneaky little bugger and was merged in a fashion which can only be described as "ninja like".

I dunno if you guys know a work around for this, but if not I will jut use megalith's code from above.