Function calls cause stack overflow in Excel VBA

Question

4.00/5 (2 votes)

See more:

Not really sure if this the right place to ask an _Excel_ VBA question, but here we go anyway:

I have to debug a macro code in a huge excel file which triggers some cascaded function calls. The first parameter of the function calls is always a Worksheet, as in:

VB

Function ( sheet As Worksheet, bla...)

So after a few cascades, Excel raises the error that the stack is full.

Two questions:
1. Does another local copy of the worksheet gets created by each cascaded function call (this is my assumption why the overflow occurs), or does Excel use calls by reference anyway ?
2. If yes, what is the best way to fix that:
a. Use ByRef (But will the modification made to the worksheet by a function call remain when the function is left ?)
b. Pass the name of the worksheet instead of the worksheet itself ?
c. Another option ?

Thanks,

Posted 29-Jan-13 6:55am

Rage

Add a Solution

Comments

Mike Meinz 29-Jan-13 14:42pm

1. There is only one copy of the worksheet unless your VBA code explicitly has programming to create another worksheet. However, I am concerned that passing a worksheet as a parameter creates a copy of the worksheet on the stack. I strongly recommend that you just pass the worksheet name as a parameter rather than the entire worksheet. The function can then check to see if the current active worksheet is the appropriate worksheet and if not switch to it. Don't forget, if it changes to another active worksheet, it should change it back to the prior worksheet when it exits.

2. Pass the name of the worksheet! Generally though, you will want the function to operate on the current ActiveWorksheet so you shouldn't have to pass a parameter to identify the worksheet to the function.

3. If the stack overflow is not caused by the above, then the most likely cause of Stack Overflow :
If you are getting a stack overflow, the most likely cause is that some function is getting called again by the same function or a function that is called by that function.

Example:
1. Function AAA calls Function BBB
2. Function BBB calls Function CCC
3. Function DDD calls Function AAA
4. Function AAA calls Function BBB
5. Function BBB calls Function CCC
6. Function DDD calls Function AAA
... And it keeps going until you get a stack overflow.

Rage 30-Jan-13 2:54am

Thank you. Passing the name of the worksheet will then be the first move.
Note to 1 : With copy, I meant, as you guessed, a copy on the stack. I was not sure whether the interpreter is able to automatically use a reference instead of a stack copy. I guess that a compiler would be able to do that, but not the interpreter...

Mike Meinz 30-Jan-13 8:28am

The the worksheet that the function will be working on is the current Active Worksheet, you don't need to pass the name of the worksheet. Just have the function use the ActiveSheet property.

Please accept the Solution so that this Question is marked as Answered.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-01-30T02:28:00

Solution 1

Do not pass the WorkSheet object. Pass the name of the Worksheet or use the ActiveSheet property within the function to operate on the currently Active worksheet.

Posted 30-Jan-13 2:28am

Mike Meinz