Solver in Excel Model

Hey guys, need a bit of help to create an excel model for an assignment. If you do finance, I'm creating a model that automatically calculates the efficient frontier, so I need to use solver, and thus need to use a macro, as solver is a piece of **** that only runs once.

What I need help with;

Why does solver sometimes not show up in my macro recorder and sometimes it does? I've tried resetting solver everytime I use it but it still sometimes doesn't show up.

I also get a compile error, something to do with 'sub', it highlights the first line of code with the name of the macro in yellow. - I referenced solver so I think the issue might be solved, but recording solver still doesn't work.

How would you go about creating a range for the 'value of' function in solver...it only accepts numbers that are manually put in, I want to make the 'value of' based off a percentile range that is relevant to the minimum variance portfolio or the optimal portfolio...I hope that makes sense...basically I need to be able to cell reference the 'value of' box because numbers I put in there won't always be relevant to the returns and standard deviation given.

Cheers for the help guys

Using solver in a macro you must also set a reference to the solver.xlam file in the "Microsoft Visual Basic" window. Go to Tools -> References and mark box for solver. If solver not found you must browse for it and solver is normally found "C:\Program Files\Microsoft Office\Office14\Library\Solver\Solver.xlam"

The default setting when you browse is set for Library files so change that to All files (*.*) in order to find the solver.xlam file.

For the other questions upload a sample of your model. As per forum rules members should not do homework but we can help with hints of how to solve specific problems.

Alf

Hi Alf, thanks for the help. I have added the reference by ticking the 'SOLVER' box. I main issue now is that my macro recorder is recording the solver functions when I use it, any idea why this could happen?

In terms of uploading my spreadsheet...All I need to know is how to cell reference the 'value of' function in solver, how do I do this through the macro?

Thanks for your help

All I need to know is how to cell reference the 'value of' function in solver, how do I do this through the macro?

Just start a macro recording and then you go to Data -> Solver and sett all the parameters you need in solver i.e. target cell, cells to change, your constraint and click solve and stop recording.

Perhaps this old microsoft code for calculating square rootscould be of help?

Please Login or Register  to view this content.

If you check folder "\Microsoft Office\Office14\SAMPLES\" there is a file named "SOLVERSAMP.XLS" that contains a number of solver examples. could also be usefull.

Alf

Thanks Alf, I think I now know how to set the value of function to what I want.

Still need an answer to why my recorder is may not be recording anything I do in solver??

why my recorder is may not be recording anything I do in solver??

I've never encountered that problem before. Can you record any macro at all or do you only have a problem with "solver" macros?

Alf

I can record the other function I want the macro to do which is copy and paste the data yet it will not record the solver.

In one way it's to bad you can record other macros because if you could not record any macro at all it would have been a setup problem when you installed Excel.

Not being able to record macros when setting up solver I've no idea what the problem could be. Perhaps you could upload some pgn files showing how you do when you are recording the solver macro.

I've uploaded a file with solver macros. Macro1 is the one I got using the recorder, macro "Macro_modded" is macro1 "trimmed" a bit.

I've also uploaded some pgn files showing how it looks when I recorded the solver macro.

Alf