t-hom’s diary

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

VBA クラスモジュールで連結リストを作成する方法

今回はクラスモジュールを使って連結リストを作成してみる。
実験ではなく、必要性があって作ることになったのでそういう意味では実用的なサンプルとして紹介できるかと思う。

連結リストとは

情報処理技術者試験などで学習したことがある人も要ると思うが、構造としてはあるアイテムが次のアイテムを指すポインタ(参照)を持っている構造で、いわゆるリストと呼ばれるデータ構造のことである。

こんなイメージ。
f:id:t-hom:20161015081258p:plain

VBAのCollectionオブジェクトなんかも、リスト構造である。

メモリ上での具体的なイメージは以下の過去記事を参照。
thom.hateblo.jp

今回はリスト構造のデータを自分で作ってしまおうというわけ。

連結リストを作成したくなった経緯

もともとはVBAと全然関係ないところから始まった。
最新PCの調達にあたり、ネットで見つけたCPUの性能一覧をExcelに転記してグラフ化したかったのだ。
ただ取得できたデータの形式が単純な1行1レコードではなかったので、これを一旦モデル化して連結リストに変換してから別シートに転記することを思い立った次第。

モデル化については以下参照。
thom.hateblo.jp

過去記事で紹介したように、私はPC本体にあまりお金をかけていない。
thom.hateblo.jp

もともとのPC使用目的がブラウジングVBAYoutubeやレンタルDVDの閲覧くらいなので、十分だったのだ。
ただ最近、Minecraft面白そうだなーとか、Android Studioを快適に動かしたいとか、動画編集に挑戦したいとか、いろいろやりたいことが出てきて、今のPCのCPUでは明らかに力不足な模様。

それで今から貯金して、PC買おうかなーと迷い中なんだけれど、そもそも今のCPU Core 2 DureからCore iシリーズにしたときに、どれくらい性能が変わるのかってことが知りたくて、CPUの性能一覧を探してみた。

発見 → CPUパフォーマンス比較表-MAXIMUMs ROOM

最近のCPUは全然載ってないけれど、私の使用しているCore 2 Duo P8600は相当古いので、それと比べてCPUが大幅に進化している感じがわかれば良いので、このリストで十分。

ただこれ、ブラウザからビーって選択してExcelにべたっと貼り付けるとこんな感じのデータになってしまう。
f:id:t-hom:20161015083652p:plain

さて、どうしたものか。ここでモデル化の出番である。

1データに2行かつときどき空行の入ったデータをVBAでうまく整理する

1つのデータが複数行で表示されているというのは割とよくある。
Excel上で加工しても良いけど、今回はそのままの形でVBAで取得してみよう。

まずCPUのレコード構成は次の配置になっているので、製品セルからのOffsetで各値を取得できることになる。
f:id:t-hom:20161015084800p:plain

つまり製品セルを順に取得できれば各値については解決する。

単に隙間なく1レコード2行で並んでいたら、ForでStep2を指定すれば良いのだが、今回はさらに不規則な空行があるのでこれを飛ばすことも考えなければならない。

今回は製品セルから次の製品セルまでが空セルであることを利用し、RangeのEndメソッドを利用する。
コードは次のとおり。
このコードは標準モジュールではなく、CPUの一覧を貼り付けたSheet1モジュールに書く。

Sub ENDメソッドで次々CPUをDebugPrint()
    Dim CurrentRange As Range
    Set CurrentRange = Range("A4")
    For i = 1 To 10 'ためしに10個
        Debug.Print CurrentRange.Value
        Set CurrentRange = CurrentRange.End(xlDown)
    Next
End Sub

最初にCurrentRangeにはA4セルをセットし、その後、ループ中でCurrentRangeが指すRangeオブジェクトが次々変化していく仕組みだ。
SelectとSelectionで実際に選択されているセルを変化させていくやり方もあるけど、「セルを選択する」という操作は動作も遅く、そもそもユーザー領域なので安易に触りたくない。プログラムは極力裏方で動くべしという私のポリシーに反する。これはまたいずれ記事を書きたいと思う。

さて、結果はこうなる。
f:id:t-hom:20161015085845p:plain

