Excel VBA项目已经生成了多个Workbook对象 [英] Excel VBA Project has generated multiple Workbook objects

问题描述

我负责一个非常大的Excel 2010电子表格，其中包含各种外部数据源（包括彭博社），65个工作表与vba模块以及对其他vba加载项的引用。

$ b $我注意到VBA项目已经获得了多个Workbook对象。

有标准的ThisWorkbook。但是，许多工作表也被Excel转换成Workbook对象，将原始工作表作为前一个工作表的副本减去代码。

这不似乎是任何人的行为的结果。实际上，我并没有想到可以有多个Workbook对象！

例如，我有一个名为wksInputs的工作表，现在已经变成了一个Workbook对象，而原来的wksInputs现在被称为wksInputs1。

我无法删除wksInputs Workbook对象。

有人可以帮忙解释发生了什么在这里，我可以如何解决问题？

非常感谢。

解决方案

当我将工作表传递给Sub作为参数时，我的代码中出现了此问题，如下所示：

调用BuildCodeStrings（Sheet2，sAccount）

Sub BuildCodeStrings（wsSource As Worksheet，as As String）

为了解决这个问题，我创建了一个新的工作簿，将我原来的所有合法工作表的所有数据复制到id中在我的新工作簿中有名的工作表。然后将所有代码从原始文件复制到新的工作簿。

然后我将子程序调用更改为

调用BuildCodeStrings（IC帐户，sAccounts）

Sub BuildCodeStrings（sSource As String，s As String）

并添加了一行代码到我的子例程BuildCodeString：

设置wsSource = ThisWorkbook.Sheets（sSource）

我不知道是什么原因导致这个问题，但这种解决方法对我有用。

I am responsible for a very large Excel 2010 spreadsheet with links to all sorts of external data sources including Bloomberg, 65 worksheets with vba modules and references to other vba add-ins.

I have noticed that the VBA project has acquired multiple Workbook Objects.

There's the standard ThisWorkbook. However, a number of the worksheets have also been turned into Workbook objects by Excel, leaving the original worksheet as a copy of the previous one, minus the code.

This doesn't appear to be a result of anyone's actions. Indeed, I didn't think it possible to have more than one Workbook object!

For example, I had one worksheet called wksInputs which has now been turned into a Workbook object, and the original wksInputs is now called wksInputs1.

I can't delete the wksInputs Workbook object.

Please could someone help explain what's going on here, and how I might be able to resolve the problem...?

Many thanks.

解决方案

This problem occurred in my code when I passed a worksheet to a Sub as a parameter, like this:

Call BuildCodeStrings(Sheet2, sAccount)
Sub BuildCodeStrings(wsSource As Worksheet, s As String)

To fix the problem, I created a new workbook, copied all the data from all the legitimate sheets in my original into identically named sheets in my new workbook. Then copied all the code from the original to the new workbook.

Then I changed the subroutine call to

Call BuildCodeStrings("IC Accounts", sAccounts)
Sub BuildCodeStrings(sSource As String, s As String)

and added one line of code to my subroutine BuildCodeString:

Set wsSource = ThisWorkbook.Sheets(sSource)

I don't know what causes this problem, but this workaround worked for me.

这篇关于Excel VBA项目已经生成了多个Workbook对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！