知らずに落ちると抜け出せなくなるVBAの落とし穴です。
Offsetプロパティを使ったセルの指定が行数とズレることにお悩みの方は、
この落とし穴に落ちていないかご確認ください。
発生する罠
罠の内容はタイトルの通りです。
A1:C3の3×3正方形がセル結合されているとしましょう。
この状態では、
「Range("A1").Offset(1)は、結合下端の1つ下であるA4セルを取得」
します。
それどころか、結合エリア全体からのOffsetである、
「Range("A1:C3").Offset(1)も同じく、『単独の』A4セルを取得」
します。
- A1⇒A2ではなく、結合エリアの外であるA4まで行ってしまう
- Offsetの特長である「元のRangeの大きさを維持」もできない
ということで、もはや使用に耐えるプロパティではありませんね。
解決策
↑の通り、この仕様を理解した上でどうこうという話ではないので、
対策は同時に使用しないことに限ります。
ひとまず、結合セルがあるシートでのOffsetの使用は控えましょう。
というと「セル結合は滅ぶべし」派が怒るかな？
「使用を控えるのはセル結合の方だろ！」って(笑)
どちらか一方を選べと言われれば、そりゃもちろんOffsetを選ぶのですが、
実務ではそうも言ってられませんからね。
「使用を制限するのが簡単＝自分が我慢するだけ」
なOffsetさんに割を食ってもらうことの方が多いです。
悪貨は良貨を駆逐するというやつですな┐(´∀｀)┌
セル結合の是非の話は長くなるので置いておき、
例えば
◇ データシート
◇ 領収書
という風にデータシートと印刷シートをしっかり分けて作り、
- データ側ではセル結合をしない
でくれよ頼むよホント - 帳票のセル指定にOffsetプロパティを使わない
と役割を明確にしてあげるだけでも、ある程度この罠は回避できると思います。
お互いの良さを生かして共存していきましょう。
今回の罠に特に気を付けたいのは、
こんな印刷シートに、
Const 金額のセルアドレス = "F9" Const ページの高さ = 12 For ページ番号 = 0 To ページ数 - 1 Range(金額のセルアドレス).Offset(ページの高さ * ページ番号) = ～～
こんな書き方ができるとすごく便利そうなのですが、
残念ながら危険すぎて使えません。
Cells(R金額セル + ページの高さ * ページ番号 , C金額セル)
で頑張りましょう。
もちろんこんな帳票を1シートに縦に並べていくのはやめて、
別のシートに複製していった方が理想なんですけどね。
実務では(ry
おまけ：結合セル周りのOffsetプロパティの細かい動き
結合セル.Offsetの良からぬ動きの細かい仕様を下記します。
が、最初に断っておきますが、この仕様を把握して、結合セルのあるシートでOffsetを連発するのは絶対にやめましょう(笑)
MergeCells・MergeAreaプロパティなどを使って結合セルの位置と大きさを調べて、
結合セル.Offsetを美しく実装することはできますが、そんな美しさは誰も求めていません。
今回の罠が発生してしまい、根本的な解決が難しそうな場合に、
「緊急回避で致し方なくOffsetの数値を手でいじるために使う」
程度のご利用に抑えておくことを、激しく推奨しておきます。
まとめると、
- セル結合エリアの内部にある単独セル
- 結合エリアと完全に一致したセル範囲
からのOffsetが、結合エリア直下の単独セルになってしまうようです。
（上記の通り、位置だけでなく元のRangeの大きさも消えてしまう）
その上で、Offset(10)などで次の結合セルに到達してもその影響は受けません。
Offsetする元のRangeの結合だけが影響します。
※ もちろんOffset(3).Offset(3)と結合セルを跳ねていくようなことをすれば、
Offsetごとに別の結合の影響を受ける
ただし、Offset値がマイナスの場合は、なぜか単独セルだけは結合の影響を受けなくなります。
結合されたエリア全体からのマイナスOffsetは相変わらず影響を受けていますが。
また、B7:E7からの3行のように、同じ大きさの結合セルで作られた表っぽい場所を通っていくようなOffsetでも、Offset後のRangeオブジェクトは単セルになってしまいます。
これがエリアごとにOffsetしていくのであれば、まだ使い道があったのに。
最後に、
- 結合エリアの内部のセル範囲
- 複数の結合エリアをまたぐセル範囲
- 結合エリアを含む大きなセル範囲
のOffsetは、実は結合の影響を受けず、正常に動いています。
セル範囲.Offsetは、結合エリアにぴったり一致している場合のみ影響を受けるんですね。
そうだ！こいつを利用しよう！
この帳票でやりたかったOffsetは、
Range(金額アドレス).Resize(100).Offset(ページの高さ * ページ番号).Resize(1)
と、「結合エリアと絶対に一致しないデカいエリアにResizeしてからOffsetし、
その後元の大きさにResizeしなおす」という美しい奥義で実現することができます。
絶対にやらない方がいいと思いますが、
もしやるならせめてこのページのURLをコード内にコメントしてあげてください。
Resize(100)は、まさにマジックナンバーです。
この100という数字は何のことかわからない。
でもなぜかプログラムは動く。まるで手品だ（皮肉）
という、マジックナンバーの由来が、ここまで似合う数字もなかなかないでしょう。
ちゃんと、この有害サイトから教わりましたと、種明かしをしてあげてください。
