When processing columns sequentially using a For loop in VBA, it is common to handle columns numerically, such as Cells(row_number, column_number). However, there are times during processing when you want to convert the “28th column” into a familiar alphabetical column name like “Column AB”.
Unfortunately, VBA does not provide a direct built-in function like ColumnNumberToLetter(). However, you can achieve this conversion using a simple technique that utilizes the .Address property of a cell.
In this article, I will introduce this smart conversion method as a reusable function.
Complete VBA Code (Reusable Function)
Below is a custom Function that takes a column number as an argument and returns the corresponding column name. If you copy this function into a standard module, you can call it from your macros at any time.
' Function to convert column number to alphabetical column name
Function ConvertColumnNumberToLetter(ByVal colNumber As Long) As String
Dim result As String
' Check if column number is within valid range
If colNumber > 0 And colNumber <= Columns.Count Then
' Get column name using the .Address property
result = Split(Cells(1, colNumber).Address, "$")(1)
Else
result = "Error"
End If
ConvertColumnNumberToLetter = result
End Function
' --- Sample code showing how to use the function above ---
Sub Demo_GetColumnLetter()
Dim columnNum As Long
Dim columnLetter As String
' Example 1: Get the name of the 28th column
columnNum = 28
columnLetter = ConvertColumnNumberToLetter(columnNum)
MsgBox "The name of column " & columnNum & " is [" & columnLetter & "]." ' -> Result: [AB]
' Example 2: Get the name of the 255th column
columnNum = 255
columnLetter = ConvertColumnNumberToLetter(columnNum)
MsgBox "The name of column " & columnNum & " is [" & columnLetter & "]." ' -> Result: [IU]
End Sub
Key Points of the Code (How It Works)
The core of this technique lies in this single line: Split(Cells(1, colNumber).Address, "$")(1). Let’s break it down.
1. Cells(1, colNumber)
First, we refer to a cell in that column using the column number (colNumber) we want to convert. Since the row doesn’t matter, we specify the simplest one, row 1. For example, if colNumber is 28, this points to cell AB1.
2. .Address
Next, we get the .Address property of that cell. When arguments are omitted, this property returns the cell address as a string in absolute reference format.
Cells(1, 28).Address returns the string “$AB$1”.
3. Split(…, “$”)
We use the Split function to divide the retrieved string "$AB$1" using the delimiter $. This creates an array with three elements:
- (0):
""(Empty, because it’s before the first $) - (1):
"AB" - (2):
"1"
4. (…)(1)
Since the array created by Split starts at 0, we extract the second element (index 1). This gives us the target column name, “AB”.
Through this flow, any column number can be converted into an alphabetical column name.
Summary
Although there is no direct function in VBA to convert a numeric column number to an alphabetical column name, it can be easily achieved with a trick combining the .Address property and the Split function.
Split(Cells(1, colNumber).Address, "$")(1)
Remembering that this single line can perform the conversion is very convenient. If you save the Function introduced here in your standard module, it will be useful in various macro development situations.
