VBA Static変数を活用してローテーション表を作成する

今回はちょっと使いどころの難しいStatic変数を活用してみようというお話。

今回作成するもの

勤務シフト表とか、何かの当番表を作るのに、たとえば鈴木さん→佐藤さん→山田さん→鈴木さん→佐藤さん→山田さん→(略)とローテーションさせるケースがある。
f:id:t-hom:20161220222959p:plain

今回はStatic変数を活用してこのローテーション表を簡単に作成するコードを紹介する。

前提知識～ Static変数とは

VBAでは通常、プロシージャ内で宣言したローカル変数はプロシージャの終了とともに消えてしまうが、Dimの代わりにStaticで変数を宣言すると、プロシージャが終了しても値を保持させることができる。

たとえば次のコードでMainプロシージャを実行してみる。

Sub StaticSample()
    Static n As Long
    Debug.Print n
    n = n + 1
End Sub

Sub Main()
    For i = 1 To 10
        Call StaticSample
    Next
End Sub

すると、イミディエイトウインドウに0から9までの数値が出力される。
変数nがStaticで宣言されているため、プロシージャ終了後も値を保持するためだ。

ちなみにMainを2回目に実行すると10～19までの数値が出力される。

Static変数をリセットするには、VBエディタの実行メニューからリセットをクリックする。
f:id:t-hom:20161220222823p:plain

まずStatic変数を使わないやり方でローテーション表を作る

一般的には以下のように書き込み先の行数を管理する変数とは別に、配列の要素を指すカーソル変数を作ってそのカーソルを配列の最小要素から最大要素までの範囲でローテートさせる方法が取られることが多い。（カーソルと呼ぶかどうかは別として）

Sub Main()
    Dim Arr() As String: Arr = Split("鈴木 佐藤 山田")
    Dim 行 As Long: 行 = 2
    Dim Cursor As Long: Cursor = 0
    Do While Sheet1.Range("A" & 行).Value <> ""
        Select Case Weekday(Sheet1.Range("A" & 行).Value)
            Case 1, 7
                Sheet1.Range("B" & 行) = "-"
            Case Else
                Sheet1.Range("B" & 行) = Arr(Cursor)
                If Cursor < UBound(Arr) Then
                    Cursor = Cursor + 1
                Else
                    Cursor = LBound(Arr)
                End If
        End Select
        行 = 行 + 1
    Loop
End Sub

まぁこれでも良いけれど、管理すべきことが増えるためメインコードがごちゃごちゃしてしまう。

「いま何番目まで来たから最初にもどる」という具体的な処理をメインコードで対応しようとすると、本来対処すべき主要なロジックを考えるのに邪魔になるのだ。

これを「次の人、次の人、次の人、次の人…」という風にシンプルな繰り返し処理に変換したい。

Static変数を活用して配列の内容を繰り返す

まず、配列の要素を次々に取り出すために専用の関数 ArrRotateを作成する。
コードは以下のとおり。

Function ArrRotate(Arr, Optional reset As Boolean = False)
    Static n As Long
    ArrRotate = Arr(LBound(Arr) + n)
    n = n + 1
    If LBound(Arr) + n > UBound(Arr) Or reset Then
        n = LBound(Arr)
    End If
End Function

このときnはStaticで宣言しているため、値を保持する。そして呼び出されるたびにnの値が増え、配列の最大要素に達すると最小要素に戻る。
また、リセットボタンを押さなくても初期化できるように、別途Optionalでresetという引数を用意している。Trueが渡されると配列の最小要素に戻る仕組み。

そして作成したArrRotateを使ったコードがこちら。

Sub Main()
    Dim Arr() As String: Arr = Split("鈴木 佐藤 山田")
    ArrRotate Arr, True 'ローテーションのリセット
    Dim 行 As Long: 行 = 2
    Do While Sheet1.Range("A" & 行).Value <> ""
        Select Case Weekday(Sheet1.Range("A" & 行).Value)
            Case 1, 7
                Sheet1.Range("B" & 行).Value = "-"
            Case Else
                Sheet1.Range("B" & 行).Value = ArrRotate(Arr)
        End Select
        行 = 行 + 1
    Loop
End Sub

「何番目の人」という管理がなくなり、単純にArrRotateを呼び出すたびに「次の人」が取得できるようになった。

もう少し自分流に書き換えるとこんな感じ。

Sub Main()
    Dim Arr() As String: Arr = Split("鈴木 佐藤 山田")
    ArrRotate Arr, True 'ローテーションのリセット
    Dim r As Range: Set r = Sheet1.Range("A2")
    Do While r.Value <> ""
        Select Case Weekday(r.Value)
            Case 1, 7
                r.Offset(0, 1).Value = "-"
            Case Else
                r.Offset(0, 1).Value = ArrRotate(Arr)
        End Select
        Set r = r.Offset(1, 0)
    Loop
End Sub