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

 

[p45cal]

1) how do you think I can speed the execution of this macro? there is a post on turning off screen updating at
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
I tried that and it did not work..

It should speed it up a little bit. If you took out the
.Activate 'optional
line, it might speed it up too, but again, not much. Often people turn off calculation, but we need to keep it. Remember what you're asking it to do; to go through thousands of calculations.

2)I also wanted to ask what is the purpose of the N variable?))) It is set to run through 1 to 7

It is in response to your: "I think I might need to add more parameters to test"

 

[zealot777]

thanks a lot)))) I wanted to ask about the screen updating...do you think it can be turned off in this macro???? Thank you very much!!!!)))

 

[p45cal]

The first line of the sub should be:
Application.ScreenUpdating = False

and the last line should be
Application.ScreenUpdating = True

If you break out of the Macro before it's finished, you should manually enter
Application.ScreenUpdating = True
then press Enter, in the Immediate Pane (Ctrl+G if not visible) of the VBE, to restore normal behaviour of Excel

 

[zealot777]

thanks a lot for your answer! I wanted to ask one question - if it is possible to post the results on a previously created worksheet called RESULTS instead of putting the results of each macro run on a new sheet??? also thanks a lot for adding that extra variable N to do more looping)))

 

[p45cal]

thanks a lot for your answer! I wanted to ask one question - if it is possible to post the results on a previously created worksheet called RESULTS instead of putting the results of each macro run on a new sheet??? also thanks a lot for adding that extra variable N to do more looping)))

Try this, I've moved things around a bit (and you'll have to put some Dim statements in yourself if you've kept Option Explicit, I don't think I've added any new variables to be defined). Untested:

Sub blahx()
Set WorkingSht = ActiveSheet
'Set newSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set newSht = Sheets("RESULTS")
GapBetweenResults = 1    'adjust to change the gap between results blocks.
DestnRow = newSht.Cells(Rows.Count, 1).End(xlUp).Row + 1 + GapBetweenResults
With WorkingSht
    .Activate  'optional
    Set SourceRng = .Range("A10:X12")
    SourceWidth = SourceRng.Columns.Count
    SourceHeight = SourceRng.Rows.Count
    GapBetweenResults = SourceHeight + GapBetweenResults

    For i = 1 To 20
        .Range("A1") = i
        For j = 1 To 5
            .Range("B2") = j
            For k = 1 To 3
                .Range("C4") = k
                For n = 1 To 7
                    .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

 

[zealot777]

Thanks a lot for this last macro....I added those previous DIM declarations you gave to me earlier and it looks like this now:

Option Explicit

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
    With ActiveSheet.ChartObjects("Chart 1").Chart
    
Set WorkingSht = ActiveSheet
'Set newSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set newSht = Sheets("RESULTS")
GapBetweenResults = 1    'adjust to change the gap between results blocks.
DestnRow = newSht.Cells(Rows.Count, 1).End(xlUp).Row + 1 + GapBetweenResults
With WorkingSht
    .Activate  'optional
    Set SourceRng = .Range("B4:AE7")
    SourceWidth = SourceRng.Columns.Count
    SourceHeight = SourceRng.Rows.Count
    GapBetweenResults = SourceHeight + GapBetweenResults

    For i = 1 To 20
        .Range("i19") = i
        For j = 1 To 5
            .Range("j19") = j
            For k = 1 To 3
                .Range("k19") = k
                For n = 1 To 7
                    .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

I try to run it and it shows the following error:

Compile Error:
Expected End IF

and highlights the last line of code (END SUB)...not sure why...can you please see if there is something that prevents this beautiful macro from operating?)

Thanks so much!)))

 

[p45cal]

Difficult to see how there could be a missing End IF, though there might be a missing End WITH.

How has this line crept in?:

With ActiveSheet.ChartObjects("Chart 1").Chart

Remove it.

 

[zealot777]

Difficult to see how there could be a missing End IF, though there might be a missing End WITH.

How has this line crept in?:

With ActiveSheet.ChartObjects("Chart 1").Chart

Remove it.

Fantastic!)) works very well) thanks again for your continuous support in this!)

 

[zealot777]

I also have a quick question - where do I specify in the last macro the row number where the results of each loop will be placed on the RESULTS page? In the previous I just changed
DestnRow = 3

now I am not so sure
Thanks!
Dima

 

[p45cal]

I also have a quick question - where do I specify in the last macro the row number where the results of each loop will be placed on the RESULTS page? In the previous I just changed
DestnRow = 3

now I am not so sure

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.