1

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?

Haki_z
  • 85
  • 1
  • 9
  • Typically, if in the sheet, people use the Columns to help generate letters (which can take you to XFD in later Excel versions) http://blog.thejaytray.com/how-to-auto-fill-sequential-letters-in-excel/ – QHarr Apr 06 '18 at 17:16
  • @QHarr Unfortunately I am not using Excel. I need a programmatic solution. – Haki_z Apr 06 '18 at 17:19
  • What application are you programming in? – QHarr Apr 06 '18 at 17:20
  • @QHarr it is vba language but a different IDE (mapbasic). I don't really need code, I just need help with the logic. – Haki_z Apr 06 '18 at 17:23
  • Possible duplicate of [How to convert a column number (eg. 127) into an excel column (eg. AA)](https://stackoverflow.com/q/181596/11683) – GSerg Apr 06 '18 at 20:25

3 Answers3

2

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
Haki_z
  • 85
  • 1
  • 9
1

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.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Thank you but unfortunately it is not Excel VBA I am working with. It is MapBasic VBA :( – Haki_z Apr 06 '18 at 17:35
  • See my edit, I don't have time to check the details right this moment, but as long as Excel is installed on the machine there's still a way to use Excel objects, same idea as calling Excel from Word. – ashleedawg Apr 06 '18 at 17:37
  • There's also way way to do it mathematically,same idea as calculating [extended file attributes](https://technet.microsoft.com/en-us/library/ee176615.aspx) from a `chmod`-like value, You'd work backwards: check if it a number that 3 digits (>703) use division and `mod` to figure out the 3rd letter, deduct that code from the total, then onto the 2nd letter (27 to 702) etc. When written properly the entire loop should be <10 lines of code. Sorry I don't have time to finish that thought right now. If it doesn't make sense, maybe @Qharr knows what I'm talking about. – ashleedawg Apr 06 '18 at 17:48
  • Thank you but I found a solution which I will post shortly :) – Haki_z Apr 06 '18 at 19:01
1

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
C. van Dorsten
  • 152
  • 7
  • 1
    Thanks, this is pretty much the same logic I used in my solution which I posted as an answer. – Haki_z Apr 06 '18 at 19:52