Could you please mail the file. Need to take a look at the source data and other supporting routines, to see if it is an infinite chain of recursion and if it can be eliminated.
regards, Ajit On Mon, Sep 22, 2008 at 6:29 PM, larry <[EMAIL PROTECTED]> wrote: > > 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 > > > > -- Thank You, Ajit Navre --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
