Merging models

[Rainmanne]

I have currently 4 models I use to import the data from an excel spreadsheet and manipulate them. The source data come in two templates and two types. Therefore 4 models to cover each option. One of the types also has two subtypes.

So I've been thinking about merging the four models into one and use a user form to determine the options. I just wonder what would be the best way to do it. Because basically I will have 6 options.

The model is not too sophisticated but has a fair amount of code. It works as follows. Let's assume that there two templates: ABC and XYZ, two types: External and Internal and the Internal type has also two tiers: Tier 1 and Tier 2.

The model is a spreadsheet with a few worksheets, which present the data in the way I need. All the data are linked to one or two worksheets I import from a source workbook. As the source worksheets are not present at the beginning, all formulas in the resulted worksheets are commented.

So when a user runs the model, it opens the source workbook, copies the necessary worksheets into the model, saves the file under a new name, uncomments the formulas in the worksheets, which present the data in a particular way, does some necessary formatting, removes conditional formatting and saves the file again.

The two templates are quite similar but different enough to have slightly different code, though some chunks of the code are the same.

So I've been thinking I can do it two ways. I can copy main macros for each option and have an IF structure to decide which o ne to run depending on the choice of options in the user form. Some worksheets are different depending on the template, so I will have to delete those I do not need and rename the ones I need.

Alternatively, I can merge the code in one macro and run the chunks of the code depending on the choice in the user form.

I just do not know which is the more efficient way of doing it?

 

[Rainmanne]

I don't want to create a new thread with a practically same question. I will try to continue here as I have made some progress so far. If I don't have responses because people probably do not look at old threads I will post it as a separate one.

I have created a new functional framework with a user form, which pops up when the file is opened. The user form provides a way to select parameters for the model. I've changed the names a bit from the post above and I am copying from my other thread:

Option Buttons: Template 1 and Template 2

Combo Box: with two options Public and Private (Public has two tiers below while Public has no tiers)

Option Buttons: Tier 1 and Tier 2

Check Box: wipe out the formatting

I have set up an IF statement to make the structure work. Instead of actual macros I have put message boxes to test if it works and it seem to work OK.

Now I need to figure out how to make the model work with the real data. I can either create six macros and call them instead of displaying message boxes. But the macros are quite similar to a larger extent. The issue is that if I need to change anything in a part of the code which common across all 6 macros I had to change it six times in each of them.

Another option is probably to have a parts of the code in the general IF structure so I will have one main sub. One thing I am not sure about is that at the moment the IF structure sits in the UserForm Private sub button click event. I wonder if I can build a full macro their or I have to use the results from this IF macro in the macro in the main module and how? My UserForm1 (Code) looks like this at the moment, so I wonder i someone could suggest the best way to go forward.

Private Sub datatype_Change()
If datatype.Value = "Private" Then
    radiotier1.Enabled = True
    radiotier2.Enabled = True
Else
    radiotier1.Enabled = False
    radiotier2.Enabled = False
End If
End Sub

Private Sub clear_btn_Click()
radiotempl1.Value = False
radiotempl2.Value = False

datatype.Clear

With datatype

    .AddItem "Public"
    .AddItem "Private"

End With

radiotier1.Value = False
radiotier2.Value = False
radiotier1.Enabled = False
radiotier2.Enabled = False

wipe_format.Value = True

End Sub
Private Sub cancel_btn_Click()
    Unload Me
    Sheets("Model").CommandButton1.Visible = True
End Sub

Private Sub modelrun_btn_Click()

If radiotempl1.Value = True Then
    If datatype.Value = "Public" Then
        If wipe_format.Value = True Then
                MsgBox "Template 1 Public Model Wipe Out"
        Else
                MsgBox "Template 1 Public Model No Wipe Out"
        End If
    Else
        If radiotier1.Value = True Then
            If wipe_format.Value = True Then
                MsgBox "Template 1 Private Model Tier 1 Wipe Out"
            Else
                MsgBox "Template 1 Private Model Tier 1 No Wipe Out"
            End If
        Else
            If wipe_format.Value = True Then
                MsgBox "Template 1 Private Model Tier 2 Wipe Out"
            Else
                MsgBox "Template 1 Private Model Tier 2 No Wipe Out"
            End If
        End If
    End If
Else
    If datatype.Value = "Public" Then
        If wipe_format.Value = True Then
            MsgBox "Template 2 Public Model Wipe Out"
        Else
            MsgBox "Template 2 Public Model No Wipe Out"
        End If
    Else
        If radiotier1.Value = True Then
            If wipe_format.Value = True Then
                MsgBox "Template 2 Private Model Tier 1 Wipe Out"
            Else
                MsgBox "Template 2 Private Model Tier 1 No Wipe Out"
            End If
        Else
            If wipe_format.Value = True Then
                MsgBox "Template 2 Private Model Tier 2 Wipe Out"
            Else
                MsgBox "Template 2 Private Model Tier 2 No Wipe Out"
            End If
        End If
    End If
End If
End Sub

Private Sub UserForm_Initialize()

'Reset the form

radiotempl1.Value = False
radiotempl2.Value = False

datatype.Clear

With datatype

    .AddItem "Public"
    .AddItem "Private"

End With

radiotier1.Value = False
radiotier2.Value = False
radiotier1.Enabled = False
radiotier2.Enabled = False


wipe_format.Value = True


End Sub

 

[Rainmanne]

Another thing I have just realised is that if a user does not select any option or only some of them, the code still return one of the outcomes and I need to make sure that it does not happen. So I either have to add a bunch of other options which return a message "Please select ..." or find some other way to prevent the model running without everything is selected.