I need to extract the current cell in VBA without passing it in parameter of the function. I mean, for exemple, if the formula is in cell B35, I want to extract the adress (line = 35, colomn = 2), in the VBA code of the formula.
I thought the active cell would do it, but no, it extracts the adress of thecell where the cursor is.
Do you know how i could do it? Thanks in advance for help.
CodePudding user response:
Try below codes
Sub ExAddress()
MsgBox "Row: " & ActiveCell.Row & vbNewLine & _
"Column: " & ActiveCell.Column
End Sub
CodePudding user response:
I think you mean Application.Caller
which is the object that calls VBA. It can be used inside a function to return the cell calling the UDF, not the ActiveCell
Notice the output is different in all of them, even if they use the same UDF with no arguments.
CodePudding user response:
A similar one to Foxfire And Burns And Burns' answer, but no vba needed.
Apply this formula, then copy it and paste as values.
="line = "&ROW()& " column= "&COLUMN()