Best solution to work on a VBA problem without prior knowledge

[spark.tsang]

In the meantime I only need to solve an automation problem: calculating the maximum value of G5 for all possible variations of G2 and G3. It's going to be implemented in a number of similar cases. I have no knowledge of VBA but I know the only way to automate such calculation instead of manually clicking "Solver" every time for the solution is by working on VBA.

(By the way even Solver cannot find the accurate solution all the time, it finds the solution by trial and error and it tests only a range which is narrowed-down by the past calculations it already ran through, but in many times it skips looking in a different range which may contain the real solution. )

I have completely no idea what it takes to finish this task. Do I have to learn VBA from zero and reach a certain level before I can solve the specific problem above? If so how long does it take in general? Is it worth the time or maybe is it better to outsource it so someone with that knowledge can finish it in minutes instead of me figuring that out in days or months? Or do we have a simple VBA template to copy and paste, changing the cell reference to make it done?

Please enlighten me, very much appreciated.

[mehmetcik]

Use the Macro recorder to record your actions in Excel.

Stop the Macro recorder.

Manually insert loops into your code.

eg:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[spark.tsang]

Thanks, it's great to know about such a useful tool. But is it used to repeat the manual clicking of Excel Solver? As I said Excel solver cannot give accurate solutions in my case. For instance sometimes it gives 0% as the solution of G5 if the other nearby values of G2 and G3 would give a negative number for G5 but actually the real solution can be 60%, and can be found testing another range of values of G2 and G3. So Solver is not reliable in this case. Thus I believe the only way is to use VBA. Is this conclusion right? Much appreciated.

[MrShorty]

Do you have examples of when Solver fails to find a solution? Exactly how are you implementing Solver? Just looking at the one example you've given here, I see no reason for Solver to fail to find a solution. As you note, the region far removed from the solution bottoms out at 0. Solver's algorithms could easily get stuck in this region if you give it bad starting guesses. I wonder if, rather than abandoning Solver, you will be better served to come up with a simple algorithm for providing good initial guesses, then let Solver take over.

That will probably require you to become familiar with the behavior of the objective function (G5) at different values of the input cells (G2 and G3). If it helps, I used a 2D data table to quickly generate a table of values (and then a scatter chart) so I could see how the function behaves (https://www.excel-easy.com/examples/data-tables.html ). Obviously, I can only look at the one example you have provided, so I don't know how many different variations your problem may have, and therefore cannot offer specific suggestions. However, general approach I would take with something like this:

1) Spend some time becoming familiar with the behavior of the OF for different scenarios until you are comfortable with how the function behaves.
2) From that information, figure out how you can come up with good initial guesses for many/most/all possible scenarios.
3) Feed those initial guesses into Solver and let solver find the optimum for each scenario.

It seems to me that step 2 (and maybe step 1) are currently missing from your approach.

[spark.tsang]

Thank you for the detailed inputs. Very sensible.

The function behaves in a way that upon the maximum solution of G5, G2 is always one of the values from column B and G3 is always one from column C.
As a result, when looking for the maximum, it usually gets stuck and looks to the one direction which is approaching a higher value of G5. All the values in column B and C act like a magnet to the Solver because approaching them on the way of the calculation always seem to be approaching a maximum. It's quite a matter of luck whether the range it's looking into is the real maximum.

The condition is the same even with better initial guesses of G2 and G3. Without a good enough initial guess, like when G3<G2, it can't calculate at all.

Back to my question, I wish to automate the calculation and I wish to know what it takes. I don't have enough information to decide what to do. Is VBA recommended here or not?

[wornhall]

It is likely that you are on the way to a formula solution, but when a complex formula gets to be troublesome, there remains a possible assist in Excel. Try the "Evaluate formula" procedure with the formula cell currently active. Try Alt-m-v to bring up the process. Each time you click on "Evaluate" or press the letter "e", it will give the solution to the next deeper step in the formula. This process often leads to a formula solution that can be elusive otherwise.

Best of luck.

[MrShorty]

The function behaves in a way that upon the maximum solution of G5, G2 is always one of the values from column B and G3 is always one from column C.
As a result, when looking for the maximum, it usually gets stuck and looks to the one direction which is approaching a higher value of G5. All the values in column B and C act like a magnet to the Solver because approaching them on the way of the calculation always seem to be approaching a maximum. It's quite a matter of luck whether the range it's looking into is the real maximum.

Do you have an example of this behavior? Looking at the one example in your OP, as long as I give it reasonable starting values, I can run Solver and it consistently and reliably converges on the one maximum solution. I don't see it getting stuck in other areas of the function. Which Solver engine are you using? Note that I am using the standard GRG non-linear engine available in 2007, which seems to work well for this OF.

Is VBA recommended here or not?

