兼業主夫 ときどき 指揮者 のち ギーク?

2011-08-19

列名から列Noを取得して返すFunctionを作ってみた

 「列Noから列名を取得して返すFunctionを作ってみた」に続いて、逆バージョンも作ってみた。

MsgBox getColumnNoFromColumnName("A")

とすると、「1」と表示される。

 引数として"Z"を与えれば「26」が返ってくるし、"AA"を与えれば「27」が返ってくる。

 ちなみにこのFunctionを作ろうと思ったきっかけは、

  • 管理画面では「列名」を指定したい
  • 管理画面で指定した列の○列右を指定したい

という事をしたかったから。

 そういうやり方が正しいのかどうか特に自信がある訳ではなく、もっと別なやり方もあるような気もするのだが、まぁ成長の過程の記録として記しておくことにする。

 誰かが使うかもしれないし。

'列名から列Noを取得して返す
Public Function getColumnNoFromColumnName(columnName As String) As Long
Dim i   As Integer
Dim tmp As Long

    For i = 1 To Len(columnName)
        tmp = tmp + ((Asc(Mid(columnName, i, 1)) - 64) * (26 ^ (Len(columnName) - i)))
    Next i
    
    getColumnNoFromColumnName = tmp
End Function

列Noから列名を取得して返すFunctionを作ってみた

 出先ではVB6とExcel VBAを駆使している私。

 いろいろあって「クリックしたセルの10列右」とかやりたい時がある。

 現在の列 = Activecell.Column

とやれば、アクティブなのがA列なら「1」が返ってくる。

 なので、例えば

 現在の列の10列右 = chr(Activecell.Column + 64 + 10)

とかやると、「K」が返ってくる。(64を足しているのは、Chr(65)が「A」だから)

 これはこれで良いのだが、Excelは「Z」列の右が「AA」列。

 仮にZ列のどこかを選択した状態で

 Z列の1列右 = chr(Activecell.Column + 64 + 1)

というのを実行すると、「[」が返ってきてしまう。

 文字コードの順番としてそうなっているのだから仕方ないと言えば仕方ない。

 そこで、列の番号を与えたらその列名を返してくれるFunctionを作ってみた。

 MsgBox getColumnNameFromColumnNo(1)

とやれば「A」と表示されるし、

 MsgBox getColumnNameFromColumnNo(Activecell.Column + 1)

とやれば、現在の列の1列右の列名を返してくれる。

 現在Z列がアクティブになっているなら、返ってくるのはもちろん「AA」だ。

 Excel2002まで対応したものは既に作成済みだったのだが、ここに書くにあたって手持ちのExcel2010の最大列「XFD」まで対応できるように作り直してみた。

 これが最善の方法とは思わないが、コピペで使いたい人もいるだろうから実用第一で置いておく事にする。

'列Noから列名を取得して返す
Public Function getColumnNameFromColumnNo(columnNo As Long) As String
Dim tmp As String
Dim i   As Long
Dim j   As Long
Dim k   As Long

    i = (columnNo - 1) \ 26
    If i > 0 Then
        k = (i - 1) \ 26
        If k > 0 Then
            tmp = Chr(64 + k)
            i = i Mod 26
            If i = 0 Then i = 26
        End If
        
        tmp = tmp & Chr(64 + i)
    End If
    j = columnNo Mod 26
    If j = 0 Then
        j = 26
    End If

    getColumnNameFromColumnNo = tmp & Chr(64 + j)
End Function