+ Reply to Thread
Results 1 to 8 of 8

Using external subroutine in Solver (in VBA)

Hybrid View

  1. #1
    Registered User
    Join Date
    01-19-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Using external subroutine in Solver (in VBA)

    Hello,

    I am fairly new to using Solver in VBA, hence the question.

    I have a subroutine which implements an algorithm to print the final results (10 values in 10 different cells). The results depend on 6 assumed parameters within the algorithm. I was planning to use Solver to optimize on these assumed parameters, in order to arrive at the exact results (10 values) I want to achieve. From my understanding, Solver can be used in Excel if the cells referenced in 'Set Objective' have a formula. Can we also use Solver if the cells referenced in 'Set Objective' do not have a formula but are the results from a subroutine in excel VBA.

    I would be using the following logic:
    1. 'SolverAdd' to add the 6 parameters and set constraints on them.
    2. Use the algorithm (instead of a cell formula) to arrive at the results (10 values). Compare with the required results I am hoping to achieve.
    3. Use solver to change the 6 parameters.
    4. Iterate between 2 and 3 to get the required results.

    It would be great if someone can let me know whether this can be done using Solver, OR if there is a different tool in Excel that I can make use of. Any suggestions are appreciated.

    Thank you,
    VJ

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,519

    Re: Using external subroutine in Solver (in VBA)

    I don't think you can accomplish exactly what you describe. There might be "pay" upgrades to Solver that can do this, but I am not certain.

    You don't give any details of what you algorithm is doing or how it is coded. My first thought is to rewrite your VBA procedures so that they are "User-Defined Functions" (function procedures that can be called from a spreadsheet cell like a regular Excel function). These can work very well as part of Solver models.

    Of course, if you understand the functions well enough, you can program your own optimization algorithms (Newton Raphson or bisection or whatever) into VBA.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using external subroutine in Solver (in VBA)

    The objective cell must contain a formula.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    01-19-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Using external subroutine in Solver (in VBA)

    Thank you for the reply MrShorty and shg.
    Answering MrShorty's query, the algorithm is fairly long and is an application of chemical thermodynamics. That is why i did not go through the details. But, it does not contain a lot of defined functions in excel, except a couple of vlookup tables.
    Appreciate the quick response.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,519

    Re: Using external subroutine in Solver (in VBA)

    I am familiar with some of those equations (mostly VLE type equations, such as cubic EOS's, activity coefficient equations, and the like), and I use VBA user-defined functions for many of those equations. So, my answer is still basically the same. Revise your current algorithm so that it fits into one or more user-defined functions. Then you can enter those functions into a spreadsheet and use Solver's algorithms to optimize or solve the mathematical model.

    Either that or learn to program the kinds of algorithms that Solver uses, and incorporate those algorithms into your VBA. I use this strategy in some of my code as well, frequently using newton raphson or bisection type algorithms to solve and optimize equations. A lot will depend on exactly what algorithms you are working with and how you are implementing them, but many of these root finding and optimization algorithms are not overly difficult to program, once you have the "main" algorithm in place.

  6. #6
    Registered User
    Join Date
    01-19-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Using external subroutine in Solver (in VBA)

    MrShorty, I am working on a newton raphson optimization solution which would help me for later tasks too. That and I do not really want to tinker with the existing code right now.
    Thank you for the advise.

  7. #7
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Using external subroutine in Solver (in VBA)

    I put GoalSeek in these examples but it has some Solver in it as well. I would advise doing it manually before automating it though.

    'http://support.microsoft.com/en-us/kb/843304
    ' http://www.solver.com/content/basic-solver-solversolve-function
    
    
    Sub Create_Square_Root_Table()
    
        ' Add a new worksheet to the workbook.
        Set w = Worksheets.Add
    
        ' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
        w.Range("C1").Value = 2
        w.Range("C2").Formula = "=C1^2"
    
        ' A loop that will make 10 iterations, starting with the number 1,
        ' and finishing at the number 10.
        For i = 1 To 10
    
            ' Set the Solver parameters that indicate that Solver should
            ' solve the cell C2 for the value of i (where i is the number
            ' of the iteration) by changing cell C1.
            SolverOK SetCell:=Range("C2"), ByChange:=Range("C1"), _
                MaxMinVal:=3, ValueOf:=i
    
            ' Do not display the Solver Results dialog box.
            SolverSolve UserFinish:=True
    
            ' Save the value of i in column A and the results of the
            ' changing cell in column B.
            w.Cells(i, 1) = i
            w.Cells(i, 2) = Range("C1")
    
            ' Finish and discard the final results.
            SolverFinish KeepFinal:=2
    
        Next
    
    End Sub
    
    
    Sub sken()
      SolverOK SetCell:=Range("CF21"), ByChange:=Range("CE21"), _
          MaxMinVal:=0, ValueOf:=0
    
    'SolverOk SetCell:="$X$14", MaxMinVal:=3, ValueOf:=1, ByChange:="$X$12", Engine _
            :=1, EngineDesc:="GRG Nonlinear"
    '    SolverSolve userfinish:=True
    
      ' Do not display the Solver Results dialog box.
     ' SolverSolve UserFinish:=False
    
    
      ' Finish and discard the final results.
      'SolverFinish KeepFinal:=2
    End Sub
    
    
    Sub test_Goalseek()
      Range("CE21").Value = 0
      Debug.Print "Run1 at 0", GoalSeek(Range("CF21"), 0, Range("CE21"))
      Range("CE21").Value = 1000
      Debug.Print "Run1 at 1,000", GoalSeek(Range("CF21"), 0, Range("CE21"))
      
      Range("CE21").Value = 0
      Debug.Print "Run2 at 0", GoalSeek(Range("CG21"), 0, Range("CE21"))
      Range("CE21").Value = 1000
      Debug.Print "Run2 at 1,000", GoalSeek(Range("CG21"), 0, Range("CE21"))
      
      Range("CE21").Value = 0
      Debug.Print "Run2 at 0", GoalSeek(Range("CH21"), 0, Range("CE21"))
      Range("CE21").Value = 1000
      Debug.Print "Run2 at 1,000", GoalSeek(Range("CH21"), 0, Range("CE21"))
      
      Range("CE21").Value = 0
      Debug.Print "Run2 at 0", GoalSeek(Range("CI21"), 0, Range("CE21"))
      Range("CE21").Value = 1000
      Debug.Print "Run2 at 1,000", GoalSeek(Range("CI21"), 0, Range("CE21"))
      
      Range("CE21").Value = 0
      Debug.Print "Run2 at 0", GoalSeek(Range("CJ21"), 0, Range("CE21"))
      Range("CE21").Value = 1000
      Debug.Print "Run2 at 1,000", GoalSeek(Range("CJ21"), 0, Range("CE21"))
      
      '=LINEST(BA3:BA189,AT3:AT189^{1,2,3,4,5,6}) 'i=6 degree fit
      Range("CE21").Value = 0
      Debug.Print "Run2 at -2.24291098117828 rut, 0 initial", GoalSeek(Range("CJ21"), -2.24291098117828, Range("CE21"))
      Range("CE21").Value = 1000
      Debug.Print "Run2 at -2.24291098117828 rut, 1,000 initial", GoalSeek(Range("CJ21"), -2.24291098117828, Range("CE21"))
    End Sub
    
    Function GoalSeek(formulaCell As Range, dGoal As Double, rChangingCell As Range) As Double
      Application.Volatile True
      'On Error Resume Next
      formulaCell.GoalSeek Goal:=dGoal, ChangingCell:=rChangingCell
      GoalSeek = rChangingCell.Value
      
      'If rChangingCell=0 it shows 1228.71, 1000 shows 1122.5
      'OKSolver below shows 1119.8 for both 0 and 1000 rChangingCell initial values
    End Function
    
    
    Sub OKSOlver()
        SolverOK SetCell:="$CF$21", MaxMinVal:=20000, ValueOf:=0, ByChange:="$CE$21", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True, "ShowTrial"
    End Sub
    
    'https://msdn.microsoft.com/en-us/library/office/ff197237.aspx?f=255&MSPPError=-2147217396
    Sub MSDN()
      Worksheets("Sheet1").Activate
      SolverReset
      SolverOptions Precision:=0.001
      SolverOK SetCell:=Range("TotalProfit"), _
          MaxMinVal:=1, _
          ByChange:=Range("C4:E6")
      SolverAdd CellRef:=Range("F4:F6"), _
          Relation:=1, _
          FormulaText:=100
      SolverAdd CellRef:=Range("C4:E6"), _
          Relation:=3, _
          FormulaText:=0
      SolverAdd CellRef:=Range("C4:E6"), _
          Relation:=4
      SolverSolve UserFinish:=False, ShowRef:="ShowTrial"
      SolverSave SaveArea:=Range("A33")
    End Sub
    
     
    Function ShowTrial(Reason As Integer)
      MsgBox Reason
      ShowTrial = 0
    End Function

  8. #8
    Registered User
    Join Date
    01-19-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Using external subroutine in Solver (in VBA)

    Hello,
    Just wanted to share some observations.

    It looks like you can use Solver and a VBA procedure for the above problem I described. Heres how,
    1. Use a "Worksheet_Change" subroutine to capture any changes occurring to certain cells of interest (Solver variables) in the excel worksheet.
    2. Inside the worksheet_change, call the procedure you want to run to print out the results in excel. Calculate an objective function in excel (using excel formula). Set this cell as Objective in Solver.

    Use Solver to setup the optimization problem. When constraint variables are modified by the Solver, it is captured by the "Worksheet_Change" subroutine, and this inturn runs the algorithm you have coded and minimizes the objective function. Below is an example with 6 parameters in cells C1:C6.

    This has worked well for me, and I can print the Solver results after each iteration.
    Comments are welcome.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim WatchRange1 As Range
        Dim IntersectRange1 As Range
    
        Set WatchRange1 = Range("C1:C6")
        Set IntersectRange1 = Intersect(Target, WatchRange2)
        If Not IntersectRange1 Is Nothing Then
            
            Call YourProcedure 'Run the algorithm you need which uses the parameters set by the Solver in cells C1 to C6
    
        End If
    End Sub
    Last edited by abc500007; 01-27-2016 at 03:59 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Activating a Subroutine from within another Subroutine
    By camikhazi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2016, 10:25 AM
  2. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  3. macro not keeping solver solutions when solver is successful
    By jimmypants in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2014, 01:45 AM
  4. Replies: 6
    Last Post: 05-18-2013, 05:49 AM
  5. Can't start Solver. Error message says Solver.xlam already open.
    By DaveHills in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-21-2012, 11:02 AM
  6. solver macro + simulation code + not updating solver values
    By sabinemaria in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2012, 11:37 AM
  7. Interesting Solver problem (Solver encounters an error)
    By MrShorty in forum Excel General
    Replies: 3
    Last Post: 12-22-2005, 06:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1