Day 12 — Practice questions and interview answers

Everything from the first eleven days, as problems. Try each one before you look.

How to use this page

Read the question, write your answer in the box, run it. The code already in each box is a solution, not the solution - clear the box first and only look afterwards. Getting there by a different route is a good sign.

1. Fill the totals column

Put a Total heading in H1 and the units times the price on every data row. Do not hard-code where the data ends.

Sub Q1()
    Dim i As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("H1").Value = "Total"

    For i = 2 To lastRow
        Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
    Next i

    Debug.Print "Filled to row", lastRow
End Sub

2. Count the orders for one salesperson

Sub Q2()
    Dim c As Range
    Dim n As Long

    For Each c In Range("D2:D17")
        If UCase(Trim(c.Value)) = "RAHUL" Then n = n + 1
    Next c

    Debug.Print "Rahul has", n, "orders"
End Sub

3. Find the largest single order

Print the value and the row it is on.

Sub Q3()
    Dim i As Long
    Dim best As Double
    Dim bestRow As Long
    Dim v As Double

    For i = 2 To 17
        v = Cells(i, 6).Value * Cells(i, 7).Value
        If v > best Then
            best = v
            bestRow = i
        End If
    Next i

    Debug.Print "Largest:", best, "on row", bestRow
    Debug.Print "That is", Cells(bestRow, 4).Value, "selling", Cells(bestRow, 5).Value
End Sub

4. Average order value, two decimal places

Sub Q4()
    Dim i As Long
    Dim total As Double
    Dim n As Long

    For i = 2 To 17
        total = total + Cells(i, 6).Value * Cells(i, 7).Value
        n = n + 1
    Next i

    Debug.Print "Orders:", n
    Debug.Print "Average:", WorksheetFunction.Round(total / n, 2)
End Sub

5. Band every order

Write Large, Medium or Small into column I - 100000 and over, 25000 and over, below that.

Sub Q5()
    Dim i As Long
    Dim v As Double

    Range("I1").Value = "Band"

    For i = 2 To 17
        v = Cells(i, 6).Value * Cells(i, 7).Value

        Select Case v
            Case Is >= 100000
                Cells(i, 9).Value = "Large"
            Case Is >= 25000
                Cells(i, 9).Value = "Medium"
            Case Else
                Cells(i, 9).Value = "Small"
        End Select
    Next i

    Debug.Print "Row 2:", Cells(2, 9).Value
    Debug.Print "Row 8:", Cells(8, 9).Value
End Sub

6. A code for every order

Region initial, first three letters of the product in capitals, a dash, then the order ID.

Sub Q6()
    Dim i As Long

    Range("I1").Value = "Code"

    For i = 2 To 17
        Cells(i, 9).Value = UCase(Left(Cells(i, 3).Value, 1)) & _
                            UCase(Left(Cells(i, 5).Value, 3)) & _
                            "-" & Cells(i, 1).Value
    Next i

    Debug.Print Cells(2, 9).Value, Cells(9, 9).Value
End Sub

7. Orders in the first quarter only

Sub Q7()
    Dim i As Long
    Dim n As Long
    Dim total As Double

    For i = 2 To 17
        If Month(Cells(i, 2).Value) <= 3 Then
            n = n + 1
            total = total + Cells(i, 6).Value * Cells(i, 7).Value
        End If
    Next i

    Debug.Print "Q1 orders:", n
    Debug.Print "Q1 value:", total
End Sub

8. A reusable function

Write RegionTotal(name) and use it four times.

Sub Q8()
    Debug.Print "North", RegionTotal("North")
    Debug.Print "South", RegionTotal("South")
    Debug.Print "East", RegionTotal("East")
    Debug.Print "West", RegionTotal("West")
End Sub

Function RegionTotal(ByVal region As String) As Double
    Dim i As Long
    Dim lastRow As Long
    Dim t As Double

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If UCase(Cells(i, 3).Value) = UCase(region) Then
            t = t + Cells(i, 6).Value * Cells(i, 7).Value
        End If
    Next i

    RegionTotal = t
End Function

9. The whole thing in memory

Q1 again, but reading and writing the block in one go instead of cell by cell.

Sub Q9()
    Dim src As Variant
    Dim out As Variant
    Dim i As Long

    src = Range("F2:G17").Value
    out = Range("H2:H17").Value

    For i = 1 To UBound(src, 1)
        out(i, 1) = src(i, 1) * src(i, 2)
    Next i

    Range("H1").Value = "Total"
    Range("H2:H17").Value = out

    Debug.Print "H2", Cells(2, 8).Value
    Debug.Print "H17", Cells(17, 8).Value
End Sub

10. A finished summary block

Region totals in J and K, formatted, with the header bold.

