用于引用单元格而不是单元格值的 VBA 语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17480670/
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 Syntax for Reference of a cell instead of cell value
提问by Reccax
I want the VBA syntax for the reference of a cell instead of the value.
我想要用于引用单元格而不是值的 VBA 语法。
I know that
我知道
Workbook().Worksheet().Cell().Valuereturns the value of the cell.
Workbook().Worksheet().Cell().Value返回单元格的值。
I want to know the cell reference, something like this ='[Vba Source Test.xlsx]Source'!$B$8
我想知道单元格引用，像这样 ='[Vba Source Test.xlsx]Source'!$B$8
I tried using:
我尝试使用：
workbook().worksheet().cell().address
but it only returns the $B$8part.
但它只返回$B$8部分。
I will be using it in a code like this.
我将在这样的代码中使用它。
Workbook(Master).Worksheet(Summary).range(a1).value =
workbook(Source).Worksheet(Data).cell(2,8).address
Thank you again for whoever can help.
再次感谢任何可以提供帮助的人。
回答by David Zemens
You need to concatenate the object names & the range address, with the appropriate separators:
您需要使用适当的分隔符连接对象名称和范围地址：
"='["Workbook(Source).Name & "]" & Worksheet(Data).Name & "'" & Cells(2,8).Address
"='["Workbook(Source).Name & "]" & Worksheet(Data).Name & "'" & Cells(2,8).Address
Because what you are doing is assigning a Stringvalue to the cell's Formula, I think this should work (but I have not tested, so there may be typo):
因为您正在做的是String为单元格的分配一个值Formula，所以我认为这应该有效（但我没有测试过，所以可能有错字）：
Dim myFormula as String
myFormula = "='["Workbook(Source).Name & "]" & Worksheet(Data).Name & "'!" & Cells(2,8).Address
Workbook(Master).Worksheet(Summary).range(a1).Formula = myFormula
You may be able to omit the Workbook Namefrom this, I'm pretty sure that the Workbook name is automatic as long as you specify the worksheet belonging to an open workbook. If the file is not open when you insert the formula, you will have to include the workbook name.
您可以从中省略工作簿Name，只要您指定属于打开的工作簿的工作表，我很确定工作簿名称是自动的。如果插入公式时文件未打开，则必须包含工作簿名称。
