カテゴリー
Microsoft

【Excel VBA】イテレータを作って、列の選択肢を順番に選んでフィルタをかけるコード

ポイント

Iterator クラスでは、もれなく反復するために、最低次の機能があればよい。

  • HasNext 関数を用意し、次の要素があるかを判定できる機能
  • Next 関数を用意し、次の要素を取得できる機能

前提・要望

  • エクセルの Sheet1 に表がある。
  • 1行目はヘッダー、2行目以降がデータ
  • 2列目には何種類かのデータが入っている。
  • 2列目でフィルタをかける。
  • フィルタの選択肢はいくつかあり、1つずつ選択してフィルタ表示するのをすべての選択肢で行う。
  • 以上のことを、VBA のコードで実現したい。

エクセルデータ

「Sheet1」という名前で、次のデータを対象といたしました。1行目もエクセルシートに記載しております。

No 都道府県
1 北海道
2 青森県
3 岩手県
4 秋田県
5 岩手県
6 秋田県
7 秋田県
8 青森県
9 岩手県
10 秋田県

これが Iterator への入力データとなります。

2 列目には都道府県が4種類、「北海道 」「青森県」「岩手県」「秋田県」、入っております。

この2列目でフィルタし、4種類を1つずつ選んでフィルタリング表示するのを VBA コードで実現したいですの♪

VBA コード

まずは、ThisWorkbook.cls です。ここで Iterator をインスタンス化し、使用しております。

Option Explicit

' エクセル列の選択肢を順番に選んでフィルタを避けたい。ループするときにはイテレータを使いたい。
Sub test()
  Dim fi As FilterIterator
  Set fi = New FilterIterator
  Call fi.Init("Sheet1", 2)

  ' フィルタ対象があるかぎりループ
  Dim filtered As Range
  Dim i As Long
  Dim item As Variant
  Do While (fi.HasNext)
    ' フィルタをかけ、Range を取得
    ' ここにデバッグポイントを置き、エクセルシートの様子をみると状況がよく分かる。
      Set filtered = fi.NextItem
      For Each item In filtered
        Debug.Print item
      Next item
  Loop

  ' 後処理
  Selection.AutoFilter
End Sub

つづいて、Iterator クラス本体です。

Collection の要素数を調べたり、Collection の要素を取得したりすることで、HasNext 関数と NextFilter 関数の機能を実現しております。

Collection といえば要素数を意識しなくとも For Each でもれなく要素を取得できることが特徴ですけれども、For Each は使用しておりませんの♪

Option Explicit

' 対象ワークシート
Private mobjSheet As Worksheet
' フィルタ対象列番号
Private mlngCol As Long
' データ範囲の最大行数
Private mlngMaxRow As Long
' データ範囲の最大列数
Private mlngMaxCol As Long
' 要素位置
Private mlngIndex As Long
' 重複なしのフィルタ選択肢
Private mobjList As Collection

''' <summary>
''' 初期化処理を実行します。
''' </summary>
''' <param name="strSheet">操作対象ワークシート</param>
''' <param name="lngCol">フィルタ対象列番号</param>
Public Sub Init(ByVal strSheet As Worksheet, ByVal lngCol As Long)
  Set mobjSheet = Worksheet(strSheet)
  mlngCol = lngCol
  mlngMaxRow = mobjSheet.mobjSheetCells(Rows.Count, 1).End(xlUp).Row
  mlngMaxCol = mobjSheet.mobjSheetCells(1, Columns.Count, 1).End(xlToLeft).Column
  mlngIndex = 1
  Call SetNoDuplicateCollection
End Function

''' <summary>
''' 次要素があるかどうかを判定します。
''' </summary>
''' <returns>次要素がある場合は True、ない場合 False</returns>
Public Function HasNext() As Boolean
  Dim blnRes As Boolean

  If mobjList.Count > mlngIndex - 1 Then
      blnRes = True
  Else
      blnRes = False
  End If

  HasNext = blnRes
End Function

''' <summary>
''' 次要素を取得します。
''' </summary>
''' <returns>フィルタリングされた Range</returns>
Public Function NextFilter() As Range
  Dim strFilterKey As String
  strFilterKey = mobjList.item(mlngIndex)

  ' フィルタをかけて表示
  mobjSheet.Range("A1").AutoFilter Field:=mlngCol, Criterial:=strFilterKey

  ' 表示されているデータを取得(ヘッダ行は除く)
  Dim objTarget As Range
  Set NextFilter = Range(Cells(2, 1), Cells(mlngMaxRow, mlngMaxCol)) _
    .SpecialCells(xlCellTypeVisible)

  mlngIndex = mlngIndex + 1
End Function

' 重複なしの Collection を生成してプロパティに設定
Private Sub SetNoDuplicateCollection()
  ' 対象シートを設定
  mobjSheet.Select

  ' 重複なしとして絞り込む対象列を取得(ヘッダ行は除く)
  Dim objTarget As Range
  Set objTarget = Range(Cells(2, mlngCol), Cells(mlngMaxRow, mlngCol))

  ' 重複を回避して、目的の変数を生成
  ' 重複買い費用の変数
  Dim objNoDuplicate As Object
  Set objNoDuplicate = CreateObject("Scripting.Dictionary")
  Dim objItem As Range
  ' 返却用の変数
  Dim objResult As Collection
  Set objResult =  New Collection
  ' 重複位を判定し、初めてであれば返却値に追加'
  For Each objItem In objTarget
    If Not objItem = Empty Then
      If Not objNoDuplicate.Exists(objItem.Value) Then
        objNoDuplicate.Add Key:=objItem.Value, item:=Null
        objResult.Add item:=objItem.Value
      End If
    End If
  Next objItem

  Set mobjList = objResult
End Sub

結果

フィルタした状態を Range で取得して、Debug.Print で出力いたしました。

イミディエイトウィンドウの表示は次のようになりました。

1
北海道
2
青森県
8
青森県
3
岩手県
5
岩手県
9
岩手県
4
秋田県
6
秋田県
7
秋田県
10
秋田県

2列目の都道府県の種類ごとにフィルタリングされた様子がよくわかります♪成功ですの♪

なお、デバッグポイントを設定して、フィルタが順番にかかる様子を見ると、もっと状態をはっきりと把握できます!おすすめですの♪

おわりに

VBA にはイテレータが備わっていないようですの><。ですけれども、クラスは使える。。。

であれば、Iterator パターンのデザインパターンを作ることができるのではないかしら!そう思ったのがきっかけでしたわ。

幸い、sawadybomb さまが次のページで詳しく例を示してくださいました!

これは、『増補改訂版Java言語で学ぶデザインパターン入門』著: 結城浩、の内容をとてもよく実現しております。後から本を読み返してそのことに気が付きましたの♪

今回、わたくしたちのコードでは、インターフェースや Aggregater は作成いたしませんでした。

  • 他でも Iterator を使う箇所がなかったということ、
  • エクセルシートにフィルタをかけることが主目的で、フィルタ済みの Range は実は要らなかった。つまり NextFilter の返却値は不要で、Aggregator を作る必要がなかったということ、
  • インターフェースや Aggregater に分けると抽象度が上がって理解が難しくなった(わたくしたちの頭が悪いのですの><)といこうこと、

といった理由からですわ。

それでも、Iterator パターンに必要なのは

  • HasNext 関数での次があるかの判定
  • Next 関数での次の要素の取得

の 2 つと存じますので、これに絞って実装いたしました♪

以上です。

「【Excel VBA】イテレータを作って、列の選択肢を順番に選んでフィルタをかけるコード」への1件の返信

コメントを残す