Need Design Help - VBA newb in trouble.

[JSA715]

Hey Guys,

Im working on a VBA app and playing around with Error Handling, and this is what my call for help revolves around.

Im trying to determine the best way (and possible ways) to implement error handling. I've learned VBA is pretty limited in ErrHand (I've been doing some C# as well, so I was spoiled with try/catch)

Is it possible to pass a value to an error handler? I would like to set up my 'custom' error numbers with a select statement. If so, then I can place an On Error above each spot where I expect an error. I've tried to pass values as if it were an independent Sub...

On Error Goto ErrHander(3)

But I'm sure all of the pros already know that won't work. I do not want to create a different method altogether for err handling because then I lack the ability of exiting the current method should a catastrophic error occur.

I guess I should also lay out my situation so you may better understand my question.

In part of my code, I have two lines searching for two different values.

  pRow = pFromWorkbook.Range(HeaderRange).Find(pRowHeader, lookat:=xlPart).Row
  pCol = pFromWorkbook.Range(BrRange).Find(pBR, lookat:=xlPart).Column

There is a possibility that either value cannot be found. In either case, I need to prompt the user and log each one specifically and independently. My idea was to be able to pass a value onto the ErrHand to indicate which one failed. So, in a world where it works, it would look as such:

On Error GoTo ErrHandler 1
  pRow = pFromWorkbook.Range(HeaderRange).Find(pRowHeader, lookat:=xlPart).Row
  On Error GoTo ErrHandler 2
  pCol = pFromWorkbook.Range(BrRange).Find(pBR, lookat:=xlPart).Column

But of course, you cannot pass a var the tradional way, if at all. So, another options is to have seperate Errhands for each item, one for HeaderRange and one for BrRange, but just seems messy.

Does anyone have any input on this/suggestions?

 

[BrianB]

Something like this ... ?

Sub test()
    Dim MyErrorNumber As Integer
    On Error GoTo ErrHandler
    '---------------------------------
    MyErrorNumber = 1
    'x = 1 / 0    'generate error  - remove ' to test
    '---------------------------------
    MyErrorNumber = 2
    x = 1 / 0    'generate error
    '---------------------------------
    Exit Sub
ErrHandler:
    MsgBox ("Error Number " & MyErrorNumber)
End Sub

 

[JSA715]

Brilliant.

Thats a great idea.

Thanks Brian.

-Justin