My form allows users to filter by various controls, including a search box for strings. A separate function, CalculateSearchString, processes this search field for filtering (keywords, exact phrases etc); and within this function I use error handling to trap errors caused by incorrect user input (i.e. mucking up the punctuation). The error handling works without a hitch, but I would like the code to come to a complete stop if the search input is incorrect.

What I want:

1. user inputs incorrectly formatted search string, clicks on the filter
2. function CalculateSearchFilter throws error. Message box: "fix your search terms!"
3. code stops completely, and filter is not applied

What actually happens:

1. user inputs incorrectly formatted search string, clicks on the filter
2. function CalculateSearchFilter throws error. Message box: "fix your search terms!" Exit function
3. calling procedure cmdFilterOn still runs, applying an incomplete filter (as though the search box had been empty)

Question: How do I halt code execution completely, not just in the function but in its calling procedure(s)? The function is used in more than one place, so merging it with the calling procedure is not practical.

Private Sub cmdFilterOn()
    Dim strSearch As String
    strSearch = CalculateSearchFilter

    'do more stuff
End Sub

Private Function CalculateSearchFilter() As String
On Error GoTo ErrHandler
    'do stuff
    If 'user input is wrong, then raise a custom error:
        Err.Raise 50000
    End If

ExitHandler:
    Exit Function
ErrHandler:
    If Err.Number = 50000 Then msgbox "Fix your search terms!"
    Resume ExitHandler
End Sub

Private Sub cmdYetAnotherButton()
    'which also calls on CalculateSearchFilter
End Sub

Moving the input validation to the calling procedure isn't practical either, as it would force me to repeat much of the code in CalculateSearchFilter().

(As I was writing this another solution occurred to me, which is to set CalculateSearchFilter = "an error occurred" in the function error handler, and in the calling procedure

If CalculateSearchFilter = "an error occurred" Then Exit Sub

...but is there a more "official" answer?)

CodePudding user response：

Please, try the next adapted function:

Private Function CalculateSearchFilter() As String
 On Error GoTo ErrHandler
    'do stuff
    If 1 = 1 Then 'user input is wrong, then raise a custom error:
        err.Raise 50000
    End If

ExitHandler:
    On Error GoTo 0
    Exit Function
ErrHandler:
    If err.Number = 50000 Then MsgBox "Fix your search terms!"
    CalculateSearchFilter = "Wrong String" 'the function will return this string
    Resume ExitHandler
End Function

Then, place the next code line in the subs calling the function:

  strSearch = CalculateSearchFilter
  If strSearch = "Wrong String" Then Exit Sub

CodePudding user response：

You could try to return a boolean value instead

Option Explicit

Private Sub cmdFilterOn()
    Dim strSearch As String
    If CalculateSearchFilter(strSearch) Then

        'do more stuff
    
    End If
End Sub

Private Function CalculateSearchFilter(ByRef strSearch As String) As Boolean
On Error GoTo ErrHandler
    'do stuff
    If 'user input is wrong, then raise a custom error:
        Err.Raise 50000
    End If

    CalculateSearchFilter = True
    
ExitHandler:
    Exit Function
ErrHandler:
    If Err.Number = 50000 Then MsgBox "Fix your search terms!"
    CalculateSearchFilter = False
    Resume ExitHandler
End Function

Private Sub cmdYetAnotherButton()
    'which also calls on CalculateSearchFilter
End Sub

Alternative: As mentioned in the comments if you would like to get a string back then I would return an empty string. I would not recommend to return a string like an error occured or whatsoever

Option Explicit

Private Sub cmdFilterOn()
    Dim strSearch As String
    strSearch = CalculateSearchFilter
    If Len(strSearch) > 0 Then

        'do more stuff
    
    End If
End Sub

Private Function CalculateSearchFilter() As String
On Error GoTo ErrHandler
    'do stuff
    If 'user input is wrong, then raise a custom error:
        Err.Raise 50000
    End If

    
    
ExitHandler:
    Exit Function
ErrHandler:
    If Err.Number = 50000 Then MsgBox "Fix your search terms!"
    CalculateSearchFilter = vbNullString
    Resume ExitHandler
End Function

Private Sub cmdYetAnotherButton()
    'which also calls on CalculateSearchFilter
End Sub