• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5760
  • Last Modified:

Error handling with Excel VBA

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
    Else
        BoxType = "Caught an error"
    End If
End Function

Cheers, friends.

Leon
0
Astroman
Asked:
Astroman
  • 3
  • 2
1 Solution
 
antratCommented:
Hi Astroman

Seems like it is not possible to trap a worksheet Function Error within the function itself. So you may want to place a worksheet error handler in the
Workbook_SheetChange Event like below. It can even be set up to handle any errors.




Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If IsError(Target.Value) And Target.Formula Like ("=BoxType(*)") Then
  errval = Target.Value
    Select Case errval
        Case CVErr(xlErrDiv0)
            Target.Value = "Cannot Divide by zero"
        Case CVErr(xlErrNA)
            Target.Value = "Value not available"
        Case CVErr(xlErrName)
            Target.Value = "Unrecognised text"
        Case CVErr(xlErrNull)
            Target.Value = "Null"
        Case CVErr(xlErrNum)
            Target.Value = "Cannot get number"
        Case CVErr(xlErrRef)
           Target.Value = "Cannot reference"
        Case CVErr(xlErrValue)
            Target.Value = "Cannot find match"

Case Else
            MsgBox "This should never happen!!"
    End Select
    End If
End Sub



in fact, I think I may set one up for myself :)

antrat
0
 
tureCommented:
Astroman,

How about this solution?

Function boxtype(idcell As Range) As String
  Dim co As Variant
  On Error Resume Next
  co = Application.WorksheetFunction.Match(idcell.Value, Range("CTXSuffix"), 0)
  On Error GoTo 0
  If IsEmpty(co) Then
    boxtype = "Caught an error"
  Else
    boxtype = co
  End If
End Function

Ture Magnusson
Karlstad, Sweden
0
 
AstromanAuthor Commented:
Antrat - I like your code - it looks useful in many situations, but in my specific case Ture's answer is the best fit. I can understand what Antrats code does, but not Tures.

Can you explain you solution to me please Ture?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AstromanAuthor Commented:
Hi People,

Antrat - I don't think an incorrect data type is the cause of my #VALUE error message, purly because the function results change to this without any change to the worksheet (referneceed values or otherwise).

Ture - I'd still rather use named ranges because the ranges may grow or shrink. The ranges refer to static lookup tables anyway, so the recalculation isn't an issue.

I don't really want to use application.volatile. The spreadsheet is mamoth (there are 9 sheets most pretty big. If I had my way I'd use a relational database instead, and just use Excel as a front-end. Grumble grumble)

I'm wondering if maybe this is an excel bug. The closest thing to match what I've seen (which I can't clearly define, and therefore repeat) is the forst knowledge base article that was posted, but I'm running SR-2, so this shouldn't be an issue.

I'm confused @ |
0
 
tureCommented:
Astroman,

Here's an attempt to explain my code...

Function boxtype(idcell As Range) As String
  'Declare the variable co as a variant.
  'When a new variant variable is declared,
  'it will have the value EMPTY.
  Dim co As Variant

  'Turn on error handling so that the
  'code will just go on if there's an error
  On Error Resume Next
 
  'Use MATCH to set the value of co. If
  'there is an error (the value is not
  'found) the code will just go on and
  'co will not get a new value. It will
  'still be EMPTY.
  co = Application.WorksheetFunction.Match(idcell.Value, Range("CTXSuffix"), 0)

  'Turn off error handling again
  On Error GoTo 0

  'Now, we'll check if co is empty. If
  'it is empty, the reason is that no
  'match was found.
  If IsEmpty(co) Then
    boxtype = "Caught an error"
  Else
    boxtype = co
  End If
End Function

/Ture
0
 
AstromanAuthor Commented:
Thank you very much. I've never used any of the Error handling before, but your description makes sense to me.

Thanks again,

Leon
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now