如何在 Excel 2007 中使用 VBA 重命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16046365/
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
How to rename range using VBA in excel 2007
提问by Kapil Sharma
I've 8+ yes professional programming experience in PHP and Java but none in VBA. Learning it right now.
我在 PHP 和 Java 方面有 8 年以上的专业编程经验，但在 VBA 方面没有。马上学起来。
I'm trying to make a Home budget sheet (Just for VBA learning purpose). For that, I'd done following
我正在尝试制作家庭预算表（仅用于 VBA 学习目的）。为此，我做了以下
* In new excel (2007) file, rename sheet 1 as 'Forms' And Sheet2 as 'CatAcc'
* In sheet 'CatAcc', I'm using (planning) column A for categories and B for Account
* Row 1 is heading (A1 = "Categories", B1="Account"
* Using forms sheet (cell C2) & VBA button, I want to add a new category,
sort it alphabetically and then rename range to add newly added row.
I wrote following code for that (Recorded testmacro to check how to name a range)
我为此编写了以下代码（记录测试宏以检查如何命名范围）
Sub AddCat_Click()
'Copy data as last row
Worksheets("CatAcc").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("Forms").Cells(2, "C").Value
'Find total Rows in categories
Dim totalRows
totalRows = Worksheets("CatAcc").Range("A2").End(xlDown).Row
'Define Range
Dim rng
rng = "A2:A" & totalRows
'MsgBox rng
'Select the range - Getting error in following line.
Worksheets("CatAcc").Range(rng).Select
'Name the range
ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:="=CatAcc!R2C1:R3C1"
'Sort range alphabetically
'Apply range as drop-down options
End Sub
Sub testmacro()
Range("A2:A3").Select
ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:= _
"=CatAcc!R2C1:R3C1"
End Sub
While selecting the range, I got following error
选择范围时，出现以下错误
Run-time error '1004'
Select method of Range class failed
I'm unable to understand what that error mean and why I'm getting that by just adding worksheet name.
我无法理解该错误的含义以及为什么仅通过添加工作表名称就可以得到该错误。
Again, What does following line mean? What is happening there? I could not understand meaning of R2C1:R3C2. This values came from recorded macro when I named A2:A3 cells as 'categories'
同样，下面这行是什么意思？那里发生了什么？我无法理解 R2C1:R3C2 的含义。当我将 A2:A3 单元格命名为“类别”时，此值来自录制的宏
ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:="=CatAcc!R2C1:R3C1"
采纳答案by Siddharth Rout
You do not need to select the range Range("A2:A3").Select. The "=CatAcc!R2C1:R3C1"in the next line is automatically taking care of it.
您不需要选择范围Range("A2:A3").Select。在"=CatAcc!R2C1:R3C1"下一行自动照顾它。
Explanation
解释
"=CatAcc!R2C1:R3C1"
"=CatAcc!R2C1:R3C1"
- CatAcc is the Sheet where the range is
- R2C1 means Row 2 Col 1 which is nothing but A2
- Similarly R3C1 is Row 3 Col 1 which is nothing but A3
- CatAcc 是范围所在的工作表
- R2C1 表示第 2 列第 1 行，它只是 A2
- 同样 R3C1 是第 3 列第 1 行，它只是 A3
So the above can also be written as
所以上面的也可以写成
ActiveWorkbook.Names.Add NAME:="categories", RefersTo:="=CatAcc!$A:$A"
