"This cell" in a function for Excel

Question

When I write a function (myfunc) in VBA for Excel and attach it to a cell through making =myfunc the value of the cell, I need to get information about the cell when the function is executed. VBA is sort of objected-oriented and the OO languages that I know have operators like This or Self. I haven't discovered any such thing for VBA for Excel.

Answer 1

Please try this:

Function UseThisCell(r As Range)

UseThisCell = r.Address

End Function

The above function will return the selected cell address.

Answer 2

I am not sure that I am on the same wavelength as yourself but the following might help.

You can't make =myfunc the value of the cell because as soon as you enter =myfunct in the cell then any previous value in the cell is gone and replaced with the formula = myfunct()

To return information about the cell containing the function then the code below is an example of returning some information about the cell as per the screen shot.

AFAIK the UDF cannot be used to set or change formats in the cell.

To display as above:

• Set the interior color
• Set the Font color
• Turn on Wrap Text
• Enter the formula in the cell   =MyFunct()

Function MyFunct() As String

    Dim varCaller As Variant

    Dim rngCaller As Range

    varCaller = Application.Caller.Address

    Set rngCaller = Range(varCaller)

    rngCaller.WrapText = True     'This does not do anything. Cannot set formats from the UDF

    MyFunct = rngCaller.Address & vbLf & _

                rngCaller.Interior.Color & vbLf & _

                rngCaller.Font.Color & vbLf & _

                rngCaller.Font.Name

End Function

You could omit the variable varCaller plus the line assigning the Application.Caller.Address to it and use the following line to directly assign Application.Caller to rngCaller.

Set rngCaller = Range(Application.Caller.Address)

If not the answer you are looking for then I need more information. Please provide examples of the formula you want to use in the cell and examples of what you expect to be returned.