Solved

Error handling with Excel VBA

Posted on 2000-03-28
6
5,685 Views
Last Modified: 2012-06-27
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
Comment
Question by:Astroman
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:antrat
Comment Utility
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
 
LVL 22

Accepted Solution

by:
ture earned 88 total points
Comment Utility
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
 
LVL 1

Author Comment

by:Astroman
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:Astroman
Comment Utility
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
 
LVL 22

Expert Comment

by:ture
Comment Utility
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
 
LVL 1

Author Comment

by:Astroman
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Outlook Free & Paid Tools
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now