Search code examples
vbaexcelcelludf

Getting the address of the cell a function has been typed into

I have been unable to figure this out, and googling has yielded no help!

I have a VBA function which I would like to run in individual cells in a spreadsheet.

The function needs to know the row the function was just typed into.

I currently pass this information by having a column dedicated to row count (ie. it goes 1-2-3-4-5-6-etc.).

This is a bit clumsy and not particularly slick, so I was trying to find a way to basically just type the function (ie. =myfunc()) into the cell and then have a line within the cell that does something along the lines of:

CurrentRow = CellFunctionWasTypedInto.Row
Solution

  • You can access the Application.ThisCell object in your function which gives you information about from which cell your formula is called (e.g. Application.ThisCell.Row). Similar is Application.Caller which is applicable for cell arrays.

    See: https://msdn.microsoft.com/en-us/library/office/ff834969.aspx