I use solver a lot in my career. However, solver breakdowns when the function is a step function. I am attempting to write an optimization for a step function (a function that increases or decreases abruptly from one constant value to another.) and need some help with the loop.
Function_Range = A2
Variable_Range = B2
Start_Range = C2
Width_Range = D2
I manually created a solution using excel formulas but need to transition this to VBA. I'll use an example where the function is "=B2*100-B2^2" (This is not a step function but just for example).
I then change the variable using a starting point and width. Start_Range = 100 and Width_Range = 9 I will then create a table where I go down from 100 with intervals of 9 tens times below and ten times above. 10, 19, 28, 37....100....172,181,190. I then find the maximum function and identify that variable. In this case, it is 46. I then take 46 and now bump the table up and down by 1/5 the original width. (9/5=1.8) (e.g. Start_Range = 46, Width_Range = 1.8) for a new table 28,29.8,31.6,...,46,60.4,62.2,64. Identify the maximum function. In this case, it is 49.6. Redo table with Start_Range =49.6, Width_Range = 0.36 (1.8/5).
I repeat this process 5 times arriving at very close to the maximum point. The output of the model is the variable input to the latest run where the function is maximized. Can someone help me get this coded in VBA?
Excel example: https://www.dropbox.com/s/2ospmmsikym75nd/Solver_DM.xlsx?dl=0
Thanks
Function_Range = A2
Variable_Range = B2
Start_Range = C2
Width_Range = D2
I manually created a solution using excel formulas but need to transition this to VBA. I'll use an example where the function is "=B2*100-B2^2" (This is not a step function but just for example).
I then change the variable using a starting point and width. Start_Range = 100 and Width_Range = 9 I will then create a table where I go down from 100 with intervals of 9 tens times below and ten times above. 10, 19, 28, 37....100....172,181,190. I then find the maximum function and identify that variable. In this case, it is 46. I then take 46 and now bump the table up and down by 1/5 the original width. (9/5=1.8) (e.g. Start_Range = 46, Width_Range = 1.8) for a new table 28,29.8,31.6,...,46,60.4,62.2,64. Identify the maximum function. In this case, it is 49.6. Redo table with Start_Range =49.6, Width_Range = 0.36 (1.8/5).
I repeat this process 5 times arriving at very close to the maximum point. The output of the model is the variable input to the latest run where the function is maximized. Can someone help me get this coded in VBA?
Excel example: https://www.dropbox.com/s/2ospmmsikym75nd/Solver_DM.xlsx?dl=0
Thanks
