Home » Tip Bank » Tasks and Techniques » Algorithms

Language: VBA
Expertise: Advanced

Oct 2, 2007

While converting to Excel 2007, I had to update the column-number-to-string code in my program to handle columns of 703 and above (AAA). I found Yassine Moe's code and simplified it. Here are the results:

Function ExcelColNonRec(ByVal intCol As Long) As String
    While (intCol > 0)
        intCol = intCol - 1
        ExcelColNonRec = Chr(65 + (intCol Mod 26)) + ExcelColNonRec
        intCol = intCol \ 26
    Wend
End Function

Corresponding MFC version:

CString ColumnNumberToLetter( long lColumnNumber )
{
	CString sColumn;

	while ( lColumnNumber )
	{
		lColumnNumber--;
		sColumn = static_cast< TCHAR >( _T( 'A' ) + 
( lColumnNumber % 26 ) ) +
sColumn;
		lColumnNumber /= 26;
	}
	return ( sColumn );
}