Excelは関数だけでなく、VBAを使うことで、業務に合わせた「簡易アプリケーション」を作ることができます。今回は、Excel VBAで「売上管理アプリ」を構築し、商品・顧客・売上のデータをフォームから登録・編集・削除・表示できる仕組みを紹介します。
フォームUIを通じて、データ操作がしやすくなることで、業務効率の向上も図れます。VBA初心者の方でもステップバイステップで構築できるように丁寧に解説していきます。
目次
1. シートの構成とデータ設計
以下の3つのシートを用意します。
商品シート(商品)
商品ID | 商品名 | 単価 |
---|---|---|
P001 | コーヒー | 150 |
顧客シート(顧客)
顧客ID | 顧客名 | 電話番号 |
---|---|---|
C001 | 山田太郎 | 080-XXXX-XXXX |
売上シート(売上)
売上ID | 顧客ID | 商品ID | 数量 | 合計 | 日付 |
---|---|---|---|---|---|
1 | C001 | P001 | 2 | 300 | 2025/3/29 |
2. 売上フォームの作成(UserForm1)
このフォームでは、以下の機能を持たせます。
- 売上データの登録(前回と同じ)
- 売上データの表示(ListBox)
- 売上データの選択 → 編集 → 更新
- 売上データの削除
コントロール構成例
- ComboBox:顧客ID、商品ID
- TextBox:数量、合計(読み取り専用)、日付(初期値:Today)
- ListBox:売上一覧表示
- ボタン:新規登録、更新、削除、クリア
ListBoxに売上データを表示するコード
Private Sub UserForm_Initialize()
Call LoadSales
' 顧客と商品を読み込み
Dim i As Long
With Sheets("顧客")
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
cboCustomer.AddItem .Cells(i, 1).Value
Next i
End With
With Sheets("商品")
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
cboProduct.AddItem .Cells(i, 1).Value
Next i
End With
End Sub
Sub LoadSales()
Dim ws As Worksheet
Dim i As Long
Dim rowCount As Long
Set ws = Sheets("売上")
ListBox1.Clear
rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 2 To rowCount
ListBox1.AddItem ws.Cells(i, 1).Value ' 売上ID
ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Cells(i, 2).Value ' 顧客ID
ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Cells(i, 3).Value ' 商品ID
ListBox1.List(ListBox1.ListCount - 1, 3) = ws.Cells(i, 4).Value ' 数量
ListBox1.List(ListBox1.ListCount - 1, 4) = ws.Cells(i, 5).Value ' 合計
ListBox1.List(ListBox1.ListCount - 1, 5) = ws.Cells(i, 6).Value ' 日付
Next i
End Sub
編集・削除対象を選ぶ
Private Sub ListBox1_Click()
Dim selectedRow As Integer
selectedRow = ListBox1.ListIndex + 2
cboCustomer.Value = Sheets("売上").Cells(selectedRow, 2).Value
cboProduct.Value = Sheets("売上").Cells(selectedRow, 3).Value
txtQuantity.Value = Sheets("売上").Cells(selectedRow, 4).Value
txtTotal.Value = Sheets("売上").Cells(selectedRow, 5).Value
txtDate.Value = Sheets("売上").Cells(selectedRow, 6).Value
End Sub
更新・削除ボタンの処理
Private Sub btnUpdate_Click()
Dim r As Long
r = ListBox1.ListIndex + 2
With Sheets("売上")
.Cells(r, 2).Value = cboCustomer.Value
.Cells(r, 3).Value = cboProduct.Value
.Cells(r, 4).Value = txtQuantity.Value
.Cells(r, 5).Value = Val(txtQuantity.Value) * GetUnitPrice(cboProduct.Value)
.Cells(r, 6).Value = txtDate.Value
End With
MsgBox "売上データを更新しました"
LoadSales
End Sub
Private Sub btnDelete_Click()
Dim r As Long
r = ListBox1.ListIndex + 2
Sheets("売上").Rows(r).Delete
MsgBox "売上データを削除しました"
LoadSales
End Sub
3. 商品・顧客フォームの構成(UserForm2, UserForm3)
商品用フォーム:UserForm2
顧客用フォーム:UserForm3
それぞれに以下の要素を設けます。
- ListBoxで一覧表示
- TextBoxでデータ入力
- 登録・編集・削除ボタン
例:商品フォームの登録処理
Private Sub btnAddProduct_Click()
Dim ws As Worksheet
Set ws = Sheets("商品")
Dim newRow As Long
newRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
ws.Cells(newRow, 1).Value = txtProductID.Value
ws.Cells(newRow, 2).Value = txtProductName.Value
ws.Cells(newRow, 3).Value = txtUnitPrice.Value
MsgBox "商品を登録しました"
LoadProductList
End Sub
商品の編集・削除処理も売上と同様のロジックで作成可能です。
4. 補助関数:単価取得など
Function GetUnitPrice(productID As String) As Double
Dim ws As Worksheet
Set ws = Sheets("商品")
Dim i As Long
For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
If ws.Cells(i, 1).Value = productID Then
GetUnitPrice = ws.Cells(i, 3).Value
Exit Function
End If
Next i
GetUnitPrice = 0
End Function
5. 各フォームへのアクセス用ボタン作成
各フォーム(売上、商品、顧客)にアクセスするため、メニュー用のボタンを用意すると使いやすくなります。
Sub ShowSalesForm()
UserForm1.Show
End Sub
Sub ShowProductForm()
UserForm2.Show
End Sub
Sub ShowCustomerForm()
UserForm3.Show
End Sub
まとめ
Excel VBAを使えば、商品・顧客・売上という3種類のデータを連携させながら、フォームUIで登録・編集・削除・表示まで完結する業務アプリが作成できます。
今回紹介したように、ListBoxとComboBoxを組み合わせ、データベース的に管理することで、Excelの可能性を大きく広げることができます。業務効率の向上はもちろん、スキルアップにもつながります。
次は「検索」「売上集計」「帳票出力」なども加えて、さらに便利な売上管理システムに進化させていきましょう。