在 VBA 中设置范围并定义命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28489982/
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
Set range and define named range in VBA
提问by Jabberwocky
I am trying to define a named range (with Workbook scope) in VBA based on certain inputs to help figure out the cells that should be included in the range. To make it really simple, I have tried the following two with the errors indicated below. A, B, X, Y are found from some calculations
我试图根据某些输入在 VBA 中定义一个命名范围（带有工作簿范围），以帮助确定应该包含在该范围内的单元格。为了使它变得非常简单，我尝试了以下两个错误，并指出了以下错误。A, B, X, Y 是从一些计算中找到的
rName = <some string that decides the name>
Set TempRng = .Range(.Cells(A,B), .Cells(X,Y))
ActiveWorkbook.Names.Add Name:=rName, RefersTo:=Worksheets("Sheet1").TempRng
This gives me an "object or method not supported" error or something like that, on the Set TempRng line.
这在 Set TempRng 行上给了我一个“不支持的对象或方法”错误或类似的错误。
rName = <string ...>
Set TempRng = Worksheets("Sheet1").Range(Cells(A,B), Cells(X,Y))
ActiveWorkbook.Names.Add Name:=rName, RefersTo:= <blah blah...>
This gives me an "application defined or object defined error" on the same line.
这给了我同一行上的“应用程序定义或对象定义错误”。
Can you please help me find the correct way to define this range?
你能帮我找到定义这个范围的正确方法吗？
回答by L42
I think this is a bit more direct.
我觉得这个更直接一些。
TempRng.Name = rname
Or you can even directly assign the name without using variable.
或者您甚至可以不使用变量直接分配名称。
TempRng.Name = "myrangename"
The scope of the name is Workbookjust in case that is critical in your needs. HTH.
名称的范围Workbook只是以防万一，这对您的需求至关重要。哈。
回答by Simon
Use:
用：
Refersto:="=" & TempRng.Address
Or more directly (as L42 says)
或者更直接（如 L42 所说）
TempRng.Name = stName
To set your range make sure you've included "With" before you start "."ing things
要设置范围，请确保在开始“.”之前已包含“With”
With wsWhatever
Set TempRng = .Range(.Cells(A,B), .Cells(X,Y))
end with
