Today's Task

Put everything from the first 8 days together to complete the exercises below. The point of these mini VBA practice projects is to string variables, loops, objects, and error handling into complete, working programs.

Exercises

Exercise 1: Score Analyzer

Build a program that totals student scores and assigns a grade. Hint: store the scores in an array, total them in a loop, and grade with Select Case.

Exercise 2: Data Filler

Automatically fill a range of cells with data. Hint: pair Cells(row, col) with loops, and use With to format the header in one go.

Exercise 3: Worksheet Processor

Loop through every worksheet and perform a batch operation on each. Hint: For Each ws In Worksheets.

Reference Code Skeleton

vba
Sub ScoreAnalyzer()
    ' Uses variables, arrays, conditionals, and loops
End Sub

Sub DataFiller()
    ' Uses Range and loops
End Sub

One complete implementation of the Score Analyzer (matches the .bas file):

vba
Sub ScoreAnalyzer()
    Dim scores(1 To 10) As Integer
    Dim i As Integer, total As Integer
    Dim avg As Double, grade As String

    Randomize
    For i = 1 To 10
        scores(i) = Int(Rnd * 41) + 60  ' between 60 and 100
    Next i

    For i = 1 To 10
        total = total + scores(i)
    Next i
    avg = total / 10
    Select Case avg
        Case Is >= 90: grade = "Excellent"
        Case Is >= 70: grade = "Average"
        Case Else: grade = "Fail"
    End Select
    MsgBox "Total: " & total & "  Average: " & avg & "  Grade: " & grade
End Sub

Requirements

Write complete code
Add comments where they help
Add error handling
Test your results

Exercise Solutions

Score Analyzer: store the scores in an array, compute the total and average in a loop, and grade with If/Select Case.
Data Filler: use a nested loop over rows and columns, assigning with Cells(row, col).Value.
Worksheet Processor: iterate with For Each ws In Worksheets and act on each sheet in turn.

Real-World Scenario

Procurement needs a price list of 20 products every month, with simulated prices and a bold header. One small program does it all:

vba
Sub GeneratePriceList()
    Dim i As Integer
    On Error GoTo ErrorHandler

    For i = 1 To 20
        Cells(i + 1, 1).Value = i
        Cells(i + 1, 2).Value = "Product" & i
        Cells(i + 1, 3).Value = Int(Rnd * 1000) + 100
    Next i

    With Range("A1:C1")
        .Value = Array("No.", "Product Name", "Price")
        .Font.Bold = True
    End With
    MsgBox "Price list generated", vbInformation
    Exit Sub

ErrorHandler:
    MsgBox "Generation failed: " & Err.Description, vbCritical
End Sub

This scenario ties the first 8 days together: a loop plus Cells to target rows with a variable (Day04, Day06), With to format the header in one shot (Day06), and On Error GoTo as the safety net (Day07). Run it and you get a list with a bold header; any failure produces a clear message instead of a crash.

Common Errors and Troubleshooting

Compile error: Variable not defined — the module has Option Explicit at the top and a new variable was never declared with Dim; add the declaration.
Run-time error '6': Overflow — Integer cannot hold the running total (its ceiling is 32767); switch the sum variable to Long.
Run-time error '11': Division by zero — the count variable was never assigned, so it is still 0 when you compute the average; check the denominator first.
Assigning Array(...) to a range raises Run-time error '1004' — the number of array elements does not match the size of the range.
Rnd produces the same sequence on every run — Randomize is missing at the start, so the random sequence is pinned.

Extension Exercises

Extend the Score Analyzer with three more metrics: highest score, lowest score, and a count of failing scores.
Change the Data Filler to read the row count from InputBox, and reject non-numeric input.
Rework the Worksheet Processor: write the number of rows in each sheet's used range to cell A2 (UsedRange.Rows.Count).

由在线工具箱(www.vba.net)整理制作

🌱 Day09 - Consolidation Exercises