VBA amacoda

セル参照のA1形式・R1C1形式の表示を切り替えるマクロ

Toggle Reference Style A1 Style To/From R1C1 Style

 

はじめに

Excelの参照形式には２種類ありますが、エクセルの標準機能を使うときは「A1形式」を使い、VBAプログラムを書くときは「R1C1形式」を使います。

以下、Excel 2016の例で説明します。

「A1形式」とは

次のような列見出しで表現される参照形式をいいます。

行見出しは「1～1048576」の数字で表現され、列見出しは「A～XFD」の英字で表現されます。直観的ですが、プログラム作成上、柔軟性に欠ける表現形式です。

「R1C1形式」とは

次のような列見出しで表現される参照形式をいいます。

行見出しは「A1形式」と同じく、「1～1048576」の数字で表現され、列見出しは「A1形式」とは異なり、「1～16384」の数字で表現され、プログラムを書かない方にはなじみのない表現形式ですが、プログラム作成上、非常に柔軟性に富んだ表現形式です。

カンタンな比較例

次のようなプログラムを書くときには、「R1C1形式」のほうが柔軟に、カンタンに書くことができます。

Sub S_Sample1()
    Dim i As Long
    
    For i = 1 To 26
        Cells(1, i).Value = i
    Next i
End Sub

「A列」から「Z列」までの1行目のセルにそれぞれの列番号を書く、何ということもないプログラムですが、このカンタンなプログラムを「A1」形式にこだわって書くと、次のようなメンドウなプログラムになります。

Sub S_Sample2()
    Dim i As Long
    
    For i = 65 To 90
        Range(Chr(i) & "1").Value = i - 64
    Next i
End Sub

メンドウな点は4点もあります。1つは、「A」から「Z」までの文字列の文字コードを調べなければならないこと。2つ目は、「Chr関数」を使って数値を文字列に変換しなければならないこと。3つ目は、「Rangeプロパティ」の特殊な使い方をしなければならないこと。4つ目は、列番号を導き出すために、文字コードの数値から64を引かなればならないことです。

この4点は、いずれも「R1C1形式」を用いていれば、不必要な点です。

しかも、「A1形式」では、列見出しは「A～XFD」まであります。「AA～XFD」列を数値で表すには、さらにメンドウな処理が必要です。

そんなばかばかしいことはやめましょう。

「R1C1形式」ではこのような点を回避し、カンタンにプログラムを書けます。これが「R1C1形式」の利便性であり、柔軟性です。

 

セル参照のA1形式・R1C1形式の表示を切り替えるマクロのソースコード

Sub S_ToggleA1R1C1()
    With Application
        If .ReferenceStyle = xlA1 Then
            .ReferenceStyle = xlR1C1
        ElseIf .ReferenceStyle = xlR1C1 Then
            .ReferenceStyle = xlA1
        End If
    End With
End Sub

 

セル参照のA1形式・R1C1形式の表示を切り替えるマクロの解説

オプション画面から「A1形式」と「R1C1形式」を切り替えるのはメンドウです。

そこで、プログラムを書き、ボタンに登録し、クリック一発で切り替えらえるようにします。

「ReferenceStyleプロパティ」は、エクセルのセル参照および行列見出しを「A1形式」または「R1C1形式」のどちらで表示するかを取得したり設定したりします。

取得したり設定したりできる定数は、「XlReferenceStyle列挙体」のxlA1またはxlR1C1です。

xlA1が設定されている場合は、「A1形式」で行列見出しが表現され、xlR1C1が設定されている場合は、「R1C1形式」で行列見出しが表現されます。

このマクロではReferenceStyleプロパティにxlA1が設定されているときはxlR1C1を設定し、xlR1C1が設定されているときはxlA1を設定します。

 

重要なポイント

このマクロは、個人用マクロブックに記述するのがベストです。

他人様に配布することでもない限りは、アドインにする必要はありません。

このブログでは、VBA超入門 – Excel VBA ミニマクロサンプル集という記事を紹介しましたが、それらと同様に、個人用マクロブックに登録することをおススメします。

このように、個人用マクロブックに記述しておけば、毎回起動しますし、アドインのように登録の手間もありません。

 

ボタンへの登録

画面の操作

「ファイル」メニューの「オプション」を選択します。

「クイックアクセスツールバー」をクリックします。

「コマンドの選択」メニューから「マクロ」を選択します。

個人用マクロブックに登録されているマクロの中から今回作成したPERSONAL.XLSB!S_ToggleA1R1C1を選択します。

「追加」ボタンを押すと、「クイックアクセスツールバー」に登録されます。
「変更」ボタンを押すと、「表示名」とアイコンを変更することができます。

「クイックアクセスツールバー」に登録されています。

これで、ボタンをクリックするだけで、「A1形式」と「R1C1形式」を自由自在に入れ替えて、行列見出しを変更することができます。

 

おわりに

VBAプログラムを書くうえで、「R1C1形式」はとても役に立ちます。「A1形式」もエクセルの標準機能を使ううえではとても役に立ちます。したがって、「R1C1形式」も「A1形式」も両方マスターしましょう。

コメントを残す

RECOMMENDこちらの記事も人気です。