Excel 工作表突然无法运行 VBA 代码 - 在“某些”其他机器上工作 - 复制工作表工作正常

Excel Sheet Suddenly Won't Run VBA Code - Works on 'Some' Other Machines - Duplicated Sheet Works Fine

提问by AJS

I have an issue with my Excel workbook. I have code both in modules and embedded in the sheet.

我的 Excel 工作簿有问题。我在模块中和嵌入在工作表中都有代码。

Until recently, everything was working fine but suddenly the code embedded in the sheet does not work.

直到最近，一切正常，但突然间嵌入在工作表中的代码不起作用。

I have a sub routine for right clicked cells. This used to create a drop-down menu of its own rather than the standard right click menu but recently it stopped working. I have tried deleting the entire code and simply putting msgbox("Test"), but it still doesn't work.

我有一个用于右键单击单元格的子例程。这曾经用于创建自己的下拉菜单而不是标准的右键单击菜单，但最近它停止工作。我试过删除整个代码并简单地放置msgbox("Test")，但它仍然不起作用。

Macros are enabled as this is the interesting thing ... if I right click the sheet tab and copy it ... the new version works absolutely fine. It is as if the original sheet has become corrupt.

启用宏，因为这是一件有趣的事情……如果我右键单击工作表选项卡并复制它……新版本绝对可以正常工作。就好像原始工作表已损坏。

This is further evidenced by when I try to delete the original sheet manually (right click tab and delete).

当我尝试手动删除原始工作表（右键单击选项卡并删除）时，进一步证明了这一点。

This causes Excel to crash.

这会导致 Excel 崩溃。

I can delete any other sheet no problems.

我可以删除任何其他工作表没有问题。

However, a colleague of mine opened the workbook and it works fine for him without having to duplicate the sheet.

但是，我的一位同事打开了工作簿，他无需复制工作表就可以正常工作。

When it first happened I did the duplicate fix then renamed the original to "OBSOLETE". Not a pretty workaround, but worked nevertheless.

当它第一次发生时，我做了重复的修复，然后将原来的重命名为“过时”。不是一个很好的解决方法，但仍然有效。

It has now happened again, however, and I am reluctant to keep patching it in this way.

然而，它现在又发生了，我不愿意继续以这种方式修补它。

If I try to address the sheet using VBA from another sheet:

如果我尝试使用另一张工作表中的 VBA 处理工作表：

Code in sheet 2 ("Slave"): msgbox(sheets("Master").cells(1,1).value)

表 2 中的代码（“Slave”）： msgbox(sheets("Master").cells(1,1).value)

it gives the following error:

它给出了以下错误：

Run-time error 32809: Application-defined or object-defined error

运行时错误 32809：应用程序定义或对象定义错误

I have tried addressing it as sheet1.cellsinstead of sheets("Master").cellsas well and this gives the same error.

我已经尝试解决它sheet1.cells而不是sheets("Master").cellsas well 并且这给出了相同的错误。

Changing the code to point to Sheet3works fine. It is literally just this sheet causing problems.

将代码更改为指向Sheet3工作正常。从字面上看，正是这张纸引起了问题。

I tried 'open and repair' on the document too.

我也尝试过对文档进行“打开和修复”。

I have read around extensively and tried the deletion of .exd files etc. No luck.

我已经广泛阅读并尝试删除 .exd 文件等。没有运气。

Any thoughts? It seems independent of code - more a specific issue with this workbook and its sheet which over time becomes 'corrupt' but only for some machines/users.

有什么想法吗？它似乎独立于代码——更像是这个工作簿及其工作表的一个特定问题，随着时间的推移它会变得“损坏”，但仅适用于某些机器/用户。

There is also a command button in the sheet. This also does not work (clicking it just does nothing). If I go into the VBA editor and manually activate the sub-routine, I get the following error:

工作表中还有一个命令按钮。这也不起作用（单击它什么也不做）。如果我进入 VBA 编辑器并手动激活子例程，则会出现以下错误：

Compile error:

Object library invalid or contains references to object definitions that could not be found.

编译错误：

对象库无效或包含对无法找到的对象定义的引用。

Again, this still happens even if I strip the code right down to a simple msgbox operation.

同样，即使我将代码剥离为简单的 msgbox 操作，这种情况仍然会发生。

Any thoughts would be appreciated. Thanks

任何想法将不胜感激。谢谢

回答by ed kelly

Hi i am having the same issue running code that populates combo boxes on my sheet. It seems that the sheet index is being deleted so the workbook o longer 'sees' the sheet you are trying to reference.

嗨，我在运行填充我工作表上组合框的代码时遇到了同样的问题。似乎正在删除工作表索引，因此工作簿不再“看到”您尝试引用的工作表。

A simple workaround i found is to resave the workbook with a different name and it should work fine but this is not ideal when it is running on a regular basis. It does however allow you to recover the original workbook after it has been corrupted on the other machine.

我发现一个简单的解决方法是使用不同的名称重新保存工作簿，它应该可以正常工作，但这在定期运行时并不理想。但是，它允许您在其他计算机上损坏后恢复原始工作簿。

It seems that some security updates made by microsoft in December 2014 have resulted in issues running sheets across the old and new machines. They are aware of the issue and are working to resolve it. This blog explains the issues and work arounds: http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/11/forms-controls-stop-working-after-december-2014-updates-.aspx

微软在 2014 年 12 月进行的一些安全更新似乎导致在新旧机器上运行工作表时出现问题。他们知道这个问题并正在努力解决它。该博客解释了问题和解决方法：http: //blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/11/forms-controls-stop-working-after-december-2014-updates-.aspx

Their solution provided on the above blog requires all users to have this update and then to delete all .exd files from the temp folder that are related to the issue and then recompile the code (this still requires all machines to have the new update).

他们在上述博客上提供的解决方案要求所有用户都进行此更新，然后从临时文件夹中删除与问题相关的所有 .exd 文件，然后重新编译代码（这仍然需要所有机器都有新的更新）。

Another option (the one I will be taking until they resolve the issue) is to roll back the problem machines to a pre Dec version prior to making the update.

另一种选择（在他们解决问题之前我将采用的方法）是在进行更新之前将有问题的机器回滚到 12 月之前的版本。

Hope that helps!

希望有帮助！