カテゴリー
Microsoft

【改善版】【Excel VBA】ワークシートに入力したマスタデータを VBA から簡単に利用できるようにしたクラスのコード!2

の改善版!

ポイント

  • プロパティ (クラス変数) は Public メソッド以外から呼ばないようにした。
  • 関数型プログラミング入門 | プログラミング | POSTD の「副作用がない」コードという考え方を参考にした。
  • 結果、Private 関数の引数が増えた。デメリットと感じる。
  • 結果、プロパティが減り、ロジックが単純になった。うれしい!

VBA コード

Option Explicit

Private mcolReaded As Collection

' 自身のクラスをモデルとして扱いたい。
' インスタンス化したときに値を設定できるように、Public に設定
Public mlngNo As Long
Public mstrWeekday As String
Public mdtmFrom As Date
Public mdtmTo As Date
Public mlngPrice As Long

''' <summary>
''' コンストラクタ
''' </summary>
Private Sub Class_Initialize()
  Set mcolReaded = New Collection
End Sub

''' <summary>
''' 初期化処理を実行します。
''' </summary>
''' <param name="strTartgetWorksheet">捜査対象ワークシート</param>
Public Sub Init(ByVal strTartgetWorksheet As String)
  ' 変数をセット
  Dim objSheet As Worksheet: Set objSheet = Worksheets(strTartgetWorksheet)
  With objSheet
    ' 下端の行番号、右端の列番号を取得
    Dim lngMaxRow As Long: lngMaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Dim lngMaxCol As Long: lngMaxCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    ' マスタデータ部分を取得。1行目はヘッダーのため無視
    Dim objMaster As Range: Set objMaster = .Range(.Cells(2, 1), Cells(lngMaxRow, lngMaxCol))
  End With
  ' マスタデータのクラスコレクション生成
  Dim i As Long
  For i = 1 To objMaster.Rows.Count
    Dim udtRow As PriceTable: Set udtRow = New PriceTable
    With udtRow
      .mlngNo = objMaster(i, 1)
      .mstrWeekday = objMaster(i, 2)
      .mdtmFrom = objMaster(i, 3)
      .mdtmTo = objMaster(i, 4)
      .mlngPrice = objMaster(i, 5)
    End With
    Call mcolReaded.Add(udtRow)
  Next i
End Sub

''' <summary>
''' 引数を条件として絞り込んだ料金を返却します。
''' 結果が複数ある場合は、最初の料金を返却します。
''' 絞込結果が 0 件の場合は、0 を返却します。
''' </summary>
''' <param name="strWeekday">曜日</param>
''' <param name="dtmTime">時刻</param>
''' <returns>料金</returns>
Public Function GetPrice( _
  ByVal strWeekday As String, _
  ByVal dtmTime As Date) As Long

  Dim colFiltered As Collection: Set colFiltered = Filter(mcolReaded, strWeekday, dtmTime)

  GetPrice = ExtractPrice(colFiltered)
End Function

''' <summary>
''' 絞込処理を実行します。
''' </summary>
''' <param name="colTarget">フィルタ対象の PriceTable コレクション</param>
''' <param name="strWeekday">曜日</param>
''' <param name="dtmTime">時刻</param>
Private Function Filter( _
  ByVal colTarget As Collection, _
  ByVal strWeekday As String, _
  ByVal dtmTime As Date)

  Dim colResults As Collection: Set colResults = New Collection

  Dim target As PriceTable
  For Each target In colTarget
    If Instr(target.mstrWeekday, strWeekday) > 0 And _
      (target.mdtmFrom <= dtmTime And target.mdtmTo > dtmTime) Then
      Call colResults.Add(target)
    End If
  Next target

  Set Filter = colResults
End Function

''' <summary>
''' 絞り込まれた金額を返却します。
''' 結果が複数ある場合は、最初の料金を返却します。
''' 絞込結果が0件の場合は、0を返却します。
''' <param name="colTarget">金額取り出し対象の PriceTable コレクション</param>
''' <returns>料金</returns>
Private Function ExtractPrice( _
  ByVal colTarget As Collection) As Long
  
  ExtractPrice = 0

  If colTarget.Count > 0 Then
    ExtractPrice = colTarget(1).mlngPrice
  End If
End Function

Public Function ToString() As String
  Dim s As String
  Dim v As PriceTable
  For Each v In mcolReaded
    s = s & _
    v.mlngNo _
    & ", " & v.mstrWeekday _
    & ", " & v.mdtmFrom _
    & ", " & v.mdtmTo _
    & ", " & v.mlngPrice & vbCrLf
  Next v
  ToString = s
End Function

確認用データ、コード

以前と同じデータとなりますけれども、掲載いたします。

エクセル

次のエクセル表をマスタデータとして扱うことといたします。

  • シート名: Sheet1
  • 1行目もエクセルに含める。
  • A1 セルから始まる。
No 曜日 時刻(以降) 時刻(未満) 料金
1 月,火,水,木,金 9:00:00 12:00:00 1000
2 月,火,水,木,金 13:00:00 18:00:00 2000
3 月,火,水,木,金 18:00:00 21:00:00 3000
4 9:00:00 12:00:00 1200
5 13:00:00 18:00:00 2200
6 18:00:00 21:00:00 3200
7 9:00:00 12:00:00 1500
8 13:00:00 18:00:00 2500
9 18:00:00 21:00:00 3500

VBA コード

Option Explicit

Public Sub Test()
  Dim pt As PriceTable
  Set pt = New PriceTable
  Call pt.Init("Sheet1")

  Debug.Print pt.ToString

  Debug.Print "予想: 月、21:00 ⇒ 検索結果無しで0円。結果: " & pt.GetPrice("月", #9:00:00 PM#) & "円"
  Debug.Print "予想: 月、10:15 ⇒ 1000円。結果: " & pt.GetPrice("月", #10:15:00 AM#) & "円"
  Debug.Print "予想: 火、13:00 ⇒ 2000円。結果: " & pt.GetPrice("火", #1:00:00 PM#) & "円"
  Debug.Print "予想: 火、12:00 ⇒ 2000円。結果: " & pt.GetPrice("火", #1:00:00 PM#) & "円"
  Debug.Print "予想: 水、18:00 ⇒ 3000円。結果: " & pt.GetPrice("水", #6:00:00 PM#) & "円"
  Debug.Print "予想: 木、9:00 ⇒ 1000円。結果: " & pt.GetPrice("木", #9:00:00 AM#) & "円"
  Debug.Print "予想: 金、17:59 ⇒ 2000円。結果: " & pt.GetPrice("金", #5:59:59 PM#) & "円"
  Debug.Print "予想: 土、20:59 ⇒ 3200円。結果: " & pt.GetPrice("土", #8:59:59 PM#) & "円"
  Debug.Print "予想: 土、11:59 ⇒ 1500円。結果: " & pt.GetPrice("日", #11:59:59 AM#) & "円"
End Sub

結果

こちらも前回と同じ内容ですわ。

1, 月,火,水,木,金, 9:00:00, 12:00:00, 1000
2, 月,火,水,木,金, 13:00:00, 18:00:00, 2000
3, 月,火,水,木,金, 18:00:00, 21:00:00, 3000
4, 土, 9:00:00, 12:00:00, 1200
5, 土, 13:00:00, 18:00:00, 2200
6, 土, 18:00:00, 21:00:00, 3200
7, 日, 9:00:00, 12:00:00, 1500
8, 日, 13:00:00, 18:00:00, 2500
9, 日, 18:00:00, 21:00:00, 3500

予想: 月、21:00 ⇒ 検索結果無しで0円。結果: 0円
予想: 月、10:15 ⇒ 1000円。結果: 1000円
予想: 火、12:00 ⇒ 2000円。結果: 2000円
予想: 水、18:00 ⇒ 3000円。結果: 3000円
予想: 木、9:00 ⇒ 1000円。結果: 1000円
予想: 金、17:59 ⇒ 2000円。結果: 2000円
予想: 土、20:59 ⇒ 3200円。結果: 3200円
予想: 日、11:59 ⇒ 1500円。結果: 1500円

おわりに

最初は不要なプロパティはないかしら?と探すところからリファクタリングをスタートしておりました。

そのとき、関数型プログラミング入門についての投稿を
読んでいたものですから、少し取り入れてみましたの♪

コードが複雑になってしまった部分もございましたけれども、よりシンプルになったかと存じます。

以上です。

コメントを残す