「中途半端な辞書になってしまう」事件

先日 Excel について、ある調査を依頼されました。
【価格表シート】の商品コードに紐づく価格を、上手く転記できないというのです。
そこで確認したところ、まず、元となったマクロは過去に私が作成したものでした。
※実際のマクロから、一部エッセンスだけを抜粋。

Sub PricePosting()

    Dim Dict  As Dictionary ' Microsoft Scripting Runtime 参照設定済み
    Dim r     As Range
    Dim myRng As Range
    ' 商品のコードが入力された範囲を myRng にセット。
    Set myRng = Range("A1:A100")
    ' 商品のコードと右隣にある価格で辞書を作成。
    Set Dict = New Dictionary
    For Each r In myRng
        Dict(r.Value) = r.Offset(, 1).Value
    Next

    Call そのあとの処理

End Sub

このマクロでは、辞書を VLOOKUP関数のように用いて、価格を転記しています。
(今回の本題ではないので、詳細は省略します)。
依頼者はこれを機能拡張して、複数の価格表シートから辞書を作成していました。

Sub PricePosting()

    Dim Dict  As Dictionary ' Microsoft Scripting Runtime 参照設定済み
    Dim r     As Range
    Dim myRng As Range
    
    Dim i     As Long
    
    For i = 1 To 3
        With Sheets(i)
            ' 商品のコードが入力された範囲を myRng にセット。
            Set myRng = .Range("A1:A100")
            ' 商品のコードと右隣にある価格で辞書を作成。
            Set Dict = New Dictionary
            For Each r In myRng
                Dict(r.Value) = r.Offset(, 1).Value
            Next
        End With
    Next

    Call そのあとの処理

End Sub

※この時点で、もう、ピンと来た人も居ると思います。

「上手く転記できない」の詳細を訊き、以下のことが分かりました。

  • 転記されるものと、されないものがある。
  • 転記自体は、正しく行われている。

まず、以下の観点から、調査してみました。
① 価格表シートに、対応するコードと価格が無いのでは?
  ⇒ ありました。
② 価格表シートのレイアウトが、シート毎に異なるのでは?
  ⇒ 価格表シートのレイアウトは全て共通でした。
③ 半角/全角の違いなど、辞書に登録した内容が微妙に違うのでは?
  ⇒ 違いはありませんでした。

そこで、マクロをまじまじと眺めてみて・・・原因が分かりました。

    For i = 1 To 3
' (省略)
            Set Dict = New Dictionary
' (省略)
    Next

せっかく辞書にため込んだ情報を、ループするたび辞書を「New」することで破棄してています。これが原因でした。辞書の初期化をループの手前に出すことで、無事に解決しました。

Sub PricePosting()

    Dim Dict  As Dictionary ' Microsoft Scripting Runtime 参照設定済み
    Dim r     As Range
    Dim myRng As Range
    Dim i     As Long
    
    Set Dict = New Dictionary
    
    For i = 1 To 3
        With Sheets(i)
            ' 商品のコードが入力された範囲を myRng にセット。
            Set myRng = .Range("A1:A100")
            ' 商品のコードと右隣にある価格で辞書を作成。
            For Each r In myRng
                Dict(r.Value) = r.Offset(, 1).Value
            Next
        End With
    Next

    Call そのあとの処理

End Sub

何か異常が発生した時、その原因の多くは、

  • 技術的に解決がとても難しい問題
  • とてもシンプルな問題

のどちらかである場合が多いようです。

参考まで。