Software to Test ALL Input Combinations.

[zealot777]

Hello Friends!
I need to test various parameter combinations of a model I built in Excel. I have been thinking of using a macro to run through all combinations of say tree cells (e.g. A1 (1 to 20), B2 (1 to 5) and C4(1 to 3)) – all in all 300 combinations in this example. I think I might need to add more parameters to test so I think a macro would be limiting (which would need to be recorded to add the ability to loop through more inputs). I have been thinking of using Monte Carlo simulation software for EXCEL. I have studied Palisade Risk, Frontline Risk Solver Pro and Oracle Crystal Ball and strangely enough NONE of those products allows to test ALL parameter combinations of the input variables in a model. Yes they allow all kinds of the random sampling of the parameter set universe but that is not applicable in my situation where I need exhaustive testing of ALL parameter combinations (at each run a whole set of stats needs to be copied to another sheet). I wonder if there is software that does this exhaustive test of inputs?
I would be happy to hear any suggestion!
Thanks a lot for your time!
Dima

 

[zealot777]

Experiment by changing the value in red in the line:

DestnRow = newSht.Cells(Rows.Count, 1).End(xlUp).Row + 1 + GapBetweenResults

Because you asked me put the results onto a pre-existing sheet called RESULTS, I had the macro place the data below any pre-existing data on that sheet. The macro finds that data and starts placing data below it. Maybe I was wrong to make that assumption.

If you wanted, you could change that line to just:
DestnRow = newSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
but if there is data on the sheet already, the value must be 1 or more otherwise you will overwrite data.

Finally, if you're sure the macro will start running with a blank RESULTS sheet every time (we could get the code to wipe that sheet if you wanted) then revert that line to:
DestnRow = 4
adjusting the 4, of course, to suit.

Thank you for so much for taking the time to perfect an already fabulous macro!)


I had two more questions)))

1) Yes it would be awesome to have the macro clear the contents of all columns from A to AD before the macro starts. Now I do it by hand before each time I start the macro

2) You know, this macro iteratively places different numbers to run calculations on by another part of the worksheet - I have also recently put some calculations on the RESULTS worksheet - some of these are array formulas which start to slow down the original calculations...Do you think it can be possible to turn off calculations of the formulas on the RESULTS sheet for the time the macro runs and then turn it back on after the end of the loop?)) I hope that is possible..that would speed up macro execution threefold!

Thanks a lot again for your support!)

I attach the current version of the macro I am using:

Sub blahx2()
Dim WorkingSht As Worksheet, i As Long, j As Long, k As Long, n As Long, newSht As Worksheet, DestnRow As Long
Dim SourceRng As Range, SourceWidth As Long, SourceHeight As Long, GapBetweenResults As Long
   
Set WorkingSht = ActiveSheet
'Set newSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set newSht = Sheets("RESULTS")
GapBetweenResults = 0    'adjust to change the gap between results blocks.
DestnRow = 3
With WorkingSht
    .Activate  'optional
    Set SourceRng = .Range("B4:AE7")
    SourceWidth = SourceRng.Columns.Count
    SourceHeight = SourceRng.Rows.Count
    GapBetweenResults = SourceHeight + GapBetweenResults

    For i = 1 To 1
        .Range("i19") = i
        For j = 1 To 10
            .Range("j19") = j
            For k = 1 To 5
                .Range("k19") = k
                For n = 1 To 1
                    .Range("E6") = n
                    'do your result grabbing here:
                    newSht.Cells(DestnRow, 1).Resize(SourceHeight, SourceWidth).Value = SourceRng.Value
                    DestnRow = DestnRow + GapBetweenResults
                Next n
            Next k
        Next j
    Next i

End With
End Sub

Cheers!)
Dima

 

[p45cal]

1) Yes it would be awesome to have the macro clear the contents of all columns from A to AD before the macro starts. Now I do it by hand before each time I start the macro

Untested, just added to code you've quoted here, below.

2) You know, this macro iteratively places different numbers to run calculations on by another part of the worksheet - I have also recently put some calculations on the RESULTS worksheet - some of these are array formulas which start to slow down the original calculations...Do you think it can be possible to turn off calculations of the formulas on the RESULTS sheet for the time the macro runs and then turn it back on after the end of the loop?)) I hope that is possible..that would speed up macro execution threefold!

I really don't know whether it'll work!:

Sub blahx2()
[COLOR=Red]Application.Calculation = xlCalculationManual[/COLOR]
Dim WorkingSht As Worksheet, i As Long, j As Long, k As Long, n As Long, newSht As Worksheet, DestnRow As Long
Dim SourceRng As Range, SourceWidth As Long, SourceHeight As Long, GapBetweenResults As Long
   
Set WorkingSht = ActiveSheet
'Set newSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set newSht = Sheets("RESULTS")
[COLOR=Magenta]newSht.range("A:AD").clearcontents[/COLOR]
GapBetweenResults = 0    'adjust to change the gap between results blocks.
DestnRow = 3
With WorkingSht
    .Activate  'optional
    Set SourceRng = .Range("B4:AE7")
    SourceWidth = SourceRng.Columns.Count
    SourceHeight = SourceRng.Rows.Count
    GapBetweenResults = SourceHeight + GapBetweenResults

    For i = 1 To 1
        .Range("i19") = i
        For j = 1 To 10
            .Range("j19") = j
            For k = 1 To 5
                .Range("k19") = k
                For n = 1 To 1
                    .Range("E6") = n
                    'do your result grabbing here:
                       [COLOR=Red]WorkingSht.calculate[/COLOR]
                    newSht.Cells(DestnRow, 1).Resize(SourceHeight, SourceWidth).Value = SourceRng.Value
                    DestnRow = DestnRow + GapBetweenResults
                Next n
            Next k
        Next j
    Next i

End With
[COLOR=Red]Application.Calculation =xlCalculationAutomatic[/COLOR]
End Sub

 

[zealot777]

superb!))) thank you very much for your much appreciated assistance!)
Have a nice day!)