Because I can reliably find the maximum using the built in Solver, I see no reason to recommend VBA here.

[spark.tsang]

Thanks for the input wornhall, especially the ALT+m+v tool. The first time I know it. The formula in the cell calculates correctly upon evaluation, only the Solver does not.

[spark.tsang]

Yes I do have examples, please refer to my new document for one of them. The solution is at column J. Using Solver you would find it gets stuck. You can try other values, it also gets stuck.

Yes I use GRG non-lineare engine, it's good to check. Thanks for noticing and asking.

What if I have hundreds of cases, and each case includes dozens of such calculation? I hope to find a way in which it can automatically calculate once I put the required data, no more manual solving. Much appreciated.

[MrShorty]

As I said in a previous reply, part of getting this to work reliably is to understand the behavior of your objective function. As with example B, the first thing I did with example C was to add a 2D data table and a couple of scatter charts so I could visualize the behavior of this example. The overall behavior was similar, however, I noted in example C that the OF seemed rather "flat" in the region near the maximum. I expect that what is happening is that Solver is terminating too early based on its current termination criteria.

So I started adjusting the different options in the Solver options dialog (https://www.solver.com/excel-solver-...olving-methods and https://www.solver.com/excel-solver-...solving-method ). Remember that my version is older so I may have different options than you do, but I expect you have many of the same options. After trying a few things, I found that changing two options allowed me to reliably find the maximum solution (which higher than the solution you have in column J). I checked the "use automatic scaling" option (appears to be in the main Solver options dialog for newer versions) and the "central" derivative option (in the GRG non-linear options dialog). These two changes together -- along with giving it reasonable starting values -- allowed it to reliably find the maximum at G2=3084.7, G3=4275.5, G5=54.13%.

Ultimately, I expect we can figure out how to automate this so it requires minimal interference from the user. That's going to require a good understanding of the behavior of the OF for all possible problem scenarios and figure out what options need to be set for the Solver algorithm to allow it to reliably find the maximum. Then we can figure out if and how we need to set up a VBA change or calculate event procedure that will call and run Solver with the right starting values and options.

[spark.tsang]

Great, thanks a lot. I tried the "use automatic scaling" and got the same results. It worked on other cases as well.

Having this good progress and based on my understanding so far, if the initial guess of G2 is AVERAGE(B:B) and that of G3 is AVERAGE(C:C), plus we "use automatic scaling", we should get it right in all or at least most cases, which should be satisfactory enough.

Now what does it take to set up a VBA procedure for this? Is there a template to modify or do we have to somehow start over again to write a program?

On top of that I have a related question or worry. With massive data and calculations, I cannot apply Solver directly on my original excel file. Otherwise, with each calculation it runs the iterations at extremely low speed, completing about 40 iterations in as much as half an hour in some cases. (The CPU of my laptop is not bad)
Thus, in each case basically I have to copy the two columns of data to columns B and C of a template file just like those I posted, and then close the original file, run Solver in the template file, and open the original file again, copy the G2 and G3 results to my original file. Without the presence of the massive original file, each result is calculated in a split-second.
This adds up the manual work, of course. The only improvement is to copy many cases of data to different columns of the template file before closing the original file for calculations of more cases, during which repeated manual data copying and pasting is unavoidable.

So if we set up a VBA to replace the procedures above, what would happen when the VBA runs the calculations on a large scale? Will the excel get stuck forever as the only thing it does is to call out Solver in the background (since there are many cases to calculate at the same time)? Or will it finish calculations in a split-second even with massive data because it has a somehow different logic?

Thanks a lot and sorry for the complexity of the question.

[MrShorty]

If you have no experience calling Solver from VBA, this is probably the best place to start: https://peltiertech.com/Excel/SolverVBA.html Eventually you can incorporate calling Solver as part of a change or calculate event, but first get something working in a manually called procedure.

Calling Solver from VBA is not going to speed up Solver. If Solver is slow, it is because your spreadsheet is slow, which gets magnified because Solver needs to recalculate the spreadsheet (and maybe the entire Excel environment) multiple times per iteration. I see two possibilities. Stick with your "copy inputs into a 'light' version -- Solver -- copy outputs back", all of which can be automated with VBA.

Or, you can do the work of optimizing your spreadsheet. I notice that your example sheets are using a large array formula with exact match lookups, multiple copies of the exact same lookup, and other inefficiencies. In this small sheet, they are not noticeable, but if they are representative of the kinds of formulas you have in your big spreadsheets, then I am not surprised that they are inefficient. If you dislike the copy solve paste approach, you may need to take the time to optimize your spreadsheet for better performance.

[spark.tsang]

Thanks a lot, your explanation is detailed and clear as usual. Though there's no perfect solution to my specific problem, I think I'm much more well-informed as a whole to proceed.