Sub Q10()
    Dim regions(1 To 4) As String
    Dim i As Long

    regions(1) = "North": regions(2) = "South"
    regions(3) = "East":  regions(4) = "West"

    For i = 2 To 17
        Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
    Next i

    Cells(1, 10).Value = "Region"
    Cells(1, 11).Value = "Value"

    For i = 1 To 4
        Cells(i + 1, 10).Value = regions(i)
        Cells(i + 1, 11).Value = WorksheetFunction.SumIf( _
            Range("C2:C17"), regions(i), Range("H2:H17"))
    Next i

    With Range("J1:K1")
        .Font.Bold = True
        .Interior.Color = RGB(232, 238, 248)
    End With

    Range("K2:K5").NumberFormat = "#,##0"
    Debug.Print "North total:", Cells(2, 11).Value
End Sub

11. Flag the duplicates

Mark any salesperson who appears more than once in column I.

Sub Q11()
    Dim i As Long
    Dim n As Long

    Range("I1").Value = "Repeat?"

    For i = 2 To 17
        n = WorksheetFunction.CountIf(Range("D2:D17"), Cells(i, 4).Value)

        If n > 1 Then
            Cells(i, 9).Value = "Yes (" & n & ")"
        Else
            Cells(i, 9).Value = "No"
        End If
    Next i

    Debug.Print Cells(2, 4).Value, Cells(2, 9).Value
End Sub

12. Everything at once

Totals, a band, a code, region colours, formatted numbers and a summary. This is a real report.

Sub Q12()
    Dim i As Long
    Dim lastRow As Long
    Dim v As Double

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Range("H1").Value = "Total"
    Range("I1").Value = "Band"

    For i = 2 To lastRow
        v = Cells(i, 6).Value * Cells(i, 7).Value
        Cells(i, 8).Value = v

        If v >= 100000 Then
            Cells(i, 9).Value = "Large"
            Cells(i, 9).Interior.Color = RGB(255, 243, 205)
        ElseIf v >= 25000 Then
            Cells(i, 9).Value = "Medium"
        Else
            Cells(i, 9).Value = "Small"
        End If
    Next i

    With Range("A1:I1")
        .Font.Bold = True
        .Interior.Color = RGB(232, 238, 248)
    End With

    Range("H2:H" & lastRow).NumberFormat = "#,##0"

    Debug.Print "Rows processed:", lastRow - 1
    Debug.Print "Grand total:", WorksheetFunction.Sum(Range("H2:H" & lastRow))
End Sub

That last one builds the range address as text - "H2:H" & lastRow becomes "H2:H17". It is how you point Range at something whose size you only learn while running.

The questions interviewers ask

What is the difference between a Sub and a Function?

A Sub performs an action and returns nothing. A Function calculates and hands back one value, which means you can use it inside another expression - and inside a worksheet formula. If somebody asks for a User Defined Function, they mean a Function.

How do you find the last used row?

Cells(Rows.Count, 1).End(xlUp).Row. Start at the bottom of column A, jump up to the last filled cell, take its row number. Say why you go from the bottom: starting at A1 and going down stops at the first blank inside your data.

What does Option Explicit do and why use it?

It forces every variable to be declared. It catches typos at compile time instead of letting them become silent empty variables that produce wrong answers.

ByRef or ByVal?

ByRef, the default, passes the original - the procedure can change your variable. ByVal passes a copy. Prefer ByVal so a procedure cannot alter something you did not expect it to.

How do you make a slow macro fast?

Four answers, in order of impact. Read the range into an array and work in memory. Turn off Application.ScreenUpdating and set Calculation to manual while it runs. Avoid Select and Activate entirely. Exit loops as soon as you have the answer.

Why avoid Select and Activate?

Recorded macros are full of Range("A1").Select then Selection.Value = 1. It is two operations where one will do, it is slow, and it breaks the moment a different sheet is active. Write Range("A1").Value = 1 instead.

What is the difference between .Value, .Text and .Formula?

.Value is the underlying value. .Text is the formatted string you can see on screen, and it is read-only. .Formula is the formula behind the cell as text.

How do you handle an error?

On Error Resume Next to skip past one you expect, then check Err.Number. On Error GoTo Handler to jump to a labelled block for anything serious. Say plainly that On Error Resume Next across a whole macro hides real bugs - use it for one line, then switch it back off with On Error GoTo 0.

Where to go next You now have the VBA language. What is left is the Excel object model - workbooks, worksheets, PivotTables, charts, events and UserForms - and that genuinely needs Excel open in front of you with somebody to ask. That is what the Excel VBA course in Noida covers.

Try these yourself

Do all twelve questions with the boxes cleared before you look at any solution.
Take Q12 and add a column J holding the month name of each order.
Rewrite Q3 so it also reports the smallest order.
Convert Q10 to work entirely from an array, with two visits to the sheet.
Write out your answer to "how do you make a slow macro fast" as if in an interview.

← PreviousDay 11 — Formatting, With blocks and WorksheetFunction Next →Advanced Excel Tutorial