Is there a more efficient way to identify specific errors?

[JenniferMurphy]

I have a complicated UDF that takes several ranges as parameters. Each of these ranges can have up to 30-40 values. Each of these values must conform to certain limits, which vary depending on the values in the corresponding cells in the other ranges.

The UDF does extensive validity checking to ensure that all of the ranges are of the correct shape and the values meet the requirements. If any of these tests fail, I return a Value error. The problem is trying to figure out which cell caused the error.

One solution I came up with is to include a MsgBox explaining what the error is and where it occurred and then force a breakpoint so I can do further live debugging.

  MsgBox "message...", , MyName: Stop
  WtdRtg = CVErr(xlErrValue)
  Exit Function

But I do not want this code to be active all the time. Many times I can figure out what the error is, so I don't need it. But the big problem is if the error is caused by a bug in my code, then it will occur on every call, which means that I will have clear dozens or hundreds of MsgBox dialogs or comment them out and then remember to comment them back in.

My next solution was to comment all of the MsgBox statements and only un-comment them if there is an error I cannot find.

'  MsgBox "message...", , MyName: Stop
  WtdRtg = CVErr(xlErrValue)
  Exit Function

But this is not ideal, either. I have 8-10 calls to MsgBox, so I'd have to find them all and then re-comment them out after I find the bug. My next solution was to add a switch that is either a constant or a parameter to turn all of the MsgBox statements on in one swell foop.

  Const MsgSw as Boolean = False  'Turn MsgBox off
  Const MsgSw as Boolean = True   'Turn MsgBox on

  . . .

  If MsgSw then MsgBox "message...", , MyName: Stop
  WtdRtg = CVErr(xlErrValue)
  Exit Function

Once the code is fairly stable, I can comment out these statements to improve efficiency and un-comment them if the need arises later.

Two questions:

1. Is this method inefficient? None of this code will be inside a loop of more than 30-40 iterations
2. Is there a better way

Thanks

 

[pgc01]

Hi

For debug I use compiler directives.
It's more efficient because the code is not executed if the directive conditions fails.

This is a simple example:

Option Explicit

' compiler directive
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Const]#Const[/URL]  CheckValue = False

Sub Test()
Dim v As Variant

v = Range("A1").Value

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=IF]#IF[/URL] [/URL]  CheckValue Then
    If v <= 0 Then
        MsgBox "Number in A1 must be positive"
        Exit Sub
    End If
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=END]#END[/URL] [/URL]  If

Range("A1").Value = v + 1

End Sub

Notice that if the compiler directive is False the code inside the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=IF]#IF [/URL] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=END]#END [/URL] IF is not executed. It is as if it's not there.