[Project] Build a small inventory sheet

In this lesson you combine the arrays, Subs and Functions, For statements, and Select Case covered so far into a simple inventory macro. It is the capstone of this VBA introduction.

Real business macros use this combination constantly: hold several pieces of data such as a product name and a quantity in arrays, work out the stock status from conditions, and display the results together.

The sample code sets up arrays of product names and quantities, works out each stock status with a StatusOf function, and assembles the listing into a message.

Cell operations, arrays, and error handling — everything covered in these lessons — are the foundations of real business macro development. Starting with small macros and automating the repetitive parts of your day one at a time is the shortest path to using VBA professionally.

📖 Reference code

Function StatusOf(quantity As Integer) As String
    If quantity = 0 Then
        StatusOf = "Out of stock"
    ElseIf quantity < 5 Then
        StatusOf = "Low stock"
    Else
        StatusOf = "In stock"
    End If
End Function

Sub InventoryReport()
    Dim names(2) As String
    Dim quantities(2) As Integer
    Dim i As Integer
    Dim result As String

    names(0) = "Notebook": quantities(0) = 12
    names(1) = "Eraser": quantities(1) = 3
    names(2) = "Ruler": quantities(2) = 0

    For i = 0 To 2
        result = result & names(i) & ": " & quantities(i) & " (" & StatusOf(quantities(i)) & ")" & vbNewLine
    Next i
    MsgBox result
End Sub

✍️ Your code

Type your code, then press "Run"

🧪 This site can't compile or run VBA directly, so it checks on the spot whether what you typed matches the reference code (scoring happens entirely in your browser — nothing is sent anywhere).