1

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?

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Cenderze
  • 1,202
  • 5
  • 33
  • 56
  • 1
    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é Aug 10 '15 at 08: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 Aug 10 '15 at 09: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 Aug 10 '15 at 09:05

1 Answers1

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.

Community
  • 1
  • 1
Ioannis
  • 5,238
  • 2
  • 19
  • 31
  • 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. – Cenderze Aug 10 '15 at 14:10