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

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

提问by Anthony

A colleague at work made some changes to one of our macro workbooks and now on my PC only I receive the dreaded Run-time Error '32809' when I attempt to run it. This latest version runs fine on his PC and another colleague's PC that we tested it on. The previous version runs fine on all of our PC's, all of which are running Excel 2010.

工作中的一位同事对我们的一个宏工作簿进行了一些更改，现在我只有在我的 PC 上尝试运行它时才会收到可怕的运行时错误“32809”。这个最新版本在他的 PC 和我们测试过的另一位同事的 PC 上运行良好。以前的版本在我们所有运行 Excel 2010 的 PC 上运行良好。

The error is thrown when the macro attempts to Select the Worksheet index 1, named "Info". I know that Select/Activate is not required but am just working with this Workbook for now and am trying to work out why I alone would receive this error.

当宏尝试选择名为“信息”的工作表索引 1 时，会引发错误。我知道 Select/Activate 不是必需的，但我现在只是在使用此工作簿，并试图弄清楚为什么我会独自收到此错误。

I have tried:

我试过了：

1. Reboot/Power Cycle
2. Saving a Copy of the Workbook
3. Cleaning out Temp Files with CCleaner
4. Researching online
5. Checking for ActiveX Controls (Uses Form Controls)

1. 重新启动/电源循环
2. 保存工作簿的副本
3. 使用 CCleaner 清理临时文件
4. 在线研究
5. 检查 ActiveX 控件（使用表单控件）

All with no success. I then had a bit of a mess around in the immediate window and discovered that even a simple:

一切都没有成功。然后我在直接窗口中有点乱，发现即使是一个简单的：

Debug.Print ThisWorkbook.Worksheets(1).Name

would throw the run-time error which lead me to believe that somehow that Worksheet had broke. I added a couple of events to the Worksheet including _Activate and _Change but none would fire even after confirming that:

会抛出运行时错误，这让我相信工作表以某种方式损坏了。我在工作表中添加了几个事件，包括 _Activate 和 _Change 但即使在确认之后也不会触发：

Application.EnableEvents = True

I added a simple Test Sub as follows:

我添加了一个简单的 Test Sub 如下：

Public Sub Test()

    Dim ws As Worksheet
    Dim sheetNum As Integer

    For Each ws In ThisWorkbook.Worksheets
        ws.Select           ' Selects all Sheets Without Error
        Debug.Print ws.Name ' Prints All Worksheet Names Fine
    Next ws

    Set ws = ThisWorkbook.Worksheets(1)
    ws.Select               ' Selects Sheet 1 Without Error

    ' Prints all but sheetNum = 1, Run-time Error 32809
    For sheetNum = 7 To 1 Step -1
        Debug.Print ThisWorkbook.Worksheets(sheetNum).Name
    Next sheetNum

    ' Run-time Error 32809
    ThisWorkbook.Worksheets(1).Select

End Sub

Has anyone run into anything similar to this or know of what causes this error to occur only on some PC's?

有没有人遇到过与此类似的事情或知道是什么导致此错误仅在某些 PC 上发生？

回答by Loky

In my case following helped:

在我的情况下，以下帮助：

1. Save file as .xlsx(macro-free) - all macros would be erased while saving;
2. Open source file with macros and copy modules to the .xlsxfile;
3. Save file as .xlsm- full recompile performed.

1. 将文件另存为.xlsx（无宏） - 保存时将删除所有宏；
2. 用宏打开源文件并将模块复制到.xlsx文件中；
3. 将文件另存为.xlsm- 执行完全重新编译。

Afterwards everything started working normally. I had file with 200+ sheets and 50+ macros and posting comments in each module didn't help, but this solution worked.

之后一切都开始正常工作。我有 200 多张纸和 50 多个宏的文件，并且在每个模块中发布评论无济于事，但此解决方案有效。

回答by XaivierX

I've been struggling with this for awhile too. It actually occurred due to some Microsoft Office updates via Windows Update starting in December. It has caused quite a bit of a headache, not to mention hours of lost productivity due to this issue.

我也为此苦苦挣扎了一段时间。它实际上是由于从 12 月开始通过 Windows Update 进行了一些 Microsoft Office 更新。它引起了相当多的头痛，更不用说由于这个问题导致的生产力损失数小时了。

One of the updates breaks the forms, and you need to clear the Office cache as stated by UHsoccer

