"For all i in I" type of linear programming constraints in Excel VBA

Question

I'm intending to conduct a linear program using Excel VBA. I am a novice in doing these type of problems in VBA, and hence I've followed an example in order to get some insight into this:

Dim c As Vector = Vector.Create(-1.0, -3.0, 0.0, 0.0, 0.0, 0.0)
Dim A As Matrix = Matrix.Create(4, 6, New Double() _
{ _
    1, 1, 1, 0, 0, 0, _
    1, 1, 0, -1, 0, 0, _
    1, 0, 0, 0, 1, 0, _
    0, 1, 0, 0, 0, 1 _
}, MatrixElementOrder.RowMajor)
Dim b As Vector = Vector.Create(1.5, 0.5, 1.0, 1.0)
Dim lp1 As LinearProgram = New LinearProgram(c, A, b, 4)

The problems I've stumbled upon though is:

is there a way to construct constraints of the type: sum{i in I} x[i,j], for all j in J?

And are there other ways to construct constraints rather than "manually" creating the constraint matrix, A (in this example) for types when there are a vast amount of constraints and/or variables?

You should create a "Matrix" and "Vector" class to manage the creation / manipulation, but mainly, write Matrix into a sheet instead a vba array, because Excel know how to manage this. Don't recreate the wheel ;) — Maxime Porté
– Maxime Porté, Commented Aug 10, 2015 at 8:58
Maybe with a class module you can create a class that gather all of your constraint to generate only matrix that correspond to this. — R3uK
– R3uK, Commented Aug 10, 2015 at 9:00
If you know the conditions that construct your matrix you can let VBA write them to an excel workbook, and make a matrix out of that. Or perhaps just write it to a matrix, but I am not that experienced with that. You could do this in excel by looping through all rows and all columns and use IF statements to determine the places of the 1 and -1 if you know where they should be. — Luuklag
– Luuklag, Commented Aug 10, 2015 at 9:05

Community · Accepted Answer · 2017-05-23 12:29:43Z

2

Posted as an answer because it was too long to fit as a comment.

If your intent is to create a full-blown linear programming algorithm using VBA, the source code of opensolver may be a good place to start. Open solver takes the problem from the spreadsheet and sends it to an LP solver, and uses intermediate VBA data structures such as those that you try to create. Therefore, their code could give you some idea on how to create useful data structures.

If your intent is to solve linear programs in a spreadsheet environment, rather than developing an LP solver from scratch, then (i) excel's regular solver, (ii) open solver, or even (iii) solverstudio are good tools. As for your questions, there is not much magic to do.. Looping is rather inevitable, but not necessarily too slow.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Aug 10, 2015 at 10:11

Ioannis

5,4682 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cenderze Over a year ago

Thanks! This helped me in choosing which "path" to choose, and I actually did it in Excel Solver. Accepting this answer as I believe using it in Excel Solver may ease the implemention when people in other departments with much less programming experiance need to use the program.

Collectives™ on Stack Overflow

"For all i in I" type of linear programming constraints in Excel VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related