KFCは何列目か

N列は、Aから数えて何列目か?
Nぐらいであればアルファベットの歌を歌いながら、指折り数えることもできます。
しかし、例えばKFC列の列番号を知りたい場合、指折り数えては間に合いません。

これがワークシート上ならば、Column 関数で簡単に列番号を求めることが出来ます。

f:id:Infoment:20180707072550p:plain

一方、VBA では、アルファベットから列番号を直接求めることはできないようです。
そこで、今回も必要に迫られて、ユーザー定義関数を作成してみました。

Function GetColumnNumber(str As String) As Long

    Dim i       As Long     ' 桁数
    Dim N(3)    As Long     ' 各桁が表す数字
    Dim myReg   As Object   ' 正規表現:引数確認用

        str = StrConv(str, vbUpperCase + vbNarrow)
        
    ' アルファベット1~3文字以外の場合、「-1」を返す。
        Set myReg = CreateObject("VBScript.RegExp")
        myReg.Pattern = "^[A-Z]{1,3}$"
        
        If myReg.test(str) = False Then
            GetColumnNumber = -1
            Exit Function
        End If
    
    ' 桁数に応じて行番号を計算。    
        i = Len(str)    
        N(1) = Asc(Right(str, 1)) - 64
        N(2) = -(i >= 2) * 26 * (Asc(Mid(str, i - 1, 1)) - 64)
        N(3) = -(i = 3) * 26 ^ 2 * (Asc(Left(str, 1)) - 64)
        
        GetColumnNumber = WorksheetFunction.Sum(N)
    
    ' 最大列番号を超える場合、「-2」を返す。    
        If GetColumnNumber >= Columns.Count Then
            GetColumnNumber = -2
        End If
        
End Function

今回のポイントは、以下の通りです。

  • 正規表現を用いて、変換する文字を確認
  • 条件分岐を用いない、桁数に応じた計算

例えば3桁の場合、( i = 3 )は True となり、-1 が返ります。
3桁以外の場合は False が返るため 0 が返り、3桁目に関する計算結果は 0 になる仕掛です。

f:id:Infoment:20180707075017p:plain

無事に、列番号を取得することが出来ました。
と、ここまで作りこんでおいて、ふと気づきました。あれ、ひょっとして・・・

Function GetColumnNumber2(str As String) As Long
    GetColumnNumber2 = Cells(1, str).Column
End Function

f:id:Infoment:20180707075408p:plain

これで充分でした。今回の試み、「グダグダ感」満載で終了です(残念!)。