Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston, Massachusetts, USA
    Posts
    205
    Thanks
    0
    Thanked 0 Times in 0 Posts

    change alpha value to numeric (Word 2002 VBA)

    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???

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: change alpha value to numeric (Word 2002 VBA)

    How about:

    <pre>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</pre>

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

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: change alpha value to numeric (Word 2002 VBA)

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

    <pre>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</pre>

    Does that do it??

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: change alpha value to numeric (Word 2002 VBA)

    I usually use the syntax
    <font face="Georgia">Asc(Lcase(strLetter)) - Asc("a") + 1</font face=georgia>
    to convert a letter to a number.

    And if you don't care about the readability of your code this can be simplified to
    <font face="Georgia">Asc(Lcase(strLetter)) - 96</font face=georgia>


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

    StuartR

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston, Massachusetts, USA
    Posts
    205
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change alpha value to numeric (Word 2002 VBA)

    As usual, you saved the day! Much Gracias!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •