3
\$\begingroup\$

I'm writing something that basically takes a bunch of files and renames them into a nice format, such as pic_000.jpeg, pic_001.jpeg, etc. One of the options is to write the incrementation as letters, e.g. pic_aaa.jpeg, pic_aab.jpeg, etc. Also, is they are converting a set of pic_000s into a set of pic_aaas, then there's also an option to preserve the order of increments. In other words, if you deleted pic_000 through pic_117, then the pic_aaa set would start at pic_aen, not pic_aaa. It's a little weird, but I'm just trying to give the user lot of options so they can do whatever they want.

I suppose there are two different types of output for a base-10 to base-26 function such as this:

  1. without a fixed-length result, e.g.: A, B, ... , Z, AA, AB, ... ZZ, AAA (notice that A is representing a 1 when it is the leading letter and the result is more than one letter in length, else it is representing a 0)

  2. with a fixed-length result, e.g. AAA, AAB, ... , ZZY, ZZZ (A will always represent a zero, here)

Again, I've chosen to write it both ways and let the user decide what they want. With (2), you just have to count how many input numbers there are (the numbers that we're converting in the first place) and then use an appropriately-sized result. Obviously two characters (AA, AB, etc.) isn't enough to represent a million different numbers, so you'd have see if three letters could do it, then four, and so on. (To make this easier, you can just do resultLength = ceil(log(inputNumber)/log(26)) (not vb6 code, but you get the idea) I haven't written (2) yet, but I assume it will be pretty easy.

Number 1, though, proved to be a bit of a doozy since A-Z can represent 0-25 in some places and 1-26 in others. If A is 0, then Z is 25, but unless you want to use BA for 26, skipping A_, then you have to let A be equal to 1 where it is a leading character (e.g. AA, AB, etc.) when the result is not 1 character long (i.e. for the result of A, A is 0 even though it is the leading character.)

It gets trickier, however. One result of the fact that A-Z represents 0-25 in certain slots and 1-26 in others is that figuring out how many characters the result will be is tricky.

Examples:

If input < 26^1, Then
    resultLength = 1
Elseif input < (27*(26^1) -1) Then 'ZZ represents (27*26) -1
    resultLength = 2
Elseif input < (27*(26^2) -1) Then  'ZZ represents (27*26^2) -1
    resultLength = 3
End If

Anyways, I'm pretty sure it works, so I'll let you take a look at it.

Function NumericToAlpha(inString As String) As String
    Dim outString As String
    Dim asLong As Long
    Dim Index As Integer
    Dim i As Integer
    Dim j As Double
    Dim intDiv As Integer

    asLong = Val(inString)
    ' validate input; no negative numbers or decimals
    If asLong < 0 Or InStr(inString, ".") Then
        MsgBox "Cannot process negative values or numbers that include a decimal point."
        NumericToAlpha = ""
        Exit Function
    End If

    Do While (27 * 26 ^ (Index) - 1) < asLong
        Index = Index + 1
    Loop
    If asLong = 26 Then Index = 1


    For i = Index To 0 Step -1
        j = 26 ^ i
        If (i = Index) And (Index > 1) And Int(asLong / j) = 1 Then asLong = asLong - 26 ^ (i - 1)
        intDiv = Int(asLong / j)
        If Len(outString) Then ' non-leading character
            outString = outString & Chr(Asc("A") + intDiv)
        Else 'outString leading character
            If i = 0 Then
                outString = Chr(Asc("A") + intDiv)
            Else
                outString = Chr(Asc("A") + intDiv - 1)
            End If
        End If

        asLong = asLong - (intDiv * j)

    Next i

    NumericToAlpha = outString

End Function

I test this with the following numbers, which I've come up with by doing the math by hand:

Dim newL As String
newL = Chr(13)
MsgBox NumericToAlpha(0) & newL & NumericToAlpha(25) & newL & NumericToAlpha(26) & newL & NumericToAlpha(701) & newL & NumericToAlpha(702) & newL & NumericToAlpha(18251) & newL & NumericToAlpha(18252) & newL & NumericToAlpha(474551) & newL & NumericToAlpha(474552) & newL & NumericToAlpha(474551) & newL & NumericToAlpha(474552)

If there are any bugs or if you can think up a sexier way to do this, please let me know!

\$\endgroup\$
  • \$\begingroup\$ Instead of doing it yourself, use this: gladman.plushost.co.uk/oldsite/computing/gmp4win.php and mpir.org also see: exploringbinary.com/… Then gmp_strval(number, 36) Or 62 (instead of 36) to use both upper and lowercase letters. \$\endgroup\$ – Ariel Jul 10 '12 at 19:26
  • \$\begingroup\$ @Ariel My program is in VB6, not C++. I know you can include C++ code in a VB6 program somehow (don't know if people are referring to .dll calls or what when they say you can do this), but I've never done it before. On a side not, I am rather surprised to see that there exist numerous web sites dedicated to a library made up of this function and other similar ones. \$\endgroup\$ – Michael Jul 11 '12 at 0:26
  • \$\begingroup\$ gmp is a very useful library. It's not just for this base conversion - it's for arbitrary precision math and it's one of the best. \$\endgroup\$ – Ariel Jul 11 '12 at 0:38
2
\$\begingroup\$

First Point:

For multiple If/Else blocks, I prefer to use Select Case. I would just do this out of habit, though your check is small enough that it may not matter too much.

Also, this may be trickier, given that something falling into the first case looks like it would also fall into the other two. However, unlike other languages, there is no break required, and the Select block ends at the first case it comes to (like the If/Else would have).

Select Case input

Case Is < 26 ^ 1:
    resultLength = 1
Case Is < (27 * (26 ^ 1) - 1): 'ZZ represents (27*26) -1
    resultLength = 2
Case Is < (27 * (26 ^ 2) - 1): 'ZZ represents (27*26^2) -1
    resultLength = 3
End Select

Second Point:

I know I'm not doing much in the way of efficiency (more just style, I think), but here's another. Regarding:

If (i = Index) And (Index > 1) And Int(asLong / 26 ^ (i)) = 1 Then
    j = 26 ^ i
    asLong = asLong - 26 ^ (i - 1)
Else
    j = 26 ^ i
End If

Since you assign j = 26 ^ i in both cases, why not just assign it before the If?

j = 26 ^ i

If (i = Index) And (Index > 1) And Int(asLong / 26 ^ (i)) = 1 Then
    asLong = asLong - 26 ^ (i - 1)
End If

Sorry for not getting this all out at once. I'll continue to pick apart as I have time and add to this answer as needed...

\$\endgroup\$
  • \$\begingroup\$ The If/ElseIf portion was just code to explain the concept. In the actual code, I used a Do While loop. Although the upper limit of the Long data type probably wouldn't take too long to hit with a base of 26, if this were base 2, the switch/case statement would be very long. Seeing as how the do while loop can handle everything you could throw at it, and is the best solution for other bases, I guess I just see it as proper form. \$\endgroup\$ – Michael Jul 11 '12 at 0:19
  • 1
    \$\begingroup\$ RE your edit: Yes, sorry; it is indeed redundant. I was rather tired when I wrote code. Whoops! \$\endgroup\$ – Michael Jul 11 '12 at 0:49
  • \$\begingroup\$ @Michael No worries. I've written plenty of code that I knew better not to after the fact! \$\endgroup\$ – Gaffi Jul 11 '12 at 0:50
2
\$\begingroup\$

You asked for a 'sexier' way to do this, and I think I've got it, but I'll let you be the judge:

Function NumToAlph(inString As String) As String
Dim outString As String
Dim i As Integer
Dim inLong As Long
Dim MaxDigits As Byte

inLong = CLng(inString)

MaxDigits = 10

ReDim Vals(MaxDigits)

' Identify character code for last (right-most) result digit or special case/single-digit results
If inLong >= 27 Then
    inLong = inLong - 1
    Vals(0) = inLong Mod 26
    inLong = Int(inLong / 26)
Else
    Vals(0) = inLong Mod 27
    inLong = Int(inLong / 27)
End If

' Identify character code for all other result digits
For i = 1 To MaxDigits
    Vals(i) = inLong Mod 27
    inLong = Int(inLong / 27)
Next i

' Append character to output string, except for last result digit
For i = MaxDigits To 1 Step -1
    If Vals(i) > 0 Then
        outString = outString & Chr(Vals(i) + 64)
    ElseIf Len(outString) > 0 Then
        outString = outString & "A"
    End If
Next i

If Len(outString) > 0 Then ' Adjust multiple-digit result to account for 0 base when value > 26
    Vals(0) = Vals(0) + 1
End If

outString = outString & Chr(Vals(0) + 64) ' Append final character to output string

NumToAlph = outString

End Function

I've tested this up to 1,000,000,000, which comes out as BRJAHKL (longs don't get any more significant digits...). There might be some improvements that can be bade in the for loops (Why loop twice? Because it's what I could manage for now...), but I like this pretty well the way it is.

\$\endgroup\$
0
\$\begingroup\$

I realize this is an old post, but no one mentioned this.

' validate input; no negative numbers or decimals
If asLong < 0 Or InStr(inString, ".") Then
    MsgBox "Cannot process negative values or numbers that include a decimal point."
    NumericToAlpha = ""
    Exit Function
End If

VB6 doesn't allow for short circuiting, so you might as well break this into two separate If statements. Both conditions are always being checked anyway. This has an added benefit of giving a more specific error message to the end user as well.

To keep the extra code from cluttering up your main function, I recommend creating a private IsValidInput that returns a boolean.

Private Function IsValidInput(str As String) As Boolean
    ' validate input; no negative numbers or decimals
    If asLong < 0 Then
         MsgBox "Cannot process negative values."
         Exit Function
    End If

    If InStr(inString, ".") Then
        MsgBox "Cannot process numbers that include a decimal point."
        Exit Function
    End If

    ' If we've gotten here, input is valid
    IsValidInput = True

End Function

Personally, I would raise errors rather than show message boxes, but I've not gone through the trouble here, but you should consider it. Especially if your function resides in a class.


P.S. You don't use Hungarian notation anywhere except the intDiv variable. Drop the int part and give that variable a more meaningful name.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.