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