下标超出范围错误 - Excel VBA

I know this issue has been frequently discussed but I still can't find a fix for it. I am getting the Subscript out of range error and this occurs on and off.

我知道这个问题经常被讨论过,但我仍然无法找到解决方法。我得到的下标超出范围错误,这发生在和关闭。

Sometimes the program runs but sometimes it gets stucked.

有时程序运行但有时它会被卡住。

• The program gets stuck on the following line.

该程序卡在以下行。

• The same declarations with other worksheets did not have this problem.

与其他工作表相同的声明没有这个问题。

• I only have one workbook open and I prefer to use Activeworkbook rather than specifying the Excel file.

  我只打开一个工作簿,我更喜欢使用Activeworkbook而不是指定Excel文件。

  Set shAuditTrail = ActiveWorkbook.Sheets("AuditTrail")

  设置shAuditTrail = ActiveWorkbook.Sheets(“AuditTrail”)

I have tried the following

我尝试了以下内容

1. Switching the code to name Sheet5 instead of the worksheet name.

将代码切换为名称Sheet5而不是工作表名称。

2. Making sure name is correct by printing out all worksheet names in Immediate Window.

通过在立即窗口中打印出所有工作表名称来确保名称正确。

---

Okay, after I opened and repaired the file, it seems to work. For those who don't know how to do open and repair a corrupt workbook, here are the steps (from MS support).

好的,在我打开并修复文件后,似乎工作正常。对于那些不知道如何打开并修复损坏的工作簿的人,以下是步骤(来自MS支持)。

And this blog offers some tips on troubleshooting the same issue. (Although it didn't offer the solution of repairing the workbook.)

此博客提供了解决同一问题的一些提示。 (虽然它没有提供修复工作簿的解决方案。)

I appreciate all those who responded and tried to help.

我感谢所有回复并试图提供帮助的人。

1 个解决方案

#1

I only have one workbook open and I prefer to use Activeworkbook rather than specifying the Excel file.

我只打开一个工作簿,我更喜欢使用Activeworkbook而不是指定Excel文件。

If your macro is only ever intended to run against the AuditTrail Worksheet in the same Workbook then you should use ThisWorkbook:

如果您的宏只打算针对同一工作簿中的AuditTrail工作表运行,那么您应该使用ThisWorkbook:

Set shAuditTrail = ThisWorkbook.Worksheets("AuditTrail")