Template UserForm Input - launch, input, validation, error handling

I'm building a template [Process-Tracker] spreadsheet.

The idea is that, for any process where we can define what steps should occur in what order (and preferably, how far apart), there will be a spreadsheet with buttons for:

• Adding a new [Item] to be tracked

• Updating the List of [Items] and their progress

• and a list of [Next Steps] ordered by [Due date].

I have written the [template] code for the Buttons and UserForm. The idea is that, when creating an actual process, I simply add specific validation for the expected variables/names and make sure it still outputs in a consistent format, and everything else will run just fine.

Here is my Form and My Code:

[Worksheet] Add Item Button

Public Sub Button_AddItem_Click()

    CallFormInput

End Sub

Form Input Sub

Public Sub CallFormInput()

    UserFormButtonWasPressed = False

    UF_New_Process_Item.Show

    If Not UserFormButtonWasPressed Then PrintErrorMessage "Please only exit the Form via one of the buttons provided", showMessageBox:=True, endExecution:=True

End Sub

UserForm Code

```
Option Explicit
Option Compare Text

Private values As Collection

Private Sub UF_Exit_Form_Click()

TestFormSyntax
UserFormButtonWasPressed = True
UF_New_Process_Item.Hide
Exit Sub

End Sub

Private Sub UF_Add_Item_Click()

Dim values As Collection

Dim inputsWithFailedValidation As Variant
inputsWithFailedValidation = Array()

Dim validationMessage As String, passedValidation As Boolean

Set values = New Collection

UserFormButtonWasPressed = True

ValidateAndAssignValues values, validationMessage, passedValidation, inputsWithFailedValidation

If passedValidation Then
AddItem values
Else
HandleFailedValidation validationMessage, passedValidation, inputsWithFailedValidation
End If

End Sub

Private Sub UF_Add_Item_Recurring_Click()

Dim values As Collection

Dim inputsWithFa

You have added logic to an event of a button, which is not the best choice. You should extract most of the logic to at least separate methods or much better to a separate class.

First let us take a look at this

Private Sub ValidateValues(ByRef values As Collection, ByRef validationMessage As Variant, ByRef passedValidation As Boolean, ByRef inputsWithFailedValidation As Variant)

    '/ Convert to correct varType and validate - Names, Emails, Phone Numbers, Dates etc.

    '/ If failed validation, add to validation Message, add me.controls.item(key) to inputsWithFailedValidation

    '/ Set passed Validation True/False

    passedValidation = True

End Sub

I assume that you will implement proper validation instructions later on, but I wanted to point out that having a Sub instead of a Function which is taking a ByRef Boolean which is later queried isn't the way to go.

So let us change this to a function to get rid of that parameter.

Private Function ValidateValues(ByRef values As Collection, ByRef validationMessage As Variant, ByRef inputsWithFailedValidation As Variant) As Boolean

    ValidateValues = true
End Function

The Sub AssignValues should be changed to a Function GetValues() As Collection because you have a Sub with only a ByRef parameter. As it seems that the form won't have more controls than 255 we can also change the Dim i As Long to Dim i As Integer.

Private Function GetValues() As Collection

    '/ Pass all values as text, formatting/validation will be handled later

    Dim i As Integer
    Dim controlType As String
    Dim uf_Control As Control
    Dim values As Collection

    '/ item = control item text, key = control item name
    Set values = New Collection

    For i = 0 To Me.Controls.Count - 1

        Set uf_Control = Me.Controls.Item(i)

        controlType = TypeName(uf_Control)

        If controlType = "textbox" Then

            values.Add uf_Control.Text, uf_Control.name

        End If

    Next i

    Set GetValues = values

End Function

Now let's switch to UF_Add_Item_Click() which is having almost identical code like the UF_Add_Item_Recurring_Click() method. This code duplication should be extracted to a separate method.

So let us create a method ProcessItem

Private Function ProcessItem() As Boolean

    ProcessItem = True
    Dim values As Collection

    Dim inputsWithFailedValidation As Variant
    inputsWithFailedValidation = Array()

    Dim validationMessage As String
    Dim passedValidation As Boolean

    UserFormButtonWasPressed = True

    Set values = GetValues()

    If Not ValidateValues(values, validationMessage, inputsWithFailedValidation) Then

        HandleFailedValidation validationMessage, passedValidation, inputsWithFailedValidation
        ProcessItem = False

    Else

        AddItem values

    End If

End Sub

I have changed the style the local variables are defined, because having multiple variables defined on the same line should be avoided for readability.

I have added the call to AssignValues which helps to get rid of the ValidateAndAssignValues method.

Now the former UF_Add_Item_Click() and UF_Add_Item_Recurring_Click() method will look like so

Private Sub UF_Add_Item_Click()

    ProcessItem

End Sub

Private Sub UF_Add_Item_Recurring_Click()

    If ProcessItem() Then 

        Button_AddItem_Click '/ Starts again as if clicked the "Add Item" Button on the worksheet

    End If

End Sub

I can't tell much about the AddItem method because there are calls to methods which aren't in the posted code.

StackExchange Code Review Q#106011, answer score: 8