Excelマクロ・VBA 絶対参照を一括で設定・解除する方法

2020年5月5日

絶対参照の設定解除は通常、セル入力中でしか行えず一括での設定・解除は不可能です。

しかしマクロ・VBAを使用すれば、まとめて設定・解除が可能になります。

この記事ではその方法を紹介します。

サンプルファイル

使用方法

※セキュリティの警告が出る場合は有効化をお願いします。(解除方法↓)

 また「開発」メニューがない場合は追加をお願いします。

サンプルファイルに登録されているマクロを利用します。

参照の形式を変えたいセル範囲を選択し

「開発」メニューより「マクロ」を選択します。

「開発」メニューより「マクロ」を選択します。

変換方法が4つ提示されるため適切なものを選び「実行」を押下します。

すると選択した通りの変換が実行されます。

R1C1形式でも問題なく使用可能です。

マクロウインドウから変換方法を選択

コードサンプル

コードは下記のとおりです。

標準モジュールを追加してペーストすれば

任意のブックで使用可能です。

Option Explicit

Sub 選択範囲を行列とも絶対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlAbsolute)
End Sub

Sub 選択範囲を行のみ絶対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlAbsRowRelColumn)
End Sub

Sub 選択範囲を列のみ絶対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlRelRowAbsColumn)
End Sub

Sub 選択範囲を行列とも相対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlRelative)
End Sub

Private Sub 指定範囲を指定参照形式に変換(ByVal 指定範囲 As Range, ByVal 参照形式 As XlReferenceType)
    
    Dim 変換対象セル As Range

    ' 選択範囲を全セルを操作
    For Each 変換対象セル In 指定範囲
        
        'セルが数式の場合のみ変換を行う
        If 変換対象セル.HasFormula Then

            変換対象セル.Formula = _
                Application.ConvertFormula( _
                    Formula:=変換対象セル.Formula, _
                    FromReferenceStyle:=xlA1, _
                    ToReferenceStyle:=xlA1, _
                    ToAbsolute:=参照形式)
        End If
        
    Next 変換対象セル

End Sub

「指定範囲を指定参照形式に変換」という共通関数を用意し

それぞれのマクロから選択範囲と

参照形式を引数として与えています。

そして全てのセルに対して

数式セルであれば形式変換をかけています。

(数式セルでないと不正な動作をするため)

関連記事

マクロ・VBAの学習・活用方法の記事一覧

数式がずれないようにコピー(オートフィル)する方法

連続データを効率的にセル入力する方法(オートフィルとフラッシュフィル)