雑誌コードとISBNコードを分解加工するVBAコードを組み立てる。棚卸表作成

datacopyeyecatchaa

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

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

 

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

 

前の記事の確認をするにはこちら↓から。

codeeyecatchaバーコードで在庫調べする方法。エクセルVBAのシートイベントを使う

 

「雑誌と書籍の在庫リスト」の完成形はこの記事↓をご覧ください。

【棚卸し】無料入手!在庫管理の「雑誌と書籍の在庫リスト」DLと取説

 

雑誌と書籍の在庫リスト作成に関連した記事はこちらになります。

「棚卸表作成」の記事一覧を開く

 

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

gyoka1_1138b

 

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

 

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

 

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

使用するデータは、

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

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

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

 

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

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

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

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

 

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

 

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

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

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

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

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

 

コード①

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コード

 

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

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

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

 

エラーのチェックは、

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

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

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

MEMO

シートのコピーについては、こちら↓の記事が参考にできます。

VBACopyeyecatchシートの操作 コピーを最速に理解!VBAコードで異なる結果

If条件文についてはこの記事を参考にしてください。

vbaifjyokeneyecatch「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。

最終行の取得方法についてはこちらを参考にしてください。

vbalastcelleyecatchセルの範囲選択 データ入力済セルの最終行番号を取得する

Len関数についてはこちらを参考にしてください。

vbaleneyecatchLen関数・LenB関数で文字列の文字数を知る方法。使用例で解説します

Left関数についてはこちらを参考にしてください。

vbamideyecstchMid関数・Right関数・Left関数は文字列中の文字を切り出します

For Each ~ Next ステートメントについてはこちらを参考にしてください。

vbadoloopeyecatchVBA 回数不定のループ処理はDo LoopとFor Each

 

コード②

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 Sub

 

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

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

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

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

MEMO

メッセージボックスの使い方はこちらを参考にしてください。

vbamsgboxeyecatchメッセージボックス MsgBox実際の使い方を最速に理解

 

コード③

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

 

変数の宣言については、一部「Public」を使っています。

 

 

Public CP As Integer
Public Srow As Long
Public Krow As Long
Public ShopName As String
Public SpaceName As String

 

この様な感じです。

datacopy001-2a

 

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

 

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

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

 

次回は、このような予定です。

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

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

 

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

 

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

けれども、

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

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

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

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

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

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること!

です。

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

ハッキリ言って、

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

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

 

 

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

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

 

エクセルVBAを使いだして、始めのうちに知っておきたい内容を纏めています。

「エクセルVBA最速理解」の記事一覧を開く

 

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

 

 

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