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]

It's not too complicated:

Sub blah()
For i = 1 To 20
  Range("A1") = i
  For j = 1 To 5
    Range("B2") = j
    For k = 1 To 3
      Range("C4") = k
      'do your result grabbing here
    Next k
  Next j
Next i
End Sub

and to add a 4th parameter:

Sub blah2()
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("D13") = n
        'do your result grabbing here
      Next n
    Next k
  Next j
Next i
End Sub

 

[zealot777]

thanks for the promts reply, do you think you can help me further and add a line of code to grab the A1 TO E89 range and place this set of outputs one after another on a separate sheet after each iteration?
Thanks a lot!

 

[p45cal]

Sub blahx()
Set WorkingSht = ActiveSheet
Set newSht = Sheets.Add(after:=Sheets(Sheets.Count))
DestnRow = 2
With WorkingSht
  .Activate  'optional
  Set SourceRng = .Range("A1:E89")
  SourceWidth = SourceRng.Columns.Count
  SourceHeight = SourceRng.Rows.Count
  GapBetweenResults = SourceHeight + 1  'adjust the +1 to change the gap between results blocks.

  For i = 1 To 20
    .Range("A1") = i
    For j = 1 To 5
      .Range("B2") = j
      For k = 1 To 3
        .Range("C4") = k
        'do your result grabbing here:
        newSht.Cells(DestnRow, 1).Resize(SourceHeight, SourceWidth).Value = SourceRng.Value
        DestnRow = DestnRow + GapBetweenResults
      Next k
    Next j
  Next i
End With
End Sub

What version of Excel?

 

[zealot777]

It's not too complicated:

Sub blah()
For i = 1 To 20
  Range("A1") = i
  For j = 1 To 5
    Range("B2") = j
    For k = 1 To 3
      Range("C4") = k
      'do your result grabbing here
    Next k
  Next j
Next i
End Sub

and to add a 4th parameter:

Sub blah2()
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("D13") = n
        'do your result grabbing here
      Next n
    Next k
  Next j
Next i
End Sub

Thanks for this code! I added it to a example worksheet and it shows the error that the
WorkingSht variable is not defined. Can you please see if there is an error of some kind?
The worksheet is here:
http://ifile.it/o4u97xj

Thanks!
Dima

 

[p45cal]

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

    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]

Thank you for the prompt reply...somehow the VBA editors shows the same error - highlighting the WorkingSht variable and saying it has not been defined...I am using Excel 2003 - thanks a lot!!))

 

[p45cal]

quick fix: remove Option Explicit from the top of the module - not perfection when it comes to coding practice but it should work.

 

[p45cal]

Otherwise keep Option Explicit there but add

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

as the first two lines of the sub.

 

[zealot777]

Thanks! it works!))) great!))) I had two mroe questions....

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..

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

Thanks a lot for your continued and valuable assistance in this project!)

Dima