Out of Stack Space ... Recorded Macros

[Ken]

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

 

[Ken]

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("A222").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

 

[JLatham]

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]

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

 

[Jim Thomlinson]

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("A222").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.

 

[Dave Peterson]

Just curious if you have any worksheet/workbooks/application events that are
firing when your code runs?

 

[Ken]

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

 

[Ken]

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