其中一项更新破坏了表单，您需要按照 UHsoccer 的说明清除 Office 缓存

Additionally, another answer thread here: Suddenly several VBA macro errors, mostly 32809has a link to the MS blog with details.

此外，这里的另一个答案线程： 突然出现几个 VBA 宏错误，主要是 32809有一个指向 MS 博客的链接，其中包含详细信息。

Another of the updates causes another error where if you create or modify one of these forms (even as simple as saving the form data) it will update the internals of the spreadsheet, which, when given to another person without the updates, will cause the error above.

另一个更新会导致另一个错误，如果您创建或修改这些表单中的一个（甚至像保存表单数据一样简单），它将更新电子表格的内部结构，当将其提供给没有更新的另一个人时，将导致上面的错误。

The solution (if you are working with others on the same spreadsheet)? Sadly, either have everyone you deal with use the office updates, then have them clear the office cache, or revert back to pre Dec '14 updates via a system restore (or by manually removing them).

解决方案（如果您在同一个电子表格上与其他人一起工作）？可悲的是，要么让与您打交道的每个人都使用办公室更新，然后让他们清除办公室缓存，要么通过系统还原（或手动删除它们）恢复到 14 年 12 月之前的更新。

I know, not much of a solution, right? I'm not happy either.

我知道，解决办法不多，对吧？我也不开心。

Just as a back-story, I updated my machine, keeping up with updates, and one of the companies I dealt with did not. I was pulling out my hair just before Christmas trying to figure out the issue, and without any restore points, I finally relented and reformatted.

作为一个背景故事，我更新了我的机器，跟上更新的步伐，而我与之打交道的一家公司却没有。就在圣诞节前夕，我正在拔头发试图找出问题所在，但没有任何恢复点，我终于松了口气并重新格式化。

Now, a month later, the company's IT department updated their workstations. And, without surprise, they began having issues similar to this as well (not to mention when I received their spreadsheets, I had the same issue).

现在，一个月后，公司的 IT 部门更新了他们的工作站。而且，不出所料，他们也开始遇到类似的问题（更不用说当我收到他们的电子表格时，我也遇到了同样的问题）。

Now, we are all up on the same updates, and everything is well as can be.

现在，我们都在进行相同的更新，一切都很好。

回答by Gordon Inglis

I suffered this problem while developing an application for a client. Working on my machine the code/forms etc worked perfectly but when loaded on to the client's system this error occurred at some point within my application.

我在为客户开发应用程序时遇到了这个问题。在我的机器上工作，代码/表单等工作得很好，但是当加载到客户端的系统时，这个错误发生在我的应用程序中的某个时刻。

My workaround for this error was to strip apart the workbook from the forms and code by removing the VBA modules and forms. After doing this my client copied the 'bare' workbook and the modules and forms. Importing the forms and code into the macro-enabled workbook enabled the application to work again.

我对此错误的解决方法是通过删除 VBA 模块和表单将工作簿与表单和代码分开。完成此操作后，我的客户复制了“裸”工作簿以及模块和表单。将表单和代码导入启用宏的工作簿，使应用程序能够再次工作。

回答by James Lin

This worked for me using excel 2010 and getting the same error when opening a macro-enabled .xlsmfile.

这对我使用 excel 2010 并在打开macro-enabled .xlsm文件时遇到相同的错误有用。

-after dismissing the error dialog, do "save as" tab-delimited .txtfile. click OKfor

- 关闭错误对话框后，执行“ save as”制表符分隔的.txt文件。点击OK查看

...only active sheet.

...functions not saved.

...只有活动表。

...未保存的功能。

