雑誌と書籍の在庫表作成のために、今回はバーコードリーダーで読み込んだISBNコードを使えるコードに加工します。
エクセルVBAを利用しますが、同時にコードの間違いチェックも行えるようにしていきます。
こんにちは、じゅんぱ店長(@junpa33)です。
今回は「書誌棚卸表.xlsm」の「データコピー」と「作業シート」シートの作成のためのVBAコードを組み立てていきます。
雑誌と書籍の在庫リスト(棚卸表)作成の記事編成
- 雑誌と書籍の在庫リストの使い方とダウンロード
- 書誌データベースのダウンロード
コンテンツ
雑誌コードとISBNコードの分解加工VBA
![datacopyp001](https://shimaydo.com/wp-content/uploads/2019/09/datacopyp001.jpg)
今回設置するシートの役割
VBAはこのような感じで組み立てていきたいと思います。
「データコピー」シートは使用するデータを置くシートとしています。
使用するデータは、
- 「コード入力表」のバーコードリーダーで読み込んだデータ
- 他から持ってきたコピーペーストしたデータ
のどちらでも使用できるようにしています。
使用データ選択方法は、ユーザーフォームに”トグルボタン”を設置します。
「作業シート」は使用するデータを加工するシートとしています。
データを加工する作業としては、
- 「作業シート」へ元となるデータを移動すると同時に、コードのエラーの明らかなものをチェックします。
- 元となるコードを使えるような形式に変換します。
- 同じものの冊数を数えます。
- データベースと連携してコードから書名を探して表示します。
エラーの表示はセルを着色して表示します。
「データコピー」シートでのVBAコード
Module1にコードを記述して行く前に、宣言セクション(一番上段)にパブリック変数の宣言を行います。
![vbasengeneyecatch](https://shimaydo.com/wp-content/uploads/2020/11/vbasengeneyecatch-160x120.jpg)
Public CP As Integer
Public Srow As Long
Public Krow As Long
Public ShopName As String
Public SpaceName As String
前回の記事で説明しましたが、「コード入力表」のバーコード読み取り操作が完了して、そのデータをこの「データコピー」シートに移す作業を行う場合、
「データ入力終了後の操作(シートから移動した時に起動)」で説明した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」
以外のコードについてはその入力セルがクリーム色(エラー色)となります。
メッセージボックスで「色付きセルのデータを再チェックしてください!」を表示します。
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
このプロシージャーで行うことは、
- 元のコードから雑誌コードを抜き出します。
- 元のコードから雑誌の価格情報を抜き出します。
- 書籍の価格コードから価格情報を抜き出します。
- 同じ書誌の数量をカウントします。
コードのエラーが表示されているときは、このプロシージャーを実行する前に訂正が必要です。
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](https://shimaydo.com/wp-content/uploads/2019/09/datacopy001-2a-645x1024.jpg)
使い易く加工するVBAコードのまとめ
![datacopyp002](https://shimaydo.com/wp-content/uploads/2019/09/datacopyp002.jpg)
ここまでの作業で、「棚卸表作成」に表示する情報の内、
- バーコードから読み取ったデータを使える形にしました。
- さらに同じ本の冊数をカウントしました。
次回は、
同じ書名の重複のあるリストになっている状態を、重複を削除してカブリのないスッキリしたリストに加工します。
使える形にした書誌コードを使って、データベースから該当の書名を探し出します。
短期間でエクセルVBAの独学習得を目指したいなら
![datacopyp003](https://shimaydo.com/wp-content/uploads/2019/09/datacopyp003.jpg)
エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。
けれども、
出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。
独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。
先人が行った勉強方法をあなたがそのまま利用すればよいということです。
![vbastudyeyecatch002](https://shimaydo.com/wp-content/uploads/2022/01/vbastudyeyecatch002-160x120.jpg)
独習のための大切な7つのポイントは、上記記事にて解説しています。
独習によるVBA習得のキーワードは、出来るだけ多くの実例に触れること!です。
正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。
ハッキリ言って、
本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習などその他の手法も取り入れて行うことが、
手っ取り早く短期間習得できるというのは間違いないでしょう。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
![vbastudyeyecatch2](https://shimaydo.com/wp-content/uploads/2022/01/vbastudyeyecatch2-160x120.jpg)
今回の記事はここまでです。 最後までご覧いただき有難うございました。
<記事内容についての告知>
VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。