VBA：如何以编程方式创建指向以编程方式创建的工作表的命名范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22606486/
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
VBA: How to programmaticaly create named range pointing to programmatically created sheet?
提问by przemo_li
It seems this question also contains proper answer, Excel for some unfathomable reason won't execute it without error. So the question has changed a bit:
似乎这个问题也包含正确的答案，Excel 出于某种深不可测的原因不会毫无错误地执行它。所以问题有点变了：
Why 1004?
为什么是1004？
Basically I want to use something like (This give me 1004):
基本上我想使用类似的东西（这给了我 1004）：
Dim rngTmp As Range
For Each offer In SanitizedConstInfo("offers").keys()
Set rngTmp = Sheets(offer).Range(Cells(1, 1), Cells(2, 2))
ActiveWorkbook.Names.add name:=offer, RefersToR1C1:=rngTmp
ActiveWorkbook.Names(offer).RefersToRange.Cells(1, 1) = offer
Next offer
offer is string containing some name (Yeah, I want to have both sheet and named range with same name - for now at least).
Will have unknown number of those, so I just loop for each.
offer 是包含一些名称的字符串（是的，我希望工作表和命名范围都具有相同的名称 - 至少现在是这样）。将有未知数量的那些，所以我只是循环for each。
Q: How to add sheet information to RefersToR1C1, so that named range refers to certain sheet? (I know about 'Sheetname'!A1:A10 Syntax but want to do that with Sheet/Range/Cell objects if possible)
问：如何向 中添加工作表信息RefersToR1C1，使命名范围指向某个工作表？（我知道 'Sheetname'！A1:A10 语法，但如果可能的话，我想用 Sheet/Range/Cell 对象来做到这一点）
回答by SWa
It's because you aren't fully qualifying your ranges, you need to be explicit:
这是因为你没有完全限定你的范围，你需要明确：
With Sheets(offer)
Set rngTmp = .Range(.Cells(1, 1), .Cells(2, 2))
End With
Dots before Cells are important.
Cells 之前的点很重要。
