+ Reply to Thread
Results 1 to 6 of 6

Optimization - running VBA subroutine at intermediate steps

  1. #1
    Registered User
    Join Date
    06-12-2022
    Location
    Pacific Northwest, USA
    MS-Off Ver
    Office 365 MSO
    Posts
    3

    Optimization - running VBA subroutine at intermediate steps

    Hi,
    I am using Solver for an optimization problem, but I need to run a VBA subroutine at each intermediate step in order to correctly calculate the objective function. As near as I can tell from Google searches, I probably need the following lines:
    SolverOptions StepThru:=True
    SolverSolve UserFinish:=True, ShowRef:="subroutine_name_goes_here"

    In the above line the subroutine name is actually between " ". But I think the syntax for the SolverSolve line above is wrong. From a Google search is seems that many have had the same question but cannot really find a lucid answer.
    Note that I am using VBA code to set the solver parameters and then call Solver.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Optimization - running VBA subroutine at intermediate steps

    Can you help us understand at least a little bit about what this VBA subroutine needs to do that is essential to calculating the objective function? My first thought would be to code the VBA parts of the objective function into a Function procedure that you can use as a UDF. This UDF can then easily fit into the spreadsheet logic path that results in the objective function, without needing to do anything special with the Solver model.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-12-2022
    Location
    Pacific Northwest, USA
    MS-Off Ver
    Office 365 MSO
    Posts
    3

    Re: Optimization - running VBA subroutine at intermediate steps

    Thanks for getting back to me so quickly. I will be really upfront on the application. I am working on an application to analyze pressure vessels made from carbon fiber composites. The PV is made from several layers of composite,
    which layer having a different angle. I want to iterate on the play angles to find the lowest stress. This entails taking the material properties and generating a stiffness matrix and force vector, and solving a set of simultaneous
    equations. The set of equations is on the order of 100x100. There is around 1100 lines of VBA code that is doing all this stuff. Not all of it would have to be run during the intermediate steps. For example, reading most of the input,
    and writing most of the output, would not be required.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Optimization - running VBA subroutine at intermediate steps

    As I understand it, the main need here for VBA is that Excel's built in MINVERSE() function is limited to about a 50x50 matrix and cannot handle a 100x100 matrix. Or are the simultaneous equations non-linear and you are using Solver to invert the matrix? How much of the 1100 lines of VBA code are involved in the matrix inversion, and how many are involved in the optimization?

    I'm still leaning towards a VBA UDF, assuming that the matrix inversion part of the problem can be separated from the overall optimization part of the problem. The idea being that you have your inputs, then the UDF performs the inversion directly in the spreadsheet, then Solver can perform the optimization (and the UDF should automatically calculate with each of Solver's iterations without needing any special commands or anything). Does that sound possible?

  5. #5
    Registered User
    Join Date
    06-12-2022
    Location
    Pacific Northwest, USA
    MS-Off Ver
    Office 365 MSO
    Posts
    3

    Re: Optimization - running VBA subroutine at intermediate steps

    I am not aware that MINVERSE is limited to 50x50. The main reason I do not want to use it is that for my problem I can get zeros in the diagonals of my matrix, and MINVERSE
    will not work in that case. I contacted FrontLine about using subroutines in Solver and they responded that subroutines are not supported. But you are saying UDF's are.
    I might be able to turn the equation solver subroutine into a UDF, as long as the about can be an array. It is only about 75 lines of code, so not so bad. Do you have
    a clear understanding of how to call a UDF from the Solver? The specific syntax is what I am looking for.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Optimization - running VBA subroutine at intermediate steps

    I might be able to turn the equation solver subroutine into a UDF, as long as the about can be an array.
    In my older versions of Excel, I don't have any trouble returning an array from a UDF. My UDF behaves just like other, built in array functions (like LINEST() or MINVERSE()). My understanding is that the new spill feature of Excel also has no trouble with UDFs that return arrays, but I cannot verify that myself. The usual in function strategy I use for creating array functions is outlined here: https://www.excelforum.com/excel-pro...ml#post4071488 [ignore the follow up questions, since the new spill feature should take care of those questions, assuming it works as expected].
    Do you have a clear understanding of how to call a UDF from the Solver? The specific syntax is what I am looking for.
    I could be misunderstanding, but the point of turning this into a UDF is that there is no special need to have Solver call the UDF (just as there is no need to have Solver call any built in Excel functions that your spreadsheet uses). A well written UDF should behave in the spreadsheet exactly like any of Excel's built in functions (except a bit slower). If you build the overall spreadsheet carefully, then Solver should be able to operate on the spreadsheet without any special considerations or distinctions between built in functions and VBA UDFs that are present in the spreadsheet.

+ 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. Running this Subroutine Causes my File Size to Increase Dramatically
    By faithdm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-10-2021, 07:34 PM
  2. [SOLVED] Array formula to return all steps of cumulative / running total
    By Stormin' in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-03-2018, 05:18 AM
  3. [SOLVED] VBA code works in debug mode but misses steps running in normal mode
    By supereeg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2016, 05:42 AM
  4. vba running by steps / hide code
    By zico8 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2015, 03:42 PM
  5. VBA unable to move to a different active cell - while running subroutine
    By Mykal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-25-2011, 09:54 PM
  6. Running a subroutine at 10 min intervals
    By graeme86 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2007, 02:23 PM
  7. How to make userform disappear while called subroutine is running
    By Cornelia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2006, 05:50 PM

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