Converting a Number to Alphanumeric in VBA

Question

I am using the below code as a portion of a much larger code to convert a number to its alphanumeric equal i.e 1=A, 2=B, etc. While this does work it is crazy long code and I am sure there is a better way to do this and was hoping you guys could assist.

Sub Convert()

Time = Range("A1")

If Time = 1 Then
    E = "A"
Else
If Time = 2 Then
    E = "B"
Else
If Time = 3 Then
    E = "C"
Else
If Time = 4 Then
    E = "D"
Else
If Time = 5 Then
    E = "E"
Else
If Time = 6 Then
    E = "F"
Else
If Time = 7 Then
    E = "G"
Else
If Time = 8 Then
    E = "H"
Else
If Time = 9 Then
    E = "I"
Else
If Time = 10 Then
    E = "J"
Else
If Time = 11 Then
    E = "K"
Else
If Time = 12 Then
    E = "L"
Else
If Time = 13 Then
    E = "M"
Else
If Time = 14 Then
    E = "N"
Else
If Time = 15 Then
    E = "O"
Else
If Time = 16 Then
    E = "P"
Else
If Time = 17 Then
    E = "Q"
Else
If Time = 18 Then
    E = "R"
Else
If Time = 19 Then
    E = "S"
Else
If Time = 20 Then
    E = "T"
Else
If Time = 21 Then
    E = "U"
Else
If Time = 22 Then
    E = "V"
Else
If Time = 23 Then
    E = "W"
Else
If Time = 24 Then
    E = "X"
Else
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

MsgBox E



End Sub

A note on your code: To avoid multiple `If` and `End If` statements you could use the `If ... elseif ... elseif ... End if` syntax. — tospig, Jan 19 '15 at 00:08
Or alternatively, the `Select Case`, something like `Select Case Time`, `Case 1: E = "A"`, `Case 2: E = "B"`, etc. until `End Select`. — Matteo NNZ, Jan 19 '15 at 00:09

score 2 · Accepted Answer · answered Jan 18 '15 at 23:33

2

Easier and more solid way is to use what Excel already offers, which means "every letter is associated to a number in the ranges":

Public Function numberToLetter(ByVal myNumber As Integer) As String
    numberToLetter = Split(Cells(1, myNumber).Address, "$")(1)
End Function

answered Jan 18 '15 at 23:33

Matteo NNZ

11,930
12
52
89

Nice and succinct; I wasn't aware of this method but will be using it from now on – tospig Jan 18 '15 at 23:37
Your's a nice algorithm, but this will just give you more certainty because it plays on the fact that "every column can be expressed as a letter (A, B, C...) or a number (1,2,3...) – Matteo NNZ Jan 18 '15 at 23:41
Matteo to the rescue more than once in a single day – Chad Portman Jan 19 '15 at 00:07

tospig · Answer 2 · 2015-01-18T23:35:05.683

1

This is one way.

Sub numberToLetter()

    Dim Time As Integer
    Dim E As String

    Time = Range("A1")

    If Time > 26 Then
        E = Chr(Int((Time - 1) / 26) + 64) & Chr(((Time - 1) Mod 26) + 65)
    Else
        E = Chr(Time + 64)
    End If

End Sub

Notes

Chr returns a character based on the ASCII value

edited Jan 18 '15 at 23:35

answered Jan 18 '15 at 23:13

tospig

7,762
14
40
79

Interesting thanks. Would have never figured that out no idea where those numbers came from or what they mean – Chad Portman Jan 18 '15 at 23:29
@ChadPortman, I suggest you to read the link that tospig added to understand better what Chr() does. – Matteo NNZ Jan 18 '15 at 23:40
Will do sir I still have a ton to learn about VBA. I can run circles around most people. But then I start talking about what VBA could do and I get assigned task that are above my knowledge so you guys help me out :) – Chad Portman Jan 19 '15 at 00:35

Converting a Number to Alphanumeric in VBA

2 Answers2