雑誌コードとISBNコードを使い易く加工するVBA

datacopyeyecatchaa

雑誌と書籍の在庫表作成のために、今回はバーコードリーダーで読み込んだISBNコードを使えるコードに加工します。
エクセルVBAを利用しますが、同時にコードの間違いチェックも行えるようにしていきます。

こんにちは、じゅんぱ店長(@junpa33)です。

今回は「書誌棚卸表.xlsm」の「データコピー」と「作業シート」シートの作成のためのVBAコードを組み立てていきます。

雑誌と書籍の在庫リスト(棚卸表)作成の記事編成

雑誌コードとISBNコードの分解加工VBA

datacopyp001

今回設置するシートの役割

VBAはこのような感じで組み立てていきたいと思います。

「データコピー」シートは使用するデータを置くシートとしています。

使用するデータは、

  1. 「コード入力表」のバーコードリーダーで読み込んだデータ
  2. 他から持ってきたコピーペーストしたデータ

のどちらでも使用できるようにしています。

使用データ選択方法は、ユーザーフォームに”トグルボタン”を設置します。

「作業シート」は使用するデータを加工するシートとしています。

データを加工する作業としては、

  1. 「作業シート」へ元となるデータを移動すると同時に、コードのエラーの明らかなものをチェックします。
  2. 元となるコードを使えるような形式に変換します。
  3. 同じものの冊数を数えます。
  4. データベースと連携してコードから書名を探して表示します。

エラーの表示はセルを着色して表示します。

「データコピー」シートでのVBAコード

Module1にコードを記述して行く前に、宣言セクション(一番上段)にパブリック変数の宣言を行います。

vbasengeneyecatch 宣言方法で変数の適用範囲を変える エクセルVBA
VBA
Public CP As Integer
Public Srow As Long
Public Krow As Long
Public ShopName As String
Public SpaceName As String

前回の記事で説明しましたが、「コード入力表」のバーコード読み取り操作が完了して、そのデータをこの「データコピー」シートに移す作業を行う場合、

「データ入力終了後の操作(シートから移動した時に起動)」で説明したVBAコードを起動します。

そこで説明している「バーコード」という名前のプロシージャーをModule1に先に記述しておきます。

変数「CP」はトグルボタン(バーコードモードボタン)が押された時に「1」を代入するように設定しています。

ボタンが押されていない時はこのプロシージャーは何もせず終了します。(読み取りデータを「データコピー」シートへは移しません。)

VBA
Sub バーコード()
        If CP = 1 Then
            Worksheets("コード入力表").Range("A:B").Copy _
                Destination:=Worksheets("データコピー").Range("A1")
            Application.CutCopyMode = False
        Else
            Exit Sub
        End If
End Sub

作業シートのVBAコード

「在庫情報コピー」という名前のプロシージャーを作ります。

このプロシージャーで行うことは、

  1. 「データコピー」シートのA列とB列のデータを「作業シート」にコピーペーストします。
  2. 「作業シート」のC列で同じ書誌の冊数を数えます。
  3. 取り込んだバーコードデータがエラーコードかどうかをチェックします。

エラーのチェックは、

  1. 18桁で且つ先頭3文字が「491」
  2. 13桁で且つ先頭3文字が「978」
  3. 13桁で且つ先頭3文字が「192」

以外のコードについてはその入力セルがクリーム色(エラー色)となります。

メッセージボックスで「色付きセルのデータを再チェックしてください!」を表示します。