性能も表示させてみた。

Sub Offsetで性能も表示させてみる()
    Dim CurrentRange As Range
    Set CurrentRange = Range("A4")
    For i = 1 To 10 'ためしに10個
        Debug.Print CurrentRange.Value, "性能は"; CurrentRange.Offset(0, 4)
        Set CurrentRange = CurrentRange.End(xlDown)
    Next
End Sub

f:id:t-hom:20161015090906p:plain

次に、10個ではなく、全データを走査したい。
するとコードはこのようになる。

Sub 全CPUの走査()
    Dim CurrentRange As Range
    Set CurrentRange = Range("A4")
    Do
        Debug.Print CurrentRange.Value, "性能は"; CurrentRange.Offset(0, 4)
        Set CurrentRange = CurrentRange.End(xlDown)
        If CurrentRange.Value = "" Then
            '次の改良のために、あえてWhileを付けず、内部でExit Doさせている。
            Exit Do
        End If
    Loop
End Sub

Do WhileやLoop Whileなどを付けずにDoとLoopだけで囲うと無限ループができるが、あえてそうしているのは次のモデル化の段階でブロック前後ではなく内部に終了条件を設けたほうがやりやすいから。

読み取ったデータを連結リストとしてオブジェクトに保存する。

データの読み取り方法がわかったところで、これを別シートに転記していきたいところであるが、読み取り座標と書き込み座標を同時に考えるのはなかなか骨が折れる作業だ。
読み取ったデータは、あとで柔軟にデータを取り出すことができる、オブジェクトとして保管しておきたい。

そこでクラスモジュール「CPU」を作成し、以下のコードを記述する。

Public 製品 As String
Public 開発コード As String
Public キャッシュ As String
Public クロック As Long
Public コア数 As String
Public 効率 As Double
Public 性能 As Long

Private nextCPU As CPU

Function CreateNextItem()
    Set nextCPU = New CPU
    Set CreateNextItem = nextCPU
End Function

Property Get NextItem() As CPU
    Set NextItem = nextCPU
End Property

ここで注目したいのはPrivate nextCPU As CPUである。
この変数はCPU型のオブジェクトへの参照を保持することができる。

そしてCPU→CPU→CPUと連鎖的に参照を保持させることで、連結リストが作れるのだ。

また、Function CreateNextItemは、内部で新しいCPUオブジェクトを生成し、それを自分のnextCPUに保持させたうえで、今作成したCPUオブジェクトを戻り値として返す。
オブジェクトのメソッドで、同形のオブジェクトを生成するテクニックである。
これを利用すれば自信のプロパティーをすべてコピーしたコピーオブジェクトを生成するCloneメソッドなども作成可能だが、それはまた別の話。

Sheet1モジュールに以下のコードを貼り付ける。

Sub 全CPUをモデル化したうえで列挙()
    Dim CurrentRange As Range: Set CurrentRange = Range("A4")
    Dim StartCPU As CPU: Set StartCPU = New CPU
    
    Dim CurrentCPU As CPU
    Set CurrentCPU = StartCPU
    
    '■モデル化するコード
    Do
        CurrentCPU.製品 = CurrentRange.Value
        CurrentCPU.開発コード = CurrentRange.Offset(0, 1)
        CurrentCPU.クロック = CurrentRange.Offset(0, 2)
        CurrentCPU.効率 = CurrentRange.Offset(0, 3)
        CurrentCPU.性能 = CurrentRange.Offset(0, 4)
        CurrentCPU.キャッシュ = CurrentRange.Offset(1, 1)
        CurrentCPU.コア数 = CurrentRange.Offset(1, 2)
        
        Set CurrentRange = CurrentRange.End(xlDown)
        If CurrentRange.Value = "" Then
            Exit Do
        Else
            Set CurrentCPU = CurrentCPU.CreateNextItem
        End If
    Loop
    
    '■列挙するコード
    Set CurrentCPU = StartCPU
    Do Until CurrentCPU Is Nothing
        Debug.Print CurrentCPU.製品
        Set CurrentCPU = CurrentCPU.NextItem
    Loop
    
End Sub

