I should of posted the functions. Apparently I was causing a disconnect because the functions even though (marginally) acceptable code in VBA were giving me !value# in Excel. Once I gave up on the functions and just coded the formulas, the program ran correctly.
Munson, Herb wrote: > If there is explicit or implicit recursion going on, you can get a stack > overflow problem. For example, if EIC calls PC, and PC calls EIC, you can > generate a call stack of such depth that you run out of stack space. > > I will confess, though, that I have not looked at the code you included in > any depth, and I have no idea whether there is, in fact, any recursion here, > or what else might be going on. > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Sent: Tuesday, September 09, 2008 1:17 PM > To: MS Excel & VBA Macros > Subject: $$Excel-Macros$$ Stack error problem > > > 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 excel-macros@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
