Menu
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
321
It is called “Active Cell” in VBA. If your active cell not visible in your window, then look at the name box. The name is usually the row character followed by the column number, such as cell A1. read more, it will show you the active cell address.
The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.
If the Excel VBA Range object you want to refer to is a single cell, the syntax is simply “Range(“Cell”)”. For example, if you want to make reference to a single cell, such as A1, type “Range(“A1″)”.
Now you need to enter the row size and column size. Type a starting parenthesis and enter the number to define the row size and then a number to define the column size. In the end, add “. Select” to tell VBA to select the range, and when you run this code, it will select the range.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
