0

Can we use : On Error GoTo ... in a VBA function ?

I always use it to manage my eventual error inside my Sub but when I use it inside a function it's like it didn't work as expected and always go through. When I remove it the function work well.

There is a specific way to manage error in function ?

EDIT

Here is an exemple of one of my function :

Function Row_Nbr_In_Sheet(shtName As String) As Integer
  On Error GoTo ErrorManager
  
  Row_Nbr_In_Sheet = ThisWorkbook.Worksheets(shtName).UsedRange.Rows.Count

ErrorManager:
  MsgBox("Something weird here")
  End
End Function

this function work well without "GoTo" but it trigger the MsgBox when I use it. It's exactly the same for each functions of my code.

Xodarap
  • 343
  • 1
  • 6
  • 23
  • 1
    Yes, its the same for subs and functions. – Kostas K. Jun 23 '21 at 12:56
  • 2
    Error handling should work the same in a Sub or Function. If you post your code we could help you better. – Brian M Stafford Jun 23 '21 at 12:56
  • 1
    https://stackoverflow.com/q/12687105/11683? – GSerg Jun 23 '21 at 12:56
  • 1
    Yes ```On Error GoTo ...``` can be used, but you shouldn't name your error handler "error". VBA doesn't like ambiguous names. Call it for example "eh" (for error handler), thus ```On Error GoTo eh```, and be sure to add a tag with that name in the same sub/function. – Vincent Jun 23 '21 at 12:56
  • 1
    @Vincent It's not a matter of ambiguous name but a keyword. VBA has no problem with ambiguous names as long as they're fully qualified. – Kostas K. Jun 23 '21 at 12:58
  • well, it's weird. I will edit my post with some code in few minutes – Xodarap Jun 23 '21 at 13:23
  • `Error` is a keyword, pick something else. The same goes for `Err`. – Kostas K. Jun 23 '21 at 13:35
  • I renamed Error into ErrorManager but the issue is still the same – Xodarap Jun 23 '21 at 13:38
  • 2
    You need to add `Exit Function` right above `ErrorManager:`. – Brian M Stafford Jun 23 '21 at 13:40
  • this is very specific to my functions, I use "GoTo" in many Sub without any probleme but each time I use it in a function it fail – Xodarap Jun 23 '21 at 13:40
  • Thanks for your help Brian but I guess "End" is stronger than "Exit" if I just exit the function the program will continue with the error – Xodarap Jun 23 '21 at 13:42
  • @Xodarap No, it won't. It won't reach the `exit function` in the case of an error. – GSerg Jun 23 '21 at 13:42
  • You don't get it, If I reach an error in my process I want to stop everything because each functions are important to get the good result. If I use "Exit function" the function is avoiding but the process continue, if I use End it stop. (I just test it) – Xodarap Jun 23 '21 at 13:52
  • The purpose of an error handler is to handle an error. If you cannot handle an error, you don't handle it, you let it bubble up to the caller. In which case the caller will see it as an error and will be able to handle it or not. One function should not have authority to stop the entire processing, it should only report the error (by not handling it, or re-throwing it). And then only the topmost procedure will include an error handler that will trap everything, display a message box and exit (for which there would not need to be an `End` either). – GSerg Jun 23 '21 at 14:21
  • OK I understand, I'm still a begginer so I'm not yet able to make a workflow like that by myself at the moment. Thanks to you I understand better the role of the error handler. – Xodarap Jun 23 '21 at 14:39

0 Answers0