表根据范围会影响VBA范围变量 [英] Tables interfere with VBA range variables depending on scope

问题描述

Excel文件包括部署在表（VBA listobjects）中的VBA编码的用户定义函数（UDF）。现在，为了逃避我的原因，如果UDF模块包含声明在任何子或函数范围之外的变量，那么当打开文件时，我会发出非常显着的警告：自动错误 - - 灾难性故障。

An Excel file includes VBA-coded user-defined functions (UDFs) that are deployed in tables (VBA listobjects). Now, for reasons that escape me, if the UDF module contains Range variables that are declared outside the scope of any sub or function, I get a very dramatic warning when the file is opened: "Automatic error -- Catastrophic failure".

灾难性似乎是夸张的，因为在警告被关闭后，该文件似乎正常工作。但是我仍然希望了解问题是什么。我已经设法用MVC的例子复制了这个问题，如下。我在Windows 10上运行Excel 2016（更新）。

"Catastrophic" seems like an exaggeration because after the warning is dismissed, the file seems to work correctly. But I would still like to understand what the issue is. I have managed to replicate the issue with an MVC example as follows. I'm running Excel 2016 (updated) on Windows 10.

有两个表（即VBA listobjects）：表1 列出项目和表2 列出项目功能（通过选择数据并在插入上单击表生成两个表标签）。表2在字段 Item_Name 中的名为 ITEM_NAME（）的UDF作为项目的函数返回项目名称ID，请参阅截图：

There are two tables (i.e. VBA listobjects): Table 1 lists "items" and Table 2 lists "item features" (both tables were generated by selecting the data and clicking Table on the Insert tab). Table 2 has a UDF called ITEM_NAME() in the field Item_Name that returns the item name as a function of the item ID, see the screenshot:

< img src =https://i.stack.imgur.com/nZTiL.pngalt =在此输入图像说明>

功能 ITEM_NAME（）本质上是常规工作表函数INDEX和MATCH的包装，如下面的代码：

The function ITEM_NAME() is essentially a wrapper around the regular worksheet functions INDEX and MATCH, as in the following code:

Option Explicit

Dim mrngItemNumber As Range
Dim mrngItemName As Range

Public Function ITEM_NAME(varItemNumber As Variant) As String
' Returns Item Name as a function of Item Number.
    Set mrngItemNumber = Sheets(1).Range("A4:A6")
    Set mrngItemName = Sheets(1).Range("B4:B6")
    ITEM_NAME = Application.WorksheetFunction.Index(mrngItemName, _
    Application.WorksheetFunction.Match(varItemNumber, mrngItemNumber))
End Function

所以，要重复一下，使用此设置，当打开文件时，我会收到自动化错误 。但是当我执行以下任何操作时，错误消失：

So, to repeat, with this setup I get the Automation error when the file is opened. But the error disappears when I do any of the following:

1. 将声明移动到函数的范围内。这个解决方案没有吸引力，因为它需要更多的代码行，每个UDF都有一个代码，并且有很多。

1. Move the declarations into the scope of the function. This solution is not attractive since it requires many more lines of code, one for each UDF, and there are many.

从范围到别的东西，例如整数（所以函数显然不起作用）。

Change the variable type from Range to something else, for example Integer (so the function will obviously not work).

转换表2到一个普通的范围（即删除表）。这也是一个不方便的解决方案，因为我真的想在我的代码中使用表格功能用于其他目的。

Convert Table 2 to an ordinary range (i.e. remove the table). This is also an inconvenient solution since I really want to use the Table features for other purposes in my code.

删除函数 ITEM_NAME （）从表2.（显然没有吸引力的选项..）

Remove the function ITEM_NAME() from Table 2. (Obviously no attractive option..)

上？为什么我收到错误讯息？为什么尽管有警告，该文件似乎仍然正常工作？有没有我错过的解决方法？

What's going on? Why do I get the error message? And why does the file still seem to work properly despite the warning? Is there a workaround that I've missed?

我怀疑它可能与sheet对象和listobjects如何交互有关，但不能确定。 此答案可能提供了另一个问题：

I suspect it might have something to do with how sheet objects and listobjects interact, but not sure. A possible hint is provided in this answer to another question:

如果您不想使用表格引用表，可以使用
a hack Application.Range（ListObjectName）.ListObject 。

注意：这个黑客依赖于Excel总是为
表的DataBodyRange创建与该表名称相同的命名范围。

NOTE: This hack relies on the fact that Excel always creates a named range for the table's DataBodyRange with the same name as the table.

其他地方已经报告了类似的问题（在 Stackoverflow 和 Microsoft Technet ），但不具有这种特殊的味道。建议的解决方案包括检查破坏的引用或其他在后台运行的进程，我已经做到这一点没有效果。我还可以补充说，在创建表2之后输入函数 ITEM_NAME 是没有区别的;唯一的区别是它使用结构化参考（如上面的截图）。

Similar problems have been reported elsewhere (at Stackoverflow and Microsoft Technet), but not with this particular flavor. Suggested solutions include checking for broken references or other processes running in the background, and I've done that to no avail. I can also add that it makes no difference whether the function ITEM_NAME is entered after Table 2 is created rather than before; the only difference is that it uses structured references in that case (as in the screenshot above).

更新：启发于@ SJR的下面的评论我尝试了代码的以下变体，其中声明了一个 ListObject 变量来存储表Items。请注意，范围声明现在在函数的范围之内，并且只有 ListObject 声明在外面。这个也产生相同的自动化错误！

UPDATE: Inspired by @SJR's comments below I tried the following variation of the code, where a ListObject variable is declared to store the table "Items". Note that the Range declarations are now inside the scope of the function, and that only the ListObject declaration is outside. This also generates the same Automation error!

Option Explicit

Dim mloItems As ListObject

Public Function ITEM_NAME(varItemNumber As Variant) As String
' Returns Item Name as a function of Item Number.
    Dim rngItemNumber As Range
    Dim rngItemName As Range
    Set mloItems = Sheet1.ListObjects("Items")
    Set rngItemNumber = mloItems.ListColumns(1).DataBodyRange
    Set rngItemName = mloItems.ListColumns(2).DataBodyRange
    ITEM_NAME = Application.WorksheetFunction.Index(rngItemName, _
    Application.WorksheetFunction.Match(varItemNumber, rngItemNumber))
End Function

更新2：现在的问题似乎已经解决了，但是我并不多更明智的是什么造成了它。由于没有人可以复制（甚至没有我的朋友在不同的系统上打开同一个文件），我开始认为这是一个本地问题。我尝试修复Excel，然后甚至从头重新安装完整的Office包。但是问题依然存在，我的MCV文件用于创建上面的例子和我发现问题的原始文件。

UPDATE 2: The problem now seems to be solved, but I'm not much wiser as to what actually caused it. Since no one could replicate (not even friends of mine who opened the same file on different systems), I began to think that it was a local issue. I tried repairing Excel and then even reinstalled the complete Office package from scratch. But the issue still persisted, both with my MCV files used to create the example above and the original file where I discovered the problem.

我决定尝试创建一个新版本的MCV示例，灵感来自于AndrewD的下面的答案，我使用 .ListObjects（）设置范围，而不是使用 .Range（）。这确实有效。我可能会为我的工作适应这个解决方案（但是看到我在AndrewD的问题解释为什么我可能更喜欢 .Range（）。）

I decided to try to create a new version of the MCV example where, inspired by AndrewD's answer below, I used .ListObjects() to set the range instead of using .Range(). This did indeed work. I will probably adapt that solution for my work (but see my comments under AndrewD's question explaining why I might prefer .Range().)

为了仔细检查这个解决方案是否正常工作，我设置了两个新的文件，一个复制我自己的例子，如上所述，唯一的区别是切换到 ListObjects（）。在这个过程中，我注意到我在原始文件中的代码开头实际上缩进了 Range 声明，例如：

