部屋割りマクロ(Excel)のリファクタリング(2)
部屋割りマクロのリファクタリング
画像ExcelIcon
返り値を改善する
前回の
この状態では、処理が無事に終わるとTrue、んで、うまく行かなかった場合は
理由の如何にかかわらずFalseが返る
という
超不親切設計
だった。
今回は、この点を改善する。
返り値を独自の列挙体型にする
成功した場合にTrueを返すのは良いが、失敗した場合に、失敗の原因別の値が返せると、呼び出し元でいろいろ処理を分岐できるので便利だろう。
今のところ、処理が失敗する原因として考えられるのは、
- 第1引数targetRange(書き込み先のセル範囲)が1列でない場合
- 第2引数roomData(部屋の名前と定員数を表すセル範囲)が2列でない場合
- 書き込み先のセル範囲の数が部屋の全定員数よりも多い場合
- 処理中にエラーが出た場合
あたりだろう。
そんなわけで、宣言セクションに次のような列挙体を宣言した。
リスト1 標準モジュールの宣言セクション
Public Enum AssignRoomsResult assignSuccessed = True '……(*)' failedByMultipleColumns = 1 failedByNotTwoColumnsTable failedByOverCapacity failedByUnknownReason = 10 End Enum
VBAでは、Trueの正体はー1、すなわち整数なので、列挙体のメンバにできる。これは今回初めて知った。
これを使って、前回
過去記事
のリスト1を書き換える。
リスト2 標準モジュール
Public Function assignOfRooms(ByVal targetRange As Range, _ ByVal roomData As Range, _ Optional ByVal hasHeader As Boolean = True) _ As AssignRoomsResult On Error GoTo errorHandler '2列以上選択していたら終了……(1)' If targetRange.Columns.Count <> 1 Then assignOfRooms = failedByMultipleColumns Exit Function End If '部屋定員表が2列以外の表だったら終了……(2)' If roomData.Columns.Count <> 2 Then assignOfRooms = failedByNotTwoColumnsTable Exit Function End If '部屋定員表の項目ラベルがあるときは、項目ラベルを表から除く。' With roomData If hasHeader Then Set roomData = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) End With '部屋定員表を2次元配列化する。' Dim roomDataArray As Variant roomDataArray = roomData.Value Dim rowCount As Integer rowCount = UBound(roomDataArray, 1) '振り番対象のセルの数が、収容人数オーバーだったら終了' Dim capacityOfAllRooms As Integer Dim i As Integer For i = 1 To rowCount capacityOfAllRooms = capacityOfAllRooms + roomDataArray(i, 2) Next If capacityOfAllRooms < targetRange.Count Then '……(3)' assignOfRooms = failedByOverCapacity Exit Function End If targetRange.ClearContents '振り番処理' Dim getFromArrayCount As Integer '配列にアクセスした回数を記録する変数' getFromArrayCount = 1 Dim loopOfArray As Integer For i = 1 To targetRange.Count '配列からの値取得が何周目かを計算する。' loopOfArray = (getFromArrayCount - 1) \ rowCount + 1 With targetRange.Cells(i, 1) '振り番しようとする部屋が定員に達していたら次の部屋に進める' Do While loopOfArray > _ roomDataArray(((getFromArrayCount - 1) Mod rowCount + 1), 2) getFromArrayCount = getFromArrayCount + 1 loopOfArray = (getFromArrayCount - 1) \ rowCount + 1 Loop .Value = roomDataArray(((getFromArrayCount - 1) Mod rowCount + 1), 1) getFromArrayCount = getFromArrayCount + 1 End With Next assignOfRooms = assignSuccessed '……(4)' Exit Function errorHandler: assignOfRooms = failedByUnknownReason '……(5)' Debug.Print Err.Number & ":" & Err.Description End Function
まず、(1)の
'2列以上選択していたら終了……(1)' If targetRange.Columns.Count <> 1 Then assignOfRooms = failedByMultipleColumns Exit Function End If
では、第1引数として渡されたtargetRangeの列数を調べ、「1」でなかったらfailedByMultipleColumnsを返して処理を抜ける。
(2)の
'部屋定員表が2列以外の表だったら終了……(2)' If roomData.Columns.Count <> 2 Then assignOfRooms = failedByNotTwoColumnsTable Exit Function End If
では、第2引数roomDataの列数を調べ、「2」でなかったらfailedByNotTwoColumnsTableを返して処理を抜ける。
(3)の
If capacityOfAllRooms < targetRange.Count Then '……(3)' assignOfRooms = failedByOverCapacity Exit Function End If
では、第1引数targetRangeの数、つまり書き込み先セルの数、すなわち部屋割り対象人数が、部屋定員数の合計を上回っていたら、failedByOverCapacityを返して処理を抜ける。
(4)まで無事にたどり着いたということは、部屋割り処理が無事に終わったということなので、
assignOfRooms = assignSuccessed Exit Function
でassignSuccessedを返して処理を抜ける。
あと、処理の過程でエラーが発生した場合は、(5)の
assignOfRooms = failedByUnknownReason Debug.Print Err.Number & ":" & Err.Description
でfailedByUnknownReasonを返し、ついでにイミディエイトにエラー番号と説明を表示させることにした。
これで、処理失敗の原因別に、呼び出し側で対応できる。
使ってみる
次のようなコードで実験。
リスト3 標準モジュール
Public Sub testassignOfRooms() Select Case assignOfRooms(targetRange:=Selection, _ roomData:=ActiveSheet.Range("A1").CurrentRegion, _ hasHeader:=True) Case assignSuccessed MsgBox "部屋割り完了!" Case failedByMultipleColumns Call makeUserSick("部屋割り結果を書き込む列が2列以上あるやんけぼけーwww") Case failedByNotTwoColumnsTable Call makeUserSick("部屋定員表が何で2列とちゃうねんぼけーwww") Case failedByOverCapacity Call makeUserSick("定員オーバーじゃぼけーwww") Case failedByUnknownReason Call makeUserSick("何か知らんけど失敗したやんけぼけーwww") End Select End Sub
相変わらず品性のかけらもないコードですまん。
makeUserSickメソッドについてはコチラを参照のこと。
実行結果
まずは、この状態で実行。
こうなるw
次に、
この状態で実行。
こうなるw
今度は、
こんな状態で実行。分かりにくいかも知れないけれど、3人部屋をシングルにしたので、14人の好漢たちに対して部屋のキャパが10人分しかないw
んで、
こうなるw
あと、リスト2のテキトーなところに
Err.Raise Number:=10001, _ Description:="ち~んw"
こういうコードを仕込んでから実行すると、
こうなって、
イミディエイトはこんなふうになる。
おわりに
単に処理を行うだけのメソッドでも、SubではなくFunctionにして、返り値を自作列挙体型にすることで、結構柔軟な処理ができるような気がする。