Excel VBA Standard Practice for Writing Procedures Based on Combination Variables

Question

I have a number of (complex) procedures that are performed through a series of user forms to create a new book, insert several modules of the required VB based on user input, format a new book by copying elements from a series of hidden patterns in a book containing procedures, and offer the user save the document as a .xlsm file at the end of the procedure. I have achieved this.

Some of the options for the procedures that I wrote to achieve this are written in a modular way, because custom forms act as a small visual tool for the user to set up the book that they want to generate in two ways: they can indicate how many sheets they want in the workbook , and indicate how many data tables they will be displayed for each worksheet in the book, and based on the combination of worksheets and data tables on the sheet that they want to create, the procedure in is called to put it.

This procedure creates a workbook and inserts VB code into it based on the needs of the user, and then calls a series of separate modular procedures that build each individual data table for each necessary worksheet. I can provide these code examples if required.

My question is about how I get these procedures called from a user form. I am currently using a series of If statements (unattractive, but I'm not sure if this is necessary, since each statement is based on a unique combination of numbers and each one calls a separate procedure):

'Declare the number of worksheets the user needs
Dim NumberOfTabsNeeded As String
NumberOfTabsNeeded = UserForm1.ComboBox2.Value    

'Number of data tables needed per worksheet
Dim Tab1Amount As String
Tab1Amount = UserForm1.Label71.Caption
Dim Tab2Amount As String
Tab2Amount = UserForm1.Label72.Caption

'One worksheet needed
If NumberOfTabsNeeded = "1" And Tab1Amount = "1" Then Call OneTabOneDTableAddCode
If NumberOfTabsNeeded = "1" And Tab1Amount = "2" Then Call OneTabTwoDTablesAddCode
If NumberOfTabsNeeded = "1" And Tab1Amount = "3" Then Call OneTabThreeDTablesAddCode

'Two worksheets needed
If NumberOfTabsNeeded = "2" And Tab1Amount = "1" And Tab2Amount = "1" Then Call TwoTabsOneDTableEachAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "1" And Tab2Amount = "2" Then Call TwoTabsOneDTableTwoDTablesAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "1" And Tab2Amount = "3" Then Call TwoTabsOneDTableThreeDTablesAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "2" And Tab2Amount = "1" Then Call TwoTabsTwoDTablesOneDTableAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "2" And Tab2Amount = "2" Then Call TwoTabsTwoDTablesTwoDTablesAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "2" And Tab2Amount = "3" Then Call TwoTabsTwoDTablesThreeDTablesAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "3" And Tab2Amount = "1" Then Call TwoTabsThreeDTablesOneDTableAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "3" And Tab2Amount = "2" Then Call TwoTabsThreeDTablesTwoDTablesAddCode
If NumberOfTabsNeeded = "2" And Tab1Amount = "3" And Tab2Amount = "3" Then Call TwoTabsThreeDTablesThreeDTablesAddCode

, , , , , , . , , , , , - 1, 2 3 .

, , ? If , ?

Answer 1

, , . application.run .

application.Run "tabs" & NumberOfTabsNeeded  & "tabOne" & Tab1Amount & "tabTwo" & Tab2Amount

:

NumberOfTabsNeeded  : 2
Tab1Amount: 2
Tab2Amount: 3

2tabOne2tabTwo3.

.

Answer 2

Case/Select. If/Then, , . :

Select Case NumberOfTabsNeeded
    Case 1 ' One Worksheet Needed
        Select Case Tab1Amount
            Case 1: Call OneTabOneDTableAddCode
            Case 2: Call OneTabTwoDTablesAddCode
            Case 3: Call OneTabThreeDTablesAddCode
        End Select
    Case 2
        ...    
End Select

, . Case Else "catch all", .

Select Case Expresssion
    Case [Value or Value1 To Value2]
End Select

, - . , , .