CODEWITHSUNDEEP
excelvba
Pulimon
Pulimon

Reputation: 1816

Issues with writing custom Excel function using Worksheet functions

I am very new to excel-vba programming and I was learning how to write a custom vba function using worksheet function.

If I go to a cell and type in the following formula, I will get the cell name.

=ADDRESS(ROW(), COLUMN(), 4)

E.g., If we enter the above formula into cell "A6", the cell will return the value = A6

Now I am trying to write a vba function to do the exact same thing as following:

Function GetThisCellName() As String
    GetThisCellName= WorksheetFunction.Address(WorksheetFunction.Row(), WorksheetFunction.Column(), 4)       
End Function

However when I am trying to use this function inside a cell as follows:

=GetThisCellName()

the cell will return #VALUE!. What might be wrong with my code?

Upvotes: 1

Views: 94

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

If you want to return address of cell, from which formula was called, use Application.ThisCell:

Function GetThisCellName() As String
    GetThisCellName = Application.ThisCell.Address(False, False)
End Function

Upvotes: 1

Omer Atay - MSFT
Omer Atay - MSFT

Reputation: 287

Address, Row, and Column are not members of WorksheetFunction object. You can use this syntax to get the address of the cell:

Function GetThisCellName() As String
    GetThisCellName = ActiveCell.Address(False, False, xlA1)
End Function

See also Range.Address property

Upvotes: 2

Related Questions