col2n-列記号から列番号に変換 - mytips@wiki

col2n-列記号から列番号に変換

Public Function col2n(c As String) As Long
'-- 列記号を列番号に変換して返す
' エラーのとき戻り値は０
'
   Dim c1 As String
   Dim c2 As String
   Dim r As Integer
   
   r = 0
   If Len(c) > 0 Then
       c1 = StrConv(Mid(c, 1, 1), vbUpperCase)
       If "A" <= c1 And c1 <= "Z" Then
           If Len(c) > 1 And ("A" <= c1 And c1 <= "I") Then
               ' ２文字目
               c2 = StrConv(Mid(c, 2, 1), vbUpperCase)
               If "A" <= c2 And c2 <= "Z" Then
                   '-- NOP
               Else
                   c2 = ""
               End If
           Else
               c2 = ""
           End If
           ' 変換
           If c2 <> "" Then
               r = (Asc(c1) - Asc("A") + 1) * 26
               r = r + (Asc(c2) - Asc("A") + 1)
           Else
               r = Asc(c1) - Asc("A") + 1
           End If
       End If
   End If
   col2n = r
End Function