実行すると全CPUが列挙される。今回は製品名のみの列挙とした。
ポイントはStartCPUとCurrentCPUの2つの変数である。
CurrentCPUは次々変わっていくが、StartCPUは変化せず、最初のCPUを保持しつづける。

モデル化部分では、CurrentCPU.CreateNextItemの戻り値をCurrentCPUにセットすることで、新しいCPUを作成しつつ自身の参照先をそちらに変化させている。

列挙部分では、一旦参照をStartCPUに戻したうえ、NextItemへと参照を次々と切り替えていく。
最後のCPUのNextItemは何も指していないので、Nothingが返り、Untilキーワードによりループ終了となる。

CPUのリスト化マクロ完成に向けて

今回の本題である「クラスモジュールで連結リストを作成する方法」については解説が終わったので、あとはざっくり。

まず先ほどのSheet1に書いたモデル化コードは、Functionに変更して連結リストの最初のアイテムを返す関数 CPUListとした。
書く場所は先ほどと同じ、Sheet1。

Function CPUList()
    Dim CurrentRange As Range: Set CurrentRange = Range("A4")
    Dim StartCPU As CPU: Set StartCPU = New CPU
    
    Dim CurrentCPU As CPU
    Set CurrentCPU = StartCPU
    
    Do
        CurrentCPU.製品 = CurrentRange.Value
        CurrentCPU.開発コード = CurrentRange.Offset(0, 1)
        CurrentCPU.クロック = CurrentRange.Offset(0, 2)
        CurrentCPU.効率 = CurrentRange.Offset(0, 3)
        CurrentCPU.性能 = CurrentRange.Offset(0, 4)
        CurrentCPU.キャッシュ = CurrentRange.Offset(1, 1)
        CurrentCPU.コア数 = CurrentRange.Offset(1, 2)
        
        Set CurrentRange = CurrentRange.End(xlDown)
        If CurrentRange.Value = "" Then
            Exit Do
        Else
            Set CurrentCPU = CurrentCPU.CreateNextItem
        End If
    Loop
    
    Set CPUList = StartCPU
End Function

これは他のモジュールからはSheet1がもつメソッドとして扱える。

次にシート2を挿入し、あらかじめヘッダ行を書いておく。
f:id:t-hom:20161015095903p:plain

Sheet2モジュールには次のコードを貼り付け。

Private CurrentRow As Long
Sub CPU列挙()
    Dim CurrentCPU As CPU
    Set CurrentCPU = Sheet1.CPUList
    CurrentRow = 2
    Do Until CurrentCPU Is Nothing
        With CurrentCPU
            WriteLine .製品, .開発コード, .クロック, .効率, .性能, .キャッシュ, .コア数
        End With
        Set CurrentCPU = CurrentCPU.NextItem
    Loop
End Sub

Sub WriteLine(ParamArray Line() As Variant)
    For i = 0 To UBound(Line)
        Cells(CurrentRow, i + 1).Value = Line(i)
    Next
    CurrentRow = CurrentRow + 1
End Sub

WriteLineを呼ぶ度にモジュールレベル変数のCurrentRowが変化するのでメインコードでは書き込み位置は気にせずWriteLineを呼ぶ度に行が追加される。
この仕組みは割と使い勝手が良いので他にもいろいろ活用できそうだ。

あとがき

今回はあえて連結リスト形式を自作してみたが、終わってみて、素直にコレクション使った方が楽だと実感。
組み込みのコレクションと比べて型をカチッと決めて扱えるので、そちらの方がコードとして美しい気がする。。という自己満足はある。

まあ、コレクションも内部では連結リストを保持しているので仕組みを知る意味では良いかなと思う。

これを基礎としてツリー構造や双方向連結リストなどを作ると、組み込みコレクションより便利に扱えるかもしれない。
ただ循環参照の問題があるので双方向連結リストを作った場合はオブジェクトの解放にひと工夫必要になるが。

今回の記事は情報処理技術者試験(特に基本情報技術者)の学習をしている型に読んでいただけると良いかなと思う。
私が基本情報技術者を学習していた頃はそんな知識はなく、リストとか木って何の役に立つのかサッパリ分かってなかったので学習がつまらなかったので。

以上

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