コード生成グループ「RC形式数式取得」の解説

概要

　書籍「Excel VBA開発を超効率化するプログラミングテクニック」の特典であるIkiKaiso2.xlamのリボン登録マクロの解説のための特設ページです。

出版社の書籍のページ⇒https://gihyo.jp/book/2024/978-4-297-14023-6

書籍サポートページ⇒https://gihyo.jp/book/2024/978-4-297-14023-6/support

リボン登録マクロ全体解説ページ⇒https://www.softex-celware.com/post/ikikaiso2

　本ページではIkiKaiso2.xlamの「コード生成」グループの「RC形式数式取得」ボタンの使い方を解説します。

解説

　用途

 　選択セルのR1C1形式の数式を取得する

　使い方

　動画で実際に使っている様子をまとめているので、下記をご覧ください

　動画では次の手順で「RC形式数式取得」を利用しています。

①：「J3」セルを選択する

②：「RC形式数式取得」をクリックして実行

③：「J3」セルのR1C1形式の数式が取得されてクリップボードに格納(イミディエイトウィンドウに確認表示)

④：「J3：J12」セルに数式を設定するプロシージャ（Test_説明用1）が途中まで出来ているので、この貼り付ける数式の部分に貼り付け

⑤：「Test_説明用1」を実行すると、「J3：J12」に数式が反映されている

　解説

　例えば上記の動画の様にJ3の数式をそれ以下も含めJ3:J12にコピーしたい場合は、手動でやる場合は「J3セルコピー ⇒ J3:J12セル選択 ⇒ 貼り付け（もしくは数式で貼り付け）」で可能ですが、貼り付け先のJ3:J12の範囲が毎度変化する場合はこれをVBAで自動化が必要になってきたりします。

　VBAでやる場合は最終行を取得して、「貼り付け先のJ3:J**の範囲」を自動取得して、「J3セルコピー → J3:J**セル選択 → 貼り付け（もしくは数式で貼り付け）」をマクロの記録などを利用してやれば事足りるのですが、この方法だと次のようなデメリットがあります。

• J3セルの数式が失われた場合に元情報が無くなって詰む

• セル範囲のコピー&ペーストは処理に時間がかかる(実際はそこまで影響しないので無視してよい)

　特に1番目のデメリットが大きいので、元のJ3セルの数式は壊されないように見えないセルにコピーしておくか、VBAコード内に記述しておいて呼び出せるようにしておく。などがより信頼性の高い実装となってきます。

　次に、普段Excelを使っていて見慣れている数式はA1形式だと思うのですが、こちらだと全部異なる数式が入っているように見えます。

　これを「R1C1参照形式」の表示に切り替えると、すべて同じ数式になります。

　ちなみに、「R1C1参照形式」への切り替えはExcelのオプションの下記画面にチェックを入れるか、もっと手軽に行うには「IkiKaiso1」タグ⇒「他」グループ⇒「列表示切替」で表示を切る変えるようにしてください。

　動画で作成したコードのスクリーンショットですが、このようにセル範囲に同じ数式を入れる場合は、R1C1形式（Rangeオブジェクト.FormulaR1C1プロパティ）で変更するようにしています。実はA1形式（Rangeオブジェクト.Formulaプロパティ）でもほとんど問題が無いのですが、行の挿入等による表の移動による影響を回避できないなどのほんのわずかなエラーが考えらるので、筆者はR1C1形式を採用しています。

　利用場面

　上記で説明している「特定のセル範囲に数式を複製する」処理ですが次のような場面で利用します。

　下記図のように、ヘッダーだけ用意してあって毎度異なる表が出力されるようなシートがあったとします。実際に表が二次元配列として出力された後で、年齢(J列)だけ後で数式を入力したいとします。

　実際に年齢(J列)の数式の反映だけをVBAコードで抜粋すると次のようになりますが、出力する二次元配列の行数に応じてセル範囲を変更して、同じ数式を反映するようにしています。

　ちなみに下記数式では基準セルから最終行番号を取得する汎用プロシージャGetEndRowを利用しています。

• GetEndRow → https://www.softex-celware.com/post/getendrow

　そのほか「RC形式取得」の便利なところ

　コピーしたいセルの数式にダブルクォーテーション「"」などが含まれているとします。

　例えば次のような数式の場合だと分かりやすいです。

　特定の列の値が空白ならそこの値は空白にするような処理で、Excelの数式で頻出の構築です。この数式をR1C1形式の表示にしてから、数式タブの数式をコピーしてVBAコード内に貼り付けます。（""で囲むことを忘れないようにしてください）

　「これでOKだ」と思って「Test_説明用3」を実行してみると、なんかおかしな結果となっていて。数式を見てみると元の数式ではなくなっているのが見て取れます。

　元の数式は

=IF(RC[-8]="","",ROUNDDOWN(YEARFRAC(RC[-3],TODAY()),0))

でしたがこれが、

=IF(RC[-8]=",",ROUNDDOWN(YEARFRAC(RC[-3],TODAY()),0))

と勝手に変更されています。

　こうならないようにするには元の数式の「"」を「""」に置き換えておく必要があり、本来VBAコードに貼り付けるべき必要のある数式は次のようになります。

"=IF(RC[-8]="""","""",ROUNDDOWN(YEARFRAC(RC[-3],TODAY()),0))"

　実はこのような補助処理も「RC形式取得」ボタンのマクロでは行っていて、その点では手動で行うより効率化できるように考えて処理を組み込んであります。