Base Ten to Base Letter – DEVelopers HUT

This is a pair of related functions for use in VBA. For alternate methods while working in Excel and within a limited range, see the Alternative Excel Implementation section.

Base10ToBaseLetter()
Converts a base 10 number into a “base letter” value, following the structure we’ve all seen in the column headings in Excel. So, the base 10 value 5 converts to “E”, and 29 converts to “AC”.

BaseLetterToBase10()
The second function converts a “base letter” value to base 10.

The functions serve various purposes in the Access world:

General “Count By Letter” applications
Sample Testing identification strings (ex: “11-25-10-A”, “11-25-10-B”, etc.)
Inventory Stock Location Handling (ex: Warehouse cell AF-22)
Manufacturing Revision Control, whose revisions are traditionally handled by letter incrementing after initial release

Internal comments are included in the functions to explain how the conversion process works.

Base10ToBaseLetter

Public Function Base10ToBaseLetter(ByVal lngNumber As Long) As String

'   Code courtesy of UtterAccess Wiki
'   http://www.utteraccess.com/wiki/index.php/Category:FunctionLibrary

'   Licensed under Creative Commons License
'   http://creativecommons.org/licenses/by-sa/3.0/
'
'   You are free to use this code in any application,
'   provided this notice is left unchanged.
   
' ===========================================================================================
'   Concept:
'   Base10: Decimal 123 => (1 * 10 ^ 2) + (2 * 10 ^ 1) + (3 * 10 ^ 0)

'   Base26: Decimal 123 => ( 4 * 26 ^ 1) + (19 * 26 ^ 0)
'   Representing 4 and 19 with letters: "DS"

'   MSD = Most Significant Digit
'   LSD = Least Significant Digit

' ===========================================================================================
'   Returns ZLS for input values less than 1
'   Error handling not critical. Input limited to Long so should not normally fail.
' ===========================================================================================

Dim intBase26() As Integer  'Array of Base26 digits LSD (Index = 0) to MSD
Dim intMSD As Integer       'Most Significant Digit Index
Dim n As Integer            'Counter

   If lngNumber > 0 Then

'       Calculate MSD position (Integer part of Log to Base26 of lngNumber)
'           Log of X to Base Y = Log(X) / Log(Y) for any Base used in calculation.
'           (VBA Log function uses the Natural Number as the Base)

       intMSD = Int(Log(lngNumber) / Log(26))
       ReDim intBase26(0 To intMSD)
       
       For n = intMSD To 0 Step -1
'           Calculate value of nth digit in Base26
           intBase26(n) = Int(lngNumber / 26 ^ n)
           
'           Reduce lngNumber by value of nth digit
           lngNumber = lngNumber - ((26 ^ n) * intBase26(n))
       Next
       
'       Base Letter doesn't have a zero equivalent.
'           Rescale 0 to 26 (digital representation of "Z")
'           and "borrow" by decrementing next higher MSD.
'       Digit can be -1 from previous borrow onto an already zero digit
'           Rescale to 25 (digital representation of "Y")

'       Looping from LSD toward MSD
'       MSD not processed because it cannot be zero and
'           avoids potential out of range intBase26(n + 1)

       For n = 0 To intMSD - 1
           If intBase26(n) < 1 Then
               intBase26(n) = 26 + intBase26(n)        ' Rescale value
               intBase26(n + 1) = intBase26(n + 1) - 1 ' Decrement next higher MSD
           End If
       Next
       
'       Ignore MSD if reduced to zero by "borrow"
       If intBase26(intMSD) = 0 Then intMSD = intMSD - 1
       
'       Convert Base26 array to string
       For n = intMSD To 0 Step -1
           Base10ToBaseLetter = Base10ToBaseLetter & Chr((intBase26(n) + 64))
       Next
   
   End If
   
End Function

BaseLetterToBase10

Public Function BaseLetterToBase10(ByVal strInput As String) As Long

'   Upper or lower case characters accepted as input

'   ZLS returns 0
'   Negative return value indicates error:
'       Unaceptable character or Overflow (string value exceeds "FXSHRXW")
'       Digit indicates character position where error encountered

'   MSD = Most Significant Digit

Dim intMSD As Integer       'MSD Position
Dim intChar As Integer      'Character Position in String
Dim intValue As Integer     'Value from single character
Dim n As Integer            'Counter

On Error GoTo ErrorHandler

'   Convert String to UpperCase
   strInput = UCase(strInput)
   
'   Calculate Base26 magnitude of MSD
   intMSD = Len(strInput) - 1

   For n = intMSD To 0 Step -1
       intChar = intMSD - n + 1
       intValue = Asc(Mid(strInput, intChar, 1)) - 64
       
'       Test for character A to Z
       If intValue < 0 Or intValue > 26 Then
           BaseLetterToBase10 = -intChar
           Exit For
       Else
'           Add Base26 value to output
           BaseLetterToBase10 = BaseLetterToBase10 + intValue * 26 ^ n
       End If
   Next
   
   Exit Function
   
ErrorHandler:
   BaseLetterToBase10 = -intChar
   Exit Function

End Function

Sample usage

? Base10ToBaseLetter(704)
AAB

? BaseLetterToBase10("AAB")
704

Incrementing a Letter

Let's say you know the Base Letter value, and want to auto-increment it by one. You could get the next value with a simple function that calls these two functions:

Function NextLetters(strInput As String) As String

   Dim lngTemp As Long
   
   lngTemp = BaseLetterToBase10(strInput)
   lngTemp = lngTemp + 1
   NextLetters = Base10ToBaseLetter(lngTemp)

End Function

Alternative Excel Implementation

If you are working within Excel or you reference the Excel Object Model from another application, like an Access project, you can capitalize on the nature of the Excel Column Headings by using the following two functions. However, by using these Excel based techniques, the range available for conversion is 1 to 16384 {A to XFD} with the Excel 2007 object model and higher, and 1 and 256 {A to IV} with the Excel 2003 object model and lower.

Public Function ColumnLetterToNumber(strCol As String) As Long
   ColumnLetterToNumber = ThisWorkbook.Worksheets(1).Range(strCol & "1").Column
End Function

ColumnNumberToLetter(iCol As Integer) As String
   If iCol > 0 And iCol <= Columns.Count Then ColumnNumberToLetter = Replace(Cells(1, iCol).Address(0, 0), 1, "")
End Function