Excel VBAとMySQLを連携した販売管理システムの構築

Excel VBAを使って販売管理システムを構築する手順を紹介します。この記事では、データベース処理の共通化や各機能の実装方法について説明します。

今回はデーターベースにMySQLを利用します。ExcelとDBの連携ではAccessがよく利用されていますが、AccessはOfficeの中でも高額です。また、Accessは非常に非力で本格的なシステム構築には向きません。それに対し、MySQLは大容量のデータに対しても高速で動作し、便利な機能がたくさんあるので、非常に実用性が高くなっています。また、オープンソースなので非商用利用であれば無償で使用できるため、初心者でも導入しやすく扱いやすいため非常に人気の高いデータベース管理システムです。

フロントエンドとバックエンドの切り分け

Excel VBAを使用して、販売管理システムのフロントエンド部分を実装します。VBAコードは、Excelファイル内で動作し、ユーザーインターフェースを提供します。バックエンドとしてMySQLデータベースが使用し、データの格納と処理が行われます。VBAコードは、MySQLデータベースと通信し、必要なデータを取得、更新、削除します。

フロントエンドにExcelを利用する事で、ユーザーインターフェイスの構築が素早く行える事がメリットです。また、MySQLを利用する事でPHPやJavaScript関連のものにスケールアップした際に、そのままデータを利用する事ができます。

まずは、MySQLのODBCドライバをインストールをします。以下サイトからダウンロードしてください。注意点は、Officeが32bit版ならばODBCも32bit。Officeが64bit版ならばODBCも64bit版をダウンロードする事です。

https://dev.mysql.com/downloads/connector/odbc/

また、ADODBオブジェクトを利用しますので、メニューのツール(T) ー参照設定(R)で「ActiveX Data Objects 6.1 Library」を有効化してください。

MySQLは別途インストールが必要です。
XAMPPをインストールしてしまうのが一番楽だと思います。

MySQLデータベースの設計

まず、販売管理システムのバックエンドとしてMySQLデータベースを設計します。以下に見積管理機能、受注管理機能、売上・売掛管理機能、請求管理機能、購買管理機能、在庫管理機能に関するテーブル設計を示します。

見積管理機能:
- 見積テーブル (estimates)
  - 見積ID (estimate_id) – 主キー
  - 顧客ID (customer_id) – 外部キー
  - 見積日時 (estimate_date)
  - 有効期限 (expiry_date)
  - 合計金額 (total_amount)
  - 備考 (notes)
受注管理機能:
- 受注テーブル (orders)
  - 注文ID (order_id) – 主キー
  - 顧客ID (customer_id) – 外部キー
  - 注文日時 (order_date)
  - 受注状況 (order_status)
  - 合計金額 (total_amount)
  - 備考 (notes)
売上・売掛管理機能:
- 売上テーブル (sales)
  - 売上ID (sale_id) – 主キー
  - 注文ID (order_id) – 外部キー
  - 売上日時 (sale_date)
  - 商品ID (product_id) – 外部キー
  - 数量 (quantity)
  - 金額 (amount)
- 売掛テーブル (receivables)
  - 売掛ID (receivable_id) – 主キー
  - 顧客ID (customer_id) – 外部キー
  - 請求日時 (billing_date)
  - 請求金額 (billing_amount)
  - 支払状況 (payment_status)
請求管理機能:
- 請求テーブル (invoices)
  - 請求ID (invoice_id) – 主キー
  - 受注ID (order_id) – 外部キー
  - 請求日時 (invoice_date)
  - 請求金額 (invoice_amount)
  - 支払状況 (payment_status)
購買管理機能:
- 発注テーブル (purchase_orders)
  - 発注ID (purchase_order_id) – 主キー
  - 仕入先ID (vendor_id) – 外部キー
  - 発注日時 (order_date)
  - 発注状況 (order_status)
  - 合計金額 (total_amount)
  - 備考 (notes)
- 仕入テーブル (purchases)
  - 仕入ID (purchase_id) – 主キー
  - 発注ID (purchase_order_id) – 外部キー
  - 仕入日時 (purchase_date)
  - 商品ID (product_id) – 外部キー
  - 数量 (quantity)
  - 金額 (amount)
- 出荷・入荷テーブル (shipments)
  - 出荷ID (shipment_id) – 主キー
  - 仕入ID (purchase_id) – 外部キー
  - 出荷日時 (shipment_date)
  - 入荷日時 (arrival_date)
  - 数量 (quantity)
  - 備考 (notes)
- 債務テーブル (debts)
  - 債務ID (debt_id) – 主キー
  - 仕入ID (purchase_id) – 外部キー
  - 支払期限 (due_date)
  - 債務金額 (debt_amount)
  - 支払状況 (payment_status)
