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

问题描述

我使用了Application.ActiveCell.Address，但是发现这个方法不是获取调用单元格地址的正确方法。

例如：如果我们具有保存的VBA功能的excel表，一旦重新打开工作表，上述方法将无法正常工作。

请帮助

nimo

解决方案

使用Application.Caller，但您可能最好将单元格传递给该函数。

  With Application.Caller $ b $ c CallerRows = b 
 $ b 
请参阅： http://www.cpearson.com/excel /WritingFunctionsInVBA.aspx  
 
 
 
要传递单元格：
  = SomeFunction（A1）
  
代码有：
  SomeFunction（r）
  
 
Is there a function to get the address of a cell which call a VBA function ?

I used "Application.ActiveCell.Address" , but found that this method is not a correct way to get the address of calling cell. 

e.g: If we have excel sheet with saved VBA functions, Once you reopen the sheet, the above method will not work.

please help

nimo
解决方案
You can use Application.Caller, but you would probably be best to pass the cell to the function.
   With Application.Caller
        CallerRows = .Rows.Count
        CallerCols = .Columns.Count
        CallerAddr = .Address
   End With
See: http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

To pass the cell:
 =SomeFunction(A1)
Where the code has:
SomeFunction(r)


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