Excel VBA调试器停止运行，没有错误或警告 [英] Excel VBA debugger stops without error or warning

问题描述

在尝试测试一些代码时，我遇到了一个我不记得遇到过的问题。当我单步执行代码时，它在ClearContents行之后停止……没有错误，没有警告，什么都没有。

While trying to test some code, I am having an issue that I don't recall running into before. When I step through the code it halts after the ClearContents line...there is no error, no warning, nothing.

Public Sub CreateCurMth(wsCur As Worksheet)
Dim iData As Integer, iRow As Integer
Dim wbData As Workbook
On Error GoTo err_here

  iRow = wsCur.Cells(Rows.Count, 1).End(xlUp).Row
  wsCur.Range("A10:X" & iRow).ClearContents  '<----- Fails after this line

'Assume that file is already open
  Set wbData = Workbooks("qReport.xlsx")
  ... etc
err_here:
  Beep
End Sub

wsCur是在调用子项中分配的工作表。该代码准确地将iRow返回为wsCur上最后使用的行。同样，内容也被清除了……这样行就可以执行了。死后没有播放哔声。我在Beep上也有一个断点，而且从来没有命中。

wsCur is a worksheet that is assigned in the calling sub. The code accurately returns the iRow as the last used row on wsCur. Also, the contents are cleared...so that line executes fine. There is no Beep played after it dies. I have a breakpoint on the Beep also, and it is never hit.

因此，正如我在输入问题时通常发生的情况...我一直在尝试事情，通常会在尝试完全记录我的问题时最终找到我的答案。这次的结果还算不错……由于我找不到原因，上面的代码现在可以正常工作了……但是，我在VBA的其他部分仍然遇到了与上面的部分不一样的问题。我已经在这个项目上工作了一个星期左右，并且在今天之前的所有测试中，这段代码都已经起作用了……或者至少像通常预期的那样出错了。

So, as what usually happens while I'm typing out a question...I keep trying things and usually end up finding my answer while trying to fully document my question. Semi-decent results this time...for no reason that I can find, the code above is now working...however, I keep having the same issue in other parts of my VBA that aren't similar to the section above. I've been working on this project for a week or so, and all testing prior to today, this code has worked...or at least errored out like would normally be expected.

每次调试器停止时，没有错误，也没有警告。失败的线路甚至没有任何共同点。因此，我只能假设这对我的系统来说是环境。

Each time the debugger stops, there is no error and no warning. The lines that are failing don't even have anything in common. So I'm left to assume that is it something environmental with my system.

我已经搜索并搜索过Google，但没有答案。我希望这里的某个人以前曾遇到过此问题并可以解决，或者至少可以指出正确的方向。

I've searched and Googled, and am left with no answers. I'm hoping that some one here has had this issue before and resolved it, or can at least point me in the correct direction.

我的代码编译没有错误。这是当前暂停的行：

My code compiles without error. This is the line that is currently halting:

wsTemp.Delete

已删除临时工作表...然后暂停。

Temp worksheet IS deleted...then halts.

编辑： ，所以我认为这只是一个损坏的Excel文件，因此需要执行Open&维修解决了几个小时。但是现在我遇到了同样的问题，在

Ok, so I thought this was just a corrupted Excel file, and doing an Open & Repair resolved it for a few hours. But now I'm having the same issue where the debugger fails after

wsTemp.Delete

我打开了工具>选项>常规>遇到所有错误都中断，但仍然没有错误也没有警告...代码逐步停止

I turned on Tools > Options > General > Break on ALL Errors and still I get no error and no warning...code step through just halts.

只是要彻底...这里是到故障点为止的整个过程：

Just to be thorough...here is the entire procedure up to the failure point:

