How to Trap or TEST for "#REF" error?

Posted by Chris on November 21, 2001 6:03 AM

I have a cell that is a DDE LINK, that is not updated yet. The cell value will be "#REF" or ERROR 2002 in the Visual Basic Editor. I am expecting that same CELL to have a value that is a number, and I try to do something like this:

If Range("A1").Value > 1 Then etc...

If the DDE LINK have not UPDATED, then the "A1" cell will give me a TYPE MISMATCH ERROR. And the IF STATEMENT above bails out on me.

I know that I can put at the top of the SUB

On Error Goto Label

But I want to be able to TEST for the error in LOGIC somehow? Can it be done? Like this:

If Range("A1").Value > 1 OR Range("A1").Value = "ERROR???" Then

Help?

Posted by Dank on November 21, 2001 6:31 AM

Try this:-

Sub TestForError()
If WorksheetFunction.IsError(Range("A1")) Then
MsgBox "A1 has an error"
End If
End Sub

Hope it helps,
Daniel.

Posted by Juan Pablo on November 21, 2001 8:56 AM

IsError is supported by VBA (Not neccesary to write Worksheetfunction) (NT)