调用函数的Excel单元格 [英] Excel cell from which a Function is called

问题描述

如何获取调用VBA函数的单元格？

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

换句话说，INDELECT（ROW（），COLUMN ））？

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

我不是 ActiveCell 。

我想做的是有一个简单的函数 ThisRow_Col（rColumn As String）返回它所调用的行的列X。在B2中，我调用= ThisRow_Col（A），它应该返回A2的值。

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.

编辑：感谢Charles的答案： Application.Caller 。以下代码获取当前行的列X，与选择的位置无关：

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:

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

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

End Function

请注意，将列作为范围（ ThisRow_Col （A1））比将其作为字符串传递更好，因为如果您移动或插入列，Excel可以自动更新公式。当然， 1 行 A1 只是一个惯例。

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.

推荐答案

Application.Caller 返回调用UDF的范围对象。

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

这篇关于调用函数的Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！