Minimization VBA Code

[matthew_ong12]

[TABLE="width: 428"]
<colgroup><col width="101" style="width:76pt"><col width="82" style="width:62pt"><col width="81" style="width:61pt"><col width="74" style="width:56pt"><col width="90" style="width:68pt"></colgroup><tbody>[TR]
[TD="class: xl99, width: 183, colspan: 2"]Good morning Mr Excel, would like to ask how to code the minimization VBA code.

The objective is to minimize the
Fuel cost=Cost of Fuel 1 (Fuel 1 Qty) + Cost of fuel 2 (Fuel 2 Qty)+....
while satisfying the energy balance=Heating Value of Fuel 1 (Fuel 1 Qty)+ Heating Value of Fuel 2 (Fuel 2 Qty)+...

Here's the figures:

[TABLE="width: 428"]
<colgroup><col width="101" style="width:76pt"> <col width="82" style="width:62pt"> <col width="81" style="width:61pt"> <col width="74" style="width:56pt"> <col width="90" style="width:68pt"> </colgroup><tbody>[TR]
[TD="class: xl99, width: 183, colspan: 2"]Fuel Optimization[/TD]
[TD="class: xl98, width: 81"][/TD]
[TD="class: xl98, width: 74"][/TD]
[TD="class: xl98, width: 90"][/TD]
[/TR]
[TR]
[TD="class: xl300"]Objective[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Fuel Cost (PhP)[/TD]
[TD="class: xl35, align: right"] 22.17 [/TD]
[TD="class: xl18"]Mil[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Energy Balance[/TD]
[TD="class: xl35, align: right"] - [/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[/TR]
[TR]
[TD="class: xl36"]Fuel Type[/TD]
[TD="class: xl298"]∆H (MJ/kg)[/TD]
[TD="class: xl298"](Tons)[/TD]
[TD="class: xl36"](PhP/Ton)[/TD]
[TD="class: xl298"](MJ)[/TD]
[/TR]
[TR]
[TD="class: xl18"]Coal[/TD]
[TD="class: xl18, align: right"] 23.01 [/TD]
[TD="class: xl35, align: right"] 0.01 [/TD]
[TD="class: xl18, align: right"]4800[/TD]
[TD="class: xl18, align: right"] 0.17 [/TD]
[/TR]
[TR]
[TD="class: xl18"]Napier[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18, align: right"]0[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl35, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl18"]Wood Chips[/TD]
[TD="class: xl35, align: right"] 12.53 [/TD]
[TD="class: xl18, align: right"]7917.186906[/TD]
[TD="class: xl18, align: right"]2800[/TD]
[TD="class: xl35, align: right"] 99,202.35 [/TD]
[/TR]
[TR]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18, align: right"] 99,202.53 [/TD]
[/TR]
[TR]
[TD="class: xl18"]Bagasse[/TD]
[TD="class: xl35, align: right"] 9.06 [/TD]
[TD="class: xl18, align: right"] 10,950.00 [/TD]
[TD="class: xl18, align: right"]2600[/TD]
[TD="class: xl35, align: right"] 99,202.53 [/TD]
[/TR]
</tbody>[/TABLE]

The 99,202MJ is the energy balance target that needs to be attain from using other fuel such as coal, napier and wood chips. This is equated to the product sum of Heating Values of other fuel (i.e. coal, napier & wood chips) and their quantity.

Hoping to hear from you soon


Regards,
Matthew[/TD]
[TD="class: xl98, width: 81"][/TD]
[TD="class: xl98, width: 74"][/TD]
[TD="class: xl98, width: 90"][/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

Looking at yor figures it seems obvious to me that the cheapest way of getting 99202Mj is using 10950 tons of Bagasse at 9.06 Mj/kg. So I think what this really means is that I haven't understood the question. which really means that your question is not very clear.

 

[matthew_ong12]

Hi Mr Excel, my apologies for my mistake. The fuel combination that I'm only considering are the top two only and excluding the bagasse. The objective is to match the energy content of the top two fuel type with the bagasse at the bottom. For the fuel pricing, again, its the sum of the top two only.

Hoping to hear from you soon

 

[offthelip]

I still don't really understand what the problem is, what i undertand is that you want of find out the cost per Megajoule for each fuel. This is simply the calorific value per ton times the cost per ton.
i.e 1 ton of coal will cost you 4800 php and generate 23.01 Mgj so the cost per megjoule is 4800/23.01 = 4.79, for the wood chip is it 4.475 and the bagasse it 3.48

 

[matthew_ong12]

Hi Mr Excel, what i want to minimize is the total fuel cost=Cost of Coal*Tons of Coal + Cost of Wood Chips*Tons of Wood chips. The prices of coal and wood chips are given. What i need to solve is their quantity. For their quantity, i did an energy balance wherein Heating Value of Coal*Tons of Coal + Heating Value of Wood Chips*Tons of Wood Chips=Heating Value of Bagasse*Tons of Bagasse. (i.e. Heating Value refers to the MJ/kg column). I'm having a hard time coding the vba for it. Hoping to hear from you soon

Regards,

 

[Fazza]

hi. I'm curious - is this a study question. Like homework? Why a VBA solution? What VBA do you already have?

 

[matthew_ong12]

Hi Ms Fazza, more of own formulated problem solving. What i did initially is i used the solver in excel. But i just thought of figuring how to formulate the vba code that does the same thing. Hoping you could help me out

 

[Fazza]

... i just thought of figuring how to formulate the vba code that does the same thing.

So where have you got to??
Best to post it so people can see what you've done. Thanks

 

[matthew_ong12]

Hi Farra, what i initially did was to record the steps in macro when i utilized the solver. But as i try to adapt this one to the vba code, i'm encountering a problem. It says "Sub or Function not define". Here's the code below

The cell b380 refers to the total fuel cost that has to be minimized. The Qty of coal and wood chips are in the range c382 to c384. I also placed a nonlinear equation for the qty of coal and wood chips to be >=0 to avoid returning a negative value.

Hoping to hear from you soon

SolverOk SetCell:="$B$380", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$B$380", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve

 

[Fazza]

suggest you start with https://docs.microsoft.com/en-us/office/vba/excel/concepts/functions/using-the-solver-vba-functions