【VBA】指定セルの列番号を列名（英文字）に変換してアドレスを取得する

こんにちは、Ryoです。
ExcelVBAで処理する際に行や列の番号を利用することが多いですが、列の場合は列番号ではなく列名を示す英文字を取得したいことも用途により発生すると思います。

今回は列番号を取得し列を示す英文字（アルファベット）に変換する方法について書いてみたいと思います。

1.サンプル概要

列番号をアルファベットに置換する方法については、MicroSoftサポートに記載があったので参考にしています（こちら）が、その内容では～ZZ列までは良いものの、AAA以降の列では計算値が合わなくなり正しく表示されない列が出てきます。

その為、考え方をベースにしてAAAから最終列のXFDまで正しく表示されるよう変更し関数としています。

サンプルではアクティブセル（選択しているセル）の列番号を取得し、上述の変換する関数で英文字に変換し、行番号と合わせてセルアドレスを表示するものです。

≪A-Z列≫

≪AA-ZZ列≫

≪AAA-XFD列≫

このように選択したセルの列番号から列名のアルファベット文字を取得し、行番号と連結させて表示されます。本サンプルの動作を確認する為に列A～XFDまでの16384列全てに対し、処理結果を表示させて確認しましたが、Excel2010,2016共に問題なく表示出来ていますので、大丈夫だと思います。

2.サンプルコード

◆実行側サンプルコード

Sub Sample1()

   Dim Col_NumA As Long
   Dim Col_Str As String
    
   'アクティブセルの列番号を取得
   Col_NumA = ActiveCell.Column
   'ConvertColumn関数処理
   Col_Str = ConvertColumn(Col_NumA)

   'アクティブセルアドレスを相対参照形式で取得し、処理結果と比較（検証用）
   If InStr(ActiveCell.Address(RowAbsolute:=False, _
   ColumnAbsolute:=False), Col_Str) = 1 Then
        MsgBox "アクティブセル列番号は" & Col_NumA & "," & vbCrLf & _
        "セルは[" & Col_Str & ActiveCell.Row & "]を選択しています。", vbInformation
     Else
        '処理結果とセルアドレスの列を示す文字が不整合の場合は「Error」を表示
        MsgBox "Error!", vbCritical
   End If
   
End Sub

先ず実行側のサンプルコードですが、ActiveCell.Columnで選択しているセルの列番号を取得し、ConvertColumn(Col_NumA)関数へ引き渡します。その後処理されて変換された列を示す文字列が返ってきますので、セルアドレス(相対参照形式）を取得し返ってきた文字列が含まれることをInStrを使って確認し、OKであればメッセージボックスで表示する流れです。

◆列名変換用関数サンプルコード

'指定セルの列番号から列を示す英文字に変換する関数
Function ConvertColumn(Col_NumA As Long) As String

  Dim Col_NumB, Alpha, Alpha_T, Alpha_W As Long
  Dim Col_1, Col_2, Col_3 As Integer
  
  'アルファベット数（A～Z）
  Alpha = 26
  'アルファベット2文字(AA～ZZ)計算用
  Alpha_W = 26 ^ 2
  'アルファベット3文字(AAA～WZZ）計算用
  Alpha_T = Alpha + Alpha_W
  
  Select Case Col_NumA
     '列番号26以下の処理(A～Z）
     Case Is <= 26
       ConvertColumn = Chr(ActiveCell.Column + 64)
       
     '列番号27～702までの処理(AA～ZZ）
     Case Is <= 702
　　　　Col_1 = Int((Col_NumA - 1) / Alpha)
　　　　Col_2 = Col_NumA - (Col_1 * Alpha) 
　　　　ConvertColumn = Chr(Col_1 + 64) & Chr(Col_2 + 64) 

　　　'列番号703以降の処理（AAA～XFD最終列） 
　 　Case Is > 702
       Col_1 = Application.WorksheetFunction.RoundUp _
       ((Col_NumA - Alpha_T) / Alpha_W, 0)
       Col_NumB = Col_NumA - (Col_1 * Alpha_W)
       Col_2 = Int((Col_NumB - 1) / Alpha)
       Col_3 = Col_NumB - (Col_2 * Alpha)
       ConvertColumn = Chr(Col_1 + 64) & Chr(Col_2 + 64) & Chr(Col_3 + 64)
   End Select

End Function

VBAに限らず汎用的に使えそうなものは関数として自作出来るのがプログラミングとして楽しい部分だなと思いますね。計算の基本的な考え方は冒頭に紹介したMicroSoftサポートページに準じていますので、ここでは割愛します。

列A～Z,AA～ZZは特に問題ないと思いますが、AAAからの変換を確実にする為に実施しているのは、703列以降A群、B群、C群・・・と676列単位で切替わって行きますので、現在値の列番号からA～ZZまでの702列を減算、1群単位の676で除算し小数点以下をRoundUp関数で切り上げることで、Chr関数で文字が誤変換されないようにしています。

Chr関数は文字コードを文字列で返しますので、文字コード65から順にA,B,C・・・となっていきますので、それぞれ計算した結果を基に文字列に置換するということになります。

3.まとめ

取得した列番号を文字列に変換する上では、本サンプル以外にも色々な方法があるかと思いますが、一つの方法としてこのような手段もあると捉えていただければと思います。
関数化しておけば使い勝手も良いので、手軽に置換できますからお薦めです。

又、一番簡単なのはサンプル内に記述している相対参照形式でのセルアドレスを取得し、そこから文字情報だけを抜き取るという手段もありますが、処理過程によっても様々なので、用途に応じて柔軟に対応すべく選択肢の一つにはなれるかと思ってます。




以上、指定セルの列番号を列を示す英文字に変換する方法についてでした！
今回の記事が何かの参考になれば幸いです。

Ryo