Develop Customized Warehouse Management Tools with Excel VBA

Warehouse management is an integral part of the supply chain process. Efficiently managing inventory, tracking shipments, and monitoring stock levels can significantly improve business operations. Excel VBA (Visual Basic for Applications) allows you to automate tasks, create custom solutions, and build sophisticated Warehouse Management Systems (WMS) without the need for complex third-party software.

In this detailed explanation, I will guide you through the process of developing a customized Warehouse Management Tool using Excel VBA. This tool will help manage stock levels, track orders, handle inventory, and generate necessary reports.

Prerequisites

Before diving into the code, make sure you:

1. Have basic knowledge of Excel and VBA.
2. Understand how warehouses manage inventory (stock, orders, shipments).
3. Have access to Excel’s Developer tab to write and test VBA code.

Step 1: Planning the Warehouse Management Tool

A good warehouse management system (WMS) needs certain functionalities such as:

1. Inventory Management: Track stock levels and product details.
2. Order Management: Create and manage orders.
3. Shipping: Record and track shipments of products.
4. Reporting: Generate reports (inventory levels, orders, shipments).

Let’s break down each component of the system:

1. Inventory: This will include product details such as Product ID, Product Name, Stock Level, Stock Location, and Price.
2. Orders: Information about incoming and outgoing orders, such as Order ID, Product ID, Quantity Ordered, Customer Details, etc.
3. Shipping: Managing shipments, including Shipment ID, Order ID, Shipping Date, Shipment Status, etc.
4. Reports: The system will generate reports based on the current data in the inventory, orders, and shipping lists.

Step 2: Setting up the Spreadsheet Structure

1. Inventory Sheet:
   • Columns: Product ID, Product Name, Quantity in Stock, Price, Location
2. Orders Sheet:
   • Columns: Order ID, Customer Name, Product ID, Quantity Ordered, Order Date, Status
3. Shipping Sheet:
   • Columns: Shipment ID, Order ID, Shipping Date, Tracking Number, Status
4. Report Sheet:
   • Generate dynamic reports like Stock Level Report, Order Status Report, Shipping Report.

Step 3: Writing the VBA Code for Inventory Management

To start, we will write a few VBA functions to handle basic inventory management operations, such as adding products, updating stock levels, and retrieving product details.

1. Add New Product

This code will allow you to add a new product to the Inventory sheet.

Sub AddNewProduct()
    Dim ws As Worksheet
    Dim productID As String
    Dim productName As String
    Dim quantity As Integer
    Dim price As Double
    Dim location As String   
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Inventory")   
    ' Input the new product details
    productID = InputBox("Enter Product ID")
    productName = InputBox("Enter Product Name")
    quantity = InputBox("Enter Quantity")
    price = InputBox("Enter Product Price")
    location = InputBox("Enter Product Location")   
    ' Find the next available row in Inventory sheet
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1   
    ' Add the new product details to the next available row
    ws.Cells(lastRow, 1).Value = productID
    ws.Cells(lastRow, 2).Value = productName
    ws.Cells(lastRow, 3).Value = quantity
    ws.Cells(lastRow, 4).Value = price
    ws.Cells(lastRow, 5).Value = location   
    MsgBox "New product added successfully!"
End Sub

Explanation:

• The AddNewProduct subroutine allows the user to input product details (ID, name, quantity, price, location) and adds them to the Inventory sheet.
• lastRow is used to find the next available row in the Inventory sheet.
• The product details are placed in columns A through E.

2. Update Stock Level

This code helps you update the stock level of a product when new inventory arrives or when stock is shipped out.

Sub UpdateStockLevel()
    Dim ws As Worksheet
    Dim productID As String
    Dim quantityChange As Integer
    Dim productRow As Long   
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Inventory")   
    ' Get Product ID and quantity change
    productID = InputBox("Enter Product ID")
    quantityChange = InputBox("Enter Quantity Change (positive or negative)")   
    ' Find the product row in the Inventory sheet
    On Error Resume Next
    productRow = Application.Match(productID, ws.Range("A:A"), 0)
    On Error GoTo 0   
    ' Check if the product exists
    If productRow > 0 Then
        ' Update the stock level
        ws.Cells(productRow, 3).Value = ws.Cells(productRow, 3).Value + quantityChange
        MsgBox "Stock level updated successfully!"
    Else
        MsgBox "Product ID not found!"
    End If
