Excel VBA 列のアルファベットと列番号の変換方法

今回は列のアルファベットと列番号の変換についてご説明します。

また、中にはアルファベット表記しか対応が難しい事もあると思うので、アルファベット表記で動的に範囲を指定する方法も併せてご説明します。

今回はスパークラインの機能を追加するケースでご説明したいと思います。

スパークラインを例にしたのは、スパークラインの範囲指定が「A1」形式しか指定できないためです。

列の基本的な操作は「Excel VBA 列の選択、取得、追加(挿入)、削除」をご覧下さい。

1.列番号をアルファベットに変換

まずは、列番号をアルファベットに変換するコードです。

次のコードは列100番目のアルファベットを取得しています。

Option Explicit

Sub Sample1()

    Dim ColNo As Long

    ColNo = 100'ここで列番号を指定
    MsgBox ConToLet(ColNo)

End Sub

Function ConToLet(ByVal ColNo As Integer) As String

    Dim iAlpha      As Integer
    Dim iRemainder  As Integer
    
    iAlpha = Int((ColNo - 1) / 26)
    iRemainder = ColNo - (iAlpha * 26)
    
    If iAlpha > 0 Then
    
        ConToLet = Chr(iAlpha + 64)
    
    End If
    
    If iRemainder > 0 Then
    
        ConToLet = ConToLet & Chr(iRemainder + 64)

    End If

End Function

2.アルファベットを列番号に変換

次は、アルファベットを列番号に変換するコードです。

B列を列番号に変換しています。

Sub Sample2()

    Dim GetCol  As String '出力列
    Dim ColNo   As Long
    
    GetCol = "B"

    ColNo = Range(GetCol & "1").Column
    MsgBox ColNo
    
End Sub

3.アルファベットを動的に範囲指定する方法

今回はスパークライン機能を追加するケースで動的に処理したいと思います。

スパークラインのデータソース(SourceData)部分は「A1形式」で記載する必要があります。

次のような表を用意したのでH列にスパークラインを追加したいと思います。

この表が行列に増減すると仮定したコードを紹介します。

範囲固定のコード

まずは固定の場合のコードです。

Sub Sample3()

    Range("H2:H6").SparklineGroups.Add _
    Type:=xlSparkLine, SourceData:="B2:G6"
    
End Sub

H列にスパークラインが追加されました。

動的に範囲指定するコード

次は範囲を動的に対応できるようにしたコードです。

上記で説明した「Function ConToLet」で最終列の番号をアルファベットに変更します。

次のコードで行列が動的に変動しても、スパークラインは範囲分追加されます。

今回はスタートのB2は固定で記載してます。

Sub Sample4()

    Dim MaxRow As Long
    Dim MaxCol As Long
    Dim EndCol As String 'アルファベット変換
    Dim OutCol As String 'スパークライン出力列
    
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列を取得
    
    EndCol = ConToLet(MaxCol - 1) '範囲の最終列をアルファベットに変換
    OutCol = ConToLet(MaxCol) 'スパークライン出力列をアルファベットに変換
    
    Range(OutCol & "2:" & OutCol & MaxRow).SparklineGroups.Add _
    Type:=xlSparkLine, SourceData:="B2:" & EndCol & MaxRow
    
End Sub

4.サンプルファイルのダウンロード

こちらから今回使用したサンプルファイルをダウンロード可能です。