Option Explicit
Sub Beispiele()
Debug.Print
Debug.Print "Zelle definieren:"
Debug.Print GenAddress(1, 1, Worksheet:="Tab1")
Debug.Print GenAddress(1, "A", Worksheet:="Tab2")
Debug.Print GenAddress(1, "A", Worksheet:="Tab3", ReferenceStyle:=xlR1C1)
Debug.Print
Debug.Print "Bereich definieren:"
Debug.Print GenAddress(1, 1, 5, 1, "Tab4")
Debug.Print GenAddress(1, "A", 5, "A", "Tab5")
Debug.Print GenAddress(1, "A", 5, "A", "Tab6", ReferenceStyle:=xlR1C1)
End Sub
Public Function GenAddress( _
Row, Column, _
Optional RowTo, Optional ColumnTo, _
Optional Worksheet As String, _
Optional ReferenceStyle As XlReferenceStyle = xlA1 _
) As String
If Trim$(Worksheet) = "" Then
GenAddress = Cells(Row, Column).Address(ReferenceStyle:=ReferenceStyle)
Else
GenAddress = "'" & Trim$(Worksheet) & "'!" & Cells(Row, Column).Address(ReferenceStyle:=ReferenceStyle)
End If
If Not (IsMissing(RowTo) Or IsMissing(ColumnTo)) Then
GenAddress = GenAddress & ":" & GenAddress(RowTo, ColumnTo, ReferenceStyle:=ReferenceStyle)
End If
End Function