Excel Guides

Pulling Cell Names into VBA in Excel

There are a few ways to pull cell names into VBA in Excel. One way is to use the Range object's Cells property. This property returns a Range object that represents all the cells on the worksheet (not just a specific range). You can then use the Name property of the Range object to return the name of the cell.

Dim rng As Range
Set rng = Range("A1:B2")

Debug.Print rng.Cells(1, 1).Name 'returns "A1"
Debug.Print rng.Cells(1, 2).Name 'returns "B1"
Debug.Print rng.Cells(2, 1).Name 'returns "A2"
Debug.Print rng.Cells(2, 2).Name 'returns "B2"

Another way to pull cell names into VBA in Excel is to use the Range object's Address property. This property returns the address of the cell as a string. You can then use the Split function to split the address string into an array of strings, with each element of the array being one part of the address.

Dim strAddress As String
Dim arrAddress As Variant
Dim rng As Range
Set rng = Range("A1:B2")
strAddress = rng.Address 'returns "$A$1:$B$2"
arrAddress = Split(strAddress, "$") 'returns an array with 3 elements: "", "A", and "1:B2" 
Debug.Print arrAddress(0) 'returns ""  (the first element of the array) 
Debug.Print arrAddress(1) 'returns "A"  (the second element of the array) 
Debug.Print arrAddress(2) 'returns "1:B2" (the third element of the array) 

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.