-then "save as" again, but this time select macro-enabled .xlsmformat. (to another file or overwrite original doesn't matter, but save as another feels safer.)

-then " save as" 再次，但这次选择macro-enabled .xlsm格式。（到另一个文件或覆盖原始文件无关紧要，但另存为另一个感觉更安全。）

-close out excel.

-关闭excel。

-open the newly saved .xlsmfile. In my cases, the error messages went away and the macros are working.

- 打开新保存的.xlsm文件。在我的情况下，错误消息消失了，宏正在运行。

回答by kevinLooby

It seems that 32809 is a general error message. After struggling for some time, I found that I had not clicked on the "Enable Macros" security button at the below the workbook ribbon. Once I did this, everything worked fine.

似乎 32809 是一般错误消息。经过一段时间的挣扎，我发现我没有点击工作簿功能区下方的“启用宏”安全按钮。一旦我这样做了，一切都很好。

回答by Alexander

In my case, the error occurred executing a macro in: Sheets("own sheet one").Select

在我的情况下，错误发生在执行宏： Sheets("own sheet one").Select

copy the sheet into another with other name, ie. "oso", then delete the original sheet and renamed the new one as "own sheet one"

将工作表复制到另一个具有其他名称的工作表中，即。“oso”，然后删除原始工作表并将新工作表重命名为“自己的工作表一”

Excel 2013

Excel 2013

回答by Alexander

I have the same problem and found that this is the problem of Microsoft vulnerabilities.

我也有同样的问题，发现这是微软漏洞的问题。

It works for me when I install these update patches. You can find these patches on www.microsoft.com .

当我安装这些更新补丁时，它对我有用。您可以在 www.microsoft.com 上找到这些补丁。

1. If your office 2010 version is SP1, you need download and install office SP2 pack first. Update patch name is KB2687455.

2. Install update patch KB2965240.

   This security update resolves vulnerabilities in Microsoft Office that could allow remote code execution if an attacker convinces a user to open or preview a specially crafted Microsoft Excel workbook in an affected version of Office. An attacker who successfully exploited the vulnerabilities could gain the same user rights as the current user.

3. Install update patch KB2553154.

   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.

1. 如果您的office 2010版本是SP1，则需要先下载并安装office SP2包。更新补丁名称为 KB2687455。

2. 安装更新补丁 KB2965240。

   此安全更新解决了 Microsoft Office 中的漏洞，如果攻击者诱使用户在受影响的 Office 版本中打开或预览特制的 Microsoft Excel 工作簿，则这些漏洞可能允许远程执行代码。成功利用这些漏洞的攻击者可以获得与当前用户相同的用户权限。

3. 安装更新补丁 KB2553154。

   Microsoft Office 2010 32 位版中存在一个安全漏洞，当打开恶意修改的文件时，该漏洞可能允许运行任意代码。此更新解决了该漏洞。

回答by Nightwatch

I got this problem after adding a combobox with VBA-code in a particular sheet. Testing the code etc was no problem at all, until I opened the sheet again. Stackoverflow and Microsoft comes with many work arounds, but no real solution. I use excel 2010 (dutch version) with W10 (upgraded from W7). I think the problem is in Excel 2010. In my case, I got an error on the line to unprotect a sheet by VBA, in a module which wasn't changed for a long time.

在特定工作表中添加带有 VBA 代码的组合框后，我遇到了这个问题。测试代码等完全没有问题，直到我再次打开工作表。Stackoverflow 和 Microsoft 提供了许多变通方法，但没有真正的解决方案。我使用 excel 2010（荷兰版）和 W10（从 W7 升级）。我认为问题出在 Excel 2010 中。在我的情况下，我在一个长时间未更改的模块中通过 VBA 取消对工作表的保护时出现错误。

Ok, this is how it is in my opinion: There was a security issue in FM20.DLL, for whic MS had an update in Q1 2015. This update installs a new FM20.DLL, however the language packages (FM20NLD.DLL and FM20ENU.DLL) were not updated. Possibly, if you don't use a language pack, you don't have this error. In my opinion, the language parts should have been updated as well (but there is no update available)

好吧，这就是我的看法：FM20.DLL 中存在安全问题，因此 MS 在 2015 年第一季度进行了更新。此更新安装了新的 FM20.DLL，但是语言包（FM20NLD.DLL 和 FM20ENU） .DLL) 未更新。可能，如果您不使用语言包，则不会出现此错误。在我看来，语言部分也应该更新（但没有可用更新）

Ok, deleting the .exd-files works for a moment. This is a temporary work around. MS doesn't has a real solution, but recompiling the code 'solves' the problem.

好的，删除 .exd 文件暂时有效。这是一个临时的解决方法。MS 没有真正的解决方案，但重新编译代码“解决”了问题。

That is why some people said: 'Add a comment and the problem is solved'. Yes, adding a comment forces a recompilation.

这就是为什么有人说：“添加评论，问题就解决了”。是的，添加注释会强制重新编译。

