Attribute VB_Name = "V07_Security"
Option Explicit

' ---------- A tidy macro the way it is written in the article ----------
' Same result as Build_Summary_Bad, but: no Select, no fixed addresses, the screen state is always restored
Sub Build_Summary()
    Const SALES_SHEET As String = "Sales"
    Const SUMMARY_SHEET As String = "Summary"
    Const FIRST_MARKET_ROW As Long = 3

    Dim wsS As Worksheet, wsM As Worksheet
    Dim lastSales As Long, lastMarket As Long, r As Long
    Dim wasUpdating As Boolean

    wasUpdating = Application.ScreenUpdating
    On Error GoTo Cleanup
    Application.ScreenUpdating = False

    Set wsS = ThisWorkbook.Worksheets(SALES_SHEET)
    Set wsM = ThisWorkbook.Worksheets(SUMMARY_SHEET)
    lastSales = wsS.Cells(wsS.Rows.Count, 1).End(xlUp).Row
    lastMarket = wsM.Cells(wsM.Rows.Count, 1).End(xlUp).Row
    wsM.Range("B" & FIRST_MARKET_ROW & ":B" & wsM.Rows.Count).ClearContents

    For r = FIRST_MARKET_ROW To lastMarket
        wsM.Cells(r, 2).Value = Application.WorksheetFunction.SumIf( _
            wsS.Range("B2:B" & lastSales), wsM.Cells(r, 1).Value, wsS.Range("E2:E" & lastSales))
    Next r

Cleanup:
    Application.ScreenUpdating = wasUpdating
    If Err.Number <> 0 Then MsgBox "Build_Summary stopped: " & Err.Description, vbExclamation
End Sub

' ---------- The same job the way recorded or rushed code often looks ----------
Sub Build_Summary_Bad()
    Application.ScreenUpdating = False
    Sheets("Summary").Select
    Range("B3").Select
    ActiveCell.Value = Application.WorksheetFunction.SumIf(Sheets("Sales").Range("B2:B121"), "Market A", Sheets("Sales").Range("E2:E121"))
    Range("B4").Select
    ActiveCell.Value = Application.WorksheetFunction.SumIf(Sheets("Sales").Range("B2:B121"), "Market B", Sheets("Sales").Range("E2:E121"))
    Range("B5").Select
    ActiveCell.Value = Application.WorksheetFunction.SumIf(Sheets("Sales").Range("B2:B121"), "Market C", Sheets("Sales").Range("E2:E121"))
    Range("B6").Select
    ActiveCell.Value = Application.WorksheetFunction.SumIf(Sheets("Sales").Range("B2:B121"), "Market D", Sheets("Sales").Range("E2:E121"))
End Sub

' ---------- Reading code before running it: count the red-flag keywords in a block of text ----------
Sub Scan_Code_For_Red_Flags()
    Dim wsC As Worksheet, wsF As Worksheet
    Dim lastCode As Long, lastKey As Long, k As Long, i As Long, hits As Long
    Set wsC = ThisWorkbook.Worksheets("Code to Review")
    Set wsF = ThisWorkbook.Worksheets("Red Flags")
    lastCode = wsC.Cells(wsC.Rows.Count, 1).End(xlUp).Row
    lastKey = wsF.Cells(wsF.Rows.Count, 1).End(xlUp).Row
    wsF.Range("C3:D" & lastKey).ClearContents

    For k = 3 To lastKey
        hits = 0
        For i = 2 To lastCode
            If InStr(1, wsC.Cells(i, 1).Value, wsF.Cells(k, 1).Value, vbTextCompare) > 0 Then
                hits = hits + 1
                If hits = 1 Then wsF.Cells(k, 4).Value = i       ' first line where it appears
            End If
        Next i
        wsF.Cells(k, 3).Value = hits
    Next k
End Sub

Sub Clear_Security_Results()
    ThisWorkbook.Worksheets("Summary").Range("B3:B6").ClearContents
    With ThisWorkbook.Worksheets("Red Flags")
        .Range("C3:D" & .Rows.Count).ClearContents
    End With
End Sub