Solver not working on VB

[jesus1015]

Hi!

I hope you guys can help me to solve the problem I have. I am using MAC office 2011. I am trying to do a likelihood profile. It reads values for the parameter K from a column and uses solver to calculate the value for r that minimizes the objective function. Here is my code:



Sub profile()

For i = 1 To 26

Sheets("Main").Range("B2") = i

k = Sheets("Main").Range("profile_in").Rows(i + 1)

Sheets("Main").Range("k") = k

Sheets("Main").Range("rr") = 0.6

SolverOk SetCell:="b8", MaxMinVal:=2, ValueOf:="0", ByChange:="b3"

SolverSolve UserFinish:=True

likelihood = Sheets("main").Range("scaled")

Sheets("Main").Range("profile_output").Rows(i + 1) = likelihood

Next i



End Sub



My problem is when I do step by step, I need to give solver (SolverSolve) a little time to run it and it will work ok but when I run the macro from Excel, it just solve the first case (i=1) and the remaining cases are not solved. This also happens when I do step by step quickly, any help?

When I used the same code in Windows XP works perfectly, I will appreciate any help, thanks, Jesus





Here is a table with the results:



0.000869504

1.09485E-05

3.34101E-07

4.31503E-08

1.1213E-08

4.28439E-09

2.07281E-09

1.17195E-09

7.38773E-10

5.04123E-10

3.65177E-10

2.77048E-10

2.18027E-10

1.76723E-10

1.46755E-10

1.24345E-10

1.07156E-10

9.36792E-11

8.29135E-11

7.41713E-11

6.69698E-11

6.09617E-11

5.58925E-11

5.15721E-11

4.78566E-11

4.4635E-11


And here is a table with the results with Excel xp:



0.000908182

0.005277419

0.025427526

0.095879037

0.271093711

0.562858248

0.861658306

0.998956855

0.915035585

0.695615175

0.460627264

0.277281031

0.15711879

0.086091158

0.046529477

0.025156593

0.013737611

0.007625153

0.004318797

0.002501572

0.001483347

0.000900627

0.000559728

0.000355833

0.000231188

0.000153355

 

[jaduff46]

Looked quickly, but I don't see where range ("scaled") is set, which is then assigned to likelihood.

 

[jesus1015]

"scaled" is the name of a cell containing the next instruction: EXP(-B8)/EXP(-B11) where B8 has the solver result and B11 has the maximum likelihood value, therefore the maximum value is 1. as you can see in the second table there is a scaled result (0.998956855) that gets close to the maximum. So after solver finds the solution the scaled value is copyed to the column and a plot can be done.this are the results with xp

 

[campyguy]

I hated Excel 2011, still do. It, and much of the supporting software was/is buggy and IMHO a waste of my time. Don't react yet, read on. OP, first, Excel on Windows XP isn't helpful - in the future, specify the version of the app(s) that you're using. I use the Win versions of Excel, have for over 2 decades. That written…

About the results, read this post on MS's forums: https://answers.microsoft.com/en-us...macro-in/0ae9b803-2b26-489c-9634-5c78c2ddcdcb

Solver on the Mac platform had so many bugs and incompatibilities it was a bit of a joke amongst my peers and myself. That MS post was written two years ago, about 5 years after MS put Office 2011 on the market - MS has not yet, to date, fixed a multitude of bugs with Excel 2011. I do not blame Frontline Systems for not fixing some of their bugs in their add-ins for Excel 2008/11. The Solver add-in for 2011 included a subset of the full add-in, expecting users to purchase the full add-in; if you're using the free, included add-in you'll find many commands and calls won't work.

I see you're using XP and Office 2011 apps, both no longer supported in full; Office 2011 is only updated for security issues. I'd offer that you're using out-of-date software, with Excel 2011's VBA support never being fully supported - I recommend a contact to Frontline Systems to find out what their workaround is.