The Address() worksheet function does exactly that. As it’s not available through Application.WorksheetFunction, I came up with a solution using the Evaluate() method.

This solution let Excel deals with spaces and other funny characters in the sheet name, which is a nice advantage over the previous answers.

Example:

Evaluate("ADDRESS(" & rng.Row & "," & rng.Column & ",1,1,""" & _
    rng.Worksheet.Name & """)")

returns exactly “Sheet1!$A$1”, with a Range object named rng referring the A1 cell in the Sheet1 worksheet.

This solution returns only the address of the first cell of a range, not the address of the whole range (“Sheet1!$A$1” vs “Sheet1!$A$1:$B$2”). So I use it in a custom function:

Public Function AddressEx(rng As Range) As String

    Dim strTmp As String

    strTmp = Evaluate("ADDRESS(" & rng.Row & "," & _
        rng.Column & ",1,1,""" & rng.Worksheet.Name & """)")

    If (rng.Count > 1) Then

        strTmp = strTmp & ":" & rng.Cells(rng.Count) _
            .Address(RowAbsolute:=True, ColumnAbsolute:=True)

    End If

    AddressEx = strTmp

End Function

The full documentation of the Address() worksheet function is available on the Office website: https://support.office.com/en-us/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89