(Excel VBA)選択範囲の数式の参照形式(※参照タイプとも)を絶対参照と相対参照で相互に変換する
参照形式を変換するメソッド
Application.ConvertFormulaメソッド
構文
Application.ConvertFormula(Formula, FromReferenceStyle _ [,ToReferenceStyle] [,ToAbsolute] [,RelativeTo])
引数
Formula:変換する数式を含む文字列を指定。 必ず有効な数式を指定し、数式の先頭には等号 (=) を付ける 指定例: Formula:=Range("A1").Formula
FromReferenceStyle:変換元の参照スタイル。xlReferenceStyle列挙。指定必須
ToReferenceStyle :変換先の参照スタイル。xlReferenceStyle列挙。省略可
※省略した場合、変換元の参照形式と同じ
指定例: FromReferenceStyle:=xlA1
ToAbsolute:変換先の参照タイプを指定。xlReferenceType列挙。 相対指定と絶対指定の変換が可能。 これを"省略"すると参照スタイルの変換は行われない 指定例: ToAbsolute:=xlAbsolute
RelativeTo:R1C1形式の相対参照の基点となるRangeオブジェクトを指定する
xlReferenceStyle列挙
名前 | 値 | 説明 |
---|---|---|
xlA1 | 1 | A1形式の参照スタイル |
xlR1C1 | -4150 | R1C1形式の参照スタイル |
xlReferenceType列挙
名前 | 値 | 説明 |
---|---|---|
xlAbsolute | 1 | 行・列ともに絶対参照 |
xlAbsRowRelColumn | 2 | 行は絶対参照、列は相対参照 |
xlRelRowAbsColumn | 3 | 行は相対参照、列は絶対参照 |
xlRelative | 4 | 行・列ともに相対参照 |
サンプルコード
選択範囲の数式の相対参照を絶対参照に変換する
Public Sub RelToAbs() '**ReferenceStyle : from Relative to Absolute Dim myCell As Range For Each myCell In Selection myCell.Formula = Application.ConvertFormula(Formula:=myCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=xlAbsolute) Next myCell End Sub
選択範囲の数式の絶対参照を相対参照に変換する
Public Sub AbsToRel() '**ReferenceStyle : from Absolute to Relative Dim myCell As Range For Each myCell In Selection myCell.Formula = Application.ConvertFormula(Formula:=myCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=xlRelative) Next myCell End Sub
指定したA1形式のセル指定文字列をR1C1形式に変換するファンクション
Public Function A2RC1(ByVal myA1 As String) As String '**A1形式をR1C1形式に変換 ※列最大 XFD=16384, 行最大 1048576 On Error GoTo ErrorHundler A2RC1 = Application.ConvertFormula(Formula:=myA1, _ FromReferenceStyle:=xlA1, _ ToReferenceStyle:=xlR1C1, _ ToAbsolute:=xlAbsolute) Exit Function ErrorHundler: MsgBox "書式:A2RC1(ByVal myA1 As String) As String" & vbCrLf & _ "エラー内容:引数が間違っている。A1~XFD1048576の範囲", vbCritical Err.Clear A2RC1 = "#ERROR" End Function
サンプルの結果
’**VBEのイミディエイトウィンドウにて Debug.Print A2RC1("A1:Z10") R1C1:R10C26
どんなときにA1形式をR1C1形式に変換する?
旧形式のマクロ言語「Excel4Macro」でセルを指定するときにR1C1形式で指定しなくてはなりません。Excel4Macroはファイルを開かずに中のセル内容などにアクセスが可能で、データ抽出型マクロの速度を大幅に上げることが可能です。これを使うときに馴染みのあるA1形式をR1C1形式に変換するのです。
例:D:\myFolder\myBook1.xlsx の Sheet1, K4セル = R4C11 の値を取得する場合 Sub Sample1() Dim myValue myValue = ExecuteExcel4Macro("'D:\myFolder\[myBook1.xlsx]Sheet1'!R4C11") Debug.Print "myBook.xlsxのSheet1のK4セルの値は " & myValue End Sub Sub Sample2() Dim myValue, myFolder, myBook, mySheet, myItem myFolder = "D:\myFolder\" myBook = "myBook1.xlsx" mySheet = "Sheet1" myItem = "'" & myFolder & "[" & myBook & "]" & mySheet & "'!" & A2RC1("K4") myValue = ExecuteExcel4Macro(myItem) Debug.Print "myBook.xlsxのSheet1のK4セルの値は " & myValue End Sub
以上。