vba 突然几个VBA宏错误，多为32809

Suddenly several VBA macro errors, mostly 32809

提问by

Suddenly my excel file is generating a lot of errors at startup.
No changes have been made to the file since before the errors started.

突然间，我的 excel 文件在启动时产生了很多错误。
自错误开始之前，未对文件进行任何更改。

Example of errors:

错误示例：

Error 438:

错误 438：

Sheets("setup2").CheckBox4.Caption = "L?gg automatiskt till " & _ 
    Sheets("setup2").Range("L8").Value & " m?ndag efter jour."

Error 32809:

错误 32809：

Worksheets(1).Visible = True
Worksheets(1).Cells(42, 3) = ActiveWorkbook.Path
Sheets("setup").Cells(43, 3) = Environ("UserName")
Worksheets(1).Cells(35, 13) = MostRecentDate

The workbook is not password protected.
It's Office 2013 (15.0.4659.1000) MSO (15.0.4659.1001)

工作簿不受密码保护。
这是 Office 2013 (15.0.4659.1000) MSO (15.0.4659.1001)

回答by Grant W

Similar issue. Created an application to track welding procedures. My machine was running Windows 7 and Excel 2010. Anything I created, then distributed to other machines with different OS's or different Excel versions would have 32809 errors, usually associated with selecting a worksheet

类似的问题。创建了一个应用程序来跟踪焊接程序。我的机器运行的是 Windows 7 和 Excel 2010。我创建的任何东西，然后分发到其他具有不同操作系统或不同 Excel 版本的机器都会有 32809 错误，通常与选择工作表有关

Sheets("Data Entry").Select

