ポイント
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件の返信
[…] 【Excel VBA】イテレータを作って、列の選択肢を順番に選んでフィルタをかけるコード | oki2a24 […]