Excel 2010 VBA 公式1 使用标签名称变量引用命名范围*或*单元格位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14511601/
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
Excel 2010 VBA Formula1 Referencing a Named Range *or* cell location with variable for tab name
提问by hudsonsedge
In VBA (Excel 2010), I am
在 VBA (Excel 2010) 中，我是
- dynamically creating a named range list
- using that list to create drop down choices in another column
- 动态创建命名范围列表
- 使用该列表在另一列中创建下拉选项
When creating the drop down list, (a) using the named range doesn't seem to work, and (b) if I don't use the named range - and need to reference by sheet name and cell reference, I get into trouble because my sheet has just been renamed with today's date.
创建下拉列表时，(a) 使用命名范围似乎不起作用，并且 (b) 如果我不使用命名范围 - 并且需要通过工作表名称和单元格引用进行引用，我会遇到麻烦因为我的工作表刚刚用今天的日期重命名。
This is messy, I know, but here is what I have so far:
这很混乱，我知道，但这是我目前所拥有的：
' find the name of the worksheet and replace it with today's date
Dim vTabOriginalName As String
Dim vTabDateName As String
Dim vRangeName As String
vRangeName = "StageListChoices"
vTabOriginalName = ActiveSheet.Name
vTabDateName = Format(Now(), "yyyy-mmm-dd")
ActiveSheet.Name = vTabDateName
'create a drop down list for the stage (col K)
Range("AK3").Value = "NO ACTIVITY"
Range("AK4").Value = "SOLICITATION"
Range("AK5").Value = "OPPORTUNITY"
ActiveWorkbook.Names.Add Name:="StageListChoices", RefersToR1C1:=(vTabDateName & "!R3C37:R5C37")
'~~> Creates the list
With Range("K2:K" & vReportRowCount).Validation 'report row count known earlier
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=StageListChoices"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
The recorded macro when I created the named region made sense enough:
创建命名区域时录制的宏很有意义：
ActiveWorkbook.Names.Add Name:="StageListChoices", RefersToR1C1:= _
"=2013-JAN-24!R3C37:R14C37"
ActiveWorkbook.Names("StageListChoices").Comment = ""
Originally, I had been creating the drop down in VBA with a String variable, but the "real" list is 15 items long and I was getting errors upon reopening the file that the validation had been too long (?) and so had been turned off.
最初，我一直在使用 String 变量在 VBA 中创建下拉列表，但是“真实”列表有 15 个项目长，并且在重新打开验证过长（？）的文件时出现错误，因此已被打开离开。
Basically, I've tried things like:
基本上，我试过这样的事情：
Formula1:="=StageListChoices"
Formula1:=vRangeName
Formula1:="=vRangeName"
Formula1:=vTabDateName & "!R3C37:R5C37"
Everything I've looked up says that the first one (Formula1:="=StageListChoices") should have worked - but it doesn't.
我查过的所有内容都表明第一个 (Formula1:="=StageListChoices") 应该有效 - 但它没有。
Thank you!
谢谢！
回答by Siddharth Rout
Change
改变
ActiveWorkbook.Names.Add Name:="StageListChoices", RefersToR1C1:=(vTabDateName & "!R3C37:R5C37")
ActiveWorkbook.Names.Add Name:="StageListChoices", RefersToR1C1:=(vTabDateName & "!R3C37:R5C37")
to
到
ActiveWorkbook.Names.Add Name:="StageListChoices", RefersToR1C1:=("='" & vTabDateName & "'!R3C37:R5C37")
ActiveWorkbook.Names.Add Name:="StageListChoices", RefersToR1C1:=("='" & vTabDateName & "'!R3C37:R5C37")
You were missing the =and the '
你错过了=和'
