My apologies for posting code. What it should do is list how much to
produce for a given demand over a period. When I try to run this I get
a message about being out of stack space (28). EIC and PC are
functions. If anyone has a clue on why I am triggering the stack
error, please let me know. Thanks
Option Explicit
Dim Min_cost(10, 101) As Integer, Opt_Prod(10, 101) As Integer,
Demand(10) As Integer, XStar(10) As Integer
Public N_Per As Integer, MaxInv As Integer, MaxProd As Integer,
InitInv As Integer
Public MinLim As Integer, MaxLim As Integer, LLim As Integer, Hold As
Integer, InvOld As Integer, InvNew As Integer
Public I As Integer, II As Integer, J As Integer, K As Integer, Z As
Integer, Sum As Integer
Sub prod()
N_Per = Cells(1, 2).Value
MaxInv = Cells(2, 2).Value
MaxProd = Cells(3, 2).Value
InitInv = Cells(4, 2).Value
For I = 1 To N_Per
Demand(I) = Cells(9, I + 1).Value
Next I
If Demand(N_Per) <> 0 Then
For I = 1 To Demand(N_Per)
Min_cost(N_Per, I) = PC(N_Per, Demand(N_Per) - I + 1)
Opt_Prod(N_Per, I) = Demand(N_Per) - I + 1
Next I
End If
Min_cost(N_Per, Demand(N_Per) + 1) = 0
Opt_Prod(N_Per, Demand(N_Per) + 1) = 0
For II = 1 To (N_Per - 1)
I = N_Per - II
Sum = 0
For J = I To N_Per
Sum = Sum + Demand(J)
Next J
Sum = Sum + 1
If Sum >= MaxInv + 1 Then
MinLim = MaxInv + 1
Else
MinLim = Sum
End If
For K = 1 To MinLim
If Demand(I) - K + 1 <= 0 Then
LLim = 0
Min_cost(I, K) = EIC(I, K - Demand(I) - 1) + Min_cost(I + 1, K
- Demand(I))
Opt_Prod(I, K) = 0
Else
LLim = Demand(I) - K + 1
Min_cost(I, K) = PC(I, LLim) + EIC(I, 0) + Min_cost(I + 1, 1)
Opt_Prod(I, K) = Demand(I) - K + 1
End If
LLim = LLim + 1
If MaxProd > Sum - K And Sum - K > Demand(I) + MaxInv + 1 - K Then
MaxLim = Demand(I) + MaxInv + 1 - K
ElseIf MaxProd > Sum - K And Sum - K <= Demand(I) + MaxInv + 1 - K
Then
MaxLim = Sum - K
ElseIf MaxProd <= Sum - K And MaxProd > Demand(I) + MaxInv + 1 - K
Then
MaxLim = Demand(I) + MaxInv + 1 - K
ElseIf MaxProd <= Sum - K And MaxProd <= Demand(I) + MaxInv + 1 -
K Then
MaxLim = MaxProd
End If
If LLim - 1 <> MaxLim Then
For Z = LLim To MaxLim
Hold = PC(I, Z) + EIC(I, K + Z - Demand(I) - 1) + Min_cost(I +
1, K + Z - Demand(I))
If Min_cost(I, K) > Hold Then
Min_cost(I, K) = Hold
Opt_Prod(I, K) = Z
End If
Next Z
End If
Next K
Next II
XStar(1) = Opt_Prod(1, InitInv + 1)
InvOld = InitInv + 1
For I = 2 To N_Per
InvNew = XStar(I - 1) - Demand(I - 1) + InvOld
XStar(I) = Opt_Prod(I, InvNew)
InvOld = InvNew
Next I
Cells(12, 2).Value = Min_cost(1, InitInv + 1)
For J = 1 To N_Per
Cells(13, J + 1).Value = J
Cells(14, J + 1).Value = XStar(J)
Next J
End Sub
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to [email protected]
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
