[VBA][Technic][Dictionary]レイアウト変更に負けないロジック

例えば、EXCELのシートを読み取ってデータ加工をするツールをVBAで作ったとします。

うまく作れて結果も上々、これで出来上がりかと思いきや。

読み取るシートは他の担当者から受領するファイルなのだが、シートのレイアウトが毎回異なり、うまく処理できないシチュエーションに遭遇することってありませんか?

項目は揃っているが項目の並び順が変わっていたり、よく分からない項目が追加されていたり。

これではせっかく作ったVBAのプログラムがうまく動いてくれません。

要するにレイアウト変更があることを想定したVBAのロジックが作れれば対応できる話なのだが。

そんな都合のいい話があるわけが、、、あるのっ!!?


VBAで株価の変動率を算出する

まずは以下のような表があったとします。

今回は株価データを使用してみました。

例えば高値と安値を使って変動率を算出しようとした場合、次のような計算方法となる。

変動率=([高値]-[安値])/[安値]*100

シートから値を読み取ってイミディエイト出力する簡易プログラムを作ってみます。

もともとのVBAのロジック

サンプルとしての仕様が簡単なので以下のようなシンプルなプログラムになります。

このロジックではエラー処理は考慮していません。

Sub Sample()
Dim i As Integer

    With ActiveSheet
        
        i = 2
        Do Until IsEmpty(.Cells(i, 1))
           
            Debug.Print WorksheetFunction.RoundDown(((.Cells(i, 4).Value - .Cells(i, 5).Value) / .Cells(i, 5).Value) * 100, 2)
            
            i = i + 1
        Loop
        
    End With

End Sub

突然のレイアウト変更(しかも頻度も多い)

ある日次のような表になっていた。

当然VBAで参照しているセルが違うので、変動率を算出することができません。

これは仕事をしている時に本当によくある話なんです。

担当者がその時々でレイアウトを変更してくるというパターン。

ロジックの修正を繰り返しても繰り返してもレイアウト変更が起こるたびに無駄に思える作業が発生するという話。

仕事には立場や状況によって主張できないケースも多々あるでしょう。

そんな時は、発想の転換が必要になってきます。

無駄に思える作業が発生しないロジックで状況をカバーするという発想の転換です。

これはVBAに限らずですが、こんな時にとっておきのテクニックを紹介したいと思います。

レイアウト変更の対抗策はDictionaryオブジェクト

DictionaryオブジェクトはVBAで使用できる連想配列になります。

連想配列とはキーと値の組み合わせで構成された配列です。

このDictionaryを使用してレイアウト対策ロジックに修正をしてみたいと思います。

Sub Sample()
Dim oDic As Dictionary
Dim arrList As Variant
Dim i As Integer
    
    Set oDic = New Dictionary
    
    With ActiveSheet
        
        arrList = .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight))
        
        For i = LBound(arrList, 2) To UBound(arrList, 2)
            oDic.Add arrList(1, i), i
        Next
        
        i = 2
        Do Until IsEmpty(.Cells(i, oDic("stock_cd")))
           
            Debug.Print WorksheetFunction.RoundDown(((.Cells(i, oDic("high")).Value - .Cells(i, oDic("low")).Value) / .Cells(i, oDic("low")).Value) * 100, 2)
            
            i = i + 1
        Loop
        
    End With
    
    Set oDic = Nothing

End Sub

VBAのロジック修正の解説

そんなにたくさんロジックは増えてないので安心してください。

レイアウト変更対策ロジックでは、具体的な算出処理の前に下準備があります。

ヘッダー行の項目名と位置をDictionaryへ格納しています。

このDictionaryを用意することで項目名が何列目に存在するのかを管理することが出来ます。

実際の繰り返し処理のセル参照の様子を確認してみましょう。

        Do Until IsEmpty(.Cells(i, oDic("stock_cd")))
           
            Debug.Print WorksheetFunction.RoundDown(((.Cells(i, oDic("high")).Value - .Cells(i, oDic("low")).Value) / .Cells(i, oDic("low")).Value) * 100, 2)
            
            i = i + 1
        Loop

もともとのロジックだと数値を指定していた箇所がDictionaryオブジェクトに置き換わっています。

しかもDictionaryにはキーを指定しているため、セルの参照先の項目についても一目で把握できて可読性も向上しました。

このロジックの素晴らしい点は、列を挿入されたり、順番の並びがバラバラにされても、まったく影響を受けていない点です。

なぜかというとDictionaryで項目名と位置情報をしっかり管理しているから。

レイアウト変更に負けないロジックのまとめ

いかがでしたでしょうか。

Dictionaryオブジェクトを使用することによって、レイアウト変更に強いロジックの作り方の紹介をさせて頂きました。

Dictionaryオブジェクトでヘッダーと位置情報を管理できているわけなので、項目別に個別の処理を行う際に配列の数値指定のままだと、どうしてもシートとのにらめっこになります。

Dictionaryを使用することでセルの参照先の項目が分かりやすくなることは、作業効率の向上、コードの見直しやメンテナンス作業時に多くの恩恵を得ることができます。

ちょっとしたアイデアですが、効果は抜群ですので、ぜひお試しください。