Solved

Error handling with Excel VBA

Posted on 2000-03-28
6
5,699 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
ID: 2666064
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
ID: 2666214
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
ID: 2670204
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Astroman
ID: 2670238
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
ID: 2670459
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
ID: 2670475
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

713 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