麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] "堆疊空間不足"問題請教 [打印本頁]

作者: rouber590324 時間: 2015-5-5 16:04 標題: "堆疊空間不足"問題請教

DEAR  SIR
執行如下出現 "堆疊空間不足" ???
煩不吝賜教 THANKS ROBERT  05/05

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  檔案名稱
  AA
End Sub

Sub AA()
X = Sheet51.Range("AA1")
Y = Sheet51.Range("AA2")
If X <> Y Then
Application.CutCopyMode = False
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:= _
      "C:\ " & Sheet51.Range("AA1") & ".xls", FileFormat:=xlNormal, _
      Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
      CreateBackup:=False
End If

End Sub

Sub 檔案名稱()
  Dim myWB As Workbook
  Dim I As Long
  Dim MYBASENAME As String
  Set myWB = Workbooks(1)
  MYBASENAME = myWB.Name
  I = InStrRev(MYBASENAME, ".")
  If I > 0 Then MYBASENAME = Left(MYBASENAME, I - 1)
  M = MYBASENAME
  Sheet51.Range("AA2") = M
  Set myWB = Nothing
End Sub

作者: luhpro 時間: 2015-5-5 22:36

本帖最後由 luhpro 於 2015-5-5 22:39 編輯

DEAR SIR
執行如下出現 "堆疊空間不足" ???
煩不吝賜教 THANKS ROBERT 05/05
Private Sub W ...
rouber590324 發表於 2015-5-5 16:04

那是因為工作表變更事件一直被觸發,
自然很快就Over Flow了.
這類情形可以用單步執行來發現問題點:
1. 先對檔案名稱這一行設中斷點.
2. 變更此工作表的內容觸發此程序.
3. 依次按 F8 按鍵觀察程式執行步驟.
你會發現程式是這樣跑的-
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
檔案名稱
Sub 檔案名稱()
...
Sheet51.Range("AA2") = M
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
...
(無限迴圈)

解決的方法是在不應觸發 SheetChange 程序時就不要觸發:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
檔案名稱
AA
Application.EnableEvents = True
End Sub

複製代碼

作者: rouber590324 時間: 2015-5-6 09:33

DEAR luhpro 大大
真是受益良多小弟瞭解哩 THANKS*10000

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)