Label a set of objects with (A->Z,AA->ZZ, AAA->ZZZ) in VBA

Question

I have a set which has an unknown number of objects. I want to associate a label to each one of these objects. Instead of labeling each object with a number I want to label them with letters.

For example the first object would be labeled A the second B and so on.

When I get to Z, the next object would be labeled AA

AZ? then BA, BB, BC.

ZZ? then AAA, AAB, AAC and so on.

I'm working using Mapbasic (similar to VBA), but I can't seem to wrap my head around a dynamic solution. My solution assumes that there will be a max number of objects that the set may or may not exceed.

label = pos1 & pos2

Once pos2 reaches ASCII "Z" then pos1 will be "A" and pos2 will be "A". However, if there is another object after "ZZ" this will fail.

How do I overcome this static solution?

Answer 1

Basically what I needed was a Base 26 Counter. The function takes a parameter like "A" or "AAA" and determines the next letter in the sequence.

Function IncrementAlpha(ByVal alpha As String) As String

Dim N As Integer
Dim num As Integer
Dim str As String

Do While Len(alpha)
    num = num * 26 + (Asc(alpha) - Asc("A") + 1)
    alpha = Mid$(alpha, 2,1)
Loop
N = num + 1

Do While N > 0
    str = Chr$(Asc("A") + (N - 1) Mod 26) & str
    N = (N - 1) \ 26
Loop
IncrementAlpha = str
End Function

Answer 2

If we need to convert numbers to a "letter format" where:

1 = A
26 = Z
27 = AA
702 = ZZ
703 = AAA  etc

...and it needs to be in Excel VBA, then we're in luck. Excel's columns are "numbered" the same way!

Function numToLetters(num As Integer) As String
    numToLetters = Split(Cells(1, num).Address(, 0), "$")(0)
End Function

Pass this function a number between 1 and 16384 and it will return a string between A and XFD.

---

Edit:

I guess I misread; you're not using Excel. If you're using VBA you should still be able to do this will the help of an reference to an Excel Object Library.

Answer 3

This should get you going in terms of the logic. Haven't tested it completely, but you should be able to work from here.

Public Function GenerateLabel(ByVal Number As Long) As String
  Const TOKENS As String = "ZABCDEFGHIJKLMNOPQRSTUVWXY"
  Dim i As Long
  Dim j As Long
  Dim Prev As String
  j = 1
  Prev = ""
  Do While Number > 0
    i = (Number Mod 26) + 1
    GenerateLabel = Prev & Mid(TOKENS, i, 1)
    Number = Number - 26
    If j > 0 Then Prev = Mid(TOKENS, j + 1, 1)
    j = j + Abs(Number Mod 26 = 0)
  Loop
End Function