エクセルのワークシートをスクロールさせる

VBAで、ワークシートに処理結果をたくさん出力するプログラムを作っているとする。

現在表示されている領域の外にデータが出力された場合、普通はプログラムが終了するまで待って、手動でワークシートをスクロールしなければならない。これでは面倒だから、ちゃんとリアルタイムで追いかけて確認したい。つまり、表示領域外にデータを出力した場合には、自動的にワークシートがスクロールし、最新のデータに追従して欲しい。

こうした動作は、VisibleRange, ScrollRow, ScrollColumn等を使って実現できる。

サンプルコード

' Sleepを使うためのおまじない
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'------------------------------------------------------------
' 指定されたセルに文字列を出力。
' 表示範囲外のセルに出力した場合は、スクロールさせる。
'------------------------------------------------------------
Sub printAndScroll(row, col, str)
    Cells(row, col) = str   '出力
    'シートのスクロール
    With ActiveWindow
        '行方向にスクロール（マイナス2で微調整している）
        If row > (.VisibleRange.Rows(1).row + .VisibleRange.Rows.Count - 2) Then
            .ScrollRow = .ScrollRow + 1
        End If
        '列方向にスクロール（マイナス2で微調整している）
        If col > (.VisibleRange.Columns.Columns(1).Column + .VisibleRange.Columns.Count - 2) Then
            .ScrollColumn = .ScrollColumn + 1
        End If
    End With
End Sub

'------------------------------------------------------------
' サンプルmain
'------------------------------------------------------------
Sub main()
    '右下に向かってテキトーな文字を出力
    For i = 1 To 50
        Call printAndScroll(i, i, "(" & i & ", " & i & ")")
        Sleep 100   '動作確認のためスリープ
    Next
End Sub

サンプルの動作

参考

• Office TANAKA - Excel VBA Tips[ワークシートをスクロールする]

VBAで一番下にある入力済みセルを求める

「一番下の入力済みセルの、次のセル」を選択するイディオム。とってもよく使うので記録しておく。

Dim Bottom As Long
Bottom = ActiveSheet.Rows.Count
Cells(Bottom,1).End(xlUp).Offset(1).Select

詳しいことは「Excel VBA Story」ていうサイトに蘊蓄を交えて書いてある。

また、変数宣言なしで

Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1).Select

…と書く場面のほうが現実的には多い気がする。

参考

• Excel VBA Story

VBAでeval（Evaluate関数）を使ってみる

2010年現在でも、中小零細企業では表計算から請求書や納品書等の書類にいたるあらゆる業務で Microsoft Excel が活躍する。したがって、Excel VBA でのプログラミングをすることも多い。

VBA の場合、セルにデータを入力しておくことでプログラムを制御するのが一つのマイベストプラクティス。たとえば一番最後のワークシートにプログラム上の定数を記載しておく。後日ユーザーがプログラムの挙動を変えたくなったらそのシートをユーザー自身に編集してもらうことで、自分の仕事を減らせる。

この考えをもう一歩進めると、データだけではなく「手続き」や「関数」までもセルに記述しておきたくなる。たとえばある金額の計算過程で現在は 5%増しの計算をしているのだが、来年になったら (8%増し + 100円) に変えたい、といった具合である。

以下ではこの例題をサンプルとして、変更を見越した関数を設計し、VBAの "Evaluate"関数と組み合わせることによって目的が達成できることを示す。

計算関数を定義する

引数は2つ（利率、上乗せ固定額）

Function f計算(利率、上乗せ固定額)
    f計算 = 金額 * (100 + 利率) / 100 + 上乗せ固定額
End Function

VBAでは日本語が利用可能なので、変数名や関数名になるべく日本語を使う。

グローバル変数を定義する

上記の関数内には、自由な変数、つまり仮引数として定義されていない変数「金額」がある。この値は実行時にワークシートのセルに入力されているデータであるのだが、残念ながら上記の関数内から参照することができない（Evaluateにより実行されるコードからはセルの値を"Cell(1, 1)"というような形で参照できない）。したがって関数実行前にセルから読み込み、グローバル変数に設定しておく。そのための入れ物を定義するのが次のコード。

Public 金額 As Long

このPublic文は標準モジュール等のトップレベルに書く。

計算を行うマクロを定義する

1. グローバル変数に、名前付きセル「入力セル」を代入
2. 「関数セル」に入力されているコードを適用
3. 結果を「出力セル」に入力

Sub Macro1()
  金額 = Range("入力セル").value
  計算結果 = Application.Evaluate(Range("関数セル").value)
  Range("出力セル").value = 計算結果
End Sub

ワークシートのセルに必要なデータおよび関数呼出しコードを書く

1. 任意のセルに「1000」と入力し、セルの名前を「入力セル」とする
2. 〃 「f計算(5, 0)」と入力し、 〃 「関数セル」とする
3. 任意のセルを選択して、セルの名前を「出力セル」とする

マクロを実行

1. [f8]キーを押すなどしてマクロ「Macro1」を実行
2. うまくいけば「出力セル」に 1050 と出力される

計算を変えてみる

1. 「関数セル」の内容を「f計算(8, 100)」に変更
2. マクロを実行
3. うまくいけば「出力セル」に 1180 と出力される

まとめ

以上で、セル内のプログラムコード書き換えによりプログラムの挙動を変えることができるようになった。

「f計算」以外にもっと関数を定義してやることで、「関数セル」に書けるコードも自由度が増していくはず。

ただし、入力データを参照するためにグローバル変数が必要になるのであまりきれいとは言えない。

また、( )を多用したようなコードをユーザーに書き換えてもらうのは難しいから、変更はプログラム担当者の仕事になるだろう。

参考

• エクセルVBAからのワークシート関数呼び出しメモ(Hishidama's Excel Memo "WorksheetFunction")