Solver function within macro in excel 2016 for mac

Question

I am having a problem with the Solver function within a macro.  It works fine in the spreadsheet but within a macro I get undefined function.  Normally, this is fixed by referencing solver within VBE (as in excel 2011) which I had done before upgrading to 2016.  In 2016, when I activated the solver add-in, it automatically put it in the reference section of the VBE.  If I try to look at solver.xlam within VBE, it says password protected (perhaps this is the problem). Any suggestions?????

Answer 1

I know this comes extremely late... As I just encountered this issue for the first time... Hope this can save some time for others in the future.

For anyone having this issue, on your Visual Basic Editor, there is a Add Reference button on the lower left corner. Open it up, tick Solver, press OK, then it should work.

I think the reason why this becomes such a confusing topic, is because Excel has changed its UI in 2016, and suddenly Add Reference cannot be found using previous tutorials you could find online.

Answer 2

Thanks. I do indeed see where it is now.  Unfortunately, still not closer to solving my problem which is running solver within a macro.  solver.xlam is referenced in VBE, and of course solver add-in is selected.  I can even record a new macro in which I use solver and it records just fine.  However, if I try and run that very macor it says sub or function not defined on the SolverOk line of code.  I'm still at a loss, but thank you for the info of finding contents within an app.

Answer 3

Hi

Solver add-in is inside the Excel application. To find it, right click on the Excel application and choose to show the package contents. Then explore this directory:

Contentes/Resources/ar.lproj/Add-ins

Answer 4

The error when running the macro is "sub of function not defined", which would suggest that solver.xlam is not referenced in VBE.  However, it is referenced. Once again, this is using Excel 2016 on Mac.  Interestingly, when I added the solver initially on add-in's, it was put in automatically in the reference list of VBE (this was not the case in previous excel versions where referencing had to be done manually in VBE.

Solver works fine stand alone in excel.  It is only when within a macro (whether or not a create one from scratch or record one).  I have been in touch with microsoft, but as this is for home/personal use, they want to charge me $499 for a one time help, and it sounds like they don't really know anyway.  

One last clue.  In previous excel versions, one could browse for the solver.xlam file and find it in the directory structure.  This time, I can't find it anywhere.  It seems to be imbedded in code somewhere else or was somehow downloaded remotely (cloud?).

Answer 5

What is the problem you are having? Solver.xlam has always been password protected. This is most likely NOT your problem. If the VBE shows the solver as referenced, what are you trying to do and what are the errors?