CODEWITHSUNDEEP
excelvba
ImFabien75
ImFabien75

Reputation: 189

Extract current cell address

I need to extract the current cell in VBA without passing it in parameter of the function.

I mean, for example, if the formula is in cell B35, I want to extract the address (line = 35, column = 2), in the VBA code of the formula.

I thought the active cell would do it, but no, it extracts the address of the cell where the cursor is.

Do you know how I could do it?

Thanks in advance for help.

Upvotes: 0

Views: 5985

Answers (3)

Ozgun Senyuva
Ozgun Senyuva

Reputation: 466

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()

enter image description here

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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

Application.Caller property (Excel)

An easy example would be this:

Public Function ThisCell() As String


ThisCell = "ROW:" & Application.Caller.Row & " - COLUMN:" & Application.Caller.Column

End Function

If you type now in any cell =ThisCell() it will return its row and column:

enter image description here

Notice the output is different in all of them, even if they use the same UDF with no arguments.

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36870

Try below codes

Sub ExAddress()
    MsgBox "Row: " & ActiveCell.Row & vbNewLine & _
            "Column: " & ActiveCell.Column
End Sub

enter image description here

Upvotes: 0

Related Questions