Excel VBA standard practice for writing procedures based on combinative variables

Question

I have a series of (complex) procedures which run via a series of userforms in order to create a new workbook, insert several modules of needed VB based on user input, format the new workbook by copying elements in from a series of hidden templates within the workbook that contains the procedures, and prompt the user to save the document as an .xlsm file at the end of the procedure. I have achieved this.

Some variations of the procedures I have written to achieve this are written in a modular way, because the userforms act as a small visual tool for the user to customize the workbook they wish to generate in two ways: they are able to specify how many worksheets they wish to have in the workbook, and specify how many data tables they wish to appear for each worksheet in the workbook, and based on the combination of worksheets and data tables per worksheet they wish to create, a procedure is called to accommodate it.

That procedure generates the workbook and inserts VB code into it based on the user's needs, and then calls the series of separate modular procedures which build each individual data table for each needed worksheet. I can provide these code examples if requested.

My question pertains to the way I am having these procedures called from the userform. Currently I use a series of If statements (unattractive, but I'm unsure if it is necessary in this case, as each statement is based on a unique number combination, and each 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

Based on which of these statements is True, a procedure is called which creates the new workbook, inserts code based on the number of worksheets and data tables the user needs. You can see from this example that this probably would not scale very nicely if the number of possible worksheets and data tables were to increase, the above example is only for each possible number combination of the numbers 1, 2, and 3 for up to two worksheets.

Is there a cleaner, more scaleable way to call separate procedures using If than how I am using it in this example? Or is it necessary to use If to handle each possible combination of variables the user may require?

Answer 1

Assuming you know the combination of parameters, then standardize your sub names. Then you can use application.run and feed it with a string combination e.g.

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

asssuming the following:

NumberOfTabsNeeded  : 2
Tab1Amount: 2
Tab2Amount: 3

then the sub tabs2tabOne2tabTwo3 would have been called.

No need for a large hard coded case statement or if statement block.

Answer 2

Try using Case/Select. It is a good way to avoid multiple If/Then statements how you are using them and they can be nested. Below is an example:

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

Syntax is below, where expression is the variable or value to compare. Case Else can be used as a "catch all" when no values meet the compared values.

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

One way or another, you will have to handle each combination - whether it be through your subroutines or conditional statements. Depending on what your other subroutines look like, you could pass variables to them to handle some of the logic.