调用[duplicate]函数的Excel单元格

This question already has an answer here:

这个问题已经有了答案:

• How to get address of cell which calls a VBA Functions in a Excel sheet 1 answer
• 如何在Excel表1中获取调用VBA函数的单元格地址

How can I get the cell where my VBA Function is called from?

我怎样才能得到调用VBA函数的单元格呢?

In other words, what is the VBA equivalent for INDIRECT(ROW(), COLUMN()) ?

换句话说，对于间接(ROW()、列())，VBA的等效项是什么?

I'm not looking for ActiveCell.

我不是在找ActiveCell。

What I want to do is have a simple function ThisRow_Col(rColumn As String) return the column X of the row it's called from. Say in B2 I call =ThisRow_Col("A"), it should return the value of A2. This should work regardless of which cell active.

我要做的是有一个简单的函数ThisRow_Col(rColumn As String)返回它所调用的行的X列。假设在B2中我调用=ThisRow_Col("A")它应该返回A2的值。不管哪个单元格是活动的，这都应该有效。

EDIT: Thanks Charles for the answer: Application.Caller. The following code gets the column X of the current row, independent of where the selection is:

编辑:谢谢查尔斯的回答:Application.Caller。下面的代码获取当前行的X列，与所选内容无关:

Function ThisRow_Col(rColumn As Range)
    ' Return INDIRECT(rcolumn & ROW())

    ThisRow_Col = Application.Caller.Worksheet.Cells(Application.Caller.Row, rColumn.Column).Value

End Function

Note that passing the column as a Range (ThisRow_Col(A1)) is better than passing it as a string, because Excel can automatically update the formulas if you move or insert columns. Of course the 1 row of A1 is just a convention.

注意，将列作为范围(ThisRow_Col(A1))传递比将其作为字符串传递要好，因为如果您移动或插入列，Excel可以自动更新公式。当然A1的第一行只是一个约定。

1 个解决方案

#1

Application.Caller returns the range object that the UDF is called from.

应用程序。调用者返回UDF所调用的range对象。