Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VBA
Expertise: Advanced
Oct 2, 2007

Converting Numbers to Column Names in Excel 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 );
}
John P.
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap