Excel VBA 将单元格名称转换为其坐标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24466368/
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 VBA Convert Cell Name to It's Coordinates
提问by Eyal C
Let's say I have this string which represents a cell: A2.
What should I do to covert it to coordinates: (2, 1)?
比方说，我有这样的字符串，它代表了一个单元格：A2。
我该怎么做才能将其转换为坐标：(2, 1)？
采纳答案by Dmitry Pavliv
Without VBA
没有 VBA
Suppose, cell C2contains string "A2".
假设，单元格C2包含 string "A2"。
Then
然后
=INDIRECT(C2)returns reference toA2=ROW(INDIRECT(C2))returns row number -2=COLUMN(INDIRECT(C2))returns column number -1="(" & ROW(INDIRECT(C2)) & "; " & COLUMN(INDIRECT(C2)) & ")"returns coordinates in format(x; y)-(2; 1)
=INDIRECT(C2)返回对A2=ROW(INDIRECT(C2))返回行号 -2=COLUMN(INDIRECT(C2))返回列号 -1="(" & ROW(INDIRECT(C2)) & "; " & COLUMN(INDIRECT(C2)) & ")"以格式返回坐标(x; y)-(2; 1)
UPD:
更新：
If you're using UDF, change your parameter type from Stringto Range:
如果您使用的是 UDF，请将您的参数类型从 更改String为Range：
Function GetData(Cell As Range)
MsgBox "My row is " & Cell.Row
MsgBox "My column is " & Cell.Column
End Function
if you call this UDF from worksheet like this: =GetData(A2), msg box would pop-up:
如果您像这样从工作表中调用此 UDF =GetData(A2)：，则会弹出 msg 框：
- "My row is 2"
- "My column is 1"
- “我的排是 2”
- “我的列是 1”
回答by Dimitris Kalaitzis
You can use Column and Row properties of the Range object:
您可以使用 Range 对象的 Column 和 Row 属性：
Range("A2").Row
Range("A2").Column
Examlpe:
例子：
Sub test()
Dim x As String
x = "A2"
MsgBox GetRow(x) & " " & GetColumn(x)
End Sub
Function GetRow(Cell As String)
GetRow = Range(Cell).Row
End Function
Function GetColumn(Cell As String)
GetColumn = Range(Cell).Column
End Function
