Recursive Function to Convert Column Numbers to Letters

Category:

Office/VBA

Type:

Snippets

Difficulty:

Intermediate

Author:

Martin Kremer

Version Compatibility: Visual Basic 6

More information:

I needed to be able to convert an Excel column number into the letter designation as displayed in Excel. Every code snippet that I found involved a very complicated formula or relied on the built-in Excel ActiveCell.address() function. I wanted something simple and understandable, so came up with this recursive function. I have not done rigorous testing, but have tested the boundaries at a number of places (1, 26, 1222, 1223, etc) and several numbers posted in blogs with the complicated functions (217287 = LIKE, 1000 = ALL).

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Function ColLetter(colnum As Long) As String
'**************** Column Letter Value *****************
'                 Recursive function
' Translate a column number into a letter value
'******************************************************
    ' Converting a decimal number to a series of letters
    ' is the same as converting one number system to
    ' another (eg: decimal to hex).
    '
    ' Each column represents a power of 26 -- so if the
    '   number is > 26
    '    1. Run the integer portion of (columnNumber/26)
    '       thru the process and
    '    2. Prefix the returned letter to the letter
    '       representing the remainder
    ' The function will call itself as many times as
    '   needed to get the column number below or equal
    '   to 26
    '
    ' Since the alphabet does not have a representation
    ' of zero, and the integer arithmetic involved
    ' returns a zero at the boundaries of the range, we
    ' need to make some adjustments. Whenever a mod
    ' operation returns a zero, we reset it to 26 and,
    ' where necessary, decrement the next higher column.
    '***************************************************
    Dim wk As String
    Dim wkn As Long
        
    If colnum > 26 Then
        wkn = colnum \ 26
        If colnum Mod 26 = 0 Then
            wkn = wkn - 1
        End If
        wk = ColLetter(wkn)
    End If
    wkn = (colnum Mod 26)
    If wkn = 0 Then wkn = 26
        
    ColLetter = wk & Chr(Asc("A") - 1 + wkn)
End Function