Solved

Error handling with Excel VBA

Posted on 2000-03-28
6
5,689 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

867 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

22 Experts available now in Live!

Get 1:1 Help Now