上級者のためのエクセルマクロ-参照ずれるんですけど・・・ - 初心者のためのExcelマクロ超入門（絶対できるVBA開発）

今回は閑話休題です。自分がやっているエクセルマクロ案件というか、まぁメインで作ってるんですが、分からなくて調べて、解決そして伝説へ・・・的なことです。初心者の方でも読み物くらいでお願いします。

たまにはこんな風に書いてみたいんだよ。うん。

問題はセルの参照がずれること

現在、エクセルのマクロでシミュレーションツールを作っています。簡単に言うと、データファイルが定期的（5秒とか10秒とか毎）に決まったフォルダに格納されています。（これはバッチ処理）で、エクセル側ではそのファイルを読み込み適当な計算を行った結果を出力し、CSVファイルにも出力したりします。
さらに、そのデータを決まった数だけ参照しグラフに表示するのです。そうするとデータの更新ごとにグラフを動かし、観測ツールのようなものを作成したい。

という要望だったので、読み込んで出力してグラフを標示するところまではいいのですが、肝心の計算が始まると最初に設定しておいた結果セルの参照がずれる・・・

具体的に言うとデータが出力されるところに

A
10.5
22.5
3.2
46.1

とあって、他のシートでこの値を参照し、グラフに表示していますが、データシートはセルの1番最初に挿入して新しい計算結果が出力されるのです。

A
挿入
10.5
22.5
3.2
46.1

↓

A
18.4(新しい結果）
10.5
22.5
3.2
46.1

グラフ自体はずっとA1～A4までを参照していたいのだけど・・・これがずれる・・・新しい結果が入ってくると参照しているデータエリアが・・・最初はA1～A4だったのに挿入するごとにA2～A5、A3～A6とずれて行ってしまうのです。

調べた結果

まぁ、どうしようか？とVBAで解決するのなら、挿入ごとに参照しているセルを毎回書き換える。とかも考えたのですが正直めんどいし、そんな処理を入れようものならパフォーマンスも落ちる。
どうにか、できないだろうか？固定のセルをずっと参照し続けるための方法は・・・あ、絶対参照とかも意味なかったです。どうやら、普通にセルを＝で参照する場合、挿入してもずっと元の参照を維持し続けるらしい。

グーグルさんに聞いてみる。

「VBA　式　参照　セル　挿入　ずれる」

ぽちっとな。

f:id:drumer2sh:20131120111943p:plain

おぉ・・・出た出た。最初の3つくらいを見て見たけど、いまいちよくわからない。けど、他のサイトを見て見たら。ここが参考になった。

http://www.excel.studio-kazu.jp/kw/20041224184538.html

なるほど、OFFSET関数か・・・確かに行けそうかもしれない。ということで今度はOFFSET関数をグーグル先生に聞いてみる。すると・・・

http://officetanaka.net/excel/function/function/offset.htm

やっぱり便利。Office TANAKA大先生。私は自分のことをエクセルマクロマスターだと思っていますが、全部を覚えてはいません。神じゃないんだから。わからないことは調べればいいのです。しかし、私は良く言っていますが、調べればいいじゃん。は無責任なのです。初心者には「調べ方」がわからないのです。調べればいいじゃんという人はその視点が抜けています。みんながみんな調べれば同じ情報にたどり着けると思っています。

大間違いです。

初心者は調べ方もわからないから初心者なのです。調べる方法もちゃんと教えてあげたり導いてあげる必要があると私は思っています。大切なことです。しかし、自分でいろいろと検索したり文献見たりしないで聞き続けても、初心者を抜け出すことはできません。調べ方もまず自分で調べてみて体験的に身について行くものなのです。

話を戻そう。

このOFFSET関数どうやら基準を設定して後は、そこから右にいくつ、下にいくつ。というように動いてくれる模様。
というわけでやってみました。

VBAで・・・


'参照を設定する
sht1.Cells(j, 2 * i + 27).Formula = "=" & shtP1.Range("Q19").Value & "!" & outColL & CStr(j - 1)
sht1.Cells(j, 2 * i + 27 + 1).Formula = "=" & shtP1.Range("Q19").Value & "!" & outColV & CStr(j - 1)

を


'参照を設定する
 '参照を設定する
sht1.Cells(j, 2 * i + 27).Formula = "=OFFSET(" & shtP1.Range("Q19").Value & "!$" & outColL & "$3," & CStr(j - 4) & ", 0)"
sht1.Cells(j, 2 * i + 27 + 1).Formula = "=OFFSET(" & shtP1.Range("Q19").Value & "!$" & outColV & "$3," & CStr(j - 4) & ", 0)"

としました。色々とぐちゃぐちゃ書いてるけど、要するに

=Sheet1!A4

と数式入力するところを、

=OFFSET(Sheet1!$A$1, 3, 0)

として基準のセルはずれないセルを指定するように入力するようにVBAで書いているだけ。
Formulaは数式セルの数式プロパティで、そこにOFFSET関数を文字列を連結しながら書いているだけです。

すると・・・・できたーーーー！
ちゃんとグラフの参照もずれないで、できています。計算される毎にグラフの形が変わっていく。。。素敵。

OFFSETは便利ですなぁ・・・ちなみにこのOFFSETですが、結合しているセルでもちゃんと移動してくれます。
普通なら1行目～3行目まで結合していると、For文でセルを行ごとに実行したくても２、３行目は結合されているので実行できないですが・・・OFFSET関数で1ずらすとちゃんと一気に4行目に移動してくれるので、一気に扱いやすくなります。

やべぇ、OFFFSETまじ便利だ。乱用すると読む側が大変なので、普段はあんまつかわないんだけどね。

今日は余談でした。次回はまた戻って、変数の型についてです。

今日はここまで！

かしこ