PDA

View Full Version : VBA Excel Solver



hailog
10-31-2007, 04:26 AM
Hi! I am in a dreadful state today morning! I have the spreadsheet programming course exam today and I know a question very similar to this is coming in the exam but I can't seem to figure out the solution :( Sorry, I am panicking, but can someone post the code to solve this please? Total cost has to be minimized by sticking to the plant capacity and city demand constraints. The shipment values actually seen on the xls are the optimum values, and I am supposed to get those filled in using a VBA code for solver.
Sorry, I am embarassing myself totally on an exam day! :(
Thanks for the help!

Simon Lloyd
10-31-2007, 05:23 AM
hailog, you haven't actually done any work on this yourself, even if someone provided you with a solution despite your very limited information and explanation you would not learn anything that you could use today!

As a wise "El Xid" told me on here "preparation is everything", if you know absolutely nothing about creating some VBA (even though you are on a programming course) it is probably best to hold your hands up and get the help you need!, there is always the chance of a resit.

Solver is actually an add-in to Excel, if you click on Tools, Add-ins and then on Solver Add-in next time you go to tools it will be available.

Best of luck!

hailog
10-31-2007, 05:39 AM
Simon - I appreciate your comments. I feel totally embarrassed to have even asked this. It's not like I haven't done any work, all the samples that I worked out have been more straightforward than this, like 1-1 mapping (ex: optimizing product mix w/labour and raw materials costs). This one seems to be in a matrix form (plant and city) and that's what stumps me. Anyway, the objective is to ship the product from 3 plants to the cities at min cost. Constraints are that no plant can ship more than its capacity, and each city must get what it demands.
Thanks for your input.

hailog
10-31-2007, 05:44 AM
More on what I tried (and I know it doesn't work!):

SolverOk SetCell:=Range("TotalCost"), MaxMinVal:=2, ByChange:=Range("Shipped")

The highlighted part is wrong, but I don't know how to correct it.

For the constraints:
SolverAdd CellRef:=Range("Plant1"), Relation:=1, Formulatext:="Capacity3"
SolverAdd CellRef:=Range("City1"), Relation:=3, Formulatext:="Dmd1"
...
I defined the appropriate ranges, but still think this is totally wrong. The tricky thing is I have to compute the total across plant 1 (varying city1, 2...) and compare it with the max. allowable capacity for plant 1.