VBA Function - Access cell in which function is being used

[polarisking]

Hopefully, this is an easy one. I have a Function that needs to know the Row & Column in which the Function is being executed. If I use the Function many times on the same worksheet, it's returning the same value in every cell since I'm, incorrectly, pointing to Active.Row and Active.Column.

How do I pass, implicitly, the row and column of where the Function is being called from so in the case of the Function returning the cell address; it would return A3 and G6 respectively if I have the Function in both cells?

 

[SirJB7]

Hi, polarisking!

I don't know if I fully understand your question but...

Assuming you have a function like this:

Option Explicit

Function RareFunctionWithoutGPS(iParam1 As Integer, sParam2 As String, _
                                lParamRow As Long, iParamCol As Integer) As String
    Dim A As String
    Select Case iParam1
        Case Is < 0
            A = lParamRow & "." & iParamCol
        Case Is = 0
            A = ""
        Case Is > 0
            A = iParamCol & "." & lParamRow
    End Select
    RareFunctionWithoutGPS = sParam2 & "." & A
End Function

You should call it as:
=RareFunctionWithoutGPS(-1;"XXX";FILA();COLUMNA()) -----> in english: =RareFunctionWithoutGPS(-1,"XXX";ROW(),COLUMN())

Does this help? Just advise if any issue.

Regards!

 

[polarisking]

Thanks for the reply, SirJB7. This solution gets me what I needed.​

SOLVED​

Function CellAddress() As String​

CellAddress = Application.Caller.Address(0,0)​

End Function​

 

[SirJB7]

Hi, polarisking!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!