t-hom’s diary

主にVBAネタを扱っているブログ…とも言えなくなってきたこの頃。

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("鈴木 佐藤 山田")
    DimAs 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 'ローテーションのリセット
    DimAs 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

Rangeを指すアドレスの数値を増加させていくのではなく、Rangeを指す変数rを用意して中身を次々入れ替えていく方法でループさせている。

これも考え方は似ていて、「何番目」ではなく、「次」という観点で考えたコード。

この「次、次、次…」という単純な反復手法を教えてくれたのはGoFデザインパターンに出てくるイテレーターパターンというものである。

私の読んだデザインパターンの書籍では抽象クラスやインターフェースを利用することが重要視されていたけど、イテレーターパターンの本質はそこじゃなくて、「何番目」という管理から「次」というより直観的な単純反復に置き換えるというのがその本質かと思う。

増補改訂版Java言語で学ぶデザインパターン入門

増補改訂版Java言語で学ぶデザインパターン入門

まとめ

クラスモジュールもそうだけれど、Staticについても活用事例をあまり見かけない。
いまひとつ使いにくいかなと思ってたけれど、今回の事例はStaticがハマり役だったかもしれない。まぁ、クラス使えば同じことができるんだけど。

VBAの情報って、ネットを探していても「こうするとこうなります」といった使い方の説明はいくらでも見つかるんだけれど、どう使うと便利なのかという説明がないケースが多くて、結局「だから何?」で終わってしまうんじゃないかなと思う。

SplitやArrayなどの関数群にしてもそうだし、列挙型にしてもそうだ。

「私はこう使ってます」というのがもっと発信されると良いなと思うし、このブログでも発信していきたい。

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。