+ Reply to Thread
Results 1 to 3 of 3

Using VBA to create a variable "adjustable cells" array in Solver

  1. #1
    Registered User
    Join Date
    02-22-2012
    Location
    Thailand
    MS-Off Ver
    Excel 2010
    Posts
    62

    Using VBA to create a variable "adjustable cells" array in Solver

    Hi all!

    I am creating a certain optimization system in Excel that uses Solver. Around 60 optimizations are carried out using Solver in combination with VBA. During these optimizations, the adjustable cells array varies. Therefore:

    I would like to use VBA to determine the array used in Solver as the adjustable cells.

    In other words: If cell A1 returns a value of 5, the number of rows used in the array of adjustable cells in Solver is 5. When cell B1 returns a value of 2, the number of columns used in the array of adjustable cells in Solver is 2.

    Could anybody help me please?

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Using VBA to create a variable "adjustable cells" array in Solver

    Your best chance of getting help is for you to upload a sample file.

    Alf

  3. #3
    Registered User
    Join Date
    02-22-2012
    Location
    Thailand
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Using VBA to create a variable "adjustable cells" array in Solver

    That's a good idea.

    Solverproblem.xlsm

    VBA is included!

    I'm trying to solve a number of assignment problems (in the example I haven't added the permutation matrix boundaries (sum of all columns is 1 and sum of all rows is 1 of the permutation matrix) but added a different boundary: the sum of the permutation matrix is equal to the two cells that must determine the adjustable cell array.

    In the example I have shown how I currently am solving this problem (with the cost value being dependent on the cell A2 and B2). The disadvantage of this solution is that Solver is still using the complete adjustable cell array, while only a part is necessary for the final solution. Because I am performing around 60 optimizations at an adjustable cell array of 14X14, the calculation time starts becoming a limiting factor (and that's why I want to create a variable adjustable cell array). When a smaller array for the adjustable cells is used, the calculation time drops significantly.

    I am having a very hard time explaining my problem, I hope it is clear!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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