Practical VBA workshop

Goal: find the second-highest distinct value and define what should happen with duplicates.

A performance list contains tied results. Management needs the second distinct value, not simply the second row after sorting.

What you will build

• A complete procedure that works without Select or Activate.
• Test data and expected behaviour you can verify before using real files.
• Error cases and production checks that prevent silent damage.

Complete VBA example

Option Explicit
Public Function SecondDistinctHighest(ByVal rng As Range) As Variant
    Dim cell As Range, highest As Double, second As Double
    Dim haveHigh As Boolean, haveSecond As Boolean, value As Double
    For Each cell In rng.Cells
        If IsNumeric(cell.Value2) And Len(cell.Value2) > 0 Then
            value = CDbl(cell.Value2)
            If Not haveHigh Or value > highest Then
                If haveHigh Then second = highest: haveSecond = True
                highest = value: haveHigh = True
            ElseIf value < highest And (Not haveSecond Or value > second) Then
                second = value: haveSecond = True
            End If
        End If
    Next cell
    If haveSecond Then SecondDistinctHighest = second Else SecondDistinctHighest = CVErr(xlErrNA)
End Function

How the solution works

• Define whether duplicate top values count before choosing LARGE(range,2) or a distinct-value algorithm.
• The two running values avoid sorting the worksheet and work in one pass.
• Return #N/A when no second distinct value exists so the missing case remains visible.

Run it step by step

1. Download and unzip the practice pack below. Open the matching CSV or copy it into a new macro-enabled workbook.
2. Press Alt + F11, choose File → Import File, and import this chapter’s .bas module.
3. Rename the sample worksheet to the name used in the code, or change the worksheet name in one clearly marked line.
4. Run the procedure with F5. Compare the result with the expected values and boundary cases in the CSV.
5. Undo is not available after a macro changes a workbook. Test on a copy, save only after checking totals, and keep the original source file.

Common mistakes

• Initializing highest to zero fails when every valid value is negative.
• Ignoring blanks but not text errors can produce type mismatches.
• Changing cell order just to calculate a rank disrupts the user’s data and filters.

Make it production-ready

Add Option Explicit, qualify every workbook and worksheet, validate the last row and input types, and use a single cleanup section to restore ScreenUpdating, EnableEvents and calculation settings after an error. Avoid hard-coded regulatory rates or colour-only business rules. Record the source, effective date and assumptions in a visible Notes sheet.

Practice challenge

Extend the function to return the employee name associated with the second distinct value and decide how ties should be reported.

Test normal data, blanks, text in numeric columns, duplicate values and the exact threshold boundaries. A useful macro should fail clearly rather than produce a plausible but wrong answer.