<ExcelVBA>メーカー向けを想定した受発注システムを作った時に役に立ったこと

去年の7月くらいまでVBAを書いていてそのあとpythonをはじめたのですが、VBAをやっていてこれは特徴的だなとかこれは役に立つと思った書き方をまとめてみます。
僕はメーカー向けを想定した受発注システムを作ったのでそういうものを作りたい方には少し役に立つかも。

デバックが超便利

VBAはデバックが便利です。
コードの左側をクリックすると○印がマークできます。
その後赤文字で印を書いた　▶　再生ボタンをクリックするとコードが動き出すのですがなんと一行コードが動くたびに動きが止まってくれます。
つまり動かしながらリアルタイムにエラー箇所が特定できる。しかもエラーメッセージは日本語で出力されます。
pythonをやり始めて知ったのですが、プログラムを動かしたら途中で止めることはできないし、エラーメッセージは英語なのでどこがエラーを起こしているのか特定が簡単ではありません。
VBAはエラーを起こしたコードの特定が極めて簡単なのでデバックを積極的に使うとよいと思います。


マスターデータの書き換え

    Worksheets("部材マスタ").Select
    Columns("E").Select
    Selection.Replace Listkoubaisaki.List(Listkoubaisaki.ListIndex), Textkoubaisaki
    Worksheets("製品構成マスタ").Select
    Columns("G").Select
    Selection.Replace Listkoubaisaki.List(Listkoubaisaki.ListIndex), Textkoubaisaki

購買先の名前を書き換えたいときに最初はfor文で回していたのですがもっと良い方法がありました。
以下で該当するシートと該当する列を指定して全ての行を選択。

    Worksheets("部材マスタ").Select
    Columns("E").Select

Textkoubaisakiはテキストボックスなので修正後の名前を入力することができます。
その後リストボックスであるListkoubaisakiを選択します。

 Selection.Replace Listkoubaisaki.List(Listkoubaisaki.ListIndex), Textkoubaisaki