This would trigger an Application or run time error 32809. I tried all of the fixes previously mentioned (delete all temp files, .exd files etc. No joy.

这将触发应用程序或运行时错误 32809。我尝试了前面提到的所有修复程序（删除所有临时文件、.exd 文件等。没有乐趣。

My solution ( may not work for you)

我的解决方案（可能不适合你）

Opened the application on a machine that was Excel 2007. Added one comment line of code of no consequence into one of the macros

在 Excel 2007 的机器上打开应用程序。在其中一个宏中添加了一个无关紧要的代码注释行

Sheets(sheetName).Select 
'comment of no consequence

and saved it. This caused a recompile. Closed and re-opened with Excel 2007 - all fixed.

并保存了它。这导致了重新编译。使用 Excel 2007 关闭并重新打开 - 全部修复。

If I add any code to these applications on my machine and then distribute, there is invariably an error when run, unless I get the application to recompile on the differing system. I sent the fixed version onto another machine running Excel 2007 and it worked fine.

如果我在我的机器上向这些应用程序添加任何代码然后分发，运行时总是会出现错误，除非我让应用程序在不同的系统上重新编译。我将固定版本发送到另一台运行 Excel 2007 的机器上，它运行良好。

Hope this makes sense and helps

希望这是有道理的并有所帮助

Grant

授予

回答by Hubvill

There is a similar post on error 32809:

有一个关于错误 32809 的类似帖子：

Excel VBA Run-time Error '32809' - Trying to Understand it

Excel VBA 运行时错误“32809” - 试图理解它

They mention that the file may be corrupted and theirs a solution in the comments by Siddharth Rout:

他们提到该文件可能已损坏，他们在 Siddharth Rout 的评论中提供了解决方案：

That is because the worksheet is corrupted. Try this. Create a new sheet and copy every thing from that to the new sheet and then finally delete that sheet. It will work. just tested and tried it.

那是因为工作表已损坏。尝试这个。创建一个新工作表并将其中的所有内容复制到新工作表中，然后最后删除该工作表。它会起作用。刚刚测试并尝试过。

Not sure if that will help you, but worth a try I guess.

不确定这是否会帮助你，但我想值得一试。

回答by Fen

Some ms updates could have brought on the 32809 error. Replace all sheet-embedded buttons (and other controls) with new ones; you can do it manually or with some codes.

某些 ms 更新可能会导致 32809 错误。用新的按钮替换所有嵌入表单的按钮（和其他控件）；您可以手动或使用一些代码来完成。

回答by Jake Bathman

This same error started cropping up for me, and if it's happening to you when you share a VBA workbook with others this is probably your issue.

同样的错误开始出现在我身上，如果您在与他人共享 VBA 工作簿时发生这种情况，这可能是您的问题。

The root is a security update that Microsoft pushed in December 2014. The Microsoft Support page for this issue is here: http://support.microsoft.com/kb/3025036/EN-US

根是 Microsoft 于 2014 年 12 月推送的安全更新。此问题的 Microsoft 支持页面位于此处：http: //support.microsoft.com/kb/3025036/EN-US

Here's the fix:

这是修复：

1. On the computer used to create the VBA Excel file, find an uninstall the relevant security patch under Windows Updates (KB 2726958for Office 2013, KB 2553154for Office 2010, or KB 2596927for Office 2007).
2. After uninstalling the patch, open the VBA file you're having issues with. Make some minor edit to the VBA code so Excel recompiles (just in case).
3. Save the file and test on other computers having the Active-X issues.

1. 在用于创建 VBA Excel 文件的计算机上，在 Windows 更新（KB 2726958Office 2013、KB 2553154Office 2010或KB 2596927Office 2007）下找到卸载相关安全补丁。
2. 卸载补丁后，打开您遇到问题的 VBA 文件。对 VBA 代码进行一些小的编辑，以便 Excel 重新编译（以防万一）。
3. 保存文件并在其他存在 Active-X 问题的计算机上进行测试。

Hopefully this helps someone that was in my position this morning. This is a frustrating issue, and it's hard to track down a resolution.

希望这对今天早上处于我位置的人有所帮助。这是一个令人沮丧的问题，很难找到解决方案。

---

---

Sources & notes:

来源和注释：

[1] Details for the Security Update from Microsoft Update:

[1] Microsoft Update 安全更新的详细信息：

Name: Security Update for Microsoft Office 2010 (KB2553154) 32-Bit Edition

Download size: 542 KB

Details: A security vulnerability exists in Microsoft Office 2010 32-Bit Edition that could allow arbitrary code to run when a maliciously modified file is opened. This update resolves that vulnerability.

More information: http://support.microsoft.com/kb/2553154

姓名:Security Update for Microsoft Office 2010 (KB2553154) 32-Bit Edition

下载大小：542 KB

详情：A security vulnerability exists in Microsoft Office 2010 32-Bit Edition that could allow arbitrary code to run when a maliciously modified file is opened. This update resolves that vulnerability.

更多信息：http: //support.microsoft.com/kb/2553154

[2] "Cannot insert object" error in an ActiveX custom Office solution after you install the MS14-082 security updateat http://support.microsoft.com/kb/3025036/EN-US

[2]在http://support.microsoft.com/kb/3025036/EN-US安装 MS14-082 安全更新后，ActiveX 自定义 Office 解决方案中出现“无法插入对象”错误

[3] Relief for botched Excel patches KB 2553154, 2726958 missing from January Patch Tuesdayat http://www.infoworld.com/article/2868062/operating-systems/january-patch-tuesday-a-yawn-and-several-whimpers-no-relief-for-last-months-kb-2553154-2726958-botc.html

[3]一月补丁星期二在http://www.infoworld.com/article/2868062/operating-systems/january-patch-tuesday-a-yawn-and-several- 中丢失的拙劣 Excel 补丁 KB 2553154、2726958 的救济呜咽-无救济-上个月-kb-2553154-2726958-botc.html

[4] Form Controls stop working after December 2014 Updatesat http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/18/forms-controls-stop-working-after-december-2014-updates-.aspx

[4]表单控件停止2014年12月更新后的工作在http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/18/forms-controls-stop-working-after-december-2014-updates- .aspx

回答by user6015902

We found success using the following approach.

我们使用以下方法取得了成功。

1. Open a blank version of excel.
2. Disable ActiveX controls in Excel Options.

1. 打开一个空白版的excel。
2. 在 Excel 选项中禁用 ActiveX 控件。

enter image description here

在此处输入图片说明

1. Open the offending file. (Click through any error received upon open)
2. Save the file as a new name.
3. Activate ActiveX controls.

3. 打开有问题的文件。（单击打开时收到的任何错误）
4. 将文件另存为新名称。
5. 激活 ActiveX 控件。

We found distributing this file after the steps above resolved the error for all users.

我们发现在上述步骤解决了所有用户的错误后分发此文件。

Hope this helps someone else.

希望这对其他人有帮助。

回答by Humberto

before: (worked fine in golden years)

之前：（在黄金年代工作得很好）

n = sheets(1).name

n = sheet(1).name

now:

现在：

dim ws as new worksheet

将 ws 调暗为新工作表

set ws = activeworkbook.sheets(1)

设置 ws = activeworkbook.sheets(1)

n = ws.name

n = ws.name