The company I work for has a lot of legacy programs that I either use and/ or maintain. Some are written in VBA, some in vbScript, and some on VB.net. I am, overtime, cleaning up a lot of the code. One thing I am trying to clean up now is in this vb.net application, it modifies an excel workbook but instead of creating a function that would convert the letter to a number, they did this:

Const colB As Integer = 2
Const colD As Integer = 4
Const colH As Integer = 8
Const colI As Integer = 9
Const colJ As Integer = 10
Const colK As Integer = 11
Const colL As Integer = 12
Const colM As Integer = 13
Const colN As Integer = 14
Const colO As Integer = 15
Const colQ As Integer = 17
Const colR As Integer = 18
Const colT As Integer = 20
Const colV As Integer = 22
Const colW As Integer = 23
Const colX As Integer = 24
Const colY As Integer = 25
Const colZ As Integer = 26

and there are a lot more letters than that. I want to clean it up now and convert it to a function. In VBA, I know how to do it:

Function GetColNum(ByVal ColLetter As String) As Long
    
    GetColNum = Range(ColLetter & "1").Column
    
End Function

and I found how to convert a number to a letter in vb.net:

Private Function GetColLetter(columnNumber As Integer) As String

    Dim columnLetter As String = String.Empty
    Dim modulo As Integer

    While columnNumber > 0
        modulo = (columnNumber - 1) Mod 26
        columnLetter = Convert.ToChar(65 + modulo).ToString() & columnLetter
        columnNumber = CInt((columnNumber - modulo) / 26)
    End While

    Return columnLetter
End Function

I cannot figure out how to convert a letter to a number in vb.net though.

>Solution :

Maybe something like this?

Private Function GetColNum(columnLetter As String) As Integer

    Dim base As Integer = Asc("A") - 1
    Dim result As Integer = 0

    For Each c As Char In columnLetter.ToUpper()
        result = result * 26 + Asc(c) - base
    Next

    Return result

End Function