在 VBA 中引用动态命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11868739/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Referring to Dynamic Named Ranges in VBA
提问by Michael
I'm having troubling referring to a Dynamic Name Range in VBA.
My ranges are defined as
我在引用 VBA 中的动态名称范围时遇到了麻烦。
我的范围被定义为
=OFFSET(Sheet!$B,0,0,COUNTA(Sheet!$B:$B)-1,1)
My code should search one range for all entries in another range, the intention being that any missing entries will be added. So far I have
我的代码应该在一个范围内搜索另一个范围内的所有条目，目的是添加任何缺失的条目。到目前为止我有
Sub UpdateSummary()
Dim Cell As Range
Dim rngF As Range
Set rngF = Nothing
' Step through each cell in data range
For Each Cell In Worksheets("Aspect").Range("A_Date")
' search Summary range for current cell value
Set rngF = Worksheets("Summary").Range("Sum_Date").Find(Cell.Value) // Does not work
If rngF Is Nothing Then
' Add date to Summary
End If
Set rngF = Nothing
Next Cell
End Sub
The For loop seems to work ok. However, using the .Find method is giving me an error message.
For 循环似乎工作正常。但是，使用 .Find 方法会给我一条错误消息。
Application-defined or object-defined error
It does work if I replace the named range with a specific range ($B$2:$B$5000), so it seems to be down to how the named range is being passed.
Any ideas would be appreciated.
Thanks.
如果我用特定范围 ($B$2:$B$5000) 替换命名范围，它确实有效，所以这似乎取决于命名范围的传递方式。
任何想法，将不胜感激。
谢谢。
采纳答案by Dick Kusleika
The error is almost definitely because Excel can't find a named range Sum_Date that refers to a range on a worksheet named Summary. The most common causes are
该错误几乎肯定是因为 Excel 找不到引用名为摘要的工作表上的区域的命名区域 Sum_Date。最常见的原因是
- Sum_Date refers to a sheet other than Summary. Check the RefersTo property of Sum_Date and make sure nothing is misspelled.
- There is not a named range Sum_Date, that is, it's misspelled in the VBA code. Check the spelling of the named range in the Name Manager.
- There is an error in the RefersTo formula of Sum_Date. It sounds like you already verified that this isn't the case.
- Sum_Date 指的是除摘要之外的工作表。检查 Sum_Date 的 RefersTo 属性并确保没有拼写错误。
- 没有命名范围 Sum_Date，也就是说，它在 VBA 代码中拼写错误。检查名称管理器中命名范围的拼写。
- Sum_Date 的 RefersTo 公式中存在错误。听起来您已经证实情况并非如此。
回答by Joe Hilby
I've had the a similar if not the same problem & here's how I solved it:
我遇到了类似的问题，如果不是同样的问题，我是这样解决的：
I first realized that the method I used to create my named range, using the Name Manager, my named range had a scope of Workbook. This is important because, it doesn't belong to the worksheet, & therefore will not be found there.
我首先意识到我用来创建命名范围的方法，使用名称管理器，我的命名范围具有工作簿的范围。这很重要，因为它不属于工作表，因此不会在那里找到。
So, Worksheets("Summary").Range("Sum_Date") would not work for me.
因此， Worksheets("Summary").Range("Sum_Date") 对我不起作用。
Since my range belonged to the workbook, the way I was able to find is to use ActiveWorkbook.Names("Sum_Date")
由于我的范围属于工作簿，我能够找到的方法是使用 ActiveWorkbook.Names("Sum_Date")
For me I used it to remove the formula from named range that I am using in many places. The huge advantage is that named range is updated only once instead of the formula being called for every cell location that ranged is called. Huge time delay difference!
对我来说，我用它从我在很多地方使用的命名范围中删除了公式。巨大的优势是命名范围仅更新一次，而不是为调用范围的每个单元格位置调用公式。巨大的时延差异！
Public last_Selection As String
Private Sub Worksheet_Change(ByVal Target As Range)
'excel data change detection
If Range(last_Selection).Column = 2 Then
'Disable events, so this only executes once
Application.EnableEvents = False
'This can be done with a complex formula in a cell,
'but this is easily understood
Range("B1").End(xlDown).Select
ActiveWorkbook.Names("last_Entry").Value = ActiveCell.Row
'Re-enable so this routine will execute on the next change
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'constantly store the last cell to know which one was previously edited
last_Selection = Target.Address
End Sub
回答by user3235601
I have been experimenting with this for a few days and eventually I came up with the following. It may not be the most efficient but it did work for me!
我已经尝试了几天，最终我想出了以下内容。它可能不是最有效的，但它确实对我有用！
The named range of "OhDear" was set up in the normal way
Dim vItem As Variant
Set vItem = Names("OhDear")
Debug.Print vItem.Name
Worth a try don't you think! This does not work if instead of using a variant you use something like: Dim Nm as Name: Set Nm = Names("OhDear"). Any variations using 'Nm' failed!!!
值得一试，你不觉得！如果您使用以下内容而不是使用变体，这将不起作用：Dim Nm as Name: Set Nm = Names("OhDear")。使用“Nm”的任何变体都失败了！！！
