I have this very recurrent pattern at my VBA macros, where I have to ask the user to give me certain information that will be necessary for the processes to come. Most often for example, I need him to give me some workbooks and maybe a date.
So I often declare a Type whose elements are all the workbooks I'm going to need, which will be the parameter I'll be passing around the processes. In this case, like this (variable names have been deliberately changed to hide the privacy of the actual report I'm making here):
Public Type FourTrackers aFirst As Workbook bSecond As Workbook cThird As Workbook dFourth As Workbook End Type
A userform with fields will collect strings with the fullpath to the workbooks, and perhaps some dropdown lists with possible months and years for
Option Explicit '#################### ' Always in this order ' Constructor & Destructor ' OK & Cancel buttons ' QueryClose & frmUserCancelled ' Relevant Type getter ' Form Fields events ' Helper Functions '#################### 'Private module level data variable Private mCancel As Boolean Private FourTrackers As FourTrackers '@Description("Enumeration of colours. Keep in mind that VBA spells a colour as BGR (inverse order).") Private Enum meColours good = &HFFFFFF bad = &HD9BFB End Enum '@Description("Initiate the form listing values for month and year, and marking the boxes as unempty") Private Sub UserForm_Initialize() mCancel = False Static iSingleton As Long iSingleton = iSingleton + 1 If iSingleton > 1 Then Err.Raise 1000, mainForm, "This userform is a singleton!" 'files Me.T_First.BackColor = meColours.bad Me.T_Second.BackColor = meColours.bad Me.T_Third.BackColor = meColours.bad Me.T_Fourth.BackColor = meColours.bad End Sub '@Description("") Private Sub UserForm_Terminate() Debug.Print "Termination of the userForm. Remember it should be a singleton, this shouln't appear" 'Debug.Assert False End Sub '@Description("") Private Sub btn_OK_Click() If Me.T_First.BackColor = meColours.good _ And Me.T_Second.BackColor = meColours.good _ And Me.T_Third.BackColor = meColours.good _ And Me.T_Fourth.BackColor = meColours.good _ Then FourTrackers.aFirst= Workbooks.Open(Me.T_First.Value) FourTrackers.bSecond= Workbooks.Open(Me.T_Second.Value) FourTrackers.cThird= Workbooks.Open(Me.T_Thirds.Value) FourTrackers.dFourth= Workbooks.Open(Me.T_Fourth.Value) Me.Hide Else MsgBox "Select first all reports" End If End Sub '@Description("") Private Sub btn_Cancel_Click() mCancel = True Me.Hide End Sub '@Description("in case of queryclose, remit yourself to a cancel as well.") Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True btn_Cancel_Click End If End Sub '@Description("Indicate if the form has been canceled for real. Good for QueryClose event.") Public Property Get frmUserCanceled() As Boolean frmUserCanceled = mCancel End Property '@Description("Returns the struct with the Trackers") Public Property Get frmFourTrackers() As FourTrackers frmFourTrackers = FourTrackers End Property ' double click on the box or on the dots will have the same effect Private Sub T_First_DblClick(ByVal Cancel As MSForms.ReturnBoolean) B_First_Click End Sub Private Sub T_Second_DblClick(ByVal Cancel As MSForms.ReturnBoolean) B_Second_Click End Sub Private Sub T_Third_DblClick(ByVal Cancel As MSForms.ReturnBoolean) B_Third_Click End Sub Private Sub T_Fourth_DblClick(ByVal Cancel As MSForms.ReturnBoolean) B_Fourth_Click End Sub ' Select the files Private Sub B_First_Click() Dim temp As String temp = selectFiles("First", ThisWorkbook.Path, False, "Excel files", "*.xlsx, *.xls, *.csv, *.xlsb, *.xlsm") Me.T_First.Value = temp Me.T_First.BackColor = auxSelectColor(temp) Call FillFromTheFirst(temp) End Sub Private Sub B_Second_Click() Dim temp As String temp = selectFiles("Second", ThisWorkbook.Path, False, "Excel files", "*.xlsx, *.xls, *.csv, *.xlsb, *.xlsm") Me.T_Second.Value = temp Me.T_Second.BackColor = auxSelectColor(temp) End Sub Private Sub B_Third_Click() Dim temp As String temp = selectFiles("Third", ThisWorkbook.Path, False, "Excel files", "*.xlsx, *.xls, *.csv, *.xlsb, *.xlsm") Me.T_Third.Value = temp Me.T_Third.BackColor = auxSelectColor(temp) End Sub Private Sub B_Fourth_Click() Dim temp As String temp = selectFiles("Fourth", ThisWorkbook.Path, False, "Excel files", "*.xlsx, *.xls, *.csv, *.xlsb, *.xlsm") Me.T_Fourth.Value = temp Me.T_Fourth.BackColor = auxSelectColor(temp) End Sub '@Description("Helper function. Prompts the user to select a file and returns a string with its whole path and fullname.") Private Function selectFiles(ByVal DisplayText As String, _ ByVal InitialFolder As String, _ Optional ByVal MultipleSelection As Boolean = False, _ Optional ByVal filter As String = vbNullString, _ Optional ByVal filterType As String = vbNullString _ ) As String Dim fDialog As FileDialog Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog 'Optional: FileDialog properties .AllowMultiSelect = MultipleSelection .Title = DisplayText .InitialFileName = InitialFolder 'Optional: Add filters .Filters.Clear .Filters.Add filter, filterType '?? 'Show the dialog. -1 means success! If .Show = -1 Then selectFiles = fDialog.SelectedItems(1) Else selectFiles = vbNullString End If End With End Function '@Description("Selects a colour, good if the string parameter is not empty, bad if vbNullString.") Private Function auxSelectColor(ByVal temp As String) As meColours If temp <> vbNullString Then auxSelectColor = meColours.good Else auxSelectColor = meColours.bad End If End Function ' Select the files Private Sub FillFromTheFirst(ByVal temp As String) 'just spagetthi code to try to predict the other three workbooks once we know where the first one is, so we don't have to subsequently ask the user for a lot of prompts to select files. End Sub
As I've tried, the UI will only verify that the four files have been selected on the OK button, and if so, assign the workbook references to the struct and then hide himself; so far this is the "only" interaction of the UI with the Data, which I've tried to separate as much as I can. But here is where I see all this UI Architectural Patterns appearing on my Stack Overflow reseach and I'm not sure of the approach to take.
Then, this is the call on a standard module:
Public mainForm as mainForm 'does this shuts up the creation of the default UserForm? Public Type TwoSapReports ReportMain As Workbook ReportSubTotals As Workbook End Type Public Sub DataForm(ByRef rep As TwoSapReports) Dim myTrackers As FourTrackers With New mainForm .Show If Not .frmUserCanceled Then meTrackers = .frmFourTrackers End If .Hide End With 'do stuff with myTrackers. End Sub