Error handling with Excel VBA
Posted on 2000-03-28
I have the following function which does a match in a defined range for a value. I want to use this as a worksheet function, so for example if cell a1 contains 1345, i might put "=BoxType(a1)" in cell b1, and expect the same result as if I had done the match directly in the cell b1. I am having problems with errors though. If the value is not found then the cell contains "#VALUE". I want to trap this and print something else instead.
This may seem like a toy problem but this is a simplification of what I need, these are the lines of code causing me problems. How can I handle an error in this match function (or achieve the same result)? I want this code in VBA, not by doing IF statements in the cell or anything. (more to the point, why does "iserror" not work in this case?
The Code ==>
Function BoxType(idCell As Range) As String
Dim co As Double
co = Application.WorksheetFunction.Match(idCell.Value, Range("CTXSuffix"), 0)
If Not (IsError(co)) Then
BoxType = co
BoxType = "Caught an error"