Application.WorksheetFunctionで範囲をCellsで指定したい、でももっと簡単に指定したい。

VBA

Application.WorksheetFunctionについては以前、このブログのApplication.WorksheetFunctionで他ブックやシートを参照する方法 - 早起きはyutechiの得で「ブック名・シート名・セル範囲を明記する」というようなことを書いたけれど、もうひとつメモすることが。

これまで、すごくめんどくさいと思いつつも、Application.WorksheetFunctionを使うときには必ず絶対番地指定で書いてきました。
ループを回して順番に計算させたいときも、

  1. 対象のCells.Addressをとって、
  2. Len()でAddressの長さを判定し、
  3. それをもとにInstr()で列名の場所をとり、
  4. Mid()で列名だけ切り出す。。

う～ん、非常にめんどくさい！なにかもっとうまい方法はないかなぁと常々思っていました。
セルの扱いでは超基本なのですが、Resizeを使います！

例えば、
f:id:yutechi25:20121205115023p:plain
このようなデータがあったとして、B列からD列にかけて存在するA値、B値、C値、それぞれの最大値をB7からD7に入力したいとします。この場合は

Set dp = Range("B2")
for i = 1 to 3
    dp.Cells(6,i) = Application.WorksheetFunction.Max(dp.Cells(1,i).Resize(5))
next i

と記述します。便利！
もちろん、resizeの引数を数値から変数にしてあげれば指定範囲を変更させながらのループ処理が行えます。素敵。
基本的にCells(行,列)指定のようにResize(行,列)で指定しますので出力セル位置を変えて範囲指定をResize(,5)のようにすれば行ループで1行目、2行目の計算結果を縦に結果を出したいようなときにも応用可能です。


ちなみにResizeの使い方については
これだけはおさえるセル操作（２）−Resizeで自由自在：Excel VBA｜即効テクニック｜Excel VBAを学ぶならmoug
こちらのページがわかりやすくまとまっています。参考にしてます。ありがとうございます。