Solver not working on VB

Discussion in 'Mac Basics and Help' started by jesus1015, Jan 22, 2017.

  1. jesus1015 macrumors newbie

    Joined:
    Jan 22, 2017
    Location:
    Mexico City
    #1
    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
     
  2. jaduff46 macrumors regular

    jaduff46

    Joined:
    Mar 3, 2010
    Location:
    Northern NJ
    #2
    Looked quickly, but I don't see where range ("scaled") is set, which is then assigned to likelihood.
     
  3. jesus1015 thread starter macrumors newbie

    Joined:
    Jan 22, 2017
    Location:
    Mexico City
    #3
    "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 Screen Shot 2017-01-22 at 18.49.11.png
     
  4. campyguy macrumors 68040

    Joined:
    Mar 21, 2014
    Location:
    Portland / Seattle
    #4
    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.
     

Share This Page