change alpha value to numeric (Word 2002 VBA)

Topic

New Reply

I have a text box that asks what the starting number should be for a set of exhibits. I have everything working fine with plain 1,2,3 numbers. My problem is that there is also an option to number the exhibits alphabetically. If you enter the numeric value of the letter you want the series to start at, everything works fine (3 for c, 30 for dd). However, if a user wants to start the series at C, they need to be able to type C in the text box instead of 3 and have the series start at C. Is there a way to interpret the text this way? Ascii values won’t work because they don’t go a-z and then aa-zz. Any ideas???

Reply | Quote

Replies

How about:

Function AlphabetPosition(strLetter As String) As Integer
Select Case Len(strLetter)
    Case 0
        AlphabetPosition = 0
    Case Else
        AlphabetPosition = InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
            Left(strLetter, 1), vbTextCompare)
End Select
End Function
 
Sub Test_AlphabetPosition()
MsgBox AlphabetPosition(InputBox("Give me a letter, any letter"))
End Sub

It is not case sensitive. I don’t know if this is important for your application.

Reply | Quote

I usually use the syntax
Asc(Lcase(strLetter)) – Asc(“a”) + 1
to convert a letter to a number.

And if you don’t care about the readability of your code this can be simplified to
Asc(Lcase(strLetter)) – 96

I have no idea which method is more efficient, but I thought you might like to see an alternate approach…

StuartR

Reply | Quote

I usually use the syntax
Asc(Lcase(strLetter)) – Asc(“a”) + 1
to convert a letter to a number.

And if you don’t care about the readability of your code this can be simplified to
Asc(Lcase(strLetter)) – 96

I have no idea which method is more efficient, but I thought you might like to see an alternate approach…

StuartR

Reply | Quote

How about:

Function AlphabetPosition(strLetter As String) As Integer
Select Case Len(strLetter)
    Case 0
        AlphabetPosition = 0
    Case Else
        AlphabetPosition = InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
            Left(strLetter, 1), vbTextCompare)
End Select
End Function
 
Sub Test_AlphabetPosition()
MsgBox AlphabetPosition(InputBox("Give me a letter, any letter"))
End Sub

It is not case sensitive. I don’t know if this is important for your application.

Reply | Quote

Ohhhh… you permit double letters. Okay, it gets a bit more complicated:

Function AlphabetPosition(strLetter As String) As Integer
Select Case Len(strLetter)
    Case 0
        AlphabetPosition = 0
    Case 1
        AlphabetPosition = InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
            strLetter, vbTextCompare)
    Case 2
        AlphabetPosition = (InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
            Mid(strLetter, 1, 1), vbTextCompare) * 26) + InStr(1, _
            "ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(strLetter, 2, 1), vbTextCompare)
    Case Else
        AlphabetPosition = 0
End Select
End Function
 
Sub Test_AlphabetPosition()
MsgBox AlphabetPosition(InputBox("Give me a letter (or two), any letter"))
End Sub

Does that do it??

Reply | Quote

As usual, you saved the day! Much Gracias!

Reply | Quote

Ohhhh… you permit double letters. Okay, it gets a bit more complicated:

Function AlphabetPosition(strLetter As String) As Integer
Select Case Len(strLetter)
    Case 0
        AlphabetPosition = 0
    Case 1
        AlphabetPosition = InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
            strLetter, vbTextCompare)
    Case 2
        AlphabetPosition = (InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
            Mid(strLetter, 1, 1), vbTextCompare) * 26) + InStr(1, _
            "ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(strLetter, 2, 1), vbTextCompare)
    Case Else
        AlphabetPosition = 0
End Select
End Function
 
Sub Test_AlphabetPosition()
MsgBox AlphabetPosition(InputBox("Give me a letter (or two), any letter"))
End Sub

Does that do it??

Reply | Quote