Content
Resources
To Do
Toolbox

 Base Ten to Base Letter
Function Information
Applicable Versions

Any

Dependancies

- None (VBA)

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

CODE
Public Function Base10ToBaseLetter(ByVal lngNumber As Long) As String

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

'
'   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

CODE

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:

CODE
? 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:

CODE
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.

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

CODE
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