0

I have a very simple macro in an Excel sheet, to allows users to recalculate.

I have no other macros/code in the workbook, and only this work book open

Sub Calculate()
    Calculate
End Sub

This is activated by a button.

However, when pressed I get two error boxes, see image.

What does Out of stack space mean ? And how do I resolve this issue ? enter image description here

I have looked on this website:

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/out-of-stack-space-error-28

It says I may have too many funtions ?? This macro used to work fine, and it is hardly doing a lot so cannot understand the issue.

I am able to calculate the sheet using the option under the formulas tab.

  • You have written a re-entrant function which calls itself unconditionally without restraining conditions, so it will fill the stack with the repeated calls. What are you hoping to achieve? – AFH Nov 29 '18 at 10:58
  • @AFH all the macro should do is calculate the sheet, a user enters a new date, and then hits the refresh button to re-calculate. the user may not be excel savvy, so I used the macro/button as you can't miss it then. – PeterH Nov 29 '18 at 11:01
  • Without seeing the underlying code it would be hard to provide a definitive answer as to the direct cause as there can be a few reasons - recursive function, variables consuming too much memory. As per your link, the stack is an area of working memory allocated and when you execute your macro the variables and calls it makes are 'pushed' onto the stack. When a function or variable is no longer required it is removed from the stack, freeing memory. The error suggests that your macro may be exceeding the allocated memory for the stack. – Enigman Nov 29 '18 at 11:02
  • @Enigman the only code used in the document is shown in the question, I have no other macros, this is why I am so baffled by this. – PeterH Nov 29 '18 at 11:03
3

The function you have define is recursive, calling itself unconditionally until the stack is filled with all the calls.

You should change the name of your subroutine, eg:-

Sub Calc()
    Calculate
End Sub

If you link Calc() to the button, you avoid any recursion.

  • this has solved the issue thanks !, still a bit baffled by it though, as the problem only started today and worked fine for the last few weeks. – PeterH Nov 29 '18 at 11:10
  • I can't explain how it worked before, unless another revision handled the calls differently. – AFH Nov 29 '18 at 12:00
  • I've worked with compilers [mono] that would separate two visually identical commands in a scope to be separate entities to the compiled script, even though they share a name in plaintext. idk VBA, but it may have done something similar... but no longer does. It could actually be a bug if it's meant to be 'smart' in those circumstances. – Tetsujin Dec 2 '18 at 9:59
  • @Tetsujin - I had assumed it must be something like that. Maybe it's to do with when the function becomes defined. If it's unknown as a function name until its definition is complete, then during the definition the Calculate call must link to the system function. The method of linking may have changed, but this is only speculation. – AFH Dec 2 '18 at 14:12
2

You're calling Calculate inside of Calculate. Every call to Calculate causes another call to Calculate, which will then call Calculate... Then eventually you get that error when the stack fills up.

  • so the calculate function in the code, cause my macro to re-calculate also ? I use this exact same macro in other workbooks without any problem – PeterH Nov 29 '18 at 11:08
  • @PeterH You might have been lucky up to now that it hadn't manifested earlier. Perhaps there was more space on the stack on other occasions - more apps might have been running on the workstation this time or it had been a longer time between reboots of the workstation. It's why we have to be careful when using names for modules and functions as using a reserved word or inbuilt function name can bite us later on as was the case in this situation. – Enigman Nov 29 '18 at 11:16
  • Well thanks for your help anyway, I didn't realize the name of a function really mattered to be honest – PeterH Nov 29 '18 at 11:18

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.