【Excel VBA】計算式を絶対参照で記録したい

質問

業務上Excelのマクロを頻繁に作っています。

マクロの自動記録で計算式を記録すると、R1C1形式の相対参照で記録されるのですが、
最終行等を変数で扱っている為、相対参照の場合ずれが生じてしまいます。
また、知識があまりない人から「コードが分かりづらい」と大変不評です。

いつも手動で地道に直しているのですが、如何せん面倒です。
記録する時点で、A1形式の絶対参照に設定する事は出来ないでしょうか？

使用バージョン:Excel 2000

回答No.3

xls88 · ベストアンサー

>いつも手動で地道に直しているのですが、如何せん面倒です。
計算式を一旦文字列にして記録し、VBEで「置換」作業すればどうでしょうか。
少しは楽になるかも知れません。

「マクロの記録」時に、例えば
=SUM(A1:B1)
と入力を終えた時点で、Enterで確定する前に、F2キーを押下げて編集モードにします。
文字カーソルを先頭（=記号の前）に移動し # を入力し、Enterで確定する。
以上で、記録されたコードは下記のようになります。

　　Range("C1").Select
　　ActiveCell.FormulaR1C1 = "#=SUM(A1:B1)"
　　Range("C2").Select

次に、VBEのコードウインドウで編集メニューの「置換」を使ってコードを加工します。
検索する文字列に、#=
置換後の文字列に、=
として、「すべて置換」ボタンをクリックすれば

　　ActiveCell.FormulaR1C1 = "=SUM(A1:B1)"

となります。
同じようにして、左辺の FormulaR1C1 を Formula に「置換」をします。

　　ActiveCell.Formula = "=SUM(A1:B1)"

となれば完成です。

意図しない部分が「置換」されないように十分注意して行う必要があります。
「置換」作業を行うとき、コード範囲を選択して、「すべて置換」すれば選択範囲内のみで「置換」が実行されます。

お礼

なるほど、コード上ありえない記号を用いて計算式を文字列として認識させ、その後置換処理で計算式に変換すればよいのですね。
問題は、その計算式のセルを使って展開する事が多いので、使用できる箇所が限られてしまう事ですが…。

とはいえ大変参考になりました。
有り難うございます。

回答No.4

xls88

>問題は、その計算式のセルを使って展開する事が多いので、
>使用できる箇所が限られてしまう事ですが…。
何故ですか？
使用できる箇所が限られてしまうことは無いと思いますよ。

回答No.2

mitarashi

関数を自動記録するとR1C1形式で記録されることを、最近知った者です。エクセルは大昔から使っていますが、関数入力を自動記録しようというニーズはありませんでしたね．．．
それはさておき、仕様だからどうしようもない様な気はしますが、エクセルの沼は深いので、どなたからか解決策が出てくるかもしれません。
VBAで式をA1からR1C1形式に変換（その逆も可）するコードを見つけましたので、参考URLに貼っておきます。ページのかなり下の方に、「参照形式の変換」という項があります。ご参考まで。

参考URL：

http://t_shun.at.infoseek.co.jp/My_Page/Excel-VBA/vba_page7.htm

お礼

やはり仕様ですか…。
色々と探してみたのですが、全く情報が見つかりませんでした。

参考URLを拝見しました。
確かにR1C1形式をA1形式に変換はできるようですが、コード自体を変換する様な処理は出来なさそうですね。

情報ありがとうございます。

回答No.1

n-jun

絶対参照と相対参照を切り替えるには？
http://www.geocities.jp/happy_ngi/YNxv9e96.html

こちらでしょうか。

補足

すみません。
当方絶対参照と相対参照の事を勘違いしておりました。
($A$1の事ではなく、A1形式の事を絶対参照だと思っていました)

私がやりたいのは、A1形式のマクロの記録です。