VBA
Sub 在庫情報コピー()
        Worksheets("作業シート").Select
        Worksheets("データコピー").Range("A:A").Copy _
            Destination:=Worksheets("作業シート").Range("A1")
        Worksheets("データコピー").Range("B:B").Copy _
            Destination:=Worksheets("作業シート").Range("B1")
        Application.CutCopyMode = False
        Range("C1") = "冊数"
        Srow = Cells(Rows.Count, 1).End(xlUp).Row
        Range(Cells(2, 1), Cells(Srow, 2)) _
                                .Interior.ColorIndex = 19
        For i = 2 To Srow
            If Len(Range("A" & i)) = 18 Then
                If Left(Range("A" & i), 3) = 491 Then
                    Range("A" & i).Interior.ColorIndex = 0
                End If
            ElseIf Len(Range("A" & i)) = 13 Then
                If Left(Range("A" & i), 3) = 978 Then
                    Range("A" & i).Interior.ColorIndex = 0
                End If
            Else
            End If
            If Range("B" & i) = "" Then
                If Len(Range("A" & i)) = 18 Then
                    Range("B" & i).Interior.ColorIndex = 0
                ElseIf Range("A" & i) = "" Then
                    Range("B" & i).Interior.ColorIndex = 0
                End If
            ElseIf Len(Range("B" & i)) = 13 Then
                If Left(Range("B" & i), 3) = 192 Then
                    Range("B" & i).Interior.ColorIndex = 0
                End If
            Else
            End If
        Next i
            For Each CELS In Range(Cells(2, 1), _
                                        Cells(Srow, 2))
                If CELS.Interior.ColorIndex <> xlNone Then
                    n = 1
                End If
            Next CELS
            If n > 0 Then
                MsgBox "色付きセルのデータを再チェックしてください!"
            End If
End Sub

「書誌コードチェック」という名前のプロシージャーを作ります。

このプロシージャーで行うことは、

  1. 元のコードから雑誌コードを抜き出します。
  2. 元のコードから雑誌の価格情報を抜き出します。
  3. 書籍の価格コードから価格情報を抜き出します。
  4. 同じ書誌の数量をカウントします。

コードのエラーが表示されているときは、このプロシージャーを実行する前に訂正が必要です。

VBA
Sub 書誌コードチェック()
    Dim TCA As Variant, TCB As Variant
    Dim ZC As Long, ZCa As Long, ZK As Long, ZS As Long
    Dim ZKa As Variant, ZKb As Long
    Dim t As Long
        Worksheets("作業シート").Select
        Krow = Cells(Rows.Count, 1).End(xlUp).Row
        SC = MsgBox("コードチェックはOKですか?", vbYesNo + _
                        vbQuestion, "確認")
        If SC = vbYes Then
            For Each CELS In Range(Cells(2, 1), Cells(Krow, 2))
                CELS.Interior.ColorIndex = 0
            Next CELS
        Else
            Exit Sub
        End If
        For t = 2 To Krow
            TCA = Range("A" & t)
            TCB = Range("B" & t)
            
            If Left(TCA, 4) = 4910 Then
                ZC = Mid(TCA, 5, 7)
                ZK = Right(TCA, 5)
                ZS = Len(TCA)
                Range("A" & t).Value = ZC
                If ZS = 18 Then
                    Range("B" & t).Value = ZK
                Else
                    Range("B" & t).Value = ""
                End If
            End If
            If Left(TCB, 3) = 192 Then
                ZKa = Left(TCB, 12)
                ZKb = Mid(ZKa, 8, 12)
                Range("B" & t).Value = ZKb
            End If
        Next t
        t = 2
        Do While t <= Krow
            Range("C" & t) = WorksheetFunction _
                        .CountIf(Range("A:A"), Range("A" & t))
            t = t + 1
        Loop
End Sub

Module1全体は、この様な感じになります。

datacopy001-2a

使い易く加工するVBAコードのまとめ

datacopyp002

ここまでの作業で、「棚卸表作成」に表示する情報の内、

  • バーコードから読み取ったデータを使える形にしました。
  • さらに同じ本の冊数をカウントしました。

次回は、

同じ書名の重複のあるリストになっている状態を、重複を削除してカブリのないスッキリしたリストに加工します。

使える形にした書誌コードを使って、データベースから該当の書名を探し出します。

短期間でエクセルVBAの独学習得を目指したいなら

datacopyp003

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

独習によるVBA習得のキーワードは、出来るだけ多くの実例に触れること!です。

正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習などその他の手法も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう!

エクセルVBA業務ツールで日常の業務改善を行いましょう。

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min