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