Replace A(変更前）, B（変更後）という構文になるのですが、Select→Selection.として事前にE列全てを選択しているため、for文を回すことなく該当するすべての名前を書き換えることができます。


データの新規追加
すでにデータが入力されている状態でデータを新規追加したい場合このように記載します。

    Range("B1048576").End(xlUp).Offset(1).Select
    ActiveCell.Value = Texttuika

データが入力されている場合データが入力されている最後のセルの次の行に新規に追加をしたいので、このような書き方になります。

Range("B1048576").End(xlUp).Offset(1).Select

1048576列目がExcelシートの最終行なのでそこから.End(xlup)で文字が記載されているセルまで上に上がっていきます。

文字が記載されているセルまで上がったら以下のコードで一列下がります。そこは空欄のセルですよね。

.offset(1).select

Texttuikaはテキストボックス。ActiveCellが現在選択しているセルなので以下のように書くとテキストボックスの値が現在選択しているセルに代入されます。

ActiveCell.Value = Texttuika

型式からデータを呼び出して発注する
コードが分かっていればコードを検索してそれを発注すればよいのですが型式の一部が分かっているだけで候補を検索してそれを注文できるようにしました。
コード長いです。

Private Sub buzaikatashikikensaku_Click()
Dim i As Long
    Dim Mymsg As Integer
    If Textbuzaikatashiki.Text = "" Then Exit Sub
    For i = 0 To Listbuzaimaster.ListCount - 1             ''(1)
        If InStr(Listbuzaimaster.List(i, 1), Textbuzaikatashiki.Text) > 0 Then ''(2)
            Listbuzaimaster.ListIndex = i                  ''(3)
            Mymsg = MsgBox("この型式でよろしいですか？", vbYesNo)
            If Mymsg = 6 Then Exit Sub
        End If
    Next i
    MsgBox "検索が終了しました"
End Sub

pythonでもあるようにfor文です。 やったのが大分前でちょっと読めなくなってますね。 Listの一番下まで下にスクロールすると書いている（はずです）。 本当はフィルターをかけてそれを再選択させる形にしたかったんですけど技術不足でできませんでした。今ならできるかな？

For i = 0 To Listbuzaimaster.ListCount - 1 
Next i

一行目リストのi行目が検索テキストに入力した値とイコールならば 3行目のメッセージボックスが出現します。 2行目がちょっと忘れてますね・・・。多分そのリストの行に実際移動してると思うんだけど。

        If InStr(Listbuzaimaster.List(i, 1), bkensaku.Text) Then  ''(2)
            Listbuzaimaster.ListIndex = i                  ''(3)
            Mymsg = MsgBox("この型式でよろしいですか？", vbYesNo)

Yesは6に該当するのでもしYes(6)ならばExit Subに飛ぶのでループを脱出します。

If Mymsg = 6 Then Exit Sub

検索した後に入力ボタンを押すと以下のプログラムが発動します。

Private Sub nyuryoku_Click()

    If Listbuzaimaster.ListIndex = -1 Then
        MsgBox "部材データが選択されていません"
        Exit Sub
    End If
    
    
    If Listbuzaimaster.List(Listbuzaimaster.ListIndex, 1) = "" Then
        MsgBox "部材データが選択されていません"
        Exit Sub
    End If
      
      
    If Textsuryou.Text = "" Then
        MsgBox "数量が入力されていません"
        Exit Sub
    End If
    
    If Textnouki.Text = "" Then
        MsgBox "指定納期が入力されていません"
        Exit Sub
    End If
    
    If Listbuzaimaster.List(Listbuzaimaster.ListIndex, 5) <> "在庫品" Then
        MsgBox "在庫品以外を選択しています"
        Listbuzaimaster.ListIndex = -1
        Exit Sub
    End If
        
        
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
        
        
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
        
        
    Range("L1048576").End(xlUp).Offset(1).Select
    ActiveCell.Value = Listbuzaimaster.List(Listbuzaimaster.ListIndex, 1)
    ActiveCell.Offset(, 1).Value = Listbuzaimaster.List(Listbuzaimaster.ListIndex, 2)
    ActiveCell.Offset(, 3).Value = Listbuzaimaster.List(Listbuzaimaster.ListIndex, 3)
    ActiveCell.Offset(, 4).Value = Listbuzaimaster.List(Listbuzaimaster.ListIndex, 4)
    ActiveCell.Offset(, 5).Value = "発注在庫品"
    ActiveCell.Offset(, 6).Value = Listbuzaimaster.List(Listbuzaimaster.ListIndex, 6)
    ActiveCell.Offset(, 2).Value = Textsuryou.Text
    ActiveCell.Offset(, 7).Value = Textnouki.Text
    ActiveCell.Offset(, -1).Value = "-"
    ActiveCell.Offset(, -2).Value = "-"
    ActiveCell.Offset(, -4).Value = "-"
    ActiveCell.Offset(, -5).Value = "-"
    ActiveCell.Offset(, -6).Value = "-"
    ActiveCell.Offset(, -7).Value = "-"
    ActiveCell.Offset(, -8).Value = "-"
    ActiveCell.Offset(, -9).Value = "-"
    ActiveCell.Offset(, -10).Value = "-"
    ActiveCell.Offset(, 10).Value = "済"
    ActiveCell.Offset(, 10).Font.Color = vbBlack
        MsgBox "部材データを入力しました"
    ActiveCell.Resize(1, 9).Copy
        
        
    Dim Mybook As Workbook
    Dim Mysheet As Worksheet
    'Set Mysheet = Worksheets("発注画面")
    Set Mybook = Workbooks.Add
        Mybook.Worksheets(1).Select
        'On Error GoTo 0
        
        Range("B9").Select
        ActiveSheet.Paste
        '　 企業名と注文書という書式を入れる
        ActiveCell.Offset(-1).Value = "部材型式"
        ActiveCell.Offset(-1, 1).Value = "品名"
        ActiveCell.Offset(-1, 2).Value = "数量"
        ActiveCell.Offset(-1, 3).Value = "単価"
        ActiveCell.Offset(-1, 7).Value = "指定納期"
        ActiveCell.Offset(-1, 8).Value = "納期回答"
        
        Range("A5").Value = Range("F9").Value
        Range("A5").Font.Size = 16
        Range("A5").Font.Bold = True
        Columns("B:H").AutoFit
        Range("B5").Value = "御中"
        Range("B5").Font.Size = 12
        Range("B5").Font.Bold = True
        Columns("F:H").Delete
        Range("C2").Value = "注文書"
        Range("C2").Font.Size = 30
        Range("C2").Font.Bold = True
        Columns("A").AutoFit
        'Worksheets(j).PageSetup.Orientation = xlLandscape '全てのシートの印刷を水平に
        
        Textsuryou.Text = ""
        Textnouki.Text = ""
        
        Workbooks("販売管理システム.xlsm").Activate
        Worksheets("発注画面").Select
        Range("A6").AutoFilter 9, ""
        
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        
        
    MsgBox "注文書を発行しました"
             
End Sub

長いのでポイントだけ。それで長いですが。

    Range("L1048576").End(xlUp).Offset(1).Select
    ActiveCell.Value = Listbuzaimaster.List(Listbuzaimaster.ListIndex, 1)
    ActiveCell.Offset(, 1).Value = Listbuzaimaster.List(Listbuzaimaster.ListIndex, 2)
（以下略）～
    ActiveCell.Offset(, -1).Value = "-"
    ActiveCell.Offset(, -2).Value = "-"
（以下略）～

    ActiveCell.Offset(, 10).Value = "済"
    ActiveCell.Offset(, 10).Font.Color = vbBlack
        MsgBox "部材データを入力しました"
    ActiveCell.Resize(1, 9).Copy

主に3つの機能に分かれていてマスタに登録されているデータを一番最初の以下略までがマスタに登録された発注データをセルを動かして入力していく作業。
2つめの以下略までが受注側の記述。
Offset(, -1)マイナス部分は受注側のセルになっているのですが今記載しているのは在庫品の発注なので受注とは関係なく発注しているので"-"としてデータがないよと表現しています。
実はこのシステム、受注があると→製品データを呼び出し→それを自動発注、という仕様なので、受注があった場合にはoffset(,-）マイナス部分にも数値が入力されます。
ちなみに

ActiveCell.Offset(, 10).Value = "済"

には意味があって、済になると注文書は出せない仕様なのですが、Excel上で普通に書き換えられるのでここを未発注と書き換えると再度注文書が出せるようになっています。

以下は新しいワークブックを作成してそこに注文書を作っています。
中略以下の3行はマクロの高速化です。これをやっておかないと固まったりしたもので。

    Dim Mybook As Workbook
    Dim Mysheet As Worksheet
    'Set Mysheet = Worksheets("発注画面")
    Set Mybook = Workbooks.Add
        Mybook.Worksheets(1).Select
        'On Error GoTo 0
        
        Range("B9").Select
        ActiveSheet.Paste
        '　 企業名と注文書という書式を入れる
        ActiveCell.Offset(-1).Value = "部材型式"
        ActiveCell.Offset(-1, 1).Value = "品名"
        ActiveCell.Offset(-1, 2).Value = "数量"
        ActiveCell.Offset(-1, 3).Value = "単価"
        ActiveCell.Offset(-1, 7).Value = "指定納期"
        ActiveCell.Offset(-1, 8).Value = "納期回答"
(中略)

        
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        

ちなみに受注→発注の流れの場合受注した時点では発注画面にすぐ移動するわけではなく、一度発注画面のセルに移動してリストから受注データをクリックして製品データを呼び出し、それを発注画面に反映させるという方法をとっています。
僕がいた会社ではここを一括でやろうとしていたのですが、
１．一般的に受注は営業、発注は購買と分業されていることので一気に処理すると分業が出来ない
２．おそらく分けたほうが作業時間を短縮できる
３．管理がしにくいとも思ったが受注側に発注状態という欄を設けて注文書が出れば済となるようプログラムを組めば特に問題ない

などの理由から業務を分けるシステムにしたのです。


このシステムは受注すると製品データが呼び出され、製品の構成や原価が一発で分かり、かつ注文も自動化できるといういうのが一番のウリです。
製品データの構築部分もプログラム的にはここで書いたようなもので問題なくできるのですが、順番や書き方を工夫しています。


もっとコードなど詳しく書きたいのですが、そこらへんの核の部分は省こうと思います。事情もあるので。
あとだいぶVBA忘れてるので説明がふわふわしてますね。
ごめんなさい。