Excel VBA equivalent of ADDRESS function

Question

I want to write this function in vba so that it gives me the cell id.

=ADDRESS(1,2,4,TRUE,"Sheet1")

Does anyone know the VBA syntax for that? Thanks in advance.

Solution

The normal way to do a similar thing in VBA would be either

Worksheets("Sheet1").Cells(1, 2).Address(RowAbsolute:=False, _
                                         ColumnAbsolute:=False, _
                                         External:=True)

which would return [TestWorkbook.xlsm]Sheet1!B1 or

Worksheets("Sheet1").Cells(1, 2).Address(RowAbsolute:=False, _
                                         ColumnAbsolute:=False, _
                                         External:=False)

which would just return B1.

There isn't a simple way of showing the worksheet name and cell, without also including the workbook name. A possible way would be

"'" & Worksheets("Sheet1").Name & "'!" & Worksheets("Sheet1").Cells(1, 2).Address(RowAbsolute:=False, _
                                                                                  ColumnAbsolute:=False, _
                                                                                  External:=False)

(Obviously, if you use Worksheets("Sheet1").Name then you may as well just use "Sheet1", but I wrote it that way so that you could use a variable instead of a hardcoded value. Edit: On rereading that last sentence, I realise how stupid it is - Worksheets(mySheetName).Name is the same as mySheetName, so just use "'" & mySheetName & "'!" & ...)