End Sub

Explanation:

• This code asks for a Product ID and the Quantity Change (could be negative for shipment or positive for stock addition).
• It finds the row corresponding to the Product ID in the Inventory sheet.
• The stock level in column C (Quantity in Stock) is updated based on the input.

Step 4: Writing the VBA Code for Order Management

The order management system can be built with functions that allow adding orders, updating the status, and checking order details.

1. Add Order

Sub AddOrder()
    Dim ws As Worksheet
    Dim orderID As String
    Dim customerName As String
    Dim productID As String
    Dim quantity As Integer
    Dim orderDate As String
    Dim status As String   
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Orders")
    ' Input order details
    orderID = InputBox("Enter Order ID")
    customerName = InputBox("Enter Customer Name")
    productID = InputBox("Enter Product ID")
    quantity = InputBox("Enter Quantity Ordered")
    orderDate = InputBox("Enter Order Date (MM/DD/YYYY)")
    status = "Pending"  ' Default status   
    ' Find the next available row in the Orders sheet
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    ' Add the order details to the next available row
    ws.Cells(lastRow, 1).Value = orderID
    ws.Cells(lastRow, 2).Value = customerName
    ws.Cells(lastRow, 3).Value = productID
    ws.Cells(lastRow, 4).Value = quantity
    ws.Cells(lastRow, 5).Value = orderDate
    ws.Cells(lastRow, 6).Value = status
    MsgBox "Order added successfully!"
End Sub

Explanation:

• The code captures customer order details like Order ID, Customer Name, Product ID, Quantity, Order Date, and sets the default status as « Pending ».
• The order details are added to the Orders sheet.

Step 5: Writing the VBA Code for Shipping Management

Shipping management includes updating the shipping status, generating tracking numbers, and marking shipments as complete.

1. Ship Order

Sub ShipOrder()
    Dim ws As Worksheet
    Dim orderID As String
    Dim shipmentID As String
    Dim shippingDate As String
    Dim trackingNumber As String
    Dim status As String   
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Shipping")   
    ' Input shipment details
    orderID = InputBox("Enter Order ID")
    shipmentID = InputBox("Enter Shipment ID")
    shippingDate = InputBox("Enter Shipping Date (MM/DD/YYYY)")
    trackingNumber = InputBox("Enter Tracking Number")
    status = "Shipped"   
    ' Find the order row in the Orders sheet
    Dim orderRow As Long
    On Error Resume Next
    orderRow = Application.Match(orderID, ThisWorkbook.Sheets("Orders").Range("A:A"), 0)
    On Error GoTo 0   
    If orderRow > 0 Then
        ' Add shipping details to the Shipping sheet
        Dim lastRow As Long
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
        ws.Cells(lastRow, 1).Value = shipmentID
        ws.Cells(lastRow, 2).Value = orderID
        ws.Cells(lastRow, 3).Value = shippingDate
        ws.Cells(lastRow, 4).Value = trackingNumber
        ws.Cells(lastRow, 5).Value = status    
        ' Update order status to 'Shipped'
        ThisWorkbook.Sheets("Orders").Cells(orderRow, 6).Value = "Shipped"      
        MsgBox "Order shipped successfully!"
    Else
        MsgBox "Order ID not found!"
    End If
End Sub

Explanation:

• This function records shipping details such as Shipment ID, Shipping Date, Tracking Number, and updates the Shipping sheet.
• It also updates the Orders sheet to change the order status to « Shipped ».

Conclusion

By using Excel VBA, we can automate and customize warehouse management functions like inventory tracking, order management, and shipping. This system allows easy tracking of stock levels, managing customer orders, and shipping logistics while generating useful reports. You can extend the tool with advanced features like barcode scanning, automatic reorder levels, and integration with other systems to streamline your warehouse operations.

This approach is scalable and flexible for businesses of various sizes, with Excel being a cost-effective solution to manage warehouse operations without requiring heavy software investments.