在庫管理機能:
- 在庫テーブル (inventory)
  - 商品ID (product_id) – 主キー
  - 商品名 (product_name)
  - 在庫数 (quantity)
  - 最終更新日時 (last_updated)

上記の設計は基本的な設計案であり、実際のビジネス要件に応じてテーブル構造や関係性を調整して下さい。また、データベースにインデックスや制約を追加して、パフォーマンスとデータの整合性を向上させることも重要です。

MySQLテーブル作成

MySQLデータベースの設計が完了したので、次はそれを実装していきましょう。以下は、各テーブルの作成に必要なDDL（Data Definition Language）の例です。これを実行することで、データベース内にテーブルを作成できます。

-- 見積テーブルの作成
CREATE TABLE estimates (
    estimate_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    estimate_date DATE,
    expiry_date DATE,
    total_amount DECIMAL(10,2),
    notes TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 受注テーブルの作成
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_status VARCHAR(50),
    total_amount DECIMAL(10,2),
    notes TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 売上テーブルの作成
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    sale_date DATE,
    product_id INT,
    quantity INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 売掛テーブルの作成
CREATE TABLE receivables (
    receivable_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    billing_date DATE,
    billing_amount DECIMAL(10,2),
    payment_status VARCHAR(50)
);

-- 請求テーブルの作成
CREATE TABLE invoices (
    invoice_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    invoice_date DATE,
    invoice_amount DECIMAL(10,2),
    payment_status VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 発注テーブルの作成
CREATE TABLE purchase_orders (
    purchase_order_id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT,
    order_date DATE,
    order_status VARCHAR(50),
    total_amount DECIMAL(10,2),
    notes TEXT,
    FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_id)
);

-- 仕入テーブルの作成
CREATE TABLE purchases (
    purchase_id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id INT,
    purchase_date DATE,
    product_id INT,
    quantity INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(purchase_order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 出荷・入荷テーブルの作成
CREATE TABLE shipments (
    shipment_id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT,
    shipment_date DATE,
    arrival_date DATE,
    quantity INT,
    notes TEXT,
    FOREIGN KEY (purchase_id) REFERENCES purchases(purchase_id)
);

-- 債務テーブルの作成
CREATE TABLE debts (
    debt_id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT,
    due_date DATE,
    debt_amount DECIMAL(10,2),
    payment_status VARCHAR(50),
    FOREIGN KEY (purchase_id) REFERENCES purchases(purchase_id)
);

-- 在庫テーブルの作成
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

これで、各テーブルが作成されました。次は、必要に応じてテーブル間の関連性を確立するための外部キー制約を追加します。また、テーブルにデータを挿入するためのINSERT文を使用して、必要な初期データを追加することもできます。

VBAコードの実装

Excel VBAを使用してMySQLに接続し、テーブルを作成する手順を以下に示します。まずは、VBAでMySQLに接続するために必要なライブラリの参照設定を行います。

Excelを開きます。
Alt + F11を押してVBAエディタを開きます。
メニューから「ツール」 > 「参照設定」を選択します。
「Microsoft ActiveX Data Objects x.x Library」を選択し、チェックを入れてOKをクリックします（x.xは使用可能なバージョンに応じて異なる場合があります）。

Option Explicit

Sub LoadEstimates()
    ' 見積データをロードしてシートに表示するサンプルコード
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim i As Integer
    
    ' 接続情報設定
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "DRIVER={MySQL ODBC 8.3 Unicode Driver};SERVER=MySQLサーバーアドレス;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード;"
    
    ' SQL文を定義
    strSQL = "SELECT * FROM estimates;"
    
    ' レコードセットを取得
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, conn
    
    ' シートにデータを表示
    Worksheets("見積一覧").Activate ' 表示先のシート名を適宜変更
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    Range("A2").CopyFromRecordset rs
    
    ' 接続を閉じる
    rs.Close
    conn.Close
End Sub

Sub AddNewEstimate()
    ' 新しい見積を追加するサンプルコード
    Dim conn As Object
    Dim strSQL As String
    
    ' 接続情報設定
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "DRIVER={MySQL ODBC 8.3 Unicode Driver};SERVER=MySQLサーバーアドレス;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード;"
    
    ' 新しい見積のデータを挿入するSQL文を定義（適宜データを変更）
    strSQL = "INSERT INTO estimates (customer_id, estimate_date, expiry_date, total_amount, notes) " & _
             "VALUES (1, '2024-02-15', '2024-02-28', 1000.00, '新しい見積の備考');"
    
    ' SQL文を実行
    conn.Execute strSQL
    
    ' 接続を閉じる
    conn.Close
    
    ' 更新したデータを再表示するなど、適切な処理を行う
    LoadEstimates ' 見積データを再ロードして更新を反映
End Sub

このサンプルコードは、見積データの表示と新しい見積の追加を行います。適切なデータベースの接続情報を設定してください。また、このコードはあくまでサンプルであり、実際のシステムではエラー処理やデータのバリデーションなどを追加することが必要です。

DB処理の共通化

上のサンプルコードでは、他の機能（受注管理、売上・売掛管理など）を同じ様なコードで追加すると修正が大変になってしまうので処理の共通化を行います。

データベース処理を共通化することで、コードの重複を避け、保守性を高めることができます。以下は、データベース処理を共通化するための例です。

まず、データベースに接続するための関数を作成します。

Function GetConnection() As Object
    ' データベースへの接続を取得する関数
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "DRIVER={MySQL ODBC 8.3 Unicode Driver};SERVER=MySQLサーバーアドレス;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード;"
    Set GetConnection = conn
End Function

次に、データベースからデータを取得するための関数を作成します。

Sub LoadDataFromDB(ByVal strSQL As String, ByVal targetSheetName As String)
    ' データベースからデータを取得して指定されたシートに表示する関数
    Dim conn As Object
    Dim rs As Object
    Dim i As Integer
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してレコードセットを取得
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, conn
    
    ' シートにデータを表示
    Worksheets(targetSheetName).Activate
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    Range("A2").CopyFromRecordset rs
    
    ' 接続を閉じる
    rs.Close
    conn.Close
End Sub

次にデータを追加する関数、更新する関数、データを削除する関数を作成します。

Sub InsertDataToDB(ByVal strSQL As String)
    ' データベースに新しいデータを追加する関数
    Dim conn As Object
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してデータを追加
    conn.Execute strSQL
    
    ' 接続を閉じる
    conn.Close
End Sub

Sub UpdateDataInDB(ByVal strSQL As String)
    ' データベースのデータを更新する関数
    Dim conn As Object
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してデータを更新
    conn.Execute strSQL
    
    ' 接続を閉じる
    conn.Close
End Sub

Sub DeleteDataFromDB(ByVal strSQL As String)
    ' データベースからデータを削除する関数
    Dim conn As Object
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してデータを削除
    conn.Execute strSQL
    
    ' 接続を閉じる
    conn.Close
End Sub

これで、データベース操作の処理が共通化できました。これらの関数を使って、各機能のVBAコードを簡潔に保つことができます。必要に応じて適切なSQL文を生成し、これらの関数に渡すことで、データベースの操作を行うことができます。

全ての機能を実装する

以下は、販売管理システムのExcel VBAフロントエンドの完成したコードです。見積、受注、売上、売掛、請求、発注、仕入、出荷・入荷、債務、在庫の各機能を実装しています。

Option Explicit

Sub LoadDataFromDB(ByVal strSQL As String, ByVal targetSheetName As String)
    ' データベースからデータを取得して指定されたシートに表示する関数
    Dim conn As Object
    Dim rs As Object
    Dim i As Integer
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してレコードセットを取得
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, conn
    
    ' シートにデータを表示
    Worksheets(targetSheetName).Activate
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    Range("A2").CopyFromRecordset rs
    
    ' 接続を閉じる
    rs.Close
    conn.Close
End Sub

Sub InsertDataToDB(ByVal strSQL As String)
    ' データベースに新しいデータを追加する関数
    Dim conn As Object
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してデータを追加
    conn.Execute strSQL
    
    ' 接続を閉じる
    conn.Close
End Sub

Sub UpdateDataInDB(ByVal strSQL As String)
    ' データベースのデータを更新する関数
    Dim conn As Object
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してデータを更新
    conn.Execute strSQL
    
    ' 接続を閉じる
    conn.Close
End Sub

Sub DeleteDataFromDB(ByVal strSQL As String)
    ' データベースからデータを削除する関数
    Dim conn As Object
    
    ' データベースに接続
    Set conn = GetConnection()
    
    ' SQL文を実行してデータを削除
    conn.Execute strSQL
    
    ' 接続を閉じる
    conn.Close
End Sub

Function GetConnection() As Object
    ' データベースへの接続を取得する関数
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=MySQLサーバーアドレス;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード;"
    Set GetConnection = conn
End Function

Sub LoadEstimates()
    ' 見積データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM estimates;"
    LoadDataFromDB strSQL, "見積一覧"
End Sub

Sub AddNewEstimate()
    ' 新しい見積を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO estimates (customer_id, estimate_date, expiry_date, total_amount, notes) " & _
             "VALUES (1, '2024-02-15', '2024-02-28', 1000.00, '新しい見積の備考');"
    InsertDataToDB strSQL
    LoadEstimates ' 見積データを再ロードして更新を反映
End Sub

Sub LoadOrders()
    ' 受注データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM orders;"
    LoadDataFromDB strSQL, "受注一覧"
End Sub

Sub AddNewOrder()
    ' 新しい受注を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO orders (customer_id, order_date, order_status, total_amount, notes) " & _
             "VALUES (1, '2024-02-15', '新規', 1500.00, '新しい受注の備考');"
    InsertDataToDB strSQL
    LoadOrders ' 受注データを再ロードして更新を反映
End Sub

Sub LoadSales()
    ' 売上データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM sales;"
    LoadDataFromDB strSQL, "売上一覧"
End Sub

Sub AddNewSale()
    ' 新しい売上を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO sales (order_id, sale_date, product_id, quantity, amount) " & _
             "VALUES (1, '2024-02-15', 1, 5, 750.00);"
    InsertDataToDB strSQL
    LoadSales ' 売上データを再ロードして更新を反映
End Sub

Sub LoadReceivables()
    ' 売掛データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM receivables;"
    LoadDataFromDB strSQL, "売掛一覧"
End Sub

Sub AddNewReceivable()
    ' 新しい売掛を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO receivables (customer_id, billing_date, billing_amount, payment_status) " & _
             "VALUES (1, '2024-02-15', 1500.00, '未払い');"
    InsertDataToDB strSQL
    LoadReceivables ' 売掛データを再ロードして更新を反映
End Sub

Sub LoadInvoices()
    ' 請求データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM invoices;"
    LoadDataFromDB strSQL, "請求一覧"
End Sub

Sub AddNewInvoice()
    ' 新しい請求を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO invoices (order_id, invoice_date, invoice_amount, payment_status) " & _
             "VALUES (1, '2024-02-15', 1500.00, '未払い');"
    InsertDataToDB strSQL
    LoadInvoices ' 請求データを再ロードして更新を反映
End Sub

Sub LoadPurchaseOrders()
    ' 発注データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM purchase_orders;"
    LoadDataFromDB strSQL, "発注一覧"
End Sub

Sub AddNewPurchaseOrder()
    ' 新しい発注を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO purchase_orders (vendor_id, order_date, order_status, total_amount, notes) " & _
             "VALUES (1, '2024-02-15', '新規', 2000.00, '新しい発注の備考');"
    InsertDataToDB strSQL
    LoadPurchaseOrders ' 発注データを再ロードして更新を反映
End Sub

Sub LoadPurchases()
    ' 仕入データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM purchases;"
    LoadDataFromDB strSQL, "仕入一覧"
End Sub

Sub AddNewPurchase()
    ' 新しい仕入を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO purchases (purchase_order_id, purchase_date, product_id, quantity, amount) " & _
             "VALUES (1, '2024-02-15', 1, 10, 1500.00);"
    InsertDataToDB strSQL
    LoadPurchases ' 仕入データを再ロードして更新を反映
End Sub

Sub LoadShipments()
    ' 出荷・入荷データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM shipments;"
    LoadDataFromDB strSQL, "出荷・入荷一覧"
End Sub

Sub AddNewShipment()
    ' 新しい出荷・入荷を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO shipments (purchase_id, shipment_date, arrival_date, quantity, notes) " & _
             "VALUES (1, '2024-02-15', '2024-02-20', 10, '新しい出荷・入荷の備考');"
    InsertDataToDB strSQL
    LoadShipments ' 出荷・入荷データを再ロードして更新を反映
End Sub

Sub LoadDebts()
    ' 債務データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM debts;"
    LoadDataFromDB strSQL, "債務一覧"
End Sub

Sub AddNewDebt()
    ' 新しい債務を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO debts (purchase_id, due_date, debt_amount, payment_status) " & _
             "VALUES (1, '2024-02-28', 2000.00, '未払い');"
    InsertDataToDB strSQL
    LoadDebts ' 債務データを再ロードして更新を反映
End Sub

Sub LoadInventory()
    ' 在庫データをロードしてシートに表示するサンプルコード
    Dim strSQL As String
    strSQL = "SELECT * FROM inventory;"
    LoadDataFromDB strSQL, "在庫一覧"
End Sub

Sub AddNewInventory()
    ' 新しい在庫を追加するサンプルコード
    Dim strSQL As String
    strSQL = "INSERT INTO inventory (product_id, product_name, quantity, last_updated) " & _
             "VALUES (1, '商品A', 100, NOW());"
    InsertDataToDB strSQL
    LoadInventory ' 在庫データを再ロードして更新を反映
End Sub

このコードを使って、見積、受注、売上、売掛、請求、発注、仕入、出荷・入荷、債務、在庫の各機能にアクセスし、データベースの操作を行うことができます。各機能に対応するボタンやメニューをExcelに追加し、それぞれの処理を呼び出すことができます。