I agree, this is still a work-around, but not a temporary work around. So: 1. check in which part of the VBA-code the error exists 2. add a comment by which a recompile is forced. 3. save the project again

我同意，这仍然是一种变通方法，但不是临时变通方法。所以： 1. 检查错误存在于 VBA 代码的哪一部分 2. 添加强制重新编译的注释。3.再次保存项目

that's all

就这样

回答by Kevin

I have similar problem. The VBA (ActiveX) code had been working fine on 20+ computers for a few years, the problem suddenly surfaced out when one new colleague joined, the code doesn't work on his new laptop although the Excel version is same, it showed Run-time Error '32809'. I have checked all security settings of ActiveX and Macro, all correct. I did some experiments. I found the Code created in my computer will not work on my colleague's new laptop. However if I create code in my colleague's new laptop, it works in my computer. Once I saved this code in my computer, it won't be able work in my colleague's laptop any more.

我有类似的问题。VBA (ActiveX) 代码在 20 多台计算机上运行良好几年了，当一位新同事加入时问题突然浮出水面，尽管 Excel 版本相同，但代码在他的新笔记本电脑上不起作用，它显示运行- 时间错误“32809”。我已经检查了 ActiveX 和宏的所有安全设置，都正确。我做了一些实验。我发现在我的电脑上创建的代码在我同事的新笔记本电脑上不起作用。但是，如果我在同事的新笔记本电脑上创建代码，它就可以在我的电脑上运行。一旦我将这段代码保存在我的电脑中，它就不能再在我同事的笔记本电脑上工作了。

By checking the error in details, the problem is that all code written in my computer, my colleague's Excel won't recognize them. Debug>>Compile will show compile error, even "DIM ..." is not recognized.All the ActiveX Controls , Comboboxs, Buttons... the property-name were randomly assigned a new one. For example, I have a button name as [AddNew], once open in my colleague's new laptop, it is re-assigned as [commandbutton54] . It even can not recognize the existing worksheet name.

通过详细检查错误，问题是我电脑上写的所有代码，我同事的Excel都无法识别它们。Debug>>Compile 将显示编译错误，即使“DIM ...”也无法识别。所有 ActiveX Controls 、Comboboxs、Buttons... 属性名称都随机分配了一个新的。例如，我有一个按钮名称为 [AddNew]，一旦在我同事的新笔记本电脑中打开，它就会被重新分配为 [commandbutton54]。它甚至无法识别现有的工作表名称。

After researched all the solutions , I found Delete "*.exd" file doesn't work(Even empty the recycle bin".). 'Add comments' doesn't work...

在研究了所有解决方案后，我发现删除“*.exd”文件不起作用（甚至清空回收站。）。“添加评论”不起作用......

In the end the solution is:

最后的解决办法是：

Step1: Make a copy of the sheets with codes(sheets w/o code is not required),

步骤1：复印带代码的工作表（不需要无代码的工作表），

Step2: Delete the original sheets,

Step2：删除原来的工作表，

Step3: Rename copied sheet to their original name,

步骤3：将复制的工作表重命名为其原始名称，

It starts to work. I found all ActiveX controls get back their original name in their property. It just took a minute. Hope it help those who are facing the same problem. Note: No need to save the file to *.txt or rename to xlsx...

它开始工作。我发现所有 ActiveX 控件都在它们的属性中恢复了它们的原始名称。只花了一分钟。希望它能帮助那些面临同样问题的人。注意：无需将文件保存为 *.txt 或重命名为 xlsx...

回答by Q.Reply

Error 32,809: Copying the corrupt sheet to a new sheet and changing the name or deleting the corrupt sheet works for me. Additionally, I went to the SHEET Module of the corrupt sheet and removed the coding from the sheet Module associated with the corrupt sheet. That ALSO cured the problem for me. [ The sheet modules can have routines that are triggered by events specific to that worksheet.] So in my case, I think it was a corrupt Sheet Module, not corrupt data on the worksheet itself.

错误 32,809：将损坏的工作表复制到新工作表并更改名称或删除损坏的工作表对我有用。此外，我转到了损坏工作表的 SHEET 模块，并从与损坏工作表关联的工作表模块中删除了编码。这也为我解决了这个问题。[工作表模块可以具有由特定于该工作表的事件触发的例程。] 所以就我而言，我认为这是一个损坏的工作表模块，而不是工作表本身上的损坏数据。