Runtime COMException Unhandeled
I am working on an application that writes to excel. The following snippet f code works correctly (it fills in the requested cell) but throws a runtime exception that I cannot get rid of.
For i = 1 To 1000 Step 1
If Not (cPart.Range("A" & i).Value = Nothing) Then
If (cPart.Range("L" & i).Value = Nothing) Then
cPart.Range("L" & i).Interior.ColorIndex = 3
End If
i = i + 1
End If
Next
exception: COMException was unhandled: Exception from HRESULT: 0x800A01A8
any help?
source to share
What HRESULT stands for Object Required
. So it seems that one or more of the objects you are trying to use do not exist, but since the code is written at the moment, it is difficult to be sure what it is. The immediate concern is that you are comparing values with Nothing
, in VB.Net you should use Is Nothing
to check this. Also, you've already set up a loop For
to go from 1 to 1000 in increments of 1 (which you don't need to enable as it's the default), but you do i = i + 1
, which looks like an error?
So, by fixing this and breaking it apart, it can give you a better idea of what's not working:
For i = 1 To 1000
Dim aRange As Object = cPart.Range("A" & i)
If aRange IsNot Nothing AndAlso aRange.Value IsNot Nothing Then
Dim lRange As Object = cPart.Range("L" & i)
If lRange IsNot Nothing AndAlso lRange.Value Is Nothing Then
Dim interior As Object = lRange.Interior
If interior IsNot Nothing Then
interior.ColorIndex = 3
End If
End If
End If
Next
I have declared new objects as Object
that may need to be changed to the correct data types (depending on your project settings).
Hopefully you can now step through the code without errors and you can also step through the code and find that one of the new objects ( aRange
, lRange
and interior
) is equal Nothing
at some point where it shouldn't be, which will show you why it threw this mistake before.
Another benefit of splitting code is that you can now properly dispose of Excel objects so that the Excel instance can be closed cleanly. See this Q&A for information: Excel.Range object does not delete, it does not close the Excel process
source to share