vba 如何修复“堆栈空间不足”错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17816898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fix "out of stack space" error?
提问by nedstark179
I have code which takes a table, and rearranges the table to form a new table. It worked with a small amount of data, but now that I tried to run the same code with over 1,000 records, it is getting Error 28 which is "Out of stack space". I will not copy all of my code here because it would be way too much and I think unnecessary, unless you think otherwise. I think it is a problem with my recursion of the sub. I need this because a DONOR_CONTACT_ID can only have 4 recipients, if it has more, then it must create a new record with the same DONOR_CONTACT_ID and populate the recipients.
我有一个代码，它需要一个表格，并重新排列表格以形成一个新表格。它处理少量数据，但现在我尝试运行具有 1,000 多条记录的相同代码，它收到错误 28，即“堆栈空间不足”。我不会在这里复制我的所有代码，因为这太多了，而且我认为没有必要，除非您另有想法。我认为这是我对 sub 的递归的问题。我需要这个，因为一个 DONOR_CONTACT_ID 只能有 4 个收件人，如果有更多，那么它必须创建一个具有相同 DONOR_CONTACT_ID 的新记录并填充收件人。
Here is the sub routine which is getting the error:
这是出现错误的子例程：
Sub NextDonor()
With rstOutput
.FindNext "[DONOR_CONTACT_ID] = " & strDonor2
'Find the next record in T_OUTPUT with that DONOR_CONTACT_ID
If .NoMatch Then
'If there are no more records with that DONOR_CONTACT_ID, add a new one
.AddNew
!DONOR_CONTACT_ID = strDonor1
!RECIPIENT_1 = strRecip1
!ORDER_NUMBER = strOrderNum1
.Update
Else
'A second DONOR_CONTACT_ID in T_OUTPUT exists. Check to see if all fields are filled.
If !DONOR_CONTACT_ID = strDonor2 Then
If IsNull(!RECIPIENT_2) And Not (IsNull(!RECIPIENT_1)) Then
'RECIPIENT_2 is empty, so populate it
.Edit
!RECIPIENT_2 = strRecip1
.Update
ElseIf IsNull(!RECIPIENT_3) And Not (IsNull(!RECIPIENT_2)) Then
'RECIPIENT_3 is empty, so populate it
.Edit
!RECIPIENT_3 = strRecip1
.Update
ElseIf IsNull(!RECIPIENT_4) And Not (IsNull(!RECIPIENT_3)) Then
'RECIPIENT_4 is empty, so populate it
.Edit
!RECIPIENT_4 = strRecip1
.Update
ElseIf Not IsNull(!RECIPIENT_4) Then
'RECIPIENT_4 is filled, so run this function again
Call NextDonor
End If
End If
End If
End With
End Sub
The error is in the line where it says "Call NextDonor", probably because of the recursion. If you need me to clarify what my code is trying to do, or if you want me to copy other parts of my code, just let me know.
错误出现在“Call NextDonor”那一行，可能是因为递归。如果您需要我澄清我的代码要做什么，或者如果您希望我复制代码的其他部分，请告诉我。
回答by matzone
Try this to avoid recursion ...
试试这个以避免递归......
Sub NextDonor(byref Again as Boolean)
With rstOutput
DoItAgain :
.FindNext "[DONOR_CONTACT_ID] = " & strDonor2
If ....
....
ElseIf Not IsNull(!RECIPIENT_4) Then
'RECIPIENT_4 is filled, so run this function again
Goto DoItAgain
End If
End Sub
回答by basdwarf
Actually your recursive code and 1st answer both skip past the recipient if the 4th slot is full, you iterate with another Find and you lose the current recipient! This also eliminates the recursion. instead:
实际上，如果第 4 个插槽已满，您的递归代码和第一个答案都会跳过接收者，您使用另一个 Find 进行迭代，并且您丢失了当前接收者！这也消除了递归。反而：
If .NoMatch or (not isnull(!recipient_4)Then
'If there are no more records with that DONOR_CONTACT_ID, add a new one
' or current record is full
.AddNew
!DONOR_CONTACT_ID = strDonor1
!RECIPIENT_1 = strRecip1
!ORDER_NUMBER = strOrderNum1
.Update
Else
