【ExcelVBA】「列名のアルファベット⇒列番号／列番号⇒列名のアルファベット」相互変換する方法

この記事では、列名のアルファベットを列番号に、列番号を列名のアルファベットに変換する方法についてご説明します。

【動画】列名のアルファベットを列番号に、列番号を列名のアルファベットに変換する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

列名のアルファベットを列番号に、列番号を列名のアルファベットに変換しています。

マクロ作成の流れ

①列名のアルファベットを列番号に変換

②列番号を列名のアルファベットに変換

Excelファイルの例

今回は次のExcelファイルを作成しました。

変換元の値を入力するセルには「celCngFrm」という名前を、変換された値を出力するセルには「celCngTo」という名前を付けています。

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_changeVal_Click()

    Dim val As Integer      '数値
    Dim str As String       '文字列
        
    If Sheets("work").OptionButtons("opb_allmatch_cngNum").Value = 1 Then
    
        '「数値変換」のオプションボタンが選択されている場合
    
        If IsNumeric(Sheets("work").Range("celCngFrm").Value) = False Then
        
            '変換元のセルに列名にアルファベットが入力されている場合
        
            '変換元のセルの値を取得する
            str = Sheets("work").Range("celCngFrm").Value
            
            '数値を取得する
            Sheets("work").Range("celCngTo").Value = Sheets("work").Range(str & 1).Column
        
        Else
        
            '変換元のセルに数値が入力されている場合
            MsgBox "列名のアルファベットを入力してください"
        
        End If
    
    ElseIf Sheets("work").OptionButtons("opb_allmatch_cngAlp").Value = 1 Then
    
        '「アルファベット変換」のオプションボタンが選択されている場合
    
        If IsNumeric(Sheets("work").Range("celCngFrm").Value) Then
        
            '変換元のセルに数値(列番号)が入力されている場合
        
            '変換元のセルの値を取得する
            val = Sheets("work").Range("celCngFrm").Value
    
            '列名のアルファベットを取得する
            Sheets("work").Range("celCngTo").Value = Split(Sheets("work").Cells(1, val).Address, "$")(1)
        
        Else
        
            '変換元のセルに列名のアルファベットが入力されている場合
            MsgBox "数値を入力してください。"
        
        End If
    
    End If

End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは20行目です。

            '数値を取得する
            Sheets("work").Range("celCngTo").Value = Sheets("work").Range(str & 1).Column

20行目では、列名のアルファベットをRangeオブジェクトに指定しcolumnプロパティの値を取得しています。(変数strには列名のアルファベットが格納されています)

columnプロパティからは、列名のアルファベットに合致する列番号を取得することができます。

上記コードでは、列名のアルファベットと値「1」を結合してRangeオブジェクトに指定しています。

例えば列名のアルファベット「C」の列番号を取得したい場合は、「C」と数値の「1」を結合させてRangeオブジェクトに「C1」が指定されるので、columnプロパティから「3」を取得することができます。

注目すべきコード②

次に見て頂きたいのは41行目です。

            '列名のアルファベットを取得する
            Sheets("work").Range("celCngTo").Value = Split(Sheets("work").Cells(1, val).Address, "$")(1)

41行目では、列番号をCellsプロパティの第2引数に指定しています。(変数valには列番号が格納されています)

cellプロパティのAddressプロパティからはR1C1形式の参照を取得することができます。

例えばCellsプロパティの第2引数に、4という列番号を指定した場合は「$D$1」(R1C1形式)という値がAddressプロパティに設定されています。

今回は4という列番号を指定しているので「D」という文字が欲しいのですが、この「$D$1」という値から「D」という値を取得するには、今回Split関数を使います。

Split関数の第1引数には先ほどのAddressプロパティを、第2引数に「$」を指定することで、戻り値には「」「D」「1」の3つの値が格納された配列を返します。
(※「」はブランクを指します)

なお、「$」は区切り文字を指します。

「$D$1」という値に対して「$」を指定することで、Split関数は「$D$1」という値を「$」で区切って配列で返します。

ただし、ここでは配列自体を取得したいのではなく、「D」の値が格納されている「配列の要素2番目の値」が欲しいのです。

そこで、今回は下の画像の通りに(1)を指定します。

この(1)は、「2番目の要素の値を取得したい」という意味を指します。(1)を指定することで「配列の要素2番目の値」の「D」の値を取得することができます。

なお、1番目の要素の値を取得したいなら(0)を、3番目の要素の値を取得したいなら(2)を指定します。
(今回は(0)を指定したらブランクを、(2)を指定したら1を取得することができます)

以上で「D」という列名のアルファベットを取得することができます。

動作確認

①列名のアルファベットを列番号に変換

変換元の値を入力するセルには「D」を入力しています。

列名のアルファベット「D」に合致する列番号「4」が取得できました。

②列番号を列名のアルファベットに変換

変換元の値を入力するセルには「4」を入力しています。

列番号「4」に合致する列名のアルファベット「D」が取得できました。

最後に

本記事では、列名のアルファベットを列番号に、列番号を列名のアルファベットに変換する方法についてご説明します。

列名のアルファベットから合致する列番号が欲しい時や、列番号に合致する列名のアルファベットが欲しいときは是非参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら