VBA search methods

**alee001** · 01-17-2013, 03:04 AM

I want to make a VBA search method to optimization as following example code except for using solver, I am used to looping find out the optimizated parameters but also expend more time. Anyone have good idea for improve or other method? Thanks.

Max=-1
For p1 = 5 to 20 '5~20 days for fast index
    For p2 = 20 to 120 '20~120 days for slow index
        Range("parameter1") = p1
        Range("parameter2") = p2
        If Range("target") > Max then
            Max = Range("target"): optz1 = p1: optz2 = p2
        End if
    Next p2
Next p1

**alee001** · 08-27-2013, 11:22 AM

Anyone can help me improve below code to searching/optimization quickly? Thanks....

Sub grid_search()

'Optimization for 2 no. of parameter
Dim max0 As Single, step1 As Single, step2 As Single, p1 As Single, p2 As Single, opt1 As Single, opt2 As Single
ReDim type_array(Range("parat1_min") To Range("parat1_stop"), Range("parat2_min") To Range("parat2_stop"))

Application.ScreenUpdating = False
max0 = -100: opt1 = 0: opt2 = 0

p1_type2:
    If UBound(type_array, 1) = LBound(type_array, 1) Then p1 = LBound(type_array, 1): GoTo p2_type2 Else 'for constant parameter1
    step1 = Application.max((UBound(type_array, 1) - LBound(type_array, 1)) \ Range("parat1_step"), 1)
    For p1 = LBound(type_array, 1) To UBound(type_array, 1) Step step1
p2_type2:
    If UBound(type_array, 2) = LBound(type_array, 2) Then p2 = LBound(type_array, 2): GoTo run_type2 Else 'for constant parameter2
    step2 = Application.max((UBound(type_array, 2) - LBound(type_array, 2)) \ Range("parat2_step"), 1)
    For p2 = LBound(type_array, 2) To UBound(type_array, 2) Step step2
run_type2:
    If p1 < p2 Then 'fast parameter(p1) < slow parameter(p2)
        Range("opt_parat1") = p1
        Range("opt_parat2") = p2
        If Range("optz_target") >= max0 Then
            max0 = Range("optz_target")
            opt1 = p1
            opt2 = p2
        End If
    End If
    If UBound(type_array, 2) = LBound(type_array, 2) Then GoTo p1_loop_type2 Else
    Next p2
p1_loop_type2:
    If UBound(type_array, 1) = LBound(type_array, 1) Then GoTo end_case Else
    Next p1

Application.ScreenUpdating = True
'record optimizated parapmeters
Range("opt_parat1").Value = opt1
Range("opt_parat2").Value = opt2