Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
Excel2003 ... I know nothing of writing Code ... I Record Macros only ...
That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting
"Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!") If a = 7 Then Exit Sub ActiveSheet.Unprotect Range("A2:D22").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True Calculate Range("O40").Select End Sub Thanks ... Kha "Ken" wrote: Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
Is your workbook set to manual calculate? If not, then I'd just delete the
offending Calculate statement from the code. Although I doubt if it has anything to do with it, the code always works with the ActiveSheet. That is, the sheet currently selected. Is the sheet this code was designed for the active sheet when it's run? Again, shouldn't have anything to do with out of stack problem - that's usually caused by an almost infinite loop reiteratively calling a routine/process. "Ken" wrote: SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!") If a = 7 Then Exit Sub ActiveSheet.Unprotect Range("A2:D22").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True Calculate Range("O40").Select End Sub Thanks ... Kha "Ken" wrote: Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
Yes ... WB 21mg so I set Calc to "Manual" for speed of data entry ... Then I
forced a "Calulate" in Recorded Macro ... ALL working fine when all of a sudden I had "Out of Stack Space" error message with "Calculate" the yellow high-lite offender. SS appears to still be working fine & I can "F9" Calculate with no problem .... I just can't Macro Record "F9" as "Calculate" & then assign a Forms Button to it ... as this is when the "Calculate" returns the "Out of Stack Space" Error. Issue is ... this SS to be updated by other Folks ... Setting Calc to Auto will slow data entry down ... & F9 Calc is not a key I am confident they will use when they should ... Consequently, I buried the "Calculate" within the Macro Buttons, but now a problem??? Thanks for listening & also for supporting these boards ... Any further suggestions here are welcome ... Kha "JLatham" wrote: Is your workbook set to manual calculate? If not, then I'd just delete the offending Calculate statement from the code. Although I doubt if it has anything to do with it, the code always works with the ActiveSheet. That is, the sheet currently selected. Is the sheet this code was designed for the active sheet when it's run? Again, shouldn't have anything to do with out of stack problem - that's usually caused by an almost infinite loop reiteratively calling a routine/process. "Ken" wrote: SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!") If a = 7 Then Exit Sub ActiveSheet.Unprotect Range("A2:D22").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True Calculate Range("O40").Select End Sub Thanks ... Kha "Ken" wrote: Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
Out of stack space normally has to do with a recursive call that never ends.
Somewhere in the called function a variable is created. Because the call is recursive with no stopping point it just keeps creating these variables until the stack memory is out of space. To that end do you have calculate event code that is being called? Perhaps try this... Sub Sort_Type_Rec_Ascend() If MsgBox("SORT ... Type Record (Ascending)?", _ vbYesNo, "ATTENTION!") = vbYes Then With ActiveSheet .Unprotect .Range("A2:D22").Sort Key1:=Range("A3"), _ Order1:=xlAscending, _ Header:=xlYes, _ MatchCase:=False .Protect .Calculate End With End If End Sub it only calculates the current sheet and not the entire workbook. -- HTH... Jim Thomlinson "Ken" wrote: Yes ... WB 21mg so I set Calc to "Manual" for speed of data entry ... Then I forced a "Calulate" in Recorded Macro ... ALL working fine when all of a sudden I had "Out of Stack Space" error message with "Calculate" the yellow high-lite offender. SS appears to still be working fine & I can "F9" Calculate with no problem ... I just can't Macro Record "F9" as "Calculate" & then assign a Forms Button to it ... as this is when the "Calculate" returns the "Out of Stack Space" Error. Issue is ... this SS to be updated by other Folks ... Setting Calc to Auto will slow data entry down ... & F9 Calc is not a key I am confident they will use when they should ... Consequently, I buried the "Calculate" within the Macro Buttons, but now a problem??? Thanks for listening & also for supporting these boards ... Any further suggestions here are welcome ... Kha "JLatham" wrote: Is your workbook set to manual calculate? If not, then I'd just delete the offending Calculate statement from the code. Although I doubt if it has anything to do with it, the code always works with the ActiveSheet. That is, the sheet currently selected. Is the sheet this code was designed for the active sheet when it's run? Again, shouldn't have anything to do with out of stack problem - that's usually caused by an almost infinite loop reiteratively calling a routine/process. "Ken" wrote: SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!") If a = 7 Then Exit Sub ActiveSheet.Unprotect Range("A2:D22").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True Calculate Range("O40").Select End Sub Thanks ... Kha "Ken" wrote: Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
Just curious if you have any worksheet/workbooks/application events that are
firing when your code runs? Ken wrote: SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!") If a = 7 Then Exit Sub ActiveSheet.Unprotect Range("A2:D22").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True Calculate Range("O40").Select End Sub Thanks ... Kha "Ken" wrote: Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
Jim / Dave ... Thanks for responding ...
Unfortunately, I can not respond to Jim's reply because I simply do not understand any VB Code language or terminology ... :( That said ... I had a recorded Macro named "Calculate" & I had Form Buttons attached to it ... I also had "Calculate" as an instruction within other Recorded Macros ... Since yesterday, I killed the "Calculate" Macro & I re-recorded it with a different Name "Calculate File" ... I re-assigned Buttons to this Macro & I no longer get "Stack" error when using these buttons ... This is good. Now I am going to take the apostrophe away from in front of the "Calculate" instruction that is within the other Macros to see if I am now around the "Stack" error??? Not sure if any of this will work, but in reading Jim's reply & his term "looping" I wondered if somehow the Macro "Calculate" & my imbedded "Calculate" instruction were somehow causing this??? Thanks for supporting these boards & providing the many valuable solutions .... Kha "Dave Peterson" wrote: Just curious if you have any worksheet/workbooks/application events that are firing when your code runs? Ken wrote: SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!") If a = 7 Then Exit Sub ActiveSheet.Unprotect Range("A2:D22").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True Calculate Range("O40").Select End Sub Thanks ... Kha "Ken" wrote: Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha -- Dave Peterson . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space ... Recorded Macros
Jim / Dave ... (Good morning)
Yes ... I am back on the road again ... All Buttons & Macros working as desired ... NO more "Out of Stack Space" issue with "Calculate" ... :) Many "Thanks" again ... Kha "Ken" wrote: Jim / Dave ... Thanks for responding ... Unfortunately, I can not respond to Jim's reply because I simply do not understand any VB Code language or terminology ... :( That said ... I had a recorded Macro named "Calculate" & I had Form Buttons attached to it ... I also had "Calculate" as an instruction within other Recorded Macros ... Since yesterday, I killed the "Calculate" Macro & I re-recorded it with a different Name "Calculate File" ... I re-assigned Buttons to this Macro & I no longer get "Stack" error when using these buttons ... This is good. Now I am going to take the apostrophe away from in front of the "Calculate" instruction that is within the other Macros to see if I am now around the "Stack" error??? Not sure if any of this will work, but in reading Jim's reply & his term "looping" I wondered if somehow the Macro "Calculate" & my imbedded "Calculate" instruction were somehow causing this??? Thanks for supporting these boards & providing the many valuable solutions ... Kha "Dave Peterson" wrote: Just curious if you have any worksheet/workbooks/application events that are firing when your code runs? Ken wrote: SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!") If a = 7 Then Exit Sub ActiveSheet.Unprotect Range("A2:D22").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True Calculate Range("O40").Select End Sub Thanks ... Kha "Ken" wrote: Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Out of Stack Space error | Excel Discussion (Misc queries) | |||
Copying & Pasting with recorded macros | Excel Discussion (Misc queries) | |||
recorded macros | Excel Worksheet Functions | |||
Out of Stack space - run time error 28 | Excel Discussion (Misc queries) | |||
"Out of Stack Space" Macro Error | Excel Discussion (Misc queries) |