none
having trouble with using solver through vba. RRS feed

  • Question

  • Hi im having trouble with solver when i want a optimazation through solver and vba codes

    Actually it works ok when i push the button in data tap, and solver button.
    But it does make an error when i want to call solver in vba.

    the error is called
    "Solver: An unexpected internal error occurred, or available memory was exhausted."

    and occurs when the code tries to add binary constrains.
    =====================================================
        Range("U37:V46").Select
        Selection.FormulaR1C1 = "0"
        SolverReset
        
        SolverOk SetCell:="$Z$44", MaxMinVal:=2, ValueOf:=0, ByChange:="$U$37:$V$46", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd CellRef:="$Y$37:$Y$46", Relation:=2, FormulaText:="1"

        
        SolverOk SetCell:="$Z$44", MaxMinVal:=2, ValueOf:=0, ByChange:="$U$37:$V$46", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd CellRef:="$Z$37:$AA$41", Relation:=2, FormulaText:="1"

        
        SolverOk SetCell:="$Z$44", MaxMinVal:=2, ValueOf:=0, ByChange:="$U$37:$V$46", _
            Engine:=1, EngineDesc:="GRG Nonlinear"



        SolverAdd CellRef:="$Z$42:$AA$42", Relation:=2, FormulaText:="5"
        

        SolverOk SetCell:="$Z$44", MaxMinVal:=2, ValueOf:=0, ByChange:="$U$37:$V$46", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        MsgBox ("1")
        
     
        
        SolverAdd CellRef:="$U$37:$V$46", Relation:=5, FormulaText:="2진수"//occurs an error "2진수" means "binary"
        

        SolverOk SetCell:="$Z$44", MaxMinVal:=2, ValueOf:=0, ByChange:="$U$37:$V$46", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
            

        SolverOk SetCell:="$Z$44", MaxMinVal:=2, ValueOf:=0, ByChange:="$U$37:$V$46", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        solversolve
            MsgBox ("7")
        solversolve UserFinish:=True
        SolverFinish KeepFinal:=1

    ========================================================

    I tried to find a solution for this for a week.
    here is what i did for solving problem.

    i checked solver is installed ok. and the reference is also ok.

    1. try to add "application.run" above the codes.
    it didnt work.

    2. reparing office 2016.
    it didnt work.

    3. re-installing office 2016.
    it didnt work.

    4. I send some mails to "Frontline System" since "solver" is a third-party app not Microsoft app now.
    And they suggested re-installing and cutting/pasting "meta data" located  C:\Users\<user name>\AppData\Roaming\Microsft\Excel
    and reopening excel and try the macro again.

    but it didnt work either.
    and they said unfortunately that i would need to contact the Microsoft Help Desk but i couldnt get support since i am personal user and the issue is about developing.

    Any advice will help me a lot. 

    Thursday, March 7, 2019 6:12 AM

All replies

  • Hi akaxianet,

    >> Actually it works ok when i push the button in data tap, and solver button.
    But it does make an error when i want to call solver in vba.

    Thanks for visiting our forum. Then here we mainly focus on general issues related to Excel desktop client. Since your query is involved with calling Solver in VBA, I'll move your thread to the dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Yuki Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, March 8, 2019 2:54 AM