日本語入力ソフトとVBAの覚え書き

・各種日本語入力ソフトの辞書解説 ・ちょっと楽になるExcel VBA集

(Excel VBA)選択範囲の数式の参照形式(※参照タイプとも)を絶対参照と相対参照で相互に変換する

f:id:dz_dzone:20170814233145j:plain

参照形式を変換するメソッド

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


以上。