How To Get Excel Active Cell Address Using Formula Or VBA

This article will tell you how to get the Excel active cell address and display the active cell address in a specified cell using a formula or VBA.

1. How To Get & Display Active Cell Addresses Using Formula.

1. When you select an Excel cell, it will display the cell address in the Name Box which is located in the top left corner of the Excel window before the Excel Formula Bar.
2. But how to display the Excel active cell address in the active cell? We can use the Excel ADDRESS, ROW, and COLUMN functions.
3. Input the formula =ADDRESS(ROW(), COLUMN()) in the active cell and press the enter key, then it will display the active cell’s absolute address text (such as $A$1) in the active cell.
4. The ROW function will return the active cell’s row number.
5. The COLUMN function will return the active cell’s column number.
6. The ADDRESS function will return the active cell’s address text.

2. How To Get & Display Active Cell Addresses Using VBA Code.

1. Click the Excel Developer tab —> Visual Basic item in the Code group to open the Microsoft Visual Basic for Applications window.
2. Click the worksheet on the left Project – VBAProject panel to open it.
3. Click the Insert —> Procedure… menu item in the Microsoft Visual Basic for Applications window top menu bar to open the Add Procedure dialog window.
4. Input the text Show_Active_Cell_Address to the Name: text box in the above dialog window.
5. Select the Sub radio button in the Type group.
6. Select the Public Radio button in the Scope group.
7. Click the OK button to create the public procedure on the right side worksheet source code window.
8. Input the below source code on the window’s right side and click the Save button to save the below source code.

   Public Sub Show_Active_Cell_Address()
   
       ' when execute this procedure, it will show the active cell address in cell A1 of the current worksheet.
       Range("$A$1").Value = ActiveCell.Address
   
   End Sub
   

9. Now select an active cell in the Excel worksheet, then click the Run button ( a small green triangle ) on the Microsoft Visual Basic for Applications window top menu bar to run the procedure.
10. Then it will show the active cell address in cell A1.
11. But this method is not convenient, you must run this procedure to show the active cell address every time after you select an active cell.
12. If you want to automatically show the active cell address when you activate different cells in your Excel worksheet. You can overwrite the built-in private procedure Worksheet_SelectionChange.
13. This procedure can process the selection change event when you select different active cells.
14. Copy the below source code into the VBA source code editor and save it.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        ' when execute this procedure, it will show the active cell address in cell A1 of the current worksheet.
        Range("$A$1").Value = ActiveCell.Address
    
    End Sub
    

15. Close the Microsoft Visual Basic for Applications window.
16. Now when you select an Excel cell or a cell range, it will display the active cell address in cell A1 automatically.
17. When you select another active cell, it will display the selected cell’s address in cell A1 automatically.

References

1. How To Enable Developer Tab And Write VBA Source Code In Excel.