In order to double check that this solution worked, I set about to create two new files, one to replicate my own example as described above, and one where the only difference would be the switch to ListObjects(). In the process, I noted that I had actually indented the Range declarations at the beginning of the code in my original file, like so:

Option Explicit

    Dim mrngItemNumber As Range
    Dim mrngItemName As Range

Public Function ITEM_NAME(...

没有考虑到这一点，我创建了新的文件，但没有缩进，所以这将是上一个文件的精确副本（和上面给出的示例），但是没有缩进，但是看到这个文件我无法复制自动化错误！检查这两个文件后，我注意到唯一的区别确实是缩进，所以我把这个压缩文件再次放在新的文件中，期望它再次生成自动化错误，但是问题没有重新出现，所以我然后从第一个文件中删除了缩进（用于创建上面的例子） ，现在自动化错误也从该文件中消失med这个观察，我回到了我的真实文件，我第一次发现这个问题，并在那里也删除了缩进。它工作。

Without thinking much about this, I created the new file but without indentation. So that would be an exact copy of the previous file (and the given example above), but without indentation. But behold, with this file I could not replicate the Automation error! After inspecting both files I noted that the only difference was indeed indentation, so I put the indentation back again in the new file expecting it to generate the Automation error again. But the problem did not reappear. So then I then removed the indentation from the first file (used to create the example above), and now the Automation error disappeared from that file as well. Armed with this observation, I went back to my real file where I first discovered the issue and simply removed the indentation there too. And it worked.

所以总结一下，删除 Range 声明的缩进后，我无法重新创建自动化错误之前生成的三个文件。而且，即使我把压痕重新放在原地，这个问题也不会重现。但是我仍然不明白为什么。

So to summarize, after removing the indentation of the Range declarations I fail to recreate the Automation error in any of the three files that had generated it before. And moreover, the problem does not reappear even if I put the indentation back in place again. But I still don't understand why.

感谢所有有时间看这个并分享宝贵意见的人。

Thanks everyone who took time to look at this and shared valuable ideas.

推荐答案

声明模块级变量只是为了保存每个UDF中否则需要的两行确实是不好的编码习惯。但是，如果这是您的想法，为避免 设置 ，那么为什么不按照每个UDF保存 四个 >他们在每个以及

Declaring module-level variables simply to save the two lines in each UDF that would otherwise be required is indeed bad coding practice. However, if that is your thinking, why not go all the way and save four lines per UDF by avoiding setting them in each as well!

你可以通过使用伪常量函数，如下面的代码所示：

You can do this by using pseudo-constant functions as seen in the following code:

Option Explicit

Private Function rng_ItemNumber() As Range
    Set rng_ItemNumber = Sheet1.Range("A4:A6")
End Function
Private Function rng_ItemName() As Range
    Set rng_ItemName = Sheet1.Range("B4:B6")
End Function

Public Function ITEM_NAME(varItemNumber As Variant) As String
' Returns Item Name as a function of Item Number.
  With Application.WorksheetFunction
    ITEM_NAME = .Index(rng_ItemName, .Match(varItemNumber, rng_ItemNumber))
  End With
End Function

当然，费用是函数调用的开销。

The cost, of course, is the overhead of a function call.

如果您计划使用 ListObject 类进行最终设计，那么为什么现在不使用它， 还有 使用动态命名范围（示例中的硬编码范围在那里，因此它实际上可以正常工作 - 这些应该用命名范围替换）：

If you are planning on using the ListObject class for the final design, then why not use it now, and also use dynamic named ranges (the hard-coded ranges in the example are there so it actually works as is - these should be replaced with the named ranges):

Option Explicit

Private Function str_Table1() As String
    Static sstrTable1 As String
    If sstrTable1 = vbNullString Then
      sstrTable1 = Sheet1.Range("A4:B6").ListObject.Name
    End If
    str_Table1 = sstrTable1
End Function
Private Function str_ItemNumber() As String
    Static sstrItemNumber As String
    If sstrItemNumber = vbNullString Then
      sstrItemNumber = Sheet1.Range("A4:A6").Offset(-1).Resize(1).Value2
    End If
    str_ItemNumber = sstrItemNumber
End Function
Private Function str_ItemName() As String
    Static sstrItemName As String
    If sstrItemName = vbNullString Then
      sstrItemName = Sheet1.Range("B4:B6").Offset(-1).Resize(1).Value2
    End If
    str_ItemName = sstrItemName
End Function

Public Function ITEM_NAME(varItemNumber As Variant) As String
  'Returns Item Name as a function of Item Number.
  Dim ƒ As WorksheetFunction: Set ƒ = WorksheetFunction
  With Sheet1.ListObjects(str_Table1)
    ITEM_NAME _
    = ƒ.Index _
      ( _
        .ListColumns(str_ItemName).DataBodyRange _
      , ƒ.Match(varItemNumber, .ListColumns(str_ItemNumber).DataBodyRange) _
      )
  End With
End Function

一旦逻辑/设计准备就绪，如果速度至关重要，您可以用相同名称的模块级常量替换这些函数，您需要回收函数调用开销。否则，您可以将所有内容都保留。

Once the logic/design is ready, you can replace the functions with module-level constants of the same name if speed is critical and you need to reclaim the function call overhead. Otherwise, you can just leave everything as is.

请注意，静态变量的使用不是必需的，但应减少执行时间。 （静态变量也可以在第一个例子中使用，但是我把它们保留下来）。

Note that the use of static variables is not required, but should reduce execution time. (Static variables could also have been used in the first example as well, but I left them out to keep it short.)

可能并非真的需要提取出表名变成伪常数，但是为了完整起见我已经这样做了。

It's probably not really necessary to extract out the table names into pseudo-constants, but I have done so for completeness sake.

编辑：（v2）

跟进Egalth的两个辉煌建议，导致遵循代码，从而避免了命名范围的需要， 甚至是hard-编码单元格地址 ，一样我们利用了ListObject表本身的内建动态。

Following up on Egalth's two brilliant suggestions, leads to the follow code which obviates the need for named ranges, or even hard-coded cell addresses, altogether as we leverage the builtin dynamism of the ListObject table itself.

我还将参数名称更改为匹配*相关列标题名称，因此当用户按 Ctrl + Shift + A 提示将显示要使用的列。 （如果需要，此提示以及有关如何添加Intellisense工具提示和/或获取描述以显示在功能参数对话框中的更多信息可以看到 here 。）

I have also changed the parameter name to match* the relevant column header name so when the user presses Ctrl+Shift+A a hint as to which column to use appears. (This tip and, if required, more info on how to add Intellisense tool-tips and/or get a description to appear in the Function Arguments dialog can be seen here.)

Option Explicit

Private Function str_Table1() As String
    Static sstrTable1 As String
    If sstrTable1 = vbNullString Then sstrTable1 = Sheet1.ListObjects(1).Name ' or .ListObjects("Table1").Name
    str_Table1 = sstrTable1
End Function
Private Function str_ItemNumber() As String
    Static sstrItemNumber As String
    If sstrItemNumber = vbNullString Then
      sstrItemNumber = Sheet1.ListObjects(str_Table1).HeaderRowRange(1).Value2
    End If
    str_ItemNumber = sstrItemNumber
End Function
Private Function str_ItemName() As String
    Static sstrItemName As String
    If sstrItemName = vbNullString Then
      sstrItemName = Sheet1.ListObjects(str_Table1).HeaderRowRange(2).Value2
    End If
    str_ItemName = sstrItemName
End Function

Public Function ITEM_NAME(ByRef Item_ID As Variant) As String
  'Returns Item Name as a function of Item Number.
  Dim ƒ As WorksheetFunction: Set ƒ = WorksheetFunction
  With Sheet1.ListObjects(str_Table1)
    ITEM_NAME _
    = ƒ.Index _
      ( _
        .ListColumns(str_ItemName).DataBodyRange _
      , ƒ.Match(Item_ID, .ListColumns(str_ItemNumber).DataBodyRange) _
      )
  End With
End Function

请注意使用 .Value2 。自从我发现使用的隐式类型转换导致的性能拖动和其他问题时，我一直使用 .Value2 .Value （或当依赖它作为默认属性时）。

Note the usage of .Value2. I have always used .Value2 ever since I found out about the performance drag and other issues caused by the implicit type conversion done when using .Value (or when relying on it as the default property).

_{*确保在逻辑上更新代码中的列标题名称/ /项目设计完成。}

_{* Make sure to update the column header names in the code when the logic/design of the project is finished.}

编辑：启动）

将您自己的评论重新发送到已发布的问题，我注意到这一个：

Re-reading your own comments to your posted Question, I noted this one:

我可能最终会采用这种方法，但是我仍然在设计过程中，并且移动列很多，所以索引号也可能会改变

I might adopt that approach eventually, but I'm still in the design process and moving columns around a lot so the index number might also change

尽管上面的最后一个例子允许头名称被动态地改变，移动/插入列更改索引，要求修改代码。

Whilst the last example above allows the header names to be changed dynamically, moving/inserting columns changes the indexes, requiring the code to be modified.

看起来我们回到使用命名范围。但是，这次我们只需要静态的指向头的列。

Looks like we're back to using named ranges. However, this time we only need static ones pointing to the column headers.

同样的结果是，对于这种新的情况，静态变量在设计阶段是一个不好的想法。由于列索引被缓存，所以插入一个新的列可以打破UDF直到项目被重置。

It also turns out that, for this new case, static variables are a bad idea in the design stage. Since the column indexes are cached, inserting a new column breaks the UDF until the project is reset.

我还加入了一个缩写版的无表单参考hack从你发布的报价问题：

I have also incorporated a shortened version of the sheet-less table reference hack from the quote in your posted Question:

Option Explicit

Private Function str_Table1() As String
    str_Table1 = Sheet1.ListObjects(1).Name
End Function
Private Function str_ItemNumber() As String
    With Range(str_Table1).ListObject
      str_ItemNumber = .HeaderRowRange(.Parent.Range("A3").Column - .HeaderRowRange.Column + 1).Value2
    End With
End Function
Private Function str_ItemName() As String
    With Range(str_Table1).ListObject
      str_ItemName = .HeaderRowRange(.Parent.Range("B3").Column - .HeaderRowRange.Column + 1).Value2
    End With
End Function

Public Function ITEM_NAME(ByRef Item_ID As Variant) As String
  'Returns Item Name as a function of Item Number.
  Dim ƒ As WorksheetFunction: Set ƒ = WorksheetFunction
  With Range(str_Table1).ListObject
    ITEM_NAME _
    = ƒ.Index _
      ( _
        .ListColumns(str_ItemName).DataBodyRange _
      , ƒ.Match(Item_ID, .ListColumns(str_ItemNumber).DataBodyRange) _
      )
  End With
End Function

请注意，您不能对其中一个命名范围使用 Item_name 因为它与UDF相同（不被忽略）。我建议为您的命名范围使用尾部下划线，例如 Item_name _ 。

Note that you can't use Item_name for one of the named ranges as it is the same as the UDF (case is ignored). I suggest using a trailing underscore, eg, Item_name_, for your named ranges.

所有上述方法也将解决您原来的问题。我正在等待最后的信息，以便作出有根据的猜测，为什么这个问题首先发生。

All the above methods would also have solved the original issue that you had. I'm awaiting the last pieces of info in order to make an educated guess as to why this issue was occurring in the first place.

这篇关于表根据范围会影响VBA范围变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！