函数调用导致Excel VBA中的堆栈溢出 [英] Function calls cause stack overflow in Excel VBA
问题描述
不确定这是否是一个提出_Excel_ VBA问题的合适地方,但是我们还是去了:
我必须调试巨大的宏代码excel文件触发一些级联函数调用。函数调用的第一个参数始终是Worksheet,如:
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:
Function ( sheet As Worksheet, bla...)
因此,在几次级联之后,Excel会引发堆栈已满的错误。
两个问题:
1.是否有其他本地副本每个级联函数调用创建工作表(这是我为什么会发生溢出的假设),或者Excel是否通过引用使用调用?
2.如果是,那么最好的修复方法是什么那:
a。使用ByRef(但是当函数保留时,函数调用对工作表的修改是否仍然存在?)
b。传递工作表的名称而不是工作表本身?
c。另一种选择?
谢谢,
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,
推荐答案
不要传递WorkSheet对象。传递工作表的名称或使用函数中的ActiveSheet属性来操作当前活动的工作表。
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.
这篇关于函数调用导致Excel VBA中的堆栈溢出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!