How to solve stack overflow error 28 in vba Excel

Question

This simple macro stops after 178 iterations with an Error 28 message. How can I bypass this problem so that the macro keeps running? I've tried a lot of suggestions from forums without success. I'm using Excel 2007. D Hatch Bilthoven NL

Sub Macro1()

 ' Macro1 Macro

 ' Keyboard Shortcut: Ctrl+a

  ActiveCell.Range("A1:B1").Select
  Selection.Copy
  ActiveCell.Offset(1, 0).Range("A1").Select
  ActiveSheet.Paste
  ActiveCell.Offset(-1, 0).Range("A1:B1").Select
  Application.CutCopyMode = False
  Selection.Copy
  ActiveCell.Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  ActiveCell.Offset(1, 0).Range("A1").Select
  Application.Run "Macro1"
End Sub

This is probably a call stack issue. The macro is calling itself again and again, without ever meeting a Exit or Return. I can imagine the call stack has a limited size. Ugly code. — Patrick Honorez
– Patrick Honorez, Commented Aug 23, 2015 at 20:40
What is the first ActiveCell when the macro is initiated? Is it intended to always be the currently selected cell? Do you want one Paste and one Paste Special, Values or should they both be one or the other? What stops the macro? It looks to be in an infinite loop (until it runs out of rows and crashes). — user4039065
– user4039065, Commented Aug 23, 2015 at 20:43
It looks like you are trying to do something like shift data from two columns one row further down (though it is hard to tell since there is a lot of obscurity in your code). I suspect that there should be a non-recursive macro with only 2 or 3 lines of code which does what you are trying to do. So -- what are you trying to do with all this? — John Coleman
– John Coleman, Commented Aug 23, 2015 at 21:08

Gary's Student · Accepted Answer · 2015-08-23 21:51:15Z

1

Your macro recursively calls itself without limit. The call stack should overflow. If you want a much simpler example of this, try:

Sub macro1()
  Call macro1
End Sub

answered Aug 23, 2015 at 21:51

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

joehanna · Accepted Answer · 2015-08-23 23:42:57Z

You have not provided a way of exiting the method without calling itself again.

You need to do one of the following:-

Add a condition/test around the Application.Run "Macro1" code so you give it a chance to exit
Remove this line: Application.Run "Macro1". To be honest, I can't see the point of it without it being surrounded by If/Then/Else

In fact, this method doesn't seem to make sense. If you want to copy two cells down the worksheet, there are quicker ways to do it like this:-

ActiveCell.Range("A1:B1").Select
Selection.Copy
'Select the range you want to copy it down to - but there must be *some* limit!
ActiveCell.Offset(1, 0).Range("A1:B1000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(-1, 0).Select

2 revs · Accepted Answer · 2020-11-11 05:25:17Z

0

You need to do following this step:-

Sub macromethod()
you need to all write method name simple and then exiting method call  in 

 macromethod

End Sub

if the one module to anothor module method call then you can USE Call macromethod same method in call method to simple write only macromethod

edited Nov 11, 2020 at 5:25

community wiki

2 revs
Mayur Pathak

Collectives™ on Stack Overflow

How to solve stack overflow error 28 in vba Excel

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related