Excel VBA将单元格名称转换为它的坐标(Excel VBA Convert Cell Name to It's Coordinates)

Excel VBA将单元格名称转换为它的坐标(Excel VBA Convert Cell Name to It's Coordinates)

假设我有一个表示单元格的字符串： A2 。 如何将其转换为坐标： (2, 1) ？

Let's say I have this string which represents a cell: A2. What should I do to covert it to coordinates: (2, 1) ?

最满意答案

没有VBA

假设，单元格C2包含字符串"A2" 。

然后

=INDIRECT(C2)返回对A2引用 =ROW(INDIRECT(C2))返回行号 - 2 =COLUMN(INDIRECT(C2))返回列号 - 1 ="(" & ROW(INDIRECT(C2)) & "; " & COLUMN(INDIRECT(C2)) & ")"以格式(x; y) - (2; 1)返回坐标

UPD：

如果您使用的是UDF，请将参数类型从String更改为Range ：

Function GetData(Cell As Range) MsgBox "My row is " & Cell.Row MsgBox "My column is " & Cell.Column End Function

如果从这样的工作表中调用此UDF： =GetData(A2) ，将弹出msg框：

“我的排是2” “我的专栏是1”

Without VBA

Suppose, cell C2 contains string "A2".

Then

=INDIRECT(C2) returns reference to A2 =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)

UPD:

If you're using UDF, change your parameter type from String to 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:

"My row is 2" "My column is 1"