Runtime 28 - Out of stack space

[Oggy]

Hi

I have written a macro in Excel 2003 and everything seems to works as
exspected, but accasionly i get the following error message

Runtime error 28 Out of stack space


Does anyone know what it is and is there a code i can add to my macro
to fix it?

Thanks

 

[Trevor Shuttleworth]

Does the macro call itself ?

If so, I'd guess that it doesn't get to a point where it meets the exit
criteria.

Regards

Trevor

 

[JW]

Have a look <a href="http://support.microsoft.com/kb/111867">here</a>.

 

[Jim Thomlinson]

There are 2 kinds of memory that you need to understand when you program. The
first is the heap. The heap is where a program stores all of the memeory that
needs to persist for as long as the program is active. Global and Static
variables land in the heap. The stack on the other hand stores the variables
that are volatile and which end with the function or procedure that declares
them. The biggest culpret for running our of stack is a recursive function
without a valid stop. In this case the procedure declares a variable on the
stack and then calls itself again. When it is called it declares another
variable on the stack on top of the other variabes that are already there.
This happens recursively and before too long the stack is full. Normally this
generates a Stack Overflow. Out of stack space is new too me but as a guess
it would be generally the same problem. Take a look for a sub or function
that could be called recursively (event code is a good culprete for recursive
code). Post the code that seems to be generating the error and we can take a
look at it for you.

 

[Jim Thomlinson]

That article says it only applies to XL 5.0. Is it still a proble in 2003
Version 11

 

[Oggy]

That article says it only applies to XL 5.0. Is it still a proble in 2003
Version 11
--
HTH...

Jim Thomlinson






- Show quoted text -

My code opens a userform, which a user fills out and then on the
comand button it takes the infomation and places it in a spreadsheet
and the opens another userform. This happens three times and then goes
back to the menu.

The error happened after about 30 loops

Thanks for all your help

 

[Jim Thomlinson]

Are you hiding the user form or are you unloading it. Unloading the form will
clear out the stack. Other than that post your code an dmaybe we will be able
to spot something.

 

[Oggy]

Hi Jim

I have looked through the code and the only thing i can think of is
the code to check the screen resolution and make the userforms fill
the screen may be the problem. Below is the code to one of the loops
where it opened userform 12, then 13, then 14, then 15 from a menu
userform 16. This code is to show the userforms only, there is more
code to the userforms themselves.

Any coments or alternitive ideas would be grafully recieved

Many thanks

Sub repeat()

Sheets("hide").Visible = True
Sheets("Hide").Select



' Adjusts userform size to compensate for screen resolution changes.
' Jim Cone - San Francisco, USA - Dec 2006

Dim RatioX As Single
Dim RatioY As Single
Dim ActualX As Long
Dim ActualY As Long


'Screen resolution in development environment.
'Adjust as necessary.
Const BaseX As Long = 1280
Const BaseY As Long = 800


'Call function to get actual screen resolution
Dim R As RECT
Dim hWnd As Long
Dim RetVal As Long


hWnd = GetDesktopWindow()
RetVal = GetWindowRect(hWnd, R)


ActualX = (R.x2 - R.x1)
ActualY = (R.y2 - R.y1)


'Determine ratio of actual screen resolution to
'the original or base resolution.
RatioX = ActualX / BaseX
RatioY = ActualY / BaseY



'Adjust userform magnification and size.

UserForm12.Zoom = (100 * ((RatioX + RatioY) / 2))

UserForm12.Width = UserForm12.Width * RatioX

UserForm12.Height = UserForm12.Height * RatioY

UserForm12.Show
Unload UserForm12
Set UserForm12 = Nothing

End Sub


Sub repeatparts()



Dim strRowSource As String

strRowSource = Sheets("hide").Range("b10",
Sheets("hide").Range("b65536").End(xlUp)).Address

With UserForm12.ListBox1

..RowSource = vbNullString

..RowSource = strRowSource


End With




End Sub
Sub repeatpart()


' Adjusts userform size to compensate for screen resolution changes.
' Jim Cone - San Francisco, USA - Dec 2006

Dim RatioX As Single
Dim RatioY As Single
Dim ActualX As Long
Dim ActualY As Long


'Screen resolution in development environment.
'Adjust as necessary.
Const BaseX As Long = 1280
Const BaseY As Long = 800


'Call function to get actual screen resolution
Dim R As RECT
Dim hWnd As Long
Dim RetVal As Long


hWnd = GetDesktopWindow()
RetVal = GetWindowRect(hWnd, R)


ActualX = (R.x2 - R.x1)
ActualY = (R.y2 - R.y1)


'Determine ratio of actual screen resolution to
'the original or base resolution.
RatioX = ActualX / BaseX
RatioY = ActualY / BaseY


'Adjust userform magnification and size.

UserForm13.Zoom = (100 * ((RatioX + RatioY) / 2))

UserForm13.Width = UserForm13.Width * RatioX

UserForm13.Height = UserForm13.Height * RatioY

UserForm13.Show
Unload UserForm13
Set UserForm13 = Nothing

End Sub
Sub repeatmaterial()


' Adjusts userform size to compensate for screen resolution changes.
' Jim Cone - San Francisco, USA - Dec 2006

Dim RatioX As Single
Dim RatioY As Single
Dim ActualX As Long
Dim ActualY As Long


'Screen resolution in development environment.
'Adjust as necessary.
Const BaseX As Long = 1280
Const BaseY As Long = 800


'Call function to get actual screen resolution
Dim R As RECT
Dim hWnd As Long
Dim RetVal As Long


hWnd = GetDesktopWindow()
RetVal = GetWindowRect(hWnd, R)


ActualX = (R.x2 - R.x1)
ActualY = (R.y2 - R.y1)


'Determine ratio of actual screen resolution to
'the original or base resolution.
RatioX = ActualX / BaseX
RatioY = ActualY / BaseY



'Adjust userform magnification and size.

UserForm14.Zoom = (100 * ((RatioX + RatioY) / 2))

UserForm14.Width = UserForm14.Width * RatioX

UserForm14.Height = UserForm14.Height * RatioY

UserForm14.Show
Unload UserForm14
Set UserForm14 = Nothing
End Sub