'Creates combined joblist from last and current month
Sub CreateCompareList(wsCur As Worksheet, wsLast As Worksheet, wsComp As Worksheet)
Dim wsTemp As Worksheet
Dim lLastRow As Long, lCurRow As Long, lLMRow As Long
Dim iCol As Integer, x As Integer

  wsComp.Unprotect SheetPwd
  lLastRow = wsComp.Cells(Rows.Count, 27).End(xlUp).Row         'Get the last row
  If lLastRow = 9 Then
    'No data on sheet - Don't ruin headers
  Else
    wsComp.Rows("10:" & lLastRow).ClearContents                 'Clear that section of Job names and numbers
  End If
  Set wsTemp = Worksheets.Add                                   'Add a Temp Sheet to filter jobs

  lCurRow = wsCur.[A10].End(xlDown).Row                         'Get the last row
  wsCur.Range("A10:B" & lCurRow).Copy                           'Grab all jobs for the current month
  wsTemp.[A1].PasteSpecial                                      'Paste job list from Current Month
  Application.CutCopyMode = False

  lLMRow = wsLast.[A10].End(xlDown).Row                         'Get the last row
  wsLast.Range("A10:B" & lLMRow).Copy                           'Grab all jobs from the Last month
  lLastRow = wsTemp.[A1].End(xlDown).Row                        'Get the last row
  wsTemp.Cells(lLastRow + 1, 1).PasteSpecial                    'Paste jobs from last month below jobs from current month
  Application.CutCopyMode = False

  lLastRow = wsTemp.[A1].End(xlDown).Row                        'Get new last row
  'Filter out duplicates based on the JobNo
  wsTemp.Range("$A$1:$B$" & lLastRow).RemoveDuplicates Columns:=2, Header:=xlNo
  lLastRow = wsTemp.[A1].End(xlDown).Row                        'Get new last row
  wsTemp.Range("A1:B" & lLastRow).Copy                          'Copy unique Jobs

  wsComp.[A10].PasteSpecial                                     'Paste Unique Jobs to Compare sheet

'Clean up Temp sheet
'  Application.DisplayAlerts = False
  wsTemp.Delete

我已经尝试过关闭和关闭DisplayAlerts，当警报打开时...我得到警报，表明工作表上可能有数据...我单击删除，工作表被删除...然后代码暂停。有时在上面的其他.ClearContents行上也会再次出现问题。

I have tried it with the DisplayAlerts on and off, when the Alerts are on...I get the alert that there may be data on the sheet...I click delete, the sheet is deleted...and then code halts. It occasionally has problems again on other .ClearContents lines like above.

推荐答案

关于这个问题的事后看来，得出的结论是实际上是损坏的Excel文件。幸运的是，我最近也进行了一次备份。但是，为了节省在该备份和损坏的版本之间进行编码所花费的时间，以下是我保存文件的步骤，以防万一其他人遇到我做的奇怪行为：

Postmortem on this issue seems to lead to the conclusion that this was in fact a corrupted Excel file. Luckily I had a fairly recent back up as well. However, in order to save the time that had been spent coding between that backup and the corrupted version, here are the steps I took to save the file, in case anyone else encounters the strange behavior I did:

1. 关闭所有Excel窗口，并在不打开文件的情况下启动新实例。


2. 使用Ctrl + O来搜索文件。 ..但是，不只是单击打开，而是单击向下箭头，然后选择打开&修复。


3. 单击修复


4. 另存为新副本*-已修复（正因为如此）。

1. Closed all Excel windows and started a fresh instance without opening a file.
2. Used Ctrl+O to search for the file...but instead of just clicking Open, click the down arrow and select Open & Repair.
3. Click Repair
4. Saved as a new copy *-Repaired (just because).

此后，文件按预期运行。 EOMONTH函数中的循环引用消失了，调试器按预期运行。我已经对所有代码进行了全面的测试，并且运行正常。

After this, the file ran as expected. The circular reference in the EOMONTH function went away and the debugger ran as expected. I have run full testing on all code and it is working normally.

我没有任何解释，说明它是如何损坏的，我很幸运。

I have no explanation as to how it became corrupted, just lucky I guess.

请参见 https://support.office.com/en-us/article/Repairing-a-corrupted-workbook-e5b49891-dde7-4796-b60a-49b0d2478a62 处理损坏时的其他选项Excel文件。

See https://support.office.com/en-us/article/Repairing-a-corrupted-workbook-e5b49891-dde7-4796-b60a-49b0d2478a62 for other options when dealing with corrupted Excel files.

编辑：：此问题再次出现，当前起作用的是强制执行Windows Update。该系统设置为自动，但是当我手动检查时，发现3个Office更新。安装这些文件后，我再次运行Windows Update，并为.Net框架找到了另一个更新...所以我也做到了...怀疑没有任何效果...但是...

This issue came back again and what currently has it working was forcing Windows Updates. The system is set on Automatic, but when I manually went and checked, it found 3 Office updates. After installing those, I ran Windows Update again and found another update for .Net framework...so I did that as well...doubt that had any effect...but...

所以...在使用Excel的Open&之后修复，应用更新并重新启动几次，然后打开Break on all errors ...我的代码终于可以继续正常工作了。

So...after using Excel's Open & Repair, applying updates and restarting a couple of times, turning on Break on all errors...my code is finally working again all the way through.

这篇关于Excel VBA调试器停止运行，没有错误或警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！