Exit Sub if condition not met in function procedure

Question

I want to Exit Sub if my Function procedure (Colvalidation) doesn't find a column from the array below. I know it's impossible to End Sub within the Function, and I have no idea how to handle it. Could you help me with that?

Sub test()
(...)
    'Check if "Earnings", "Deductions", "Employer" headers exist

        vals = Array("Earnings", "Deductions", "Employer")
        vals = Array("Earnings", "Deductions", "Employer Paid Benefits and Taxes")

Set myRng = Worksheets(PayrollWS).Range(Worksheets(PayrollWS).Cells(MyR, 1), Worksheets(PayrollWS).Cells(MyR, 25))

For Each val In vals
    Colvalidation myRng, val
    If ColFound = False Then 'error = "Variable not defined"
    Exit Sub
Next

(...)
End Sub

Function Colvalidation(Rng As Range, value As Variant)
Dim rngX As Range, ColFound as Boolean


    Set rngX = Rng.Find(what:=value, lookat:=xlPart, LookIn:=xlValues)
    If rngX Is Nothing Then
        MsgBox value & " - Column Not Found" 'if column not found, Exit Sub after MsgBox
        ColFound = False
        Exit Function
    End If
End Function

Have the function return a `Boolean`, make it False inside the function and then Exit Sub if False in `test`. — BigBen, Apr 02 '20 at 18:54
Btw: `Set myRng = Worksheets(PayrollWS).Range(Cells(MyR, 1), Cells(MyR, 25))` is problematic - see [Why does Range work, but not cells](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells). — BigBen, Apr 02 '20 at 18:55
I have updated the code above. I'm still not sure how to handle it. — MrNoNo, Apr 03 '20 at 07:25

score 1 · Accepted Answer · answered Apr 03 '20 at 14:09

Have the function return a Boolean, make it False if the Find did not succeed.

Public Function Colvalidation(ByVal Rng As Range, ByVal value As Variant) As Boolean
    Dim rngX As Range
    Set rngX = Rng.Find(What:=value, LookAt:=xlPart, LookIn:=xlValues)

    If rngX Is Nothing Then
        MsgBox value & " - Column Not Found" 
        Colvalidation = False
    Else
        Colvalidation = True
    End If
End Function

In the main subroutine, Exit Sub if the function returns False:

For Each val In vals
    If Not Colvalidation(myRng, val) Then Exit Sub
Next

Exit Sub if condition not met